-
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
I'm stmontgo and I approve of this message
-
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 ..
-
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
-
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?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|