Error Ora-31600 Specifying Dbms_metadata.Set_transform_param Parameter

(Doc ID 1157006.1)

Last updated on AUGUST 25, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
PL/SQL - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.

Symptoms


Using dbms_metadata to create a copy of a table after moving it but trying to avoid duplicate constraint issue fails.

Create table  EMP in REFRESH (test schema, any schema can be used)

create table emp as select * from scott.emp;


Code to create a copy of the table

declare
h1 NUMBER; -- handle returned by OPEN
h2 NUMBER; -- handle returned by OPENW
th1 NUMBER; -- handle returned by ADD_TRANSFORM for MODIFY
th2 NUMBER; -- handle returned by ADD_TRANSFORM for DDL
xml CLOB; -- XML document
errs sys.ku$_SubmitResults := sys.ku$_SubmitResults();
err sys.ku$_SubmitResult;
result BOOLEAN;
BEGIN

h1 := DBMS_METADATA.OPEN('TABLE');

-- Use filters to specify the name and schema of the table.
DBMS_METADATA.SET_FILTER(h1,'NAME','EMP');
DBMS_METADATA.SET_FILTER(h1,'SCHEMA','REFRESH');

-- Fetch the XML
xml := DBMS_METADATA.FETCH_CLOB(h1);

-- Release resources.
DBMS_METADATA.CLOSE(h1);

--Move/Rename original table
execute immediate 'alter table '||'REFRESH'||'.'||'EMP'||' rename to '||'EMP'||'_org';

-- Use the submit interface to re-create the object in another schema.
-- Specify the object type using OPENW (instead of OPEN).
h1 := DBMS_METADATA.OPENW('TABLE');

-- First, add the MODIFY transform.
th1 := DBMS_METADATA.ADD_TRANSFORM(h2,'MODIFY');

-- Specify the desired modification: remap the schema name.
DBMS_METADATA.SET_REMAP_PARAM(th1,'REMAP_TABLESPACE','USERS','REFRESH');

--Need to strip the constraints or else will get this error ORA-02264 duplication

DBMS_METADATA.SET_TRANSFORM_PARAM(th1,'CONSTRAINTS',false);
DBMS_METADATA.SET_TRANSFORM_PARAM(th1,'REF_CONSTRAINTS',false);

-- Now add DDL transform so the modified XML can be transformed into creation DDL.
th2 := DBMS_METADATA.ADD_TRANSFORM(h2,'DDL');

-- Call PUT to re-create the object.
result := DBMS_METADATA.PUT(h2,xml,0,errs);

DBMS_METADATA.CLOSE(h1);
IF NOT result THEN
-- Process the error information.
FOR i IN errs.FIRST..errs.LAST LOOP
err := errs(i);
FOR j IN err.errorLines.FIRST..err.errorLines.LAST LOOP
dbms_output.put_line(err.errorLines(j).errorText);
END LOOP;
END LOOP;
END IF;
END;
/



ERROR:

declare
*
ERROR at line 1:
ORA-31600: invalid input value NULL for parameter HANDLE in function
ADD_TRANSFORM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 541
ORA-06512: at "SYS.DBMS_METADATA", line 3720
ORA-06512: at line 32

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms