Home » SQL & PL/SQL » SQL & PL/SQL » Create Unique Constraint using Execute immediate
Create Unique Constraint using Execute immediate [message #186549] Tue, 08 August 2006 08:10 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
I made this block code to create unique constraint for '4'tables,
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?
Re: Create Unique Constraint using Execute immediate [message #186556 is a reply to message #186549] Tue, 08 August 2006 08:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Option 1:
Don't bother using an anonymous pl/sql block with dynamic SQL in it to do what you can just do with an ALTER TABLE command.

Option 2:
Show us the exact error messange plus any Dbms_Sql output that you got.
Re: Create Unique Constraint using Execute immediate [message #186585 is a reply to message #186556] Tue, 08 August 2006 10:51 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
To have more than 50 table ,i need to write 50 times 'Alter table Add...'statment.which i thought i can beter write this block code to do the same work in a 'for .. loop'way.
I've got just this error:

ORA-00911: invalid character

which character!..do you know??

bahy91
Re: Create Unique Constraint using Execute immediate [message #186588 is a reply to message #186585] Tue, 08 August 2006 11:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
bahy91 wrote on Tue, 08 August 2006 17:51

To have more than 50 table ,i need to write 50 times 'Alter table Add...'statment.which i thought i can beter write this block code to do the same work in a 'for .. loop'way.

And how long have you been busy sorting this out...?

switch the dbms_output and the execute immediate. If the execute immediate fails (which it obviously does) the dbms_output is never reached.

[Updated on: Tue, 08 August 2006 11:45]

Report message to a moderator

Re: Create Unique Constraint using Execute immediate [message #186619 is a reply to message #186588] Tue, 08 August 2006 15:10 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
So..!
Re: Create Unique Constraint using Execute immediate [message #186621 is a reply to message #186549] Tue, 08 August 2006 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>So..!
So, then you can capture the string that is failing & run it via SQL*Plus to actually see where the error exists.
Re: Create Unique Constraint using Execute immediate [message #186625 is a reply to message #186621] Tue, 08 August 2006 15:33 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Sorry..i donot fully understand .i used already dbms_output but becuse the execute immediate does not work ..i recieved only the error.
if you can explain more what you mean..that will help.
Re: Create Unique Constraint using Execute immediate [message #186628 is a reply to message #186549] Tue, 08 August 2006 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>switch the dbms_output and the execute immediate. If the execute immediate fails (which it obviously does) the dbms_output is never reached.
What part of Frank's response do you NOT understand?
Re: Create Unique Constraint using Execute immediate [message #186636 is a reply to message #186628] Tue, 08 August 2006 16:05 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
well..this is an old problem ..the response of sqlplus is the first name in the list and then sqlplus return the ORA-0091 error..but i think i understand the cause of the problem know..the full name of the constrain has a spcae in it ..is this the reason for the problem?
Re: Create Unique Constraint using Execute immediate [message #186669 is a reply to message #186636] Wed, 09 August 2006 00:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The extra space before the _CODE bit is indeed a problem.
What I was trying to show you is, like anacedent said, that if you would switch the execute immediate and the dbms_output, you would actually see the code it tries to execute. Then you can copy-paste it into sqlplus to see where the actual error is and what needs to be done to correct it.
Re: Create Unique Constraint using Execute immediate [message #186695 is a reply to message #186669] Wed, 09 August 2006 02:17 Go to previous message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
i agree with you that dbms_output will show the main cause of the error in this case,but still i think the syntax of the loop and the sql statment of execute immediate has no error,so there is no other problem but the space before' _Code'.
Previous Topic: MAIL SENDING THROUGH PL/SQL
Next Topic: query on triggers... reply asap...
Goto Forum:
  


Current Time: Sun Dec 04 13:01:27 CST 2016

Total time taken to generate the page: 0.21369 seconds