Re: Create Unique Constraint with Execute Immediate

bahy91 wrote:
> 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);
> tnames: =Tablesnames ('OSFILESYSTEMINSTANCES
> ','OSDATABASEINSTANCES','OSSERVERiNSTANCES','OSDATASOURCES');
> 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);
> END;
> /
> When I run this code on SQL*PLUS I get this error:
> ORA-00911: invalid character
>
> Can anyone have any clue how to solve this error?

By reading the source carefully I guess (ie without dumping it to this forum rightaway when any error occurs).
Unique(code) doesn't look like valid syntax to me, but then you don't post the table definitions, so no one will be capable to reproduce your problem.
A customary approach is to dump the generated sql to a file and run that file through sql*plus. sql*plus will spot the exact location.

-- 
Sybrand Bakker
Senior Oracle DBA