CODEWITHSUNDEEP
oracle-databasestored-proceduresinsert
Dmitry
Dmitry

Reputation: 161

INSERT stored procedure (PL/SQL)

I'm new to PL/SQL and stored procedures in general. I'm trying to write a stored procedure that will be executed from a Java program via a CallableStatement. The procedure takes two parameters, gets the id of the last record, increments it and inserts a new record with the newly incremented id. I found some examples online which are largely doing the same thing, but I can't resolve the errors.

CREATE OR REPLACE PROCEDURE insertEmployeeProcedure
(lastname IN VARCHAR, firstname IN VARCHAR) AS
BEGIN
    lastEmpId NUMBER := SELECT COUNT(*) 
    INTO lastEmpId 
    FROM Employees;

    INSERT INTO Employees(id, lname, fname) VALUES(lastEmpId + 1, lastname, firstname);
END insertEmployeeProcedure;
/

The errors are:

Executed successfully in 0.018 s, 0 rows affected.
Line 1, column 1
Error code 984, SQL state 42000: ORA-00984: column not allowed here
Line 8, column 5
Error code 900, SQL state 42000: ORA-00900: invalid SQL statement
Line 9, column 1
Error code 0, SQL state null: java.lang.NullPointerException
Line 9, column 1
Execution finished after 0.018 s, 3 error(s) occurred.

As far as I understand a store procedure is a mix of PL and SQL.That said, I tried incrementing lastEmpId (as lastEmpId := lastEmpId + 1) but got an "Invalid SQL statement" error. Also, Oracle docs (http://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#CIHBCBHC) do not do a good job explaining how to define and use local variables in stored procedures.

Thanks in advance.

Upvotes: 0

Views: 68119

Answers (2)

Rodrigo Villalba Zayas
Rodrigo Villalba Zayas

Reputation: 5616

You must use sequences to increment id column. Not always number of rows matches last used id

An example to create sequences:

CREATE SEQUENCE sequence_name
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

An example of how to use this sequence follows:

CREATE OR REPLACE PROCEDURE insertEmployeeProcedure
(lastname IN VARCHAR, firstname IN VARCHAR) AS
BEGIN
   INSERT INTO Employees(id, lname, fname) VALUES(sequence_name.nextval, lastname, firstname);
END insertEmployeeProcedure;

Upvotes: 2

Alex Gitelman
Alex Gitelman

Reputation: 24722

You probably want something like

CREATE OR REPLACE PROCEDURE insertEmployeeProcedure
(lastname IN VARCHAR, firstname IN VARCHAR) AS 

   lastEmpId NUMBER;
 BEGIN
    SELECT COUNT(*) 
    INTO lastEmpId 
    FROM Employees;

    INSERT INTO Employees(id, lname, fname) VALUES(lastEmpId + 1, lastname, firstname); 
 END insertEmployeeProcedure; 
 /

Note that the variable is declared before BEGIN section.

Also, if you use SQL developer, after you execute package creation code run

 show errors

It will show you any problem before you actually call the procedure.

Upvotes: 5

Related Questions