Tuesday, July 16, 2013

How to retrieve a BLOB and write it to a file in WLST

We often need to recover errored payloads from the OSB reporting archiving tables. in WLST/Python it's VERY easy (much easier than in Java)

from com.ziclix.python.sql import zxJDBC

def connectToProd(zxJDBC):
    jdbc_url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.acme.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=myservice)))"
    username = "USER_SOAINFRA"
    password = "PASSWORD"
    driver = "oracle.jdbc.OracleDriver"
    conn = zxJDBC.connect(jdbc_url, username, password, driver)
    return conn

conn = connectToProd(zxJDBC)
cursor = conn.cursor(1)
cursor.execute("select DATA_VALUE, BUSINESSUNIQUEID, TECHNICALMESSAGEID from WLI_NESOA2_REPORTING_ARCHIVE where InterfaceID='BLA' and IsError != 'OK' and EventOccuredOn >= '2012-07-16T00:00:54' and EventOccuredOn <= '2013-07-16T09:30:54' order by businessuniqueid")
print "executed select  "
columnNames = []
for item in cursor.description:

for dsrow in cursor.fetchall():
        dv = dsrow[columnNames.index('DATA_VALUE')]
        tid = dsrow[columnNames.index('TECHNICALMESSAGEID')]
        bid = dsrow[columnNames.index('BUSINESSUNIQUEID')]
        myfile = open("thefiles/" + bid + "_" + tid, "w")

No comments: