Hi,
I have the metadata of an object in one of my database table as xml. I failed to recreate the object in the other schema using metadata api. I developed a stored function to do the above job. My function doesn't throw any error as well as it doesn't create the object.
My code snippet is

CREATE OR REPLACE PROCEDURE DDI.move_table(
table_name in VARCHAR2,
from_schema in VARCHAR2,
to_schema in VARCHAR2 )
AUTHID CURRENT_USER
IS
-- Define local variables.
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 XMLTYPE; -- XML document
errs sys.ku$_SubmitResults := sys.ku$_SubmitResults();
err sys.ku$_SubmitResult;
result BOOLEAN;
BEGIN
SELECT REPOS INTO xml from ddi.ddi_repos_t where obj_id = '1801';
-- Specify the object type using OPENW (instead of OPEN).
h2 := 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_SCHEMA',from_schema,to_schema);
-- Now add the DDL transform so that 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(h2);
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;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20510,'No xml is available as metadata');
END;

Could you tell me where is the probelm?
The schema where i create and execute my function have dba privilege also.

Hi,
I ran my script on toad (I tried with SQL*PLUS also). The function should return the errors if any. When executing, i got null only. It means there was no error. I verified in the user (where i need to recreate the object). But it throws error like table or view doesn't exists when i select it. (I have checked properly).
Meanwhile i tried to creat an object like
CREATE TABLE USER2.EMP AS SELECT * FROM USER1.EMP;
The table created. (It shows no privilege problem).
Now i'm in a confusion that where i did wrong?
Regards,
madhavi.

I dont think there is a problem in my environment. I feel that its a privilege issue( i don't know what is that). DBA Privilege useful when creating the object from sql. Here the case is the metadata api recreates the object.