utPLSQL-cli run causes java.sql.SQLException: Locale not recognized

[nikita-mospan]

Good day!

I downloaded latest distribution of utPLSQL-cli and tried to execute it on my local environment. I copied ojdbc8.jar as was written in README and even orai18n.jar from Oracle website to utPLSQL-cli/bin folder. However I am receiving the following error:

$ ./utplsql run tech_user/1@ora12c
java.sql.SQLException: Locale not recognized
at oracle.jdbc.driver.T4CTTIoauthenticate.setSessionFields(T4CTTIoauthenticate.java:1493)
at oracle.jdbc.driver.T4CTTIoauthenticate.(T4CTTIoauthenticate.java:291)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:599)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:375)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:204)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:445)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:516)
at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:116)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:97)
at org.utplsql.cli.ConnectionInfo.getConnection(ConnectionInfo.java:34)
at org.utplsql.cli.RunCommand.run(RunCommand.java:149)
at org.utplsql.cli.Cli.main(Cli.java:33)

My NLS_LANG has the following value:

$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

$ java -version
java version "1.8.0_77"
Java(TM) SE Runtime Environment (build 1.8.0_77-b03)
Java HotSpot(TM) 64-Bit Server VM (build 25.77-b03, mixed mode)

My database parameters:

select * from v$version;

--
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

select * from nls_database_parameters ;

PARAMETER	VALUE
NLS_RDBMS_VERSION	12.1.0.2.0
NLS_NCHAR_CONV_EXCP	FALSE
NLS_LENGTH_SEMANTICS	BYTE
NLS_COMP	BINARY
NLS_DUAL_CURRENCY	$
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT	HH.MI.SSXFF AM
NLS_SORT	BINARY
NLS_DATE_LANGUAGE	AMERICAN
NLS_DATE_FORMAT	DD-MON-RR
NLS_CALENDAR	GREGORIAN
NLS_NUMERIC_CHARACTERS	.,
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_CHARACTERSET	AL32UTF8
NLS_ISO_CURRENCY	AMERICA
NLS_CURRENCY	$
NLS_TERRITORY	AMERICA
NLS_LANGUAGE	AMERICAN

I would greatly appreciate any help or suggestions.

Thank you in advance.

[pesse]

Hi @nikita-mospan ,
can you try to run cli with no NLS_LANG environment variable set?
I guess we'll have to revert recognition for NLS_LANG and require LC_ALL/LANG instead, which follows a different notation (e.g. "en_US.utf-8") - or we have to invest serious effort into translating Oracle-specific NLS_LANG-format into Java Locale notation.

[nikita-mospan]

Hi @pesse ,
Thank you, when I unset NLS_LANG it works.
And I would agree with you that it is better not to use NLS_LANG in connecting to Oracle via JDBC. NLS_ settings are used for applications that connect to Oracle via OCI.

I am giving a try maven integration with utPLSQL using approach described here (maven-exec-plugin + utPLSQL-cli) https://gist.github.com/nochmu/1f640b01a685f3f83a349de2db8a8882 and now will think how to unset NLS_LANG before executing utPLSQL-cli and setting it back afterwards.

[pesse]

Yeah, it's not a good solution right now.
I'm in contact with the Oracle sqlcl guys and hope to get some input how they handle the topic.

I hope to be able to release cli 3.1.1 soon, though. It will definitely include ignoring of NLS_LANG

[jgebal]

How about trying out our maven plugin.
It's still develop version but it's stable.
Will save you a lot on maven configuration

[nikita-mospan]

Hi, @jgebal

Frankly speaking, that was my first choice, and I spent quite a while on it, but didn't manage to make it work, so I gave up. =)

If you have some time I would appreciate if you take a look at the project: https://github.com/nikita-mospan/liquibase-deploy-demo where I was playing around with liquibase and your plugin.

Liquibase is working fine, however when I run

mvn test

