PLS-00103: Error when trying to use Execute Immediate

Question

I'm trying to insert records into a tables that i have already created using an Execute Immediate (requirement for assignment). The code should insert rows and if there is an error roll back to the last row successfully inserted.

I broke the code into separate blocks and ran them separately and it's getting hung up on the second value of the first row being inserted 'ACCOUNTING', and 'KING'. I get the following error:

PLS-00103: Encountered the symbol "KING" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

SET SERVEROUTPUT ON;

declare 
  sql_stmt VARCHAR2(150);
BEGIN
  sql_stmt := 'INSERT INTO dept VALUES  
  (10, 'ACCOUNTING', 'NEW YORK'),    
  (20, 'RESEARCH',   'DALLAS'),    
  (30, 'SALES',      'CHICAGO'),    
  (40, 'OPERATIONS', 'WASHINGTON (D.C.)'),    
  (50, 'MARKETING', 'BOSTON')'; 

SAVEPOINT do_dept_insert;
EXECUTE IMMEDIATE sql_stmt
USING DEPNO, DNAME, LOC;

EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK TO do_dept_insert;
commit;
end;
/

SET SERVEROUTPUT ON;
declare 
  sql_stmt_emp VARCHAR2(150);
begin
sql_stmt_emp := 'INSERT INTO emp VALUES
(7839, 'KING',   'PRESIDENT',  NULL, '17-NOV-81', 5000, NULL, 10),  
(7698, 'BLAKE',  'MANAGER',  7839, '01-MAY-81', 2850, NULL, 30),  
(7782, 'CLARK',  'MANAGER',  7839, '09-JUN-81', 2450, NULL, 10),  
(7566, 'JONES',  'MANAGER',  7839, '02-APR-81', 2975, NULL, 20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-81', 1250, 1400, 30),  
(7499, 'ALLEN',  'SALESMAN', 7698, '20-FEB-81', 1600,  300, 30),  
(7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-81', 1500, NULL, 30),  
(7900, 'JAMES',  'CLERK',    7698, '03-DEC-81',  950, NULL, 30),  
(7521, 'WARD',   'SALESMAN', 7698, '22-FEB-81', 1250,  500, 30),  
(7902, 'FORD',   'ANALYST',  7566, '03-DEC-81', 3000, NULL, 20),  
(7369, 'SMITH',  'CLERK',    7902, '17-DEC-81',  800, NULL, 20),  
(7788, 'SCOTT',  'ANALYST',  7566, '09-DEC-82', 4000, NULL, 20),  
(7876, 'ADAMS',  'CLERK',    7788, '12-JAN-83', 1100, NULL, 20),  
(7934, 'MILLER', 'CLERK',    7782, '22-JAN-82', 1300, NULL, 10),  
(7698, 'BLAKE',  'MANAGER',  7839, '01-MAY-81', 2850, NULL, 30),  
(7935, 'JONES', 'ACCOUNT',    7782, '22-JAN-82', 1700, NULL, 10)';

SAVEPOINT do_emp_insert;

EXECUTE IMMEDIATE sql_stmt_emp
USING EMPNO, ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO;

EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK TO do_emp_insert;
commit;
END;
/

See above:

The code should insert rows and if there is an error roll back to the last row successfully inserted. However, I have not been able to insert any rows and receive the following error:

       PLS-00103: Encountered the symbol "KING" when expecting one of the 
       following:

       * & = - + ; < / > at in is mod remainder not rem
       <an exponent (**)> <> or != or ~= >= <= <> and or like like2
       like4 likec between || multiset member submultiset
       06550. 00000 -  "line %s, column %s:\n%s"
       *Cause:    Usually a PL/SQL compilation error.
       *Action:

Answer 1

Well, assignment is what it is, so you have to follow the instructions.

Here's a working example, with some notes:

• variable should be long enough; 150 characters looks suspiciously too small
• problem you'll encounter regards single quotes; either double them when necessary, or (a simpler option), use the q-quoting mechanism
• after statement is created, first dbms_output.put_line it to verify whether it looks OK or not. If not, fix it. If yes, use it in execute immediate
• savepoint: there's actually nothing to be rolled back, and it can't be the way you wrote the procedure. Insert statement can't be broken into single rows, it inserts either all or nothing. If you want to rollback to the last successful insertion, then you'll have to insert row-by-row (perhaps in a loop?), otherwise it doesn't make sense.

Prepare environment:

SQL> create table dept_new as select * from dept where 1 = 2;

Table created.

SQL> set serveroutput on;

The procedure:

SQL> declare
  2    sql_stmt varchar2(1000);      --> should be large enough
  3  begin
  4    -- note the q-quoting mechanism
  5    sql_stmt := q'[insert all
  6      into dept_new (deptno, dname, loc) values (10, 'Accounting', 'New York')
  7      into dept_new (deptno, dname, loc) values (20, 'Research'  , 'Dallas')
  8      select * from dual]';
  9
 10    dbms_output.put_line(sql_stmt);    --> verify whether it looks OK; if it does,
 11                                       --  then use it in EXECUTE IMMEDIATE
 12    savepoint do_dept_insert;
 13
 14    execute immediate sql_stmt;
 15  exception
 16    when others then
 17      rollback to do_dept_insert;
 18  end;
 19  /
insert all
    into dept_new (deptno, dname, loc) values (10, 'Accounting', 'New York')
    into dept_new (deptno, dname, loc) values (20, 'Research'  , 'Dallas')
    select * from dual

PL/SQL procedure successfully completed.

SQL> select * From dept_new;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 Accounting     New York
        20 Research       Dallas

SQL>

Answer 2

The following works for me. (See dbfiddle)

create table DEPT (ID number(2) primary key, NAME varchar2(50), LOCN varchar2(50));

INSERT ALL
  INTO dept (id, name, locn) VALUES (10, 'ACCOUNTING', 'NEW YORK')
  INTO dept (id, name, locn) VALUES (20, 'RESEARCH',   'DALLAS')
  INTO dept (id, name, locn) VALUES (30, 'SALES',      'CHICAGO')
  INTO dept (id, name, locn) VALUES (40, 'OPERATIONS', 'WASHINGTON (D.C.)')
  INTO dept (id, name, locn) VALUES (50, 'MARKETING', 'BOSTON')
  SELECT * FROM dual;