My Oracle Support Banner

Error Ora-31600 Specifying Dbms_metadata.Set_transform_param Parameter (Doc ID 1157006.1)

Last updated on DECEMBER 19, 2019

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]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Generic (Platform Independent)

Symptoms

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.


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

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.