Home » SQL & PL/SQL » SQL & PL/SQL » schema export using dbms_metadata (oracle 10g)
schema export using dbms_metadata [message #440772] Tue, 26 January 2010 23:42
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
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.
Previous Topic: Extract pl/sql Result into a text file without UTL_FILE pkg and spool
Next Topic: excelsol.sql file
Goto Forum:
  


Current Time: Tue Sep 27 00:53:44 CDT 2016

Total time taken to generate the page: 0.16568 seconds