Re: Create Unique Constraint with Execute Immediate

From: <fitzjarrell_at_cox.net>
Date: 8 Aug 2006 07:12:41 -0700
Message-ID: <1155046361.895293.54070@i42g2000cwa.googlegroups.com>

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?

Line 9 provides your error:

tnames: =Tablesnames
('OSFILESYSTEMINSTANCES','OSDATABASEINSTANCES','OSSERVERiNSTANCES','OSDATASOURCES');

': =' is not a valid construct in PL/SQL. It should be ':=' with no space. Your corrected code would be:

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;
/

And should run without error.

David Fitzjarrell Received on Tue Aug 08 2006 - 09:12:41 CDT