7

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 separately, there are 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 in between the statements to make it work properly?

Cœur
  • 32,421
  • 21
  • 173
  • 232
Twiss
  • 83
  • 1
  • 1
  • 4

3 Answers3

18

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;
  /
Shannon Severance
  • 16,477
  • 3
  • 40
  • 63
9

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

Alex Poole
  • 161,851
  • 8
  • 150
  • 257
0

one can achieve in two ways

1) run the two blocks separately in pl/sql file.

2) in main block you can run two sbu-blocks in this way :

begin  // main block
      begin 
       .....   [pl/sql commands]
       ......
      end
      begin   
      .......  [pl/sql commands]
      .......
      end
end // main ends
Danilo Piazzalunga
  • 6,909
  • 5
  • 43
  • 65
Ravichandra Aj
  • 3
  • 2