DBMS_METADATA.get_ddl error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: DBMS_METADATA.get_ddl error

Hybrid View

  1. #1
    Join Date
    Nov 2004
    Posts
    6

    DBMS_METADATA.get_ddl error

    Hi,

    While running the following SQL I am getting the error ..
    Any idea why this is occurring .. and how to prevent it ..



    1 SELECT DBMS_METADATA.get_ddl('CONSTRAINT'
    2 ,constraint_name)
    3* FROM all_constraints

    SQL> /

    ERROR:
    ORA-31603: object "SYS_C00532" of type CONSTRAINT not found in schema
    "EDW_2_3_0"
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
    ORA-06512: at "SYS.DBMS_METADATA", line 628
    ORA-06512: at "SYS.DBMS_METADATA", line 1221
    ORA-06512: at line 1

    no rows selected



    Thanks in advance

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    this works for me....

    SQL> set long 9999
    SQL> select dbms_metadata.get_ddl('CONSTRAINT','MY_PK') from dual;

    DBMS_METADATA.GET_DDL('CONSTRAINT','MY_PK')
    --------------------------------------------------------------------------------

    ALTER TABLE "SYS"."MY_TEST" ADD CONSTRAINT "MY_PK" PRIMARY KEY ("ID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "SYSTEM" ENABLE
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Nov 2004
    Posts
    6
    Hi,

    I have oracle9i version 9.2.0.4.0.
    If I try to query any SYS constarint I get this error.
    But the same works for other constraints.

    Do U have any go-around this problem ...

    Thanks ..

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by gotcha1210
    Hi,

    I have oracle9i version 9.2.0.4.0.
    If I try to query any SYS constarint I get this error.
    But the same works for other constraints.

    Do U have any go-around this problem ...

    Thanks ..
    paste the code with the error
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Hhe already did - see the very first post in this thread.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by jmodic
    Hhe already did - see the very first post in this thread.
    yeah , i am admittedly confused

    I guess what I was prompting for is the magic code to gen all the constraints in the db with one select without a pl/sql block

    My code get's constraint code for one constraint, you need the block to get all the constraints ddl through, right?
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The problem here seems to be with NOT NULL constraints that are system generated as CHECK constraints. Those constraints seems to be correctly incorporated in CRATE TABLE generated with DBMS_METADATA('TABLE', table_name), but fail with DBMS_METADATA('CONSTRAINT, constraint_name).

    I thought those system-generated NOT NULL check constraints could be filtered out by using (this should be at least the closest approximation):
    Code:
    SELECT DBMS_METADATA.get_ddl('CONSTRAINT' ,constraint_name)
    FROM USER_CONSTRAINTS
    WHERE NOT (constraint_type = 'C' AND AND search_condition LIKE '%" NOT NULL';)
    but unfortuantely the column SEARCH_CONDITION in USER_CONSTRAINTS is of type long.

    So I guess the closest approximation you could get with those check constraints would be
    Code:
    SELECT DBMS_METADATA.get_ddl('CONSTRAINT' ,constraint_name)
    FROM USER_CONSTRAINTS
    WHERE NOT (constraint_type = 'C' AND GENERATED = 'GENERATED NAME');
    This of course could filter out some o your "something-different-than-not-null-check-costraint" that you didn't bother to name explicitely.
    Last edited by jmodic; 11-24-2004 at 05:21 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width