Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Create Unique Constraint with Execute Immediate
I made this block code to create unique constraint for '4'tables on
Oracle9i r2,
The main difference is that the name of the unique constraint will hold
the name of each table with a same prefix .but it did not work? Can
anyone tell me why??!
The code:
DECLARE
TYPE Tablesnames IS table OF VARCHAR2 (50);
tnames Tablesnames;
MY_ERRNUM NUMBER; MY_ERRMSG VARCHAR2 (50); SQLString_unq VARCHAR2 (4000); BEGIN DBMS_OUTPUT.ENABLE (15000);
FOR i IN tnames.FIRST .. tnames.LAST
LOOP
SQLString_unq:=
'ALTER' ||CHR(10)||
'TABLE ' || CHR(10) ||tnames(i)|| CHR(10) ||
'ADD '|| CHR(10) ||'CONSTRAINT '|| CHR(10) ||
'UN_'|| tnames(i)||' _CODE '||' UNIQUE (Code)'|| CHR(10) ||
'USING '|| CHR(10) ||'INDEX '|| CHR(10) ||'TABLESPACE '|| CHR(10)
||'&SMS_TS_INDEXES';
Execute immediate SQLString_unq;
dbms_output.put_line( SQLString_unq );
dbms_output.new_line;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
MY_ERRNUM := SQLCODE; MY_ERRMSG := SUBSTR( SQLERRM(MY_ERRNUM),1,50 );DBMS_OUTPUT.PUT_LINE(MY_ERRMSG);
Can anyone have any clue how to solve this error? Received on Tue Aug 08 2006 - 08:27:37 CDT