SQLcl 4.2 - Set the language of the help text

Gunther Pippèrr · May 2016

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

Gunther Pippèrr · May 2016

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

Gunther Pippèrr · May 2016

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 · May 2016

You can toss this into your login.sql file

script

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

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

/

Gunther Pippèrr · May 2016

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 · May 2016

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

2881746 · January 2018

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

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

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 · March 2018

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 · March 2018

Hi all,

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

Thanks,
Jose.

Jose Aróstegui · March 2018

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)

Any ideas?

Thanks,

Jose.

Jose Aróstegui · March 2018

BTW:

Oracle SQL Developer, comandos (SQLcl) version: 17.4.0.354.2224

Windows 10

Glen Conway · March 2018

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 · March 2018

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-8C:\WINDOWS\system32>sql cc/[email protected]_DEVPicked up JAVA_TOOL_OPTIONS: -Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8SQLcl: Release 17.4.0 Production on Mon Mar 26 10:08:21 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> select xx from notable;Error starting at line : 1 in command -select xx from notableError at Command Line : 1 Column : 16Error report -SQL Error: ORA-00942: table or view does not exist00942. 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 · March 2018

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

Forum Stats

Howdy, Stranger!

Quick Links

Categories

SQLcl 4.2 - Set the language of the help text

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

SQLcl 4.2 - Set the language of the help text

Best Answer

Answers