Assignment of dbms_metadata.get_ddl to pl/sql variable fails

oracle-11g-r2oracle-sql-developerplsql

I have the followig script

declare
    v_tmp  CLOB;
begin
   v_tmp := dbms_metadata.get_ddl('TABLE', 'FOO', 'BAR');
end;

Where FOO is the table's name and BAR is the name of the current user.
When I run the script I get:

06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints

.
The value returned by dbms_metadata.get_ddl appears to be of type CLOB so why isn't the assignement statement working?

Best Answer

CLOB is not the problem. You may have a special character somewhere in the table definition.

Above code works in general. But below is an exception, to reproduce your error:

begin
  execute immediate 'create table t1 (c1 varchar2(100) default ''' || chr(0) || ''')';
end;
/

desc t1

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(100)

declare
    v_tmp  CLOB;
begin
   v_tmp := dbms_metadata.get_ddl('TABLE', 'T1');
end;
/

Which gives:

ORA-06502: PL/SQL: numeric or value error
LPX-00217: invalid character 0 (U+0000)
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 4

The whole error message may reveal more, and I would check the specific table in the dictionary views (ALL_TABLES, ALL_TAB_COLS), and look for such "anomalies".

Related Question