Assignment of dbms_metadata.get_ddl to pl/sql variable fails

Question

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?

Answer 1

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".

• cristid9: This is the create script generate by SQLDeveloper for that table: CREATE TABLE USERS ( ID NUMBER(10, 0) NOT NULL , NAME VARCHAR2(255 BYTE) NOT NULL , USERNAME VARCHAR2(255 BYTE) NOT NULL , USER_ROLE VARCHAR2(255 BYTE) NOT NULL , CREATED_AT TIMESTAMP(6) , UPDATED_AT TIMESTAMP(6) ) LOGGING TABLESPACE APLICATIE PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) NOPARALLEL; It appears to look ok.
• cristid9: And If I do have an escape character in my create query what can I do? Is escaping a valid option in plsql?
• Balazs Papp: @cristid9 No error on this one. pastebin.com/bJsbdyXs
• cristid9: If I run this query select dbms_metadata.get_ddl('TABLE', 'USERS', 'STUDENT') from dual in SQLDeveloper I get the following error pastebin.com/buc9UMnq
• cristid9: Ok, this is very dumb but It worked after I've restarted sql developer.