DBMS_METADATA

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',

                  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_ddl||CHR(10)||

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