Hi,
I developed a function for a schema export using dbms_metadata package (return the metadata in xml format). The logic works for table export. But i got the error when use SCHEMA_EXPORT handle instead of TABLE_EXPORT.
The function is...
CREATE OR REPLACE FUNCTION TEST.F_ALL_XML (v_database varchar2 DEFAULT NULL,
V_SCHEMA VARCHAR2 DEFAULT NULL )
-- ,V_TABLE VARCHAR2 DEFAULT NULL)
RETURN XMLTYPE
AS
Handle NUMBER;
V_Output XMLTYPE;
V_Output_Total XMLTYPE;
CHILD_XML XMLTYPE;
V_Cnt NUMBER := 0;
BEGIN
Handle := DBMS_METADATA.OPEN('SCHEMA_EXPORT', NETWORK_LINK => V_DATABASE);
--DBMS_METADATA.SET_COUNT(Handle,1000);
DBMS_METADATA.SET_FILTER(Handle,'SCHEMA',V_SCHEMA);
-- DBMS_METADATA.SET_FILTER(Handle,'NAME',V_Table);
LOOP
V_Output := DBMS_METADATA.FETCH_XML(HANDLE);
EXIT WHEN V_OUTPUT IS NULL;
if v_cnt = 0 THEN
V_Output_Total := V_Output;
else
if v_cnt is not null and v_cnt > 0 then
SELECT EXTRACT(V_Output, '/ROWSET/ROW') INTO CHILD_XML FROM DUAL;
SELECT APPENDCHILDXML(V_OUTPUT_TOTAL,'/ROWSET',CHILD_XML) INTO V_OUTPUT_TOTAL FROM DUAL;
end if;
END IF;
V_Cnt := v_cnt +1;
END LOOP;
DBMS_METADATA.CLOSE(Handle);
return v_output_TOTAL;
END;
When i execute function like
SELECT F_ALL_XML('TESTDB','SCOTT') FROM DUAL, got error.
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,1,1,'10.02.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 907
Please tell me whats this error.
Regards,
Madhavi.