This discussion is archived
3 Replies Latest reply: Jan 9, 2012 2:17 PM by 643298 RSS

passing blobs to stored procedure

user11980810 Newbie
Currently Being Moderated
Hi,

I'm looking for a way to pass blobs of different sizes (few kB up to some mB) from python to an oracle procedure within a package. I've tried cursor.callproc but it refuses to work for blobs.
Perhaps there is somebody around somewhere who has done this already?

Thanks for your help, Hannes
  • 1. Re: passing blobs to stored procedure
    390020 Newbie
    Currently Being Moderated
    Can you please share a little example of what you are doing? I have trouble picturing a blob (I mean I could open a pdf file, read some bytes and try to upload that to the db)but nevertheless if you can share a little snippet it will help with a faster answer.
  • 2. Re: passing blobs to stored procedure
    user11980810 Newbie
    Currently Being Moderated
    Hi Mariano.

    I already have a stored procedure in Oracle which gets a blob, is doing something with blob afterward and returns another blob. My python application is reading a file in, calls the oracle procedure (passes on the file to it), receives a blob and writes it to the file system. Reading and writing files from python is not the problem. Problem is how do I transfer it to or from the stored oracle procedure.

    Example:
    In Oracle
    create or replace package my_blob_proc (blob_in in blob, blob_out out blob)
    as
    begin
    < do something with blob_in>
    < put something on blob_out>
    end my_blob_proc;

    In Python:
    import cx_Oracle
    connection = cx_Oracle.connect('...')
    cursor = connection.cursor()
    f_blob = open('anyfile','rb')
    blob = f_blob.read()
    cursor.setinputsizes(blob=cx_Oracle.BLOB)
    # question is how do I call callproc to have a blob as input and another blob
    as output
    cursor.callproc('my_blob_proc', what do I have to put here to pass the blobs?)
    and continue writing blob_out to file ...

    Thanks a lot, Hannes
  • 3. Re: passing blobs to stored procedure
    643298 Newbie
    Currently Being Moderated
    You can try with cx_oracle.Connection.cursor.var(cx_Oracle.CLOB) as described here
    http://www.oracle.com/technetwork/articles/dsl/prez-transactions-lobs-089563.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points