[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building liquibase-deploy-demo 1.0
[INFO] ------------------------------------------------------------------------
Downloading: https://maven.oracle.com/org/utplsql/utplsql-maven-plugin/3.1.0-SNAPSHOT/maven-metadata.xml
[INFO]
[INFO] --- liquibase-maven-plugin:3.2.0:update (liquibase-update) @ liquibase-deploy-demo ---
[INFO] ------------------------------------------------------------------------
[INFO] there are no resolved artifacts for the Maven project.
[INFO] Executing on Database: jdbc:oracle:thin:@ora12c:1521/orcl
INFO 12.09.18 14:25: liquibase: Successfully acquired change log lock
INFO 12.09.18 14:25: liquibase: Reading from DATABASECHANGELOG
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::create-tech_log_table.sql::nikita.mospan: SQL in file src/main/database/latest/synonym/create-tech_log_table.sql executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::create-tech_log_table.sql::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::create-tech_log_table.sql::nikita.mospan ran successfully in 25ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::create-tech_log_table_seq.sql::nikita.mospan: SQL in file src/main/database/latest/synonym/create-tech_log_table_seq.sql executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::create-tech_log_table_seq.sql::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::create-tech_log_table_seq.sql::nikita.mospan ran successfully in 5ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::create-tech_log_instances.sql::nikita.mospan: SQL in file src/main/database/latest/synonym/create-tech_log_instances.sql executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::create-tech_log_instances.sql::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::create-tech_log_instances.sql::nikita.mospan ran successfully in 3ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::grant-log_table_to_public.sql::nikita.mospan: SQL in file src/main/database/latest/grant/grant-log_table_to_public.sql executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::grant-log_table_to_public.sql::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::grant-log_table_to_public.sql::nikita.mospan ran successfully in 29ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::grant-log_instances_to_public.sql::nikita.mospan: SQL in file src/main/database/latest/grant/grant-log_instances_to_public.sql executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::grant-log_instances_to_public.sql::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::grant-log_instances_to_public.sql::nikita.mospan ran successfully in 6ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::grant-seq_log_table_to_public.sql::nikita.mospan: SQL in file src/main/database/latest/grant/grant-seq_log_table_to_public.sql executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::grant-seq_log_table_to_public.sql::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::grant-seq_log_table_to_public.sql::nikita.mospan ran successfully in 5ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::pk_util_log.pks::nikita.mospan: SQL in file src/main/database/latest/package/pk_util_log.pks executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::pk_util_log.pks::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::pk_util_log.pks::nikita.mospan ran successfully in 13ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::pk_util_log.pkb::nikita.mospan: SQL in file src/main/database/latest/package/pk_util_log.pkb executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::pk_util_log.pkb::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::pk_util_log.pkb::nikita.mospan ran successfully in 13ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::create-pk_util_log.sql::nikita.mospan: SQL in file src/main/database/latest/synonym/create-pk_util_log.sql executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::create-pk_util_log.sql::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::create-pk_util_log.sql::nikita.mospan ran successfully in 4ms
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::grant-pk_util_log_to_public.sql::nikita.mospan: SQL in file src/main/database/latest/grant/grant-pk_util_log_to_public.sql executed
INFO 12.09.18 14:25: liquibase: src/main/database/changelog/master.xml: src/main/database/changelog/latest/changelog.xml::grant-pk_util_log_to_public.sql::nikita.mospan: ChangeSet src/main/database/changelog/latest/changelog.xml::grant-pk_util_log_to_public.sql::nikita.mospan ran successfully in 5ms
INFO 12.09.18 14:25: liquibase: Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO]
[INFO] --- utplsql-maven-plugin:3.1.0-SNAPSHOT:test (default) @ liquibase-deploy-demo ---
[ERROR]
java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@ora12c:1521:orcl
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.utplsql.maven.plugin.UtPLSQLMojo.execute(UtPLSQLMojo.java:143)
at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:134)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:207)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116)
at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80)
at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51)
at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:128)
at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:307)
at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:193)
at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:106)
at org.apache.maven.cli.MavenCli.execute(MavenCli.java:863)
at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:288)
at org.apache.maven.cli.MavenCli.main(MavenCli.java:199)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289)
at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229)
at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 23.646 s
[INFO] Finished at: 2018-09-12T14:25:20+03:00
[INFO] Final Memory: 18M/196M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.utplsql:utplsql-maven-plugin:3.1.0-SNAPSHOT:test (default) on project liquibase-deploy-demo: No suitable driver found for jdbc:oracle:thin:@ora12c:1521:orcl -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

