Skip to Main Content
Forums

SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQLcl 4.2 - Set the language of the help text

Gunther PippèrrMay 3 2016 — edited Mar 27 2018

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
This post has been answered by Gunther Pippèrr on May 9 2016
Jump to Answer

Comments

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

Kris Rice-Oracle

You can toss this into your login.sql file

script

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

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

/

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

Gunther Pippèrr
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

Marked as Answer by Gunther Pippèrr · Sep 27 2020
1 person found this helpful
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

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

Jose Aróstegui

Hi all,

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

Thanks,
Jose.

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.

Jose Aróstegui

BTW:

Oracle SQL Developer, comandos (SQLcl) version: 17.4.0.354.2224

Windows 10

Glen Conway

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

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

to

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

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.

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

1 - 12

Post Details

Added on May 3 2016
12 comments
2,635 views