Forum Stats

  • 3,735,100 Users
  • 2,247,106 Discussions
  • 7,857,683 Comments

Discussions

Python Code for Saving a BLOB FILE in ORACLE Database

I am using the following code to create a record in Oracle Database using python cx_Oracle. Currently the routine waits for a file in the inbox ( incomin file directory ) directory and creates a record in the table with blob fields. As you can see below I am using the EMPTY_BLOB() to successfully create a record. The incoming files are XML or TXT files which I would like to save in these BLOB fields which you can see in the insert query.

db = oracdbconn.get_db_connect()
#constr = sys.argv[1]
print "Successfully connected to Database : %s" % db
while True:
print "Waiting for a new File.............."
time.sleep(period)
files = [os.path.join(dir, f) for f in os.listdir(dir)]
files = filter(os.path.isfile, files)
curs = db.cursor()
print "%s" %curs
print "%s" %cx_Oracle.Connection
table_name = config.staging_db_tablesNames['in_msgs']
if sorter:
files = sorted(files, cmp=sorter)
for filename in files:
action(filename)
file_extension = message_type(filename)
curs.execute ("INSERT INTO %s VALUES (SEQ_GSAP_RECID.nextval,SEQ_GSAP_MSGID.nextval,'SHELLSAP',sysdate,'%s', *EMPTY_BLOB()*, 1, SYSDATE,*EMPTY_BLOB()*, SYSDATE)" %(table_name,file_extension))
db.commit()
print "A %s file type found.. " % message_type(filename)
move_datafile(filename)

What I want to do now is to save the physical file itself into the database in the blob field. Can any one help me how to do that as in all the examples which I have seen they refer to preparing the sample binary data using char or random 0-255 functions and using setvalue(0, binary_data) for storing the string. This is confusing me as they are passing the string not the file path or file itself.

Here I want to save the file itself. I am stuck that how can I pass the file path name which should pickup the file from directory and save (using an update query ) it to the table. I have already created a directory object as follows and set the permissions on it for the current user so that oracle can read from it._ create directory inbox_scnr_dir AS '/home/xx/inbox/';

CAN ANYONE HELP ME to achieve this?
This discussion has been closed.