using dbms_metadata to get ddl for creating directories
Hi,
I am working on creating a test database, which is a copy of production for 11g upgrade testing. I want to get all the ddls from my production databse to create all the directories in the new test database. I have been trying to use dbms_metadata for this -
Below is the code that I have been trying to use. However, when I query the my_metadata table, there is nothing in there. Can someone please help me with this?
DROP TABLE my_metadata;
CREATE TABLE my_metadata(md CLOB);
CREATE OR REPLACE PROCEDURE progp1_directories_extract
AS
hndl NUMBER; --dbms_metadata handle
th NUMBER; --transform handle
DDL CLOB; --individual clobs extracted from the database
BEGIN
hndl := DBMS_METADATA.OPEN ('DATABASE_EXPORT'); --Open the metadata
DBMS_METADATA.set_filter (hndl, 'INCLUDE_PATH_EXPR', '=''DIRECTORY'''); --Filter data as appropriate
th := DBMS_METADATA.add_transform (hndl, 'DDL'); --Get the Transform Handle
DBMS_METADATA.set_transform_param (th, 'SQLTERMINATOR', TRUE); --Include the semicolon
LOOP
DDL := DBMS_METADATA.fetch_clob (hndl); --Loop through the result set, inserting into our temp table
EXIT WHEN DDL IS NULL;
INSERT INTO my_metadata
(md)
VALUES (DDL);
COMMIT;
END LOOP;
DBMS_METADATA.CLOSE (hndl);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
show errors