Skip to Main Content

Analytics Software

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!

How to modify LKM File to Oracle in ODI to use CASE WHEN statement in sql

user12251389Sep 2 2022 — edited Sep 2 2022

Dear All,
I am using ODI 12.2 version. I am trying to load text file into oracle database table. Currently there is no error during loading from odi but its loading only few rows and for few rows the bad file is getting generated with error :
ODI-40469: Value was incorrect for column 40: String N.A. is not a valid representation for a Number
ODI-40469: Value was incorrect for column 37: String N.A. is not a valid representation for a Number
I understood the issue where its trying to insert the value as N.A. in the fields which is Number datatype. But i don't want to change the datatype of those Numeric fields to string in data store as well as in oracle database table because it will change logic for precision and scale used for Numeric datatype.
I want to change LKM File to Oracle such that for all the fields with values N.A. it should set the values to null. I haven't modify LKM or IKM before as its written in groovy so dont know how to do it. I belive we need to change the Select query in LKM in source code and use something like CASE WHEN statement which can be used for all the fields.

image.png
image.png
Below is some part of Select query in Source Command in LKM File to Oracle
SELECT

{# IF $[QUERY.isDistinct()] #}{#NL#}
DISTINCT
{# ENDIF #}
{# IF ($[QUERY.getAliasList()] != 'null') #}{#NL#}
{# LIST #} $[QUERY.getSelectList().foreach(getText())] $[QUERY.getColumnAliasSeparator()] $[QUERY.getAliasList()] {# SEP #},{#NL#}{# ENDLIST #}
{# ELSE #}{#NL#}
{# LIST #} $[QUERY.getSelectList().foreach(getText())] {# SEP #},{#NL#}{# ENDLIST #}
{# ENDIF #}
{#NL#}
{# IF $[QUERY.isConstantQuery()] #}
{#NL#}
$[QUERY.getConstantFromClauseText()]
{# ELSE #}FROM {#NL#}
{# LIST #} $[QUERY.getFromList().foreach(getText())]{# SEP #} ,{#NL#} {# ENDLIST #}
{# ENDIF #}

Comments

John Thorton

post COPY & PASTE proof that you can successfully run any "sample.sql"  script

post content of xplan.sql file

You can COPY directly from Windows CMD terminal.

Orcl Apex

I am not able to run even select * from dual script.

Paulzip

Probably should be moved to the SQLcl forums.

Gaz in Oz

sqlcl version?

BluShadow

*** Moderator Note: Question now moved to the SQLcl product space as this is more about the tool than the SQL or PL/SQL languages.

Gary Graham-Oracle
Answer

There is a bug in 17.3 with a workaround:  set encoding UTF8

For example...

SQL> show version

Oracle SQLDeveloper Command-Line (SQLcl) version: 17.3.0.256.1818

SQL> show encoding

Encoding:WE8MSWIN1252

SQL> @ test.sql 

Oct 19, 2017 11:18:56 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor <init>

SEVERE: Could not process url:file:/C:/Tools/sqlcl/r17_3/sqlcl/bin/test.sql

Oct 19, 2017 11:18:56 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run

SEVERE: java.io.Reader.<init>(Unknown Source)

java.lang.NullPointerException

        at java.io.Reader.<init>(Unknown Source)

        at java.io.BufferedReader.<init>(Unknown Source)

        at java.io.BufferedReader.<init>(Unknown Source)

        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.scriptParserInit(ScriptParser.java:87)

        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.<init>(ScriptParser.java:83)

        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.<init>(ScriptParser.java:75)

        at oracle.dbtools.raptor.newscriptrunner.FallbackParserProvider.<init>(FallbackParserProvider.java:22)

        at oracle.dbtools.raptor.newscriptrunner.SqlParserProvider.getScriptParserIterator(SqlParserProvider.java:25)

        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:169)

        at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.runExecuteFile(SQLPLUS.java:3870)

        at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.run(SQLPLUS.java:210)

        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQLPLUS(ScriptRunner.java:406)

        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:243)

        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:337)

        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:225)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:390)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:401)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1225)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:477)

SQL> set encoding UTF8

SQL> @ test.sql

D

-

X

SQL>

where test.sql contains "select * from dual;"

Marked as Answer by Orcl Apex · Oct 19 2017
BG4GRAPH

Hi,
this bug seems to have been reintroduced in SqlCl 21.3.
Notice that the workaround is not acceptable if the script does not have UTF8 encoding.

Yours,
BG4GRAPH.

1 - 7

Post Details