topic Re: Oracle Table Object in Python in Python Questions

Oracle Table Object in Python

BlakeTerhune — Sun, 12 Dec 2021 03:29:55 GMT

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!

Re: Oracle Table Object in Python

JoshuaBixby — Thu, 26 Mar 2015 21:28:49 GMT

What about passing an array variable (Cursor.arrayvar) instead of a cursor?

Re: Oracle Table Object in Python

BlakeTerhune — Sun, 12 Dec 2021 03:29:57 GMT

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.

Re: Oracle Table Object in Python

JamesCrandall — Mon, 30 Mar 2015 13:36:12 GMT

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.

Re: Oracle Table Object in Python

BlakeTerhune — Mon, 30 Mar 2015 15:37:35 GMT

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.

Re: Oracle Table Object in Python

BlakeTerhune — Mon, 30 Mar 2015 15:38:06 GMT

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.

Discussion of the cx_Oracle Python interface to Oracle ()