Forum Stats

  • 3,769,554 Users
  • 2,252,976 Discussions
  • 7,875,083 Comments

Discussions

SQLcl 4.2 - Set the language of the help text

Gunther Pippèrr
Gunther Pippèrr Member Posts: 35 Blue Ribbon
edited Mar 27, 2018 2:11AM in SQLcl

Hello Team

how I can change the language of the SQLcl to English? Help Texts and so on

I try this:

set NLS_LANG=AMERICAN_AMERICA.UTF8

sql.bat /nolog

SQL> help tnsping

TNSPING

-------

Das TNSPING-Utility bestimmt, .. en k├Ânnen ...  testen Sie die Konnektivit├ñt:

Normally the NLS_LANG Settings should also define the language behavior of an Oracle product?

With the DOS Box the letters like äöü are not translated to the correct code page, I try to some of the pages, but no way to get the text without errors.


Thanks

Best Regards

Gunther

Craig Elliott

Best Answer

  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 9, 2016 7:52AM Accepted Answer

    Hello,

    I see the error, if System should be used it must be defined .-)

    script

      var System  = Java.type("java.lang.System");

      System.setProperty("user.lang","en");

      System.setProperty("user.country","us");

      System.out.println( System.getProperty("user.lang"));

      System.out.println(  System.getProperty("user.country"));

    /


    This version runs

    Best Regards

    Gunther

    Craig Elliott
«1

Answers

  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 3, 2016 11:57AM

    Hello,

    after writing the text and thinking with Java in mind I found a solution.

    I patch the call script sql.bat and add in the start of the java exe the options "-Duser.language=en -Duser.country=US"


    Line 49:   %JAVA_HOME%\bin\java.exe -Duser.language=en -Duser.country=US  


    Now it works:


    SQL> help tnsping

    TNSPING

    -------

    The TNSPING utility determines whether the listener for a service on an …

    But in the way I normally think with Oracle in my mind, the NLS_LANG variable should define the total language behavior of an application


    Is there another way to set the language without patching the call script?

    Best Regards

    Gunther

  • Kris Rice-Oracle
    Kris Rice-Oracle Posts: 1,393 Employee
    edited May 3, 2016 1:44PM

    You can toss this into your login.sql file

    script

      System.setProperty("user.lang","en");

      System.setProperty("user.country","us");

    /

  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 9, 2016 7:28AM

    Hello Kris,

    I try your script, but I get always this error:

    SQL> script

      2    System.setProperty("user.lang","en");

      3    System.setProperty("user.country","us");

      4  /

    javax.script.ScriptException: ReferenceError: "System" is not defined in <eval> at line number 1

            at jdk.nashorn.api.scripting.NashornScriptEngine.throwAsScriptException(NashornScriptEngine.java:455)

            at jdk.nashorn.api.scripting.NashornScriptEngine.evalImpl(NashornScriptEngine.java:439)

            at jdk.nashorn.api.scripting.NashornScriptEngine.evalImpl(NashornScriptEngine.java:401)

            at jdk.nashorn.api.scripting.NashornScriptEngine.evalImpl(NashornScriptEngine.java:397)

            at jdk.nashorn.api.scripting.NashornScriptEngine.eval(NashornScriptEngine.java:152)

            at javax.script.AbstractScriptEngine.eval(AbstractScriptEngine.java:264)

            at oracle.dbtools.scripting.ScriptingUtils.runScript(ScriptingUtils.java:35)

            at oracle.dbtools.raptor.scriptrunner.commands.ScriptCommand.runScript(ScriptCommand.java:158)

            at oracle.dbtools.raptor.scriptrunner.commands.ScriptCommand.handleEvent(ScriptCommand.java:67)

            at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:382)

            at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:204)

            at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:321)

            at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:212)

            at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:308)

            at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:318)

            at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:933)

            at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:372)

    Caused by: <eval>:1 ReferenceError: "System" is not defined

            at jdk.nashorn.internal.runtime.ECMAErrors.error(ECMAErrors.java:57)

            at jdk.nashorn.internal.runtime.ECMAErrors.referenceError(ECMAErrors.java:319)

            at jdk.nashorn.internal.runtime.ECMAErrors.referenceError(ECMAErrors.java:291)

            at jdk.nashorn.internal.objects.Global.__noSuchProperty__(Global.java:914)

            at jdk.nashorn.internal.scripts.Script$2$\^eval\_.:program(<eval>:1)

            at jdk.nashorn.internal.runtime.ScriptFunctionData.invoke(ScriptFunctionData.java:636)

            at jdk.nashorn.internal.runtime.ScriptFunction.invoke(ScriptFunction.java:229)

            at jdk.nashorn.internal.runtime.ScriptRuntime.apply(ScriptRuntime.java:387)

            at jdk.nashorn.api.scripting.NashornScriptEngine.evalImpl(NashornScriptEngine.java:437)

            ... 15 more

    I use this java Version to start SQLcl

    java version "1.8.0_45"

    Java(TM) SE Runtime Environment (build 1.8.0_45-b15)

    Any Ideas what I make wrong?

    Thanks for your help.

    Best Regards

    Gunther

  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 9, 2016 7:52AM Accepted Answer

    Hello,

    I see the error, if System should be used it must be defined .-)

    script

      var System  = Java.type("java.lang.System");

      System.setProperty("user.lang","en");

      System.setProperty("user.country","us");

      System.out.println( System.getProperty("user.lang"));

      System.out.println(  System.getProperty("user.country"));

    /


    This version runs

    Best Regards

    Gunther

    Craig Elliott
  • 2881746
    2881746 Member Posts: 6
    edited Jan 23, 2018 9:42AM

    Hi all,

    sorry, still does not work for me:

    C:\Local\DB\Tickets\180123>sql.bat                                                                                     

    C:\Local\DB\Tickets\180123>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8'       

    C:\Local\DB\Tickets\180123>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US -Dfile.encoding=WE8MSWIN1252'

    C:\Local\DB\Tickets\180123>set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US'                                 

                                                                                                                           

    C:\Local\DB\Tickets\180123>C:\Progs\sqlcl\bin\sql.exe test/[email protected]:1521/DB1STA.sixt.de                             

    Picked up JAVA_TOOL_OPTIONS: '-Duser.language=en -Duser.region=US'                                                     

                                                                                                                           

    SQLcl: Release 17.4.0 Production on Tue Jan 23 14:14:56 2018                                                           

                                                                                                                           

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

                                                                                                                           

      USER          = test                                                                                                 

      URL           = jdbc:oracle:oci8:@puma02:1521/DB1STA.sixt.de                                                         

      Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020                                      

      USER          = test                                                                                                 

      URL           = jdbc:oracle:thin:@puma02:1521/DB1STA.sixt.de                                                         

      Error Message = Locale not recognized                                                                                

    Username? (RETRYING) ('test/*********@puma02:1521/DB1STA.sixt.de'?)                                                    

      USER          = test                                                                                                 

      URL           = jdbc:oracle:oci8:@puma02:1521/DB1STA.sixt.de                                                         

      Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020                                      

      USER          = test                                                                                                 

      URL           = jdbc:oracle:thin:@puma02:1521/DB1STA.sixt.de                                                         

      Error Message = Locale not recognized                                                                                

    Username? (RETRYING) ('test/*********@puma02:1521/DB1STA.sixt.de'?)                                                    

    -- ==== But without language settings:

    C:\Local\DB\Tickets\180123>sql.bat                                                                                    

    C:\Local\DB\Tickets\180123>C:\Progs\sqlcl\bin\sql.exe test/[email protected]:1521/DB1STA.sixt.de                            

                                                                                                                          

    SQLcl: Release 17.4.0 Production auf Di Jan 23 14:28:02 2018                                                          

                                                                                                                          

    Copyright (c) 1982, 2018, Oracle. All rights reserved. Alle Rechte vorbehalten.                                       

                                                                                                                          

    Last Successful login time: Di Jan 23 2018 14:28:07 +01:00                                                            

                                                                                                                          

    Verbunden mit:                                                                                                        

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production                                          

    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,                                 

    Advanced Analytics and Real Application Testing options                                                               

                                                                                                                          

    login.sql wurde in CWD gefunden. Der DB-Zugriff ist f++r login.sql eingeschr+ñnkt.                                    

    Passen Sie SQLPATH an, um den Pfad f++r eine umfassende Funktionalit+ñt einzuschlie+ƒen.                              

    en                                                                                                                    

    us                                                                                                                    

                                                                                                                          

    TEST @ puma02:1521/DB1STA.sixt.de >                                                                                   

    -- ==== login.sql:

    set termout off

    define gname=idle

    column global_name new_value gname

    select lower(user)||'@' ||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name, instr(global_name,'.') dot from global_name);

    set sqlprompt '&gname>'

    set termout on

    set linesize 200

    set pagesize 100

    column VALUE_COL_PLUS_SHOW_PARAM format A65

    column NAME_COL_PLUS_SHOW_PARAM  format A40

    set sqlprompt "_user '@' _connect_identifier >"

    script

      var System  = Java.type("java.lang.System");

      System.setProperty("user.lang","en");

      System.setProperty("user.country","us");

      System.out.println( System.getProperty("user.lang"));

      System.out.println(  System.getProperty("user.country"));

    /

    Best regards,

    Sergey

  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited Mar 8, 2018 3:04PM

    Hello Sergey,

    Do you set NLS LANG also in the environment?

    The NLS_LANG Parameter changed the DB error message to German but not the SQLcl messages.

    But also you are right, there is a mistake with the java lang parameter, the correct syntax is user.language not user.lang !

    My mistake too much Oracle in my head .-)

    Fixed version:

    ```

    script

      var System  = Java.type("java.lang.System");

      System.setProperty("user.language","de");

      System.setProperty("user.country","DE");

      System.out.println( System.getProperty("user.language"));

      System.out.println(  System.getProperty("user.country"));

    /

    ```

    See this test case (SQLcl 17.4.0.354.2224) to switch the error message from English to German

    I use the Windows Powershell  on  a English Windows 10 and sql.exe to start SQLcl, the sql.bat file in this version of SQLcl does not work at all:

    ```

    # My actual setting

    echo $ENV:NLS_LANG

    AMERICAN_AMERICA.UTF8

    .\sql.exe / as sysdba

    ..

    Connected to:

    ..

    [email protected]>version

    Oracle SQLDeveloper Command-Line (SQLcl) version: 17.4.0.354.2224

    [email protected]>select * from not_exit_table;

    ...

    Error at Command Line : 1 Column : 15

    Error report -

    SQL Error: ORA-00942: table or view does not exist

    00942. 00000 -  "table or view does not exist"

    ....

    [email protected]>exit

    Disconnected from ....

    # set all to German

    set-item -path ENV:JAVA_TOOL_OPTIONS -value "-Duser.language=de -Duser.region=DE -Duser.country=DE" 

    set-item -path ENV:NLS_LANG -value "german_germany.utf8"

    .\sql.exe / as sysdba

    Picked up JAVA_TOOL_OPTIONS: -Duser.language=de -Duser.region=DEPicked up JAVA_TOOL_OPTIONS: -Duser.language=de -Duser.region=DE -Duser.country=DE  -Duser.variant=Traditional_WIN

    ..

    Verbunden mit:

    ..

    [email protected]>select * from not_exit_table;

    Fehler bei Befehlszeile: 1 Spalte: 15

    Fehlerbericht -

    SQL-Fehler: ORA-00942: Tabelle oder View nicht vorhanden

    00942. 00000 -  "table or view does not exist"

    -- check the settings:

    [email protected]>script

    var System  = Java.type("java.lang.System");

    var Locale  = Java.type("java.util.Locale")

    System.out.println( System.getProperty("user.language"));

    System.out.println(  System.getProperty("user.country"));

    System.out.println(Locale.getDefault());

    /

    de

    DE

    de_DE

    [email protected]>exit

    Abgemeldet von Oracle Database 12c ...

    ```

    Best Regards

    Gunther

  • Jose Aróstegui
    Jose Aróstegui Member Posts: 1,314 Silver Badge
    edited Mar 14, 2018 9:20AM

    Hi all,

    Today is my first day with sqlcl.... where should I put the login.sql file?

    Thanks,
    Jose.

  • Jose Aróstegui
    Jose Aróstegui Member Posts: 1,314 Silver Badge
    edited Mar 23, 2018 7:33AM

    Hi,

    I've find out where to put the login.sql file. For those like me, it's just to define an env variable called SQLPATH pointing to the directory where login.sql file is located.

    I have added to login.sql this code:

    script   var System  = Java.type("java.lang.System");  System.out.println("Executing login.sql");  System.setProperty("user.lang","en");  System.setProperty("user.country","us");  System.out.println("User.lang="+System.getProperty("user.lang"));  System.out.println("User.country="+  System.getProperty("user.country"));/

    And when I open a session I get this output:

    Executing login.sqlUser.lang=enUser.country=us

    ...but still get the console messages in spanish instead of english and with strange characters (línea=line)

    p1.png

    Any ideas?

    Thanks,

    Jose.

  • Jose Aróstegui
    Jose Aróstegui Member Posts: 1,314 Silver Badge
    edited Mar 23, 2018 7:34AM

    BTW:

    Oracle SQL Developer, comandos (SQLcl) version: 17.4.0.354.2224

    Windows 10

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Mar 23, 2018 10:35AM

    From Gunther's reply (6) for German, note the correction from

    System.setProperty("user.lang","de");

    to

    System.setProperty("user.language","de");