I even explicitly added dependency com.oracle.ojdbc7 to utplsql-maven-plugin but with no luck.

May be I am missing some configuration options.

I will be glad to discuss this on your project's page, but probably it is not relevant for utPLSQL-cli.

Thank you.

[jgebal]

You're misssing maven config to get jdbc.
Here is an example of adding Oracle repo to get jdbc from them (needs authentication)
https://github.com/utPLSQL/utPLSQL-demo-project/blob/feature/utplsql-maven-plugin/.travis/settings.xml

[nikita-mospan]

No, I have it. Below is extract from my $M2_HOME/conf/settings.xml

    <id>maven.oracle.com</id>
    <username>...</username>
    <password>...</password>
    <configuration>
        <basicAuthScope>
            <host>ANY</host>
            <port>ANY</port>
            <realm>OAM 11g</realm>
        </basicAuthScope>
        <httpConfiguration>
            <all>
                <params>
                    <property>
                        <name>http.protocol.allow-circular-redirects</name>
                        <value>%b,true</value>
                    </property>
                </params>
            </all>
        </httpConfiguration>
    </configuration>
</server>

Without those settings I wouldn't be able to install utplsql-maven-plugin into my local Maven repository but I installed it successfully.

[pesse]

Okay, Jeff Smith answered me and they do it as follows in sqlcl:

• get env LC_ALL/LANG to set java Locale (notation: en_US.utf-8)
• get env NLS_LANG and if set, do an ALTER SESSION ... once connected

Sounds like a useful approach, altering of the session might be a bit of work though.

[nikita-mospan]

Hi, @pesse

Thank you, but I didn't quite get that. Do they plan to implement this behavior? Because at the present moment it seems not to work.

$ echo $LC_ALL
en_US.utf-8

$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

$ ./utplsql run tech_user/1@ora12c
java.sql.SQLException: Locale not recognized
at oracle.jdbc.driver.T4CTTIoauthenticate.setSessionFields(T4CTTIoauthenticate.java:1493)
at oracle.jdbc.driver.T4CTTIoauthenticate.(T4CTTIoauthenticate.java:291)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:599)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:375)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:204)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:445)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:516)
at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:116)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:97)
at org.utplsql.cli.ConnectionInfo.getConnection(ConnectionInfo.java:34)
at org.utplsql.cli.RunCommand.run(RunCommand.java:149)
at org.utplsql.cli.Cli.main(Cli.java:33)

$ unset NLS_LANG

$ ./utplsql run tech_user/1@ora12c
Between string function
Returns substring from start position to end position [.431 sec]
Returns substring when start position is zero [.005 sec]

Test package for pk_util_log
Check that entry is created in tech_log_instances table [.136 sec]
g_log_entry_creation_actual: 701

Finished in .588517 seconds
3 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

[pesse]

Hi @nikita-mospan ,
no, I just got feedback from Jeff Smith (product manager for SQL Developer at Oracle) how Oracle deals with java Locale and NLS_LANG-settings in sqlcl (that new replacement command-line tool for sqlplus).
At the moment utPLSQL-cli (maintained by me) works like this:

If NLS_LANG/LANG/LC_ALL (in that order) is set, try to set it as java Locale setting.

This is plain wrong, because NLS_LANG has a different notation than LANG/LC_ALL.
I will fix that with the upcoming release so java Locale will be set from LANG/LC_ALL only.

The guys at sqlcl although do another thing: If NLS_LANG is set in environment, they open each connection with an initial ALTER SESSION SET NLS_LANGUAGE = {parsedValueFromNLS_LANG} which is a cool approach I'd like to include into utPLSQL-cli, too.

[nikita-mospan]

Got it, thank you!