I'm trying to use Python to call a procedure in another Oracle database (using cx_Oracle) that requires an input parameter of a table object. The table object needs to be comprised of some records from our Oracle SDE (a fairly simple select statement). Using Python, how do I create an Oracle table object to send it to the stored procedure?
Something kind of like:
oracle_sde = u'UserNameHere/PasswordHere@DatabaseNameHere' oracle_otherdb = u'UserNameHere/PasswordHere@DatabaseNameHere' sql = "SELECT * FROM TABLE_NAME" sde_cnxn = cx_Oracle.connect(oracle_sde) sde_cursor = sde_cnxn.cursor() sde_cursor.execute(sql) otherdb_cnxn = cx_Oracle.connect(oracle_otherdb) otherdb_cursor = otherdb_cnxn.cursor() otherdb_cursor.callproc("MyProcedure", sde_cursor.fetchall())
I know it's not really a GIS question, but I figured you guys would be up for the challenge!
What about passing an array variable (Cursor.arrayvar) instead of a cursor?
Yes, I believe I do need to create an array. I'd like to provide some more information about the procedure I'm calling. It has an input of a table type and an output of a table type. Both in and out tables are defined with a custom RECORD datatype.
Here's how the input table is defined:
TYPE meter_info_tab_type IS TABLE OF meter_info_rec_type INDEX BY BINARY_INTEGER;
Here's a sample of what the input table's record type looks like:
TYPE meter_info_rec_type IS RECORD ( c_esb_guid VARCHAR2(50), c_newserialnum VARCHAR2(10), c_remoteid VARCHAR2(15), d_install DATE, c_remotetype VARCHAR2(2), c_recordtype VARCHAR2(1), l_processed NUMBER, )
Here's how the input and output tables are used in the procedure:
PROCEDURE InsertMeterInfo ( p_error_data OUT error_tab_type, p_meter_info_data IN meter_info_tab_type ) IS ... rest of procedure code...
All the cx_oracle arrayvar() examples use VARCHAR2 or NUMBER datatypes. How do I define an arrayvar with a RECORD datatype so I can put in a list of lists or tuples for the rows? This page looked to have some promising information, but I'm still having trouble.
In your OP you are just issuing an SQL SELECT statement against the table. That will work just fine but that is a separate thing from calling PL/SQL packages with cx_Oracle --- from the link in your second post, read the section titled Part 3.4 – PL/SQL to see how he is doing that. There is also example of setting up a refcursor.
I think in any case you will have to transform from the cx_Oracle cursor into something usable for (I'm not sure what you need to do with the results). You could just populate a list and numpy array to go between spatial and non-spatial sets.
Sorry for the confusion with the first post. I was just trying to get my thoughts out into some psuedo code.
Unfortunately, the procedure I'm calling does not use a refcursor; it has to be a table object of record datatype.
Thanks for the reply's guys. I think the answer is that this can't be done. I've come across a few sources that claim it is not possible to send a PL/SQL record type table object through cx_Oracle.