CODEWITHSUNDEEP
jython
Vollk
Vollk

Reputation: 11

Oracle Data Integrator Substitution API in JYTHON script

I have a problem with using ODI SUBSTITUTION API and project variables in JYTHON script in ODI Procedure.

I'm using Jython for checking another database (DB1) for one flag every 15 minutes. Query sending to that database is stored in a table on dedicated schema from another database(DB0). I want to use ODI variables and substitution API for target table and where clause because depending on the environment there will be different schemas.

Table on DB0 with queries:

CREATE TABLE QUERY_TABLE(
TARGET_QUERY CLOB,
TARGET_SCHEMA VARCHAR2(255),
LP_NAME VARCHAR2(255)
)

The first query from JYTHON to DB0 is 

queryTable = odiRef.getObjectName("L","QUERY_TABLE","MyMetaData","D") 
LPName = odiRef.getLoadPlanInstance("LOAD_PLAN_NAME") 
getQuery = "SELECT TARGET_QUERY,TARGET_SCHEMA FROM " + queryTable + " WHERE 
PROCESS_NAME = '"+ LPName "'"

and that works perfectly fine, while ODI SUBSTITUTION API is defined in the script.

Next step is sending result of above query to DB1 :

 flagCheckQuery = queryCursor.getString("TARGET_QUERY")
targetSchema = queryCursor.getString("TARGET_SCHEMA")
conn = odiRef.getJDBCConnectionFromLSchema(targetSchema,odiRef.getContext("CTX_CODE"))
flagCheckQueryCursor =  conn.createStatement().executeQuery(query) 

while flagCheckQueryCursor.next():
    if int(flagCheckQueryCursor.getString(1)) == 1: 
        #
        #Do other things here
        #

If in QUERY_TABLE from DB0, TARGET_QUERY has hardcoded table with the owner like

INSERT INTO QUERY_TABLE(TARGET_QUERY,TARGET_SCHEMA,LP_NAME)
VALUES ('SELECT FLAG FROM Flag_Schema.FLAG_TABLE','Flag_Schema','MY_LP')

That works fine. But depending on environment schema will be FLAG_SCHEMA_TEST, FLAG_SCHEMA_TEST2 or other names, but always defined in Logical Schema in ODI as Flag_Schema and referring to other Psychical Schema in Psychical Architecture depending on context.

So I want to use ODI SUBSTITUTION API and project variables like this:

INSERT INTO QUERY_TABLE(TARGET_QUERY,TARGET_SCHEMA,LP_NAME)
VALUES ('SELECT FLAG FROM odiRef.getObjectName("L","DWH_PROCESS_STATUS","Flag_Schema","D") WHERE FLAG_DATE = to_date('':MyOdiProject.OdiDateVariable'',''mm/dd/yyyy'')','Flag_Schema','MY_LP')

But when I do that like above, Jython is throwing an exception 

java.sql.SQLException: java.sql.SQLException: SQL string is not Query

Any idea how define query in QUERY_TABLE?

Upvotes: 1

Views: 1948

Answers (1)

rpc1
rpc1

Reputation: 688

It is wrong syntax of ODI Substitution API you have to put it in <%= => And project variable have to be start with #
Try this code:

INSERT INTO QUERY_TABLE(TARGET_QUERY,TARGET_SCHEMA,LP_NAME)
VALUES ('SELECT FLAG FROM <%= odiRef.getObjectName("L","DWH_PROCESS_STATUS","Flag_Schema","D") %>
 WHERE FLAG_DATE = to_date(''#MyOdiProject.OdiDateVariable'',''mm/dd/yyyy'')','Flag_Schema','MY_LP')

And read this docs https://docs.oracle.com/cd/E17904_01/integrate.1111/e12645/odiref_reference.htm

Upvotes: 1

Related Questions