pythonoracle-databasecx-oracledbms-metadata

Using python cx_Oracle, how do I call DBMS_METADATA.SET_TRANSFORM_PARAM, which has multiple declarations?

Here's the call and error I currently receive:

cursor.callproc('DBMS_METADATA.SET_TRANSFORM_PARAM', ['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
Solution

  • The issue is related to the use of quotations. The syntax for the procedure DBMS_METADATA.SET_TRANSFORM_PARAM

    DBMS_METADATA.SET_TRANSFORM_PARAM (
   transform_handle   IN NUMBER,
   name               IN VARCHAR2,
   value              IN VARCHAR2|IN BOOLEAN DEFAULT TRUE|IN NUMBER, 
   object_type        IN VARCHAR2 DEFAULT NULL);

    the last parameter(object_type) needn't to be specified obviously, since it has NULL value as default.

    the penultimate parameter(value) might have three options for data type. Seems that is intended to be as BOOLEAN. So, remove quotes around false.

    lastly, the first parameter(transform_handle) is also considered as numeric. So, remove quotes here too.

    As a result, call the procedure as

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