How to call a procedure -- like dbms_metadata.session_transform -- with overloaded declarations?

Brad_the_Dazed

I'm trying to use cx_oracle to extract DDL from an Oracle database for further use in a non-Oracle database, so I'm looking to reduce the options created by DBMS_METADATA.GET_DDL by first calling DBMS_METADATA.SESSION_TRANSFORM, but this has an overloaded declaration. My python fu isn't up to the challenge & here is what happens:

    cursor.execute("begin DBMS_METADATA.SET_TRANSFORM_PARAM(:1,:2,:3); end;", ['DBMS_METADATA.SESSION_TRANSFORM', 'STORAGE', 'false'])

cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:

PLS-00307: too many declarations of 'SET_TRANSFORM_PARAM' match this call

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

I'm fairly certain I'm already getting the :1 parameter value wrong, but I don't get that far. (That's problem #2.)

Suggestions for calling an overloaded procedure?

Anthony Tuininga-Oracle

The first parameter is supposed to be a number. The second parameter is supposed to be a string. The third parameter is supposed to be either a string, a number or a boolean value. Its not clear which version you intend to use -- but since you haven't got the types right for any of them the database is confused! If, for example, you wanted to call the one with the boolean you would do the following:

cursor.callproc("dbms_metadata.set_transform_param", [5, "Some String", False])

If you wanted to pass a string for the third parameter instead you would do:

cursor.callproc("dbms_metadata.set_transform_param", [5, "Some String", "Some other string"])

Hope that helps!

Anthony Tuininga-Oracle

The first parameter is supposed to be a number. The second parameter is supposed to be a string. The third parameter is supposed to be either a string, a number or a boolean value. Its not clear which version you intend to use -- but since you haven't got the types right for any of them the database is confused! If, for example, you wanted to call the one with the boolean you would do the following:

cursor.callproc("dbms_metadata.set_transform_param", [5, "Some String", False])

If you wanted to pass a string for the third parameter instead you would do:

cursor.callproc("dbms_metadata.set_transform_param", [5, "Some String", "Some other string"])

Hope that helps!

Brad_the_Dazed

Thanks, this is perfect help for my overloaded declaration problem. I knew it has something to do with types, but I missed it.

Related to DBMS_METADATA, the first parameter in SET_TRANSFORM_PARAM is typically defined as DBMS_METADATA.SESSION_TRANSFORM; the "handle value". How do I reference this value in the cursor.callproc call?

I've gotten it to work with cursor.execute because the pl/sql statement recognizes the reference:

     cursor.execute("begin DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,:1,:2); end;", ['STORAGE', False])

... but I can't get it to work when using cursor.callproc (without quotes) because python considers the reference as not defined:

     cursor.callproc('DBMS_METADATA.SET_TRANSFORM_PARAM', [DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', False])

... or (with quotes) there's an ORA-06502 because python is passing a character type that needs typecasting to a number:

     cursor.callproc('DBMS_METADATA.SET_TRANSFORM_PARAM', ['DBMS_METADATA.SESSION_TRANSFORM', 'STORAGE', False])

EDIT: I've discovered the type and value of the SESSION_TRANSFORM constant is a binary integer with a value of -1  Using -1 instead of DBMS_METADATA.SESSION_TRANSFORM in the callproc works!

Anthony Tuininga-Oracle

Yes, you need to use a crafted PL/SQL block if you want to use the PL/SQL constant DBMS_METADATA.SESSION_TRANSFORM, or you can use the integer directly with callproc().