PL-SQL - Two statements with begin and end, run fine seperately but not together?

Posted by Twiss on Stack Overflow See other posts from Stack Overflow or by Twiss
Published on 2010-06-11T16:18:35Z Indexed on 2010/06/11 16:22 UTC
Read the original article Hit count: 215

Filed under:
|
|
|
|

Hi all,

Just wondering if anyone can help with this, I have two PLSQL statements for altering tables (adding extra fields) and they are as follows:

-- Make GC_NAB field for Next Action By Dropdown

begin
if 'VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')>0 then
  execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))';
elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) or
  'VARCHAR2' = 'VARCHAR2' then
  execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))';
else
  execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)';
end if;
commit;
end;

-- Make GC_NABID field for Next Action By Dropdown

begin
if 'NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')>0 then
  execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))';
elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) or
  'NUMBER' = 'VARCHAR2' then
  execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())';
else
  execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)';
end if;
commit;
end;

When I run these two queries seperately, no problems. However, when run together as shown above, Oracle gives me an error when it starts the second statement:

Error report:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "BEGIN" 
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I'm assuming that this means the first statement is not terminated properly... is there anything I should put inbetween the statements to make it work properly?

Thanks in advance everyone!

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about plsql