Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
nikita-mospan opened this Issue Sep 12, 2018 · 11 comments

Comments

Projects
None yet
3 participants
@nikita-mospan
Copy link

commented Sep 12, 2018

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

This comment has been minimized.

Copy link
Member

commented Sep 12, 2018

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

This comment has been minimized.

Copy link
Author

commented Sep 12, 2018

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

This comment has been minimized.

Copy link
Member

commented Sep 12, 2018

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

This comment has been minimized.

Copy link
Member

commented Sep 12, 2018

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

This comment has been minimized.

Copy link
Author

commented Sep 12, 2018

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

This comment has been minimized.

Copy link
Member

commented Sep 12, 2018

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

This comment has been minimized.

Copy link
Author

commented Sep 12, 2018

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

This comment has been minimized.

Copy link
Member

commented Sep 14, 2018

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

This comment has been minimized.

Copy link
Author

commented Sep 14, 2018

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

This comment has been minimized.

Copy link
Member

commented Sep 14, 2018

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

This comment has been minimized.

Copy link
Author

commented Sep 14, 2018

Got it, thank you!

@pesse pesse added this to the 3.1.1 milestone Oct 5, 2018

@pesse pesse self-assigned this Oct 5, 2018

@pesse pesse added the bug label Oct 5, 2018

pesse added a commit that referenced this issue Oct 11, 2018

Add ConnectionInit-SQL based on NLS_LANG
Remove NLS_LANG from Locale-Initialization
Fixes #101

@pesse pesse closed this in #106 Oct 18, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.