CODEWITHSUNDEEP
oracle-databaseoracle10gddl
maryam
maryam

Reputation: 147

Invalid Constraint name when selecting CONSTRAINT_NAME from USER_CONSTRAINTS

I want to get all the canstraint_name so i could generate there ddl in java: for each canstraint_name i'm going to execute :

DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',true);
DBMS_METADATA.GET_DDL('CONSTRAINT','Constraint_name','hr');

I tried to execute this statement :

selectSQL = "SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE OWNER='" +Schemas+ "'";

I get a long list of constraints including invalid ones like: pdpzZlEqTgUxb7qMBM8w==$0

It generates an error when executing : 

 DBMS_METADATA.GET_DDL('CONSTRAINT','pdpzZlEqTgUxb7qMBM8w==$0','hr')

can't find pdpzZlEqTgUxb7qMBM8w==$0 in Schemas='hr' (the error it's in French)

Upvotes: 0

Views: 1233

Answers (1)

user330315
user330315

Reputation:

The error message from GET_DDL suggest that those constraints belong to dropped tables (although those names start with BIN$). You cannot retrieve the DDL for those. If you don't want them you can do a 

purge recyclebin;

before retrieving the constraints from user_constraints.

Or exclude them when retrieving the list of constraints:

SELECT constraint_name 
FROM user_constraints 
WHERE constraint_name NOT LIKE 'BIN$%';

This query:

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE OWNER='" +Schemas+ "'";

doesn't really make sense. USER_CONSTRAINTS will only list constraints owned by the current user. You will never get constraints from a different schema when using USER_CONSTRAINTS.

If you want to get constraints from a different schema, you need to use ALL_CONSTRAINTS.

If you do want the current user's constraints, you can safely remove the WHERE clause.

Upvotes: 1

Related Questions