Databases Reference
In-Depth Information
------------------------------------------------------------------------------
PROMPT -- CREATE USER
------------------------------------------------------------------------------
INSERT INTO xlib ddl statements
(ddl object name, ddl object type, ddl base object, ddl text)
SELECT 'USER', 'USER', 'USER', DBMS METADATA.get ddl ('USER', '&&USR') DDL
FROM dual;
…
------------------------------------------------------------------------------
PROMPT -- FUNCTIONS
------------------------------------------------------------------------------
INSERT INTO xlib ddl statements
(ddl object name, ddl object type, ddl base object, ddl text)
SELECT NAME, 'FUNCTION', NAME,
DBMS METADATA.get ddl ('FUNCTION', NAME, '&&USR') ddl string
FROM (SELECT DISTINCT NAME
FROM user source
WHERE TYPE = 'FUNCTION');
------------------------------------------------------------------------------
PROMPT -- TRIGGERS
------------------------------------------------------------------------------
INSERT INTO xlib ddl statements
(ddl object name, ddl object type, ddl base object, ddl text)
SELECT trigger name, 'TRIGGER', table name,
DBMS METADATA.get ddl ('TRIGGER', trigger name, '&&USR') ddl string
FROM (SELECT trigger name, table name
FROM user triggers
WHERE trigger name NOT LIKE 'BIN$%');
------------------------------------------------------------------------------
PROMPT -- PACKAGES
------------------------------------------------------------------------------
INSERT INTO xlib ddl statements
(ddl object name, ddl object type, ddl base object, ddl text)
SELECT NAME, 'PACKAGE SPEC', NAME,
DBMS METADATA.get ddl ('PACKAGE SPEC', NAME, '&&USR') ddl string
FROM (SELECT DISTINCT NAME
FROM user source
WHERE TYPE = 'PACKAGE');
INSERT INTO xlib ddl statements
(ddl object name, ddl object type, ddl base object, ddl text)
SELECT NAME, 'PACKAGE BODY', NAME,
DBMS METADATA.get ddl ('PACKAGE BODY', NAME, '&&USR') ddl string
FROM (SELECT DISTINCT NAME
FROM user source
WHERE TYPE = 'PACKAGE BODY');
…
