DBMS_METADATA

#1

Hi,

I've written a quick script (below) to go through and dump the DDL for
each users objects in their schema to a log file on disk. The script
runs fine except for a couple of small things.

First, for certain Packages I am getting this error:

PACKAGE - LOAD_ZN - ORA-06502: PL/SQL: numeric or value error
PACKAGE_BODY - LOAD_ZN - ORA-06502: PL/SQL: numeric or value error

You'll see in the script I select the data into a CLOB data type.
These packages are only like 3000 or 4000 lines, so I am not sure why
I am getting this error since a CLOB should handle up to 4GB, right?

Also, tables with Nested Tables give me this error on the actual
Nested Table entry:

TABLE - ANN_BAL_SHEET - ORA-31603: object "ANN_BAL_SHEET" of type
TABLE not found in schema "DATA_HOLDER"

That is not too bad because the TYPES are exported and the physical
table DDL is intact. But is there a way to ignore these object types
so the error is not thrown? They are defined as TABLE in the
dictionary.

Here is the actual script:

DECLARE
v_ddl CLOB;
v_file_id UTL_FILE.FILE_TYPE;

BEGIN
FOR v_rec IN (SELECT username FROM dba_users) LOOP
v_file_id := OPEN_FILES('/tmp', v_rec.username || '_ddl.log',
'w');
FOR x_rec IN (SELECT object_name,
DECODE(object_type,'DATABASE
LINK','DB_LINK',
'MATERIALIZED
VIEW','MATERIALIZED_VIEW',
'PACKAGE
BODY','PACKAGE_BODY', object_type) object_type
FROM dba_objects
WHERE owner = v_rec.username
AND SUBSTR(object_name,1,3) <> 'BIN'
AND object_type IN ('DATABASE LINK','DIRECTORY',
'FUNCTION', 'INDEX',
'MATERIALIZED VIEW',
'PACKAGE', 'PACKAGE BODY',
'PROCEDURE',
'SEQUENCE', 'SYNONYM', 'TABLE',

'TRIGGER','TYPE','VIEW')
ORDER BY object_type, object_name) LOOP
BEGIN
SELECT DBMS_METADATA.GET_DDL(x_rec.object_type,
x_rec.object_name, v_rec.username) INTO v_ddl FROM dual;
UTL_FILE.PUT_LINE(v_file_id,CHR(10)||CHR(10)||v_dd l||CHR(10)||
CHR(10));

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('HERE: ' || v_rec.username || ' - ' ||
x_rec.object_type || ' - ' || x_rec.object_name || ' - ' || SQLERRM);
END;
END LOOP;
UTL_FILE.FCLOSE_ALL;
END LOOP;
END;
/

#2

Hi,

Am 25.10.2011 20:22, schrieb ExecMan:

Quote:

Hi,

I've written a quick script (below) to go through and dump the DDL for
each users objects in their schema to a log file on disk. The script
runs fine except for a couple of small things.

First, for certain Packages I am getting this error:

PACKAGE - LOAD_ZN - ORA-06502: PL/SQL: numeric or value error
PACKAGE_BODY - LOAD_ZN - ORA-06502: PL/SQL: numeric or value error

You'll see in the script I select the data into a CLOB data type.
These packages are only like 3000 or 4000 lines, so I am not sure why
I am getting this error since a CLOB should handle up to 4GB, right?

[...]

Quote:

Here is the actual script:

DECLARE
v_ddl CLOB;
v_file_id UTL_FILE.FILE_TYPE;

[...]

Quote:

UTL_FILE.PUT_LINE(v_file_id,CHR(10)||CHR(10)||v_dd l||CHR(10)||
CHR(10));

Your call to UTL_FILE.PUT_LINE typecasts the expression with v_ddl from CLOB
to VARCHAR2; this can only hold 32767 bytes/characters (depending on your
NLS_LENGTH_SEMANTICS). Most likely the code you dump is larger than 32K, no?

Also, DBMS_METADATA will deliver CLOBs which already contain line breaks.
UTL_FILE.PUT_LINE, otoh, will also add a (superflouos) line break after each
call, which you don't want in this case.

Regards
Peter

--
The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain

#3

On Oct 25, 5:44*pm, Peter Schneider <pschneider1... (AT) googlemail (DOT) com>
wrote:

Quote:

Hi,

Am 25.10.2011 20:22, schrieb ExecMan:

Hi,

I've written a quick script (below) to go through and dump the DDL for
each users objects in their schema to a log file on disk. *The script
runs fine except for a couple of small things.

First, for certain Packages I am getting this error:

PACKAGE - LOAD_ZN - ORA-06502: PL/SQL: numeric or value error
PACKAGE_BODY - LOAD_ZN - ORA-06502: PL/SQL: numeric or value error

You'll see in the script I select the data into a CLOB data type.
These packages are only like 3000 or 4000 lines, so I am not sure why
I am getting this error since a CLOB should handle up to 4GB, right?

[...]

Here is the actual script:

DECLARE
v_ddl * * * * CLOB;
v_file_id * * UTL_FILE.FILE_TYPE;

[...]

* * * * *UTL_FILE.PUT_LINE(v_file_id,CHR(10)||CHR(10)||v_d dl||CHR(10)||
CHR(10));

Your call to UTL_FILE.PUT_LINE typecasts the expression with v_ddl from CLOB
to VARCHAR2; this can only hold 32767 bytes/characters (depending on your
NLS_LENGTH_SEMANTICS). Most likely the code you dump is larger than 32K, no?

Also, DBMS_METADATA will deliver CLOBs which already contain line breaks.
UTL_FILE.PUT_LINE, otoh, will also add a (superflouos) line break after each
call, which you don't want in this case.

Regards
Peter

--
The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain

ExecMan, Have you considered just using SQLPlus's spool feature to
capture the DDL in a text file?

HTH -- Mark D Powell --

#4

On Tue, 25 Oct 2011 11:22:38 -0700, ExecMan wrote:

Quote:

Also, tables with Nested Tables give me this error on the actual Nested
Table entry:

TABLE - ANN_BAL_SHEET - ORA-31603: object "ANN_BAL_SHEET" of type TABLE
not found in schema "DATA_HOLDER"

You should filter out nested tables, using something like this:

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u
WHERE u.nested='NO'
USER_TABLES view has a column which describes whether it's a nested table
or not.

SQL> desc user_tables
Name Null? Type
----------------------------------------- --------
----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
CACHE VARCHAR2(20)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(12)
DROPPED VARCHAR2(3)
READ_ONLY VARCHAR2(3)
SEGMENT_CREATED VARCHAR2(3)
RESULT_CACHE VARCHAR2(7)

SQL>

--
http://mgogala.byethost5.com

#5

On Oct 26, 8:26*am, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:

Quote:

On Tue, 25 Oct 2011 11:22:38 -0700, ExecMan wrote:
Also, tables with Nested Tables give me this error on the actual Nested
Table entry:

TABLE - ANN_BAL_SHEET - ORA-31603: object "ANN_BAL_SHEET" of type TABLE
not found in schema "DATA_HOLDER"

You should filter out nested tables, using something like this:

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
* * *FROM USER_TABLES u
* * *WHERE u.nested='NO'
USER_TABLES view has a column which describes whether it's a nested table
or not.

SQL> desc user_tables
*Name * * * * * * * * * * * * * * * * * * *Null? * *Type
*----------------------------------------- --------
----------------------------
*TABLE_NAME * * * * * * * * * * * * * * * *NOT NULL VARCHAR2(30)
*TABLESPACE_NAME * * * * * * * * * * * * * * * * * *VARCHAR2(30)
*CLUSTER_NAME * * * * * * * * * * * * * * * * * * * VARCHAR2(30)
*IOT_NAME * * * * * * * * * * * * * * * * * * * * * VARCHAR2(30)
*STATUS * * * * * * * * * * * * * * * * * * * * * * VARCHAR2(8)
*PCT_FREE * * * * * * * * * * * * * * * * * * * * * NUMBER
*PCT_USED * * * * * * * * * * * * * * * * * * * * * NUMBER
*INI_TRANS * * * * * * * * * * * * * * * * * * * * *NUMBER
*MAX_TRANS * * * * * * * * * * * * * * * * * * * * *NUMBER
*INITIAL_EXTENT * * * * * * * * * * * * * * * * * * NUMBER
*NEXT_EXTENT * * * * * * * * * * * * * * * * * * * *NUMBER
*MIN_EXTENTS * * * * * * * * * * * * * * * * * * * *NUMBER
*MAX_EXTENTS * * * * * * * * * * * * * * * * * * * *NUMBER
*PCT_INCREASE * * * * * * * * * * * * * * * * * * * NUMBER
*FREELISTS * * * * * * * * * * * * * * * * * * * * *NUMBER
*FREELIST_GROUPS * * * * * * * * * * * * * * * * * *NUMBER
*LOGGING * * * * * * * * * * * * * * * * * * * * * *VARCHAR2(3)
*BACKED_UP * * * * * * * * * * * * * * * * * * * * *VARCHAR2(1)
*NUM_ROWS * * * * * * * * * * * * * * * * * * * * * NUMBER
*BLOCKS * * * * * * * * * * * * * * * * * * * * * * NUMBER
*EMPTY_BLOCKS * * * * * * * * * * * * * * * * * * * NUMBER
*AVG_SPACE * * * * * * * * * * * * * * * * * * * * *NUMBER
*CHAIN_CNT * * * * * * * * * * * * * * * * * * * * *NUMBER
*AVG_ROW_LEN * * * * * * * * * * * * * * * * * * * *NUMBER
*AVG_SPACE_FREELIST_BLOCKS * * * * * * * * * * * * *NUMBER
*NUM_FREELIST_BLOCKS * * * * * * * * * * * * * * * *NUMBER
*DEGREE * * * * * * * * * * * * * * * * * * * * * * VARCHAR2(40)
*INSTANCES * * * * * * * * * * * * * * * * * * * * *VARCHAR2(40)
*CACHE * * * * * * * * * * * * * * * * * * * * * * *VARCHAR2(20)
*TABLE_LOCK * * * * * * * * * * * * * * * * * * * * VARCHAR2(8)
*SAMPLE_SIZE * * * * * * * * * * * * * * * * * * * *NUMBER
*LAST_ANALYZED * * * * * * * * * * * * * * * * * * *DATE
*PARTITIONED * * * * * * * * * * * * * * * * * * * *VARCHAR2(3)
*IOT_TYPE * * * * * * * * * * * * * * * * * * * * * VARCHAR2(12)
*TEMPORARY * * * * * * * * * * * * * * * * * * * * *VARCHAR2(1)
*SECONDARY * * * * * * * * * * * * * * * * * * * * *VARCHAR2(1)
*NESTED * * * * * * * * * * * * * * * * * * * * * * VARCHAR2(3)
*BUFFER_POOL * * * * * * * * * * * * * * * * * * * *VARCHAR2(7)
*FLASH_CACHE * * * * * * * * * * * * * * * * * * * *VARCHAR2(7)
*CELL_FLASH_CACHE * * * * * * * * * * * * * * * * * VARCHAR2(7)
*ROW_MOVEMENT * * * * * * * * * * * * * * * * * * * VARCHAR2(8)
*GLOBAL_STATS * * * * * * * * * * * * * * * * * * * VARCHAR2(3)
*USER_STATS * * * * * * * * * * * * * * * * * * * * VARCHAR2(3)
*DURATION * * * * * * * * * * * * * * * * * * * * * VARCHAR2(15)
*SKIP_CORRUPT * * * * * * * * * * * * * * * * * * * VARCHAR2(8)
*MONITORING * * * * * * * * * * * * * * * * * * * * VARCHAR2(3)
*CLUSTER_OWNER * * * * * * * * * * * * * * * * * * *VARCHAR2(30)
*DEPENDENCIES * * * * * * * * * * * * * * * * * * * VARCHAR2(8)
*COMPRESSION * * * * * * * * * * * * * * * * * * * *VARCHAR2(8)
*COMPRESS_FOR * * * * * * * * * * * * * * * * * * * VARCHAR2(12)
*DROPPED * * * * * * * * * * * * * * * * * * * * * *VARCHAR2(3)
*READ_ONLY * * * * * * * * * * * * * * * * * * * * *VARCHAR2(3)
*SEGMENT_CREATED * * * * * * * * * * * * * * * * * *VARCHAR2(3)
*RESULT_CACHE * * * * * * * * * * * * * * * * * * * VARCHAR2(7)

SQL

--http://mgogala.byethost5.com

Thanks for everyone's help. I've resolved the 32k issues pointed out
before. The last issue with the nested tables is not quite so clear.

I have a type defined:

CREATE OR REPLACE
TYPE CONSENSUS_RECOM_TYPE AS OBJECT (
sequence NUMBER,
current_value NUMBER,
days_ago_30 NUMBER,
days_ago_60 NUMBER,
days_ago_90 NUMBER);
/

CREATE OR REPLACE
TYPE CONSENSUS_RECOM_TAB AS TABLE OF consensus_recom_type;
/

Then I use that in a table as a nested table:

CREATE TABLE STOCK_INFO (
.
.
LAST_DIV_AMOUNT NUMBER,
CONSENSUS_RECOMMENDATIONS CONSENSUS_RECOM_TAB)
.
.
NESTED TABLE CONSENSUS_RECOMMENDATIONS STORE AS
CONSENSUS_RECOMMENDATIONS

So, CONSENSUS_RECOMMENDATIONS appears in the data dictionary as a
TABLE, but it is not a table.......it just appears in DBA_OBJECTS as a
table type, but then cannot be matched to DBA_TABLES.

Any easy way to ignore this?

Much thanks for your time.

#6

On Wed, 26 Oct 2011 08:39:05 -0700, ExecMan wrote:

Quote:

So, CONSENSUS_RECOMMENDATIONS appears in the data dictionary as a TABLE,
but it is not a table.......it just appears in DBA_OBJECTS as a table
type, but then cannot be matched to DBA_TABLES.

Any easy way to ignore this?

Much thanks for your time.

There are two ways:

1) Avoid nested tables, by specifying the "NESTED" flag, as shown in
the example query.
2) Use USER_SEGMENTS instead of the USER_TABLES and specify
SEGMENT_TYPE='TABLE'. Nested tables do not have separate table
segments.

--
http://mgogala.byethost5.com

#7

On Oct 26, 9:04*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:

Quote:

On Wed, 26 Oct 2011 08:39:05 -0700, ExecMan wrote:
So, CONSENSUS_RECOMMENDATIONS appears in the data dictionary as a TABLE,
but it is not a table.......it just appears in DBA_OBJECTS as a table
type, but then cannot be matched to DBA_TABLES.

Any easy way to ignore this?

Much thanks for your time.

There are two ways:

1) Avoid nested tables, by specifying the "NESTED" flag, as shown in
* *the example query.
2) Use USER_SEGMENTS instead of the USER_TABLES and specify
* *SEGMENT_TYPE='TABLE'. Nested tables do not have separate table
* *segments.

--http://mgogala.byethost5.com

But that's just it......it appears in DBA_OBJECTS as a table, but not
in DBA_TABLES:

SQL> select object_type from dba_objects where object_name =
'CONSENSUS_RECOMMENDATIONS';

OBJECT_TYPE
-------------------
TABLE

SQL> select nested from dba_tables where table_name =
'CONSENSUS_RECOMMENDATIONS';

no rows selected

Thoughts?

#8

On Oct 26, 9:04*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:

Quote:

On Wed, 26 Oct 2011 08:39:05 -0700, ExecMan wrote:
So, CONSENSUS_RECOMMENDATIONS appears in the data dictionary as a TABLE,
but it is not a table.......it just appears in DBA_OBJECTS as a table
type, but then cannot be matched to DBA_TABLES.

