Oracle9i Supplied PL/SQL Packages and Types Reference Release 1 (9.0.1) Part Number A89852-02 |
|
DBMS_METADATA , 10 of 11
CLOSE
invalidates the handle returned by OPEN
and cleans up the associated state.
PROCEDURE close (handle IN NUMBER);
Parameter | Description |
---|---|
|
The handle returned from |
You can prematurely terminate the stream of objects established by OPEN
.
FETCH_xxx
returns NULL,
indicating no more objects, a call to CLOSE
is made transparently. In this case, you can still call CLOSE
on the handle and not get an exception. (The call to CLOSE
is not required.)
CLOSE
after the single FETCH_xxx
call to free resources held by the handle.
This example retrieves the creation DDL for all tables in the current schema whose
names begin with PAYROLL
. For each table it also returns the creation DDL for the
indexes defined on the table. The returned DDL is written to an output file.
CREATE OR REPLACE PACKAGE dbms_metadata_example AS
PROCEDURE get_payroll_tables;
END;
/
CREATE OR REPLACE PACKAGE BODY dbms_metadata_example AS
-- Global Variables
fileHandle UTL_FILE.FILE_TYPE;
-- Exception initialization
file_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT(file_not_found, -1309);
-- Package-private routine to write a CLOB to an output file.
PROCEDURE write_lob(doc IN CLOB) IS
outString varchar2(32760);
cloblen number;
offset number := 1;
amount number;
BEGIN
cloblen := dbms_lob.getlength(doc);
WHILE cloblen > 0
LOOP
IF cloblen > 32760 THEN
amount := 32760;
ELSE
amount := cloblen;
END IF;
outString := dbms_lob.substr(doc, amount, offset);
utl_file.put(fileHandle, outString);
utl_file.fflush(fileHandle);
offset := offset + amount;
cloblen := cloblen - amount;
END LOOP;
RETURN;
END;
-- Public routines
-- GET_PAYROLL_TABLES: Fetch DDL for payroll tables and their indexes.
PROCEDURE get_payroll_tables IS
tableOpenHandle NUMBER;
indexOpenHandle NUMBER;
tableTransHandle NUMBER;
indexTransHandle NUMBER;
schemaName VARCHAR2(30);
tableName VARCHAR2(30);
tableDDLs sys.ku$_ddls;
tableDDL sys.ku$_ddl;
parsedItems sys.ku$_parsed_items;
indexDDL CLOB;
BEGIN
-- open the output file... note that the 1st param. (dir. path) must be
-- included in the database's UTL_FILE_DIR init. parameter.
--
BEGIN
fileHandle := utl_file.fopen('/private/xml', 'ddl.out', 'w', 32760);
EXCEPTION
WHEN OTHERS THEN
RAISE file_not_found;
END;
-- Open a handle for tables in the current schema.
tableOpenHandle := dbms_metadata.open('TABLE');
-- Call 'set_count' to request retrieval of one table at a time.
-- This call is not actually necessary since 1 is the default.
dbms_metadata.set_count(tableOpenHandle, 1);
-- Retrieve tables whose name starts with 'PAYROLL'. When the filter is
-- 'NAME_EXPR', the filter value string must include the SQL operator. This
-- gives the caller flexibility to use LIKE, IN, NOT IN, subqueries, etc.
dbms_metadata.set_filter(tableOpenHandle, 'NAME_EXPR', 'LIKE ''PAYROLL%''');
-- Tell Metadata API to parse out each table's schema and name separately
-- so we can use them to set up the calls to retrieve its indexes.
dbms_metadata.set_parse_item(tableOpenHandle, 'SCHEMA');
dbms_metadata.set_parse_item(tableOpenHandle, 'NAME');
-- Add the DDL transform so we get SQL creation DDL
tableTransHandle := dbms_metadata.add_transform(tableOpenHandle, 'DDL');
-- Tell the XSL stylesheet we don't want physical storage information (storage,
-- tablespace, etc), and that we want a SQL terminator on each DDL. Notice that
-- these calls use the transform handle, not the open handle.
dbms_metadata.set_transform_param(tableTransHandle,
'SEGMENT_ATTRIBUTES', FALSE);
dbms_metadata.set_transform_param(tableTransHandle,
'SQLTERMINATOR', TRUE);
-- Ready to start fetching tables. We use the FETCH_DDL interface (rather than
-- FETCH_XML or FETCH_CLOB). This interface returns a SYS.KU$_DDLS; a table of
-- SYS.KU$_DDL objects. This is a table because some object types return
-- multiple DDL statements (like types / pkgs which have create header and
-- body statements). Each KU$_DDL has a CLOB containing the 'CREATE TABLE'
-- statement plus a nested table of the parse items specified. In our case,
-- we asked for two parse items; Schema and Name.
LOOP
tableDDLs := dbms_metadata.fetch_ddl(tableOpenHandle);
EXIT WHEN tableDDLs IS NULL; -- Get out when no more payroll tables
-- In our case, we know there is only one row in tableDDLs (a KU$_DDLS tbl obj)
-- for the current table. Sometimes tables have multiple DDL statements,
-- e.g., if constraints are applied as ALTER TABLE statements,
-- but we didn't ask for that option.
-- So, rather than writing code to loop through tableDDLs,
-- we'll just work with the 1st row.
--
-- First, write the CREATE TABLE text to our output file, then retrieve the
-- parsed schema and table names.
tableDDL := tableDDLs(1);
write_lob(tableDDL.ddltext);
parsedItems := tableDDL.parsedItems;
-- Must check the name of the returned parse items as ordering isn't guaranteed
FOR i IN 1..2 LOOP
IF parsedItems(i).item = 'SCHEMA'
THEN
schemaName := parsedItems(i).value;
ELSE
tableName := parsedItems(i).value;
END IF;
END LOOP;
-- Then use the schema and table names to set up a 2nd stream for retrieval of
-- the current table's indexes.
-- (Note that we don't have to specify a SCHEMA filter for the indexes,
-- since SCHEMA defaults to the value of BASE_OBJECT_SCHEMA.)
indexOpenHandle := dbms_metadata.open('INDEX');
dbms_metadata.set_filter(indexOpenHandle,'BASE_OBJECT_SCHEMA',schemaName);
dbms_metadata.set_filter(indexOpenHandle,'BASE_OBJECT_NAME',tableName);
-- Add the DDL transform and set the same transform options we did for tables
indexTransHandle := dbms_metadata.add_transform(indexOpenHandle, 'DDL');
dbms_metadata.set_transform_param(indexTransHandle,
'SEGMENT_ATTRIBUTES', FALSE);
dbms_metadata.set_transform_param(indexTransHandle,
'SQLTERMINATOR', TRUE);
-- Retrieve index DDLs as CLOBs and write them to the output file.
LOOP
indexDDL := dbms_metadata.fetch_clob(indexOpenHandle);
EXIT WHEN indexDDL IS NULL;
write_lob(indexDDL);
END LOOP;
-- Free resources allocated for index stream.
dbms_metadata.close(indexOpenHandle);
END LOOP;
-- Free resources allocated for table stream and close output file.
dbms_metadata.close(tableOpenHandle);
utl_file.fclose(fileHandle);
RETURN;
END; -- of procedure get_payroll_tables
END dbms_metadata_example;
/
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|