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

After running the above script for all columns

( or ) for fewer columns

Usefull? 🙂

Look for odiexperts for more tips and tricks.

Thanks for visiting us!