Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Create Unique Constraint with Execute Immediate

Create Unique Constraint with Execute Immediate

From: bahy91 <bahaa.fadam_at_lycos.nl>
Date: 8 Aug 2006 06:27:37 -0700
Message-ID: <1155043657.683474.166550@75g2000cwc.googlegroups.com>


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? Received on Tue Aug 08 2006 - 08:27:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US