Reputation: 899

insert / retrieve Arabic data on Oracle 12c

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:

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

https://chartio.com/resources/tutorials/how-to-fix-ora-12505-tns-listener-does-not-currently-know-of-sid-given-in-connect-descriptor/

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 Insert and Select data 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>

Upvotes: 1

Answers (3)

Ahmad Saleh

Reputation: 899

I just want to merge and improve the answers with additional notes that provided by @Plirkee comment here and @Wernfried Domscheit answer here

1. solving the issue on CMD:

Mr @Domscheit answer here suggested to to do the following:

c:\> chcp 65001
c:\> set NLS_LANG=.AL32UTF8
c:\> sqlplus ...

where if you don't want to type the first 2 commands every time you can add them via registry:

Setting Code Page to 65001:

Following the instruction in this link:

Start -> Run -> regedit
Go to [HKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor]
Add new String Value named: Autorun
Change the value to chcp 65001
Close
- Setting NLS_LANG to .AL32UTF8
  1. Start -> Run -> regedit
  2. Go to [HKEY_LOCAL_MACHINE\Software\Oracle\{OracleHome#}]
  3. Add new String Value named: NLS_LANG
  4. Change the value to .AL32UTF8
  5. Close

Now when you open CMD you can you'll find Active code page: 65001 and you can work on sqlplus directly

Microsoft Windows [Version 10.0.15063]
(c) 2017 Microsoft Corporation. All rights reserved.
Active code page: 65001

C:\Users\ahmed>sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 23 16:10:23 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: system
Enter password:
Last Successful login time: Sat Dec 23 2017 15:58:21 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from employee;

FNAME      LNAME      PHONE            AMOUNT
---------- ---------- ------------ ----------
أ ح م د       Saleh      966000000001      30000
Saleh      Salem      966000000001      40000
ح س ن        Damis      966000000001      25000
Ahmad      Saleh      966000000001      25000
Abbas      Motwali    966000000001      20000

SQL>

2. Displaying Arabic in Java: Mr @Plirkee comment here suggested to do the following:

// 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);  // old line 
// make sure that the stuff you are writing to the file is utf-8
    PrintWriter printWriter = new PrintWriter(new OutputStreamWriter(fileOutputStream, StandardCharsets.UTF_8));

this will fix the ????? characters to unknown content like Ø£ØÙ…Ø¯
To fix this, Additional to Mr @Plirkee answer make sure to change your IDE Encoding to UTF-8 (Assuming you are using Eclips):

Window -> Preferences -> General -> Workspace -> Text file encoding

change to UTF-8

Upvotes: 2

Wernfried Domscheit

Reputation: 59456

First of all ALTER DATABASE CHARACTER SET ... is de-supported since Oracle 10g. You should not use it, you may destroy your database. Follow the official guideline from Oracle: Character Set Migration or use the DMU - Database Migration Assistant for Unicode

Format of NLS_LANG is "language_territory.charset". AR8MSWIN1256 does not work, use .AR8MSWIN1256 with a dot (each component is optional, so you can skip language and territory)

SQLplus inherits the encoding from command-line. If you use chcp 28596 which means ISO 8859-6 (see Code Page Identifiers) then character set in your NLS_LANG value must be .AR8ISO8859P6 (see Character Sets)

If you like to use NLS_LANG=.AR8MSWIN1256 then you must run chcp 1256 before you start sqlplus.

Or if you prefer to use UTF-8 run it like this:

c:\> chcp 65001
c:\> set NLS_LANG=.AL32UTF8
c:\> sqlplus ...

Note, Environment variable (i.e. set NLS_LANG=.AL32UTF8) takes precedence over Registry settings.

Ensure that the font you use in cmd.exe is supporting Arabic characters. You can test with this page: https://www.fileformat.info/info/unicode/font/fontlist.htm

Check also this answer: OdbcConnection returning Chinese Characters as "?"

Regarding Java I cannot help you, I am not familiar with Java/JDBC. Bear in mind, Java does not use NLS_LANG settings, see Database JDBC Developer's Guide - Globalization Support

Upvotes: 4

PKey

Reputation: 3841

As I can see your database can accept Arabic (as you have shown in sql developer example) so the problem is with java and sqlplus (command line).

Regarding the command line, I think, the problem lies in the code page used.

Try changing the code page with chcp dos commnad. I don't know any Arabic but take a look here There are some choices. Try chcp 720 or chcp 1256 or 709 or 708 (in other words any Arabic you find on the page) and then run the sqlplus command. Also there is a possibility that there is no way of displaying Arabic in dos (as @Vahadin's link suggests).

Regarding java I don't think you gave up enough information - source code used, encoding (do you use utf-8?) etc..

Hope this helps - and doesn't make you more confused..

Upvotes: 1

insert / retrieve Arabic data on Oracle 12c

Answers (3)

Related Questions