CODEWITHSUNDEEP
databaseoracle-databasestored-proceduresplsql
Sammy Mak
Sammy Mak

Reputation: 11

PL/SQL INSERT ALL statement ignored and missing select statmenet

below is my procedure to insert information into 2 tables.

CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE(eNum IN NUMBER,
                                            eName VARCHAR2,
                                            dob DATE,
                                            ddr VARCHAR2,
                                            hDate DATE,
                                            lNum NUMBER,
                                            status VARCHAR2,
                                            expr VARCHAR2)
IS
BEGIN
INSERT ALL 
    INTO TRKEMPLOYEE (E#, NAME, DOB, ADDRESS, HIREDATE) VALUES (eNum, eName, dob, addr, hDate);
    INTO MECHANIC (E#, L#, STATUS, EXPERIENCE) VALUES (eNum, lNum, expr);
SELECT * FROM DUAL;
END INSERT_EMPLOYEE;
/

However, I have errors when I compile my procedure.

11/2     PL/SQL: SQL Statement ignored
12/93    PL/SQL: ORA-00928: missing SELECT keyword
13/3     PLS-00103: Encountered the symbol "INTO" when expecting one of
         the following:
         ( begin case declare end exception exit for goto if loop mod
           <an identifier> <a double-quoted delimited-identifier>
           <a bind variable> << continue close current delete fetch lock
           insert open rollback savepoint set sql execute commit forall
           merge pipe purge json_exists json_value json_query
           json_object json_array

LINE/COL ERROR
-------- -----------------------------------------------------------------
         The symbol "insert" was substituted for "INTO" t

I've checked and think that both my procedure and insert all syntax are correct, so I am not sure why I will get the errors.

Upvotes: 0

Views: 456

Answers (1)

Belayer
Belayer

Reputation: 14861

The select needs to supply all values so:

    insert all 
        into trkemployee (e#, name, dob, address, hiredate) values (enum, ename, dob, addr, hdate) 
        into mechanic (e#, l#, status, experience) values (enum, lnum, expr) 
    select eNum, eName, dob, addr, hDate,  lNum, expr from dual;

Dual in-and-of itself is a single row, single column.

Upvotes: 1

Related Questions