12C ODI : How to use ORACLE keyword(SIZE) in interface

3173245

Hi Experts,

For one the interface, we have source as SQL SERVER and target as ORACLE.

In SQL SERVER  View, there is an column name SIZE.

When we are using same name SIZE in interface, while creating $ tables (staging table ) at run time Interface is failing as SIZE is an KEYWORD in ORACLE.

i have tried by adding quotes (") like "SIZE" at Model, by this $ tables are creating successful but while loading data from SQL SERVER to $ table interface is failing like "SIZE" column not available at SQL SERVER view.

could any once can know any alternate way to use this SIZE column in ODI 12C interface??

Thanks

Phani

ashud1

Hi,

you can use LTRIM and RTRIM they both are applicable to oracle and SQL server

Aramast Oghiguian

Hi Phani @3173245 ,

You may create a new MS SQL Server view in your source side where you don't have Oracle reserved words simply for the column names. If you don't want to do this then you need to copy, modify and use the newly changed LKM. You mainly need to change the "<%=odiRef.getColList." part. Please take a look at the following thread:

ODI 12c Mapping, dealing with reserved words on different SRC and TRG datasources

Regards,

Aramast

Adrian_Popescu-Oracle

Hi,

You can also follow note: Reserved Words Still Causing Problems After Patch 20561136 is Applied in ODI 12c (Doc ID 2068571.1)

Adrian

3173245

Hi Aramast,

I have some TRIM,SUBSTR's used at interface.

when i'm replacing [EXPRESSION] to [[COL_HEADING]] , then TRIM and SUBSTR's are not present in back-end QUERY.

<%=odiRef.getColList(i, "", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%>

to:

<%=odiRef.getColList(i, "", "[[COL_HEADING]]\t[ALIAS_SEP] [[CX_COL_NAME]]", ",\n\t", "", "")%>

could you please help in this.

Thanks

Phani

3173245

Hi @Aramast Oghiguian

any update on this.

Thanks

Regards,

Phani

Adrian_Popescu-Oracle

Hi,

Try to follow the note I have recommend because it is the best approach.  You have just to install a patch and configure the reserved keyword.

Adrian

ashud1

Hi,

can you put some expression in between and try renaming the column there. can make sure that Expression Unit runs on Sql server itself.

Thanks

3173245

@Adrian_Popescu-Oracle

Thanks for the info.

i can't install any patch directly ,as it involves many approvals from business.

ThnakS

3173245

@ashud1

we can't execute expressions at SQL SERVER as some of the expressions like TRIM cant be executed on SQL SERVER .

Thanks

ashud1

Hi,

you can use LTRIM and RTRIM they both are applicable to oracle and SQL server