0

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?

1 Answer 1

2

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

5
  • 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
    May 14, 2017 at 18:08
  • And If I do have an escape character in my create query what can I do? Is escaping a valid option in plsql?
    – cristid9
    May 14, 2017 at 18:20
  • @cristid9 No error on this one. pastebin.com/bJsbdyXs May 14, 2017 at 18:22
  • 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
    May 14, 2017 at 18:25
  • Ok, this is very dumb but It worked after I've restarted sql developer.
    – cristid9
    May 14, 2017 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.