Any easy way to ignore this?

Much thanks for your time.

There are two ways:

1) Avoid nested tables, by specifying the "NESTED" flag, as shown in
* *the example query.
2) Use USER_SEGMENTS instead of the USER_TABLES and specify
* *SEGMENT_TYPE='TABLE'. Nested tables do not have separate table
* *segments.

--http://mgogala.byethost5.com

My follow up. Pretty messed up:

SQL> select segment_type from user_segments where segment_name =
'CONSENSUS_RECOMMENDATIONS';

SEGMENT_TYPE
------------------
NESTED TABLE

Why the hell does it say type TABLE and not appear in DBA_TABLES.
Real nice Oracle. I expect something like that from Microsoft.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DBMS_METADATA

comp.databases.oracle.server comp.databases.oracle.server

DBMS_METADATA - 10-25-2011 , 01:22 PM

Re: DBMS_METADATA - 10-25-2011 , 04:44 PM

Re: DBMS_METADATA - 10-26-2011 , 08:08 AM

Re: DBMS_METADATA - 10-26-2011 , 08:26 AM

Re: DBMS_METADATA - 10-26-2011 , 10:39 AM

Re: DBMS_METADATA - 10-26-2011 , 09:04 PM

Re: DBMS_METADATA - 10-26-2011 , 10:19 PM

Re: DBMS_METADATA - 10-26-2011 , 10:20 PM