Can't insert arabic characters into oracle database

Question

I got problems while reading arabic characters from oracle in java using JDBC driver, the main problem was i couldn't find the proper character encoding to get the correct data , but i solved the problem manually using this method:

public static String cleanORCLString(String s) throws UnsupportedEncodingException {

    byte[] bytes = s.getBytes("UTF16");
    String x = new String(bytes, "Cp1256");

    String finalS = x.substring(3);
    StringBuilder sb = new StringBuilder(finalS);

    for(int k = sb.length() - 1 ; k > 0 ; k--) {

        if(!isEven(k)) {

            sb.deleteCharAt(k);

        }

    }

    return sb.toString();
}

this method give me the correct characters like its shown in database, but when I try to update/insert arabic data, it save wrong characters. For example: my text saved in database as "?????????" instead of "مرحبا".

This is the way I connect to oracle database.

URL = ORCLConnProperties.ORCL_THIN_PREFIX + orclProp.getIpAddress()
            + orclProp.getPortNumber() + ORCLConnProperties.ORCL_THIN_SUFIX;

// URL = jdbc:oracle:thin:@10.0.0.12:1521:ORCL


    System.out.println("URL: " + URL);

    Properties connectionProps = new Properties();
    connectionProps.put("characterEncoding", "Cp1256");
    connectionProps.put("useUnicode", "true");
    connectionProps.put("user", orclProp.getUserName());
    connectionProps.put("password", orclProp.getPassword());

    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");

    } catch (ClassNotFoundException ex) {
        System.out.println("Error: unable to load driver class!");
        System.exit(1);
    }


    myDriver = new oracle.jdbc.driver.OracleDriver();

    DriverManager.registerDriver(myDriver);

    conn = DriverManager.getConnection(URL, connectionProps);

please help me in solving this issue ? Thanks.

New Note:

Database itself don't use UTF16 character set, but

"the JDBC OCI driver transfers the data from the server to the client in the character set of the database. Depending on the value of the NLS_LANG environment variable, the driver handles character set conversions: OCI converts the data from the database character set to UTF-8. The JDBC OCI driver then passes the UTF-8 data to the JDBC Class Library, where the UTF-8 data is converted to UTF-16."

this note is mentioned here: http://docs.oracle.com/cd/B10501_01/java.920/a96654/advanc.htm

Do you have the orai18n.jar on your class path? http://docs.oracle.com/cd/E11882_01/java.112/e16548/global.htm#JJDBC28643 — Brett Okken, May 28 '14 at 01:08
no i don't have it, am going to try that :) and thanks for the information and reference link :) — UsifShahin, May 29 '14 at 07:24

score 0 · Answer 1 · answered May 12 '14 at 14:21

0

Check your oracle version. if it old it can't support UTF16.

here is an article -- hope it will be useful.

http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm

answered May 12 '14 at 14:21

Alex G

41
3

Oracle version is 11 G Enterprise Edition 11.1.0.7.0 , i have already created tables from another program with this data, and as i mentioned that i can read the data by converting it from UTF16 to Cp1256, but my issue in inserting and updating to the database. – UsifShahin May 12 '14 at 14:26
I added a new note to make things more clear about my issue, please check it if you can @user3082691 – UsifShahin May 12 '14 at 14:44

Michael Shenouda · Accepted Answer · 2017-03-01T01:41:52.163

First you may check the NLS_CHARACTERSET parameter of your database using the SQL*PLUS command :-

select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';

the result should be

PARAMETER

VALUE

NLS_CHARACTERSET

AR8MSWIN1256

if it's not, you have to change the value of this parameter using :-

hit WINDOWS KEY + r on your keyboard
write :- SQLPLUS sys as sysdba
press Enter then enter the password or just hit another Enter
issue the following commands :
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256;
ALTER DATABASE CHARACTER SET AR8MSWIN1256;
SHUTDOWN IMMEDIATE
STARTUP
change the value of the NLS_LANG registry string into AMERICAN_AMERICA.AR8MSWIN1256

if your operating system is a flavor of UNIX use

AR8ISO8859P6 instead of AR8MSWIN1256 as the value of NLS_CHARACTERSET

DON'T use National datatypes (i.e NVARCHAR, NTEXT, or NCLOB ) in your database unless you are going to use other languages than (Arabic and English) inside your database
AR8MSWIN1256 character set is sufficient for mixing arabic and english inside the same field (as far as I know).

TAKEN FROM

Thanks alot it an old question that i asked, but this was the solution — UsifShahin, Mar 01 '17 at 01:50
`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](https://docs.oracle.com/database/121/NLSPG/ch11charsetmig.htm#NLSPG011) or use the [DMU - Database Migration Assistant for Unicode](https://docs.oracle.com/cd/E89575_01/DUMAG/toc.htm) — Wernfried Domscheit, Dec 23 '17 at 10:52

Can't insert arabic characters into oracle database

2 Answers2

Linked