12 Replies Latest reply on Mar 27, 2018 6:11 AM by Glen Conway

    SQLcl 4.2 - Set the language of the help text

    Gunther Pippèrr

      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

        • 1. Re: SQLcl 4.2 - Set the language of the help text
          Gunther Pippèrr

          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

          • 2. Re: SQLcl 4.2 - Set the language of the help text
            Kris Rice-Oracle

            You can toss this into your login.sql file

             

             

            script

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

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

            /

            • 3. Re: SQLcl 4.2 - Set the language of the help text
              Gunther Pippèrr

              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

              • 4. Re: SQLcl 4.2 - Set the language of the help text
                Gunther Pippèrr

                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

                • 5. Re: SQLcl 4.2 - Set the language of the help text
                  2881746

                  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/test@puma02: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/test@puma02: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

                  • 6. Re: SQLcl 4.2 - Set the language of the help text
                    Gunther Pippèrr

                    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:

                    ..

                    SYS@GPI-saturn>version

                    Oracle SQLDeveloper Command-Line (SQLcl) version: 17.4.0.354.2224

                     

                    SYS@GPI-saturn>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"

                    ....

                     

                    SYS@GPI-saturn>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:

                    ..

                    SYS@GPI-saturn>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:

                    SYS@GPI-saturn>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

                     

                    SYS@GPI-saturn>exit

                    Abgemeldet von Oracle Database 12c ...

                     

                    ```

                     

                    Best Regards

                    Gunther

                    • 7. Re: SQLcl 4.2 - Set the language of the help text
                      Jose Aróstegui

                      Hi all,

                       

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

                       

                      Thanks,
                      Jose.

                      • 8. Re: SQLcl 4.2 - Set the language of the help text
                        Jose Aróstegui

                        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.sql
                        User.lang=en
                        User.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.

                        • 9. Re: SQLcl 4.2 - Set the language of the help text
                          Jose Aróstegui

                          BTW:

                          Oracle SQL Developer, comandos (SQLcl) version: 17.4.0.354.2224

                          Windows 10

                          • 10. Re: SQLcl 4.2 - Set the language of the help text
                            Glen Conway

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

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

                            to

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

                            • 11. Re: SQLcl 4.2 - Set the language of the help text
                              Jose Aróstegui

                              Hi,

                               

                              As per indicated in Characterset and Locale Settings for SQLcl - its-people.de , the solution is to set the language before calling sqlcl:

                               

                              C:\WINDOWS\system32>SET JAVA_TOOL_OPTIONS=-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8
                              C:\WINDOWS\system32>sql cc/CC@APEX2_DEV
                              Picked up JAVA_TOOL_OPTIONS: -Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8
                              
                              SQLcl: Release 17.4.0 Production on Mon Mar 26 10:08:21 2018
                              
                              Copyright (c) 1982, 2018, Oracle.  All rights reserved.
                              
                              Connected to:
                              Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                              With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
                              
                              SQL> select xx from notable;
                              Error starting at line : 1 in command -
                              select xx from notable
                              Error at Command Line : 1 Column : 16
                              Error report -
                              SQL Error: ORA-00942: table or view does not exist
                              00942. 00000 -  "table or view does not exist"
                              *Cause:
                              *Action:
                              

                               

                              My mistake was that I was trying to set these env variables in file sql.bat... but my surprise when actually this file is useless has it has a syntax error. This comment is not well interpreted by cmd:

                               

                              <!-- OH dependencies we will want to take onboard -->
                              

                               

                              Regards,

                              Jose.

                              • 12. Re: SQLcl 4.2 - Set the language of the help text
                                Glen Conway

                                Glad you found a fix.  For the issues with the 17.4 sql.bat file see Errors in sql.bat 17.4.0.354.2224