Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    56

    Unanswered: SQLPlus: Unicode Input

    Hi all,

    I would like to do the following using Unicode text files:

    Executing SQL scripts such as INSERT, DELETE and UPDATE for a Unicode-enabled database. I am using SQLPLus to read .sql files into the database.

    Currently SQLPlus cannot process the .sql files and returns SP2-0042 error. I have tried saving .sql files in several formats such as UTF8, Unicode and ANSI but none of them was correctly interpreted by SQLPlus.

    I appreciate any clues.

    I have decided to use Unicode since I aim to store data in several scripts including IPA symbols, Traditional Chinese, Simplified Chinese, Japanese, Baltic languages and Romanian. If, however, there are less-complicated methods, suggestions are very welcome.

    Thank you in advance,

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    See this post for setting your NLS_LANG environment variable (in the example it's UTF8 but if you follow the link to NLS_LANG, you can then get to the appendices concerning languages, territories and universal character sets) :

    http://www.dbforums.com/showthread.php?t=1217491

    After that, concerning text files interpreting, you might need to set other parameters. On Linux for example, the LANGVAR environment variable seems to influence the way text files are interpreted, and should also be set to unicode. On windows, I don't think you need any other action than setting NLS_LANG.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Mar 2006
    Posts
    56
    RBARAER, I have already got NLS variables to conform to Unicode:
    NLS_CHARACTERSET = AL32UTF8
    NLS_NCHAR_CHARACTERSET = UTF8
    (from Table nls_database_parameters).

    I am wondering if there are other variables to change in order to convince SQLPlus into working with UTF8 text files...

    For reference, I am trying to set up an Oracle 9 (2nd release) database on a Windows XP SP2 computer.

    Regards

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    NLS_DATABASE_PARAMETERS reflect the database-level values, not the ones used by client applications. If you don't specify any client settings, then Oracle will default NLS_LANG to AMERICAN_AMERICA.US7ASCII, that's at least one reason why you are currently getting incorrect results, because Oracle tries to convert the content of your text file from US7ASCII to AL32UTF8 whereas your file is already in AL32UTF8.

    Follow the link from my first post and see what suits you for the language and territory parts, then add ".AL32UTF8" at the end (eg FRENCH_FRANCE.AL32UTF8), and see what happens.

    I re-read the documentation and if you are running under Windows, NLS_LANG is a registry entry, not an environment variable like under Unix or Linux.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb

    I have faced similar problem sometime back. Can you try opening that sql file in Unix & delete the unwanted leading character that are producing SP2-0042.

    One more observation to share is :: we would not be able to see Chinese, Japanese, Romanian etc languages character when we execute these with sql*plus editor(they all would be replaced by '?'). Although we can use sqlplus worksheet to execute sql commands, involving these languages.

    Abhi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •