Closed alesana-san closed 4 years ago
pesse
commented
4 years ago SQLDeveloper PlugIn has the same problem.
Which is sad, because it's using a different way to consume the output buffer if I remember correctly.
Any ideas, @PhilippSalvisberg ?
pesse
commented
4 years ago SQLcl has the same problem:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set serveroutput on
SQL> exec ut.run();
test
???? ???????? ????????????? [,665 sec] (FAILED - 1)
Failures:
1) my_test_with_ru_desc
Fail
at "APP.RU_TEST.MY_TEST_WITH_RU_DESC", line 6 ut.fail('Fail');
Finished in ,666271 seconds
1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
SQL>When trying to do a select * from table(ut.run()) we get an Encoding exception:
SQL> select * from table(ut.run());
java.io.UnsupportedEncodingException: CL8MSWIN1251
at java.base/java.lang.StringCoding.encode(Unknown Source)
at java.base/java.lang.String.getBytes(Unknown Source)
at oracle.dbtools.db.ResultSetFormatter.write(ResultSetFormatter.java:710)
at oracle.dbtools.db.ResultSetFormatter.write(ResultSetFormatter.java:703)
at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:2278)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:334)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:308)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:153)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:71)
at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:798)
at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:709)
at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:83)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:405)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:231)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:405)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:416)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1250)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:492)
PhilippSalvisberg
commented
4 years ago @pesse where do I find the test case?
pesse
commented
4 years ago @PhilippSalvisberg needs a special set-up database, then I just created a testcase with some russian chars in the description:
create or replace package ru_test as
-- %suite(test)
-- %test(Тест базового использования)
procedure my_test_with_ru_desc;
end;
/
create or replace package body ru_test as
procedure my_test_with_ru_desc
as
begin
ut.fail('Fail');
end;
end;
/I ran it on macOS. Default client character set is there UTF-8. Looks like this:
and via SQLcl this:
PhilippSalvisberg
commented
4 years ago If the result of the query:
select text from user_source where name= 'RU_TEST';looks like this
TEXT
------------------------------------------------------------------------------------------------------------------------------------
package ru_test as
-- %suite(test)
-- %test(Тест базового использования)
procedure my_test_with_ru_desc;
end;
package body ru_test as
procedure my_test_with_ru_desc
as
begin
ut.fail('Fail');
end;
end;
16 rows selected. then the NLS setup is correct and utPLSQL will show the correct test description.
In all other cases the NLS setup is wrong. It is not really a utPLSQL related issue.
alesana-san
commented
4 years ago Hi! In my case db encoding charset is ISO 8859-5 = CL8ISO8859P5 I guess UTF8 doesn't have these problems
PhilippSalvisberg
commented
4 years ago @alesana-san can you post the result of the query from SQLcl?
select text from user_source where name= 'RU_TEST';
pesse
commented
4 years ago Okay, I managed to get SQLcl and SQLDeveloper to output it correctly by using NLS_LANG=russian_cis.UTF8
But somehow SQLDeveloper PlugIn and cli still show ???
PhilippSalvisberg
commented
4 years ago @pesse where do you set NLS_LANG?
pesse
commented
4 years ago Some more investigation, using SQLcl (NLS_LANG set to russian_cis.UTF8):
I utPLSQL and paused it before consuming the OutputBuffer, so I could query the underlying table (user SYS):
SQL> select text from ut3.UT_OUTPUT_BUFFER_TMP;
Dez. 05, 2019 11:29:29 VORM. oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SCHWERWIEGEND: java.base/java.lang.String.checkIndex(Unknown Source)
java.lang.StringIndexOutOfBoundsException: index 69,length 69
at java.base/java.lang.String.checkIndex(Unknown Source)
at java.base/java.lang.AbstractStringBuilder.charAt(Unknown Source)
at java.base/java.lang.StringBuffer.charAt(Unknown Source)
at oracle.dbtools.raptor.newscriptrunner.ScriptUtils.wrap(ScriptUtils.java:1134)
at oracle.dbtools.db.SQLPLUSCmdFormatter.wrap(SQLPLUSCmdFormatter.java:2858)
at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:1484)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:334)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:308)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:153)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:71)
at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:798)
at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:709)
at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:83)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:405)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:231)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:405)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:416)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1137)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:492)
TEXT
--------------------------------------------------------------------------------
Тест базового использования 1 [,672 sec] (FAILED - 1)
Failures:
1) my_test_with_ru_desc
Fail
SQL> For me, this looks like SQLcl can output it correctly, but assumes a wrong encoding, therefore running into the index violation.
On the other hand it can query the source, containing the special chars with no problem at all:
SQL> select text from all_source where owner = 'APP';
TEXT
--------------------------------------------------------------------------------
package body ru_test as
procedure my_test_with_ru_desc
as
begin
ut.fail('Fail');
end;
package ru_test as
-- %suite(test)
-- %test(Тест базового использования 1)
TEXT
--------------------------------------------------------------------------------
procedure my_test_with_ru_desc;
end;
end;
16 Zeilen gewählt.More investigation results:
Querying ut_output_buffer_tmp directly via JDBC gives the correct output. Using the Reporter/OutputBuffer does not.
pesse
commented
4 years ago Okay, I think I could dig it down (thanks again @PhilippSalvisberg for your help!) There are 2 different possible issues:
I was able to reproduce a problem of different charset handling when using direct select * from... compared to using the OutputBuffer with its pipelined table functionality and SYS_REFCURSOR.
However, this problem did only occur on an unpatched 12.1 system (directly created from the official Oracle docker scripts).
It did not occur on 19.3, also not on Philipp's patched 12.1.
This is very likely an Oracle bug that is meanwhile fixed with one of the patches.
If I understood it correctly, Java will use the default Charset depending on the Console/Environment you use. For Windows this is mostly not UTF-8, chcp has no effect on that.
However, adding -Dfile.encoding=UTF-8 to the JVM-call does.
Unfortunately you can't just add that to the utPLSQL command (We might want to discuss if it would be beneficial to allow that).
Also, there is no way (well, there is, but a very hacky and discouraged one) to change the Default Charset once the JVM is started - which happens way before even entering the main method of any java program.
To circumvent this limitation, there's this nice little JAVA_TOOL_OPTIONS environment variable which will be read by the JVM and scanned for JVM parameters.
In my case (Powershell on Windows), setting that env variable did the trick:
> $env:JAVA_TOOL_OPTIONS="-Dfile.encoding=UTF8"
> utplsql run app/***@localhost:1523/ORCLPDB1 -d
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF8
2019-12-06 09:21:45 [main] INFO org.utplsql.cli.RunAction - ######################### utPLSQL cli #########################
...So, for NLS-Settings, use NLS_LANG environment variable.
For Charset-Settings, use JAVA_TOOL_OPTIONS environment variable with property -Dfile.encoding=YourCharset
I hope this helps and solves your problem. I will also add this to the cli README.
alesana-san
commented
4 years ago In my case it was Issue 1. I patched my Oracle 12c with the latest PSU and it worked as expected. Any additional lines (such as setting JAVA_TOOL_OPTIONS and NLS_LANG) aren't needed now.
Issue can be closed I guess. Thank you for so detailed explanation!
PhilippSalvisberg
commented
4 years ago @alesana-san, thank you for letting us know. So it was an Oracle Database bug in the end.
Hi!
What started in a Slack chat grew into this issue :)
I have a test with Russian words in its name. So I expect it to be "Russian" when I call it via
cli.Here's my preset:
Calling
ut.runin DataGrip results in a good output with all my Russian symbols.Okay, let's proceed with
cli:Calling as it is:
Nope, didn't work.
Let's try with
LC_ALL:Noooooooope. Okay, let's try with
NLS_LANG:God, plz! After looking at Oracle NLS stories I decided to do the following:
Well, let's take another shot:
Oh, I forgot to switch
chcpto1251. And again...And what about
sqlplus?It works just fine! I call
ut.runand output is okay! It's in Russian.Hands down, I don't get how charset stuff works. Any help would be appreciated!