I am facing an issue getting Arabic content from Oracle Database 12c, I've followed most answered questions but nothing is working with me.
my Arabic characters returns like this "????"
even on java when I get the data it doesn't returns Arabic values
{
"employees":[
{
"fname":"????",
"lname":"Saleh",
"amount":30000,
"phone":"96600000097"
},
{
"fname":"Saleh",
"lname":"Salem",
"amount":40000,
"phone":"96600000097"
},
{
"fname":"Hasan",
"lname":"Damis",
"amount":25000,
"phone":"96600000097"
},
{
"fname":"Ahmad",
"lname":"?????",
"amount":25000,
"phone":"96600000097"
},
{
"fname":"Abbas",
"lname":"Motwali",
"amount":20000,
"phone":"96600000097"
}
]
}
While when I use sql developer I can SELECT , INSERT Arabic values
I am using oracle on my laptop for learning:
- Windows 10 laptop (Logged in with windows 10 admin user )
- Oracle 12c (Logged in with system user)
- java version "1.8.0_152"
I've found a lot of questions here and on the internet like:
- Can't insert arabic characters into oracle database
- how to insert Arabic text to oracle database?
- https://dba.stackexchange.com/questions/23692/arabic-characters-not-displaying-in-oracle-database
- https://community.oracle.com/thread/2529426?start=0&tstart=0
but unfortunately I cannot use this command on sqlplus
SHUTDOWN IMMEDIATE
it returns this error message
ORA-01031: insufficient privileges
I've found this answer on https://itkbs.wordpress.com/2016/02/05/solving-ora-01031-insufficient-privileges-while-connecting-as-sqlplus-as-sysdba/
but unfortunately I don't have "local users and groups" section I think it's only on windows NT or server
Also one off the answers on the internet suggested to to add "NLS_LANG" on system registry and assign the value AR8MSWIN1256 for Arabic Unicode (actually there was different values)
unfortunately i faced another problem
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
and on sql plus I get this error:
I followed this answer but it doesn't connect to my database
but it keep giving me the same issue
so I renamed the registry key that I created "NLS_LANG" to different name where this is the only thing that I really changed, to get everything back and the database is running again
So my question is:
- How to
InsertandSelectdata in Arabic Value? (I don't know where is the problem Oracle or Java or SQLPLUS !)
Update 1: My Java Code:
** OracleDBConnect2 Class:**
package com.oracle.testconnect;
import java.io.BufferedReader; import java.io.File; import java.io.FileOutputStream; import java.io.InputStreamReader; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.Map;
import org.json.JSONArray; import org.json.JSONObject;
public class OracleDBConnect2 { static PrintWriter logPrintWriter;
public static void main(String args[]) { System.setProperty("file.encoding", "UTF-8"); JSONObject jsonObj = new JSONObject(); JSONArray jsonArray = new JSONArray(); // getting current datetime try { // create the connection object Connection dbConnection = DriverManager .getConnection( "jdbc:oracle:thin:@url:port:DBName", "username", "password"); // create the statement object Statement sqlStatement = dbConnection.createStatement(); // execute query String query = "select * from employee"; ResultSet results = sqlStatement.executeQuery(query); // Convert result to json array jsonArray = Convertor.convertToJSON(results); System.out.println("jsonArray " + jsonArray.toString()); // Add JSON Array results to employee JSON Object jsonObj.put("employees", jsonArray); // Getting the output stream of the file for writing File file = new File(Constants.JSON_FILE_PATH); FileOutputStream fileOutputStream = new FileOutputStream(file); PrintWriter printWriter = new PrintWriter(fileOutputStream); if (!file.exists()) { file.mkdirs(); file.createNewFile(); } // Writing the user input to the file printWriter.write(jsonObj.toString()); printWriter.flush(); fileOutputStream.close(); printWriter.close(); // close the connection object dbConnection.close(); } catch (Exception e) { System.out.println(e); } }}
** Convertor Class:**
public class Convertor {
public static JSONArray convertToJSON(ResultSet resultSet) throws Exception { JSONArray jsonArray = new JSONArray(); while (resultSet.next()) { int total_rows = resultSet.getMetaData().getColumnCount(); JSONObject obj = new JSONObject(); for (int i = 0; i < total_rows; i++) { obj.put(resultSet.getMetaData().getColumnLabel(i + 1).toLowerCase(), resultSet.getObject(i + 1)); } jsonArray.put(obj); System.out.println(resultSet.getString(1)); } return jsonArray; }}
Update 2: changing the code page on cmd
As suggested by @plirkee and @Vahadin I've changed the code page on CMD to Arabic codes also I've created a blank file with name "عربي", when I tested ls command it displays the name like this ''$'\330\271\330\261\330\250\331\212'
while when I try select statement on sqlplus I get the arabic value as ????
I understand that there is a possibility that cmd doesn't support Arabic but the results from oracle (if there was no issue with the configurations) should not be displayed as ???? instead should display it as the file name that i created in cmd
check this is a screenshot :
C:\Users\ahmed\Documents>chcp 20420
Active code page: 20420
C:\Users\ahmed\Documents>ls
'Custom Office Templates' 'My Videos'
FeedbackHub desktop.ini
'HP ePrint' hp.applications.package.appdata
'My Music' hp.system.package.metadata
'My Pictures' ''$'\330\271\330\261\330\250\331\212'
C:\Users\ahmed\Documents>chcp 28596
Active code page: 28596
C:\Users\ahmed\Documents>ls
'Custom Office Templates' 'My Videos'
FeedbackHub desktop.ini
'HP ePrint' hp.applications.package.appdata
'My Music' hp.system.package.metadata
'My Pictures' ''$'\330\271\330\261\330\250\331\212'
C:\Users\ahmed\Documents>sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 23 13:33:54 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Last Successful login time: Sat Dec 23 2017 13:27:42 +03:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from employee
2 ;
FNAME LNAME PHONE AMOUNT
---------- ---------- ------------ ----------
???? Saleh 966000000000 30000
Saleh Salem 966000000000 40000
Hasan Damis 966000000000 25000
Ahmad ????? 966000000000 25000
Abbas Motwali 966000000000 20000
SQL>
