DBMS_METADATA.get_ddl error

[gotcha1210]

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

[stmontgo]

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

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

[stmontgo]

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

[jmodic]

Hhe already did - see the very first post in this thread.

[stmontgo]

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?

[jmodic]

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):

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

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.