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

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

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

Quote:

---

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

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

Quote:

---

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?

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.