ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Generate Column_name Header for OdiSqlUnload

Last week we have shown you the code for generating the Header and populate the same in OdiSqlUnload file , but the codes seems to be limited to the Database and then separate query needs to be created for each database reading from the System table,which doesn’t makes things so easy.

In order to solve it and to make the codes independent, we have revisited and rewritten the whole codes to generate header.

All you need to is to write the query you have used in the OdiSqlUnload .

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/output.txt','r+')
myStmt = sourceConnection.createStatement()
my_query = "select * FROM ODI_WORK.SNP_TABLE"
my_query=my_query.upper()
if string.find(my_query, '*') > 0:
    myRs = myStmt.executeQuery(my_query)
    md=myRs.getMetaData()
    collect=[]
    i=1
    while (i  0:
    header = my_query[7:string.find(my_query, 'FROM')].replace("||','||",',')
else:
    header = my_query[7:string.find(my_query, 'FROM')]

print header
old=output_write.read()
output_write.seek(0)
output_write.write (header+'n'+old)

sourceConnection.close()
output_write.close()

Note :
The codes presently support only this type of queries format
select * from table_name (or)
select column1,column2 from table_name (or)
select column1||’,’||column2 from table_name

image

After running the above script for all columns

image

( or ) for fewer columns

image

Usefull? 🙂

Look for odiexperts for more tips and tricks.

Thanks for visiting us!

8 Comments

  1. Hi,

    I have copied the given code into an ODI procedure and ran it after changing the filepath and source table. I get the following error:

    org.apache.bsf.BSFException: exception from Jython:
    SyntaxError: (“no viable alternative at input ‘0’”, (”, 15, 14, ‘ while (i 0:\n’))

    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: SyntaxError: (“no viable alternative at input ‘0’”, (”, 15, 14, ‘ while (i 0:\n’))

    at org.python.core.ParserFacade.fixParseError(ParserFacade.java:96)
    at org.python.core.ParserFacade.parse(ParserFacade.java:203)
    at org.python.core.Py.compile_flags(Py.java:1685)
    at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
    … 19 more

    Can you please help in this?

  2. hi,
    In odisqlload we have parameters, in which parameter i have to enter this code.

    Please guide me,

    Thanks,
    dravid

  3. Hi,
    using the code I am getting the following error:
    org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):

    (no code object) at line 0

    SyntaxError: (‘invalid syntax’, (”, 12, 1, ‘myRs = myStmt.executeQuery(my_query)’))

    Please guide.
    Thanks

  4. Hi,
    On using the above code I am getting this error:

    org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):

    (no code object) at line 0

    SyntaxError: (‘invalid syntax’, (”, 12, 1, ‘myRs = myStmt.executeQuery(my_query)’))

    Can you please guide me?

    Thanks,
    Ritika

  5. hi, this scripts works if the source datastore is as400 db2?

    • Hi Raul ,

      The script will work ,but the query is written for Oracle as Source for DB2 you need to write the query to fetch the columns names from DB2 system tables ,If i am not wrong in db2 you can get column information from SYSTABLES,SYSCOLUMNS.
      In the command on Source provide the required technology and Schema and in the command on Target ,change the sqlstmt to the respective query for db2.
      Else manually pass the column name list into res as shown in the second part of the code and the header will be written into the File.

Leave a Reply

Required fields are marked *.