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

Question

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!

Answer 1

Yes, you need a slash / on the line after each end;.

Answer 2

Oracle can take one SQL statement or PL/SQL anonymous block at a time. (Unlike SQL Server that can except a batch at a time.) So, you have a couple of options.

1. Wrap the two anonymous blocks within another anonymous block:

   begin
     -- 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;
   end;
   

2. Tell the tool you are using to submit the PL/SQL to Oracle to send the two block seperately. How to do this will be tool specific. In SQL*PLUS, a / on a line by itself will accomplish this:

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