2 Replies Latest reply: Dec 21, 2012 5:58 PM by user6596050 RSS

    CONSTRAINTS_AS_ALTER not working with DBMS_METADATA APIs

    user6596050
      I created a SP under a DBA account that uses the DBMS_METADATA API. As documented, I am using IR for the SP.

      Whenever I uncomment set_transform_param for 'CONSTRAINTS_AS_ALTER, I get ORA-31600: invalid input value CONSTRAINTS_AS_ALTER for parameter NAME, regardless of who executes the SP.

      The code is working perfectly when this is commented out, both for privileged and unprivileged users.

      I have downloaded and read the following documentation where it pertains to this as well as looking online and I do not know why this error is occurring.
      --- Ora11gR2_Utilities_e16536.pdf
      --- Ora11gR2_PLSQLPackagesandTypesReference_e16760.pdf

      Is this not allowed with the APIs I am using?

      Thanks

      ...

      schema_handle_status := 'CLOSED';
      schema_handle := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
      schema_handle_status := 'OPENED';
      DBMS_METADATA.SET_FILTER(schema_handle,'SCHEMA',schema_to_use);
      DBMS_METADATA.SET_COUNT(schema_handle,1);
      ddl_handle := DBMS_METADATA.ADD_TRANSFORM(schema_handle,'DDL');
      --dbms_metadata.set_transform_param(ddl_handle,'CONSTRAINTS',TRUE);
      --dbms_metadata.set_transform_param(ddl_handle,'CONSTRAINTS_AS_ALTER',TRUE);
      dbms_metadata.set_transform_param(ddl_handle,'SQLTERMINATOR',TRUE);


      LOOP
      object_definition := DBMS_METADATA.FETCH_CLOB(schema_handle);

      ...