CODEWITHSUNDEEP
oracle-databaseplsql
Sasheena
Sasheena

Reputation: 1

PL/SQL Bind Variable in an Insert Statement ,

VARIABLE dept_id NUMBER

SET AUTOPRINT ON

DECLARE
max_dept departments.department_id%TYPE; 
dept_name departments.department_name%TYPE := 'Revenue';

BEGIN

SELECT MAX(department_id) 
INTO max_dept 
FROM departments; 

:dept_id := max_dept +10;

INSERT INTO departments (department_id,department_name,location_id)
VALUES(:dept_id,dept_name,NULL); 



END;

Returns error

Error report: ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID") ORA-06512: at line 13 01400. 00000 - "cannot insert NULL into (%s)" *Cause:

Upvotes: 0

Views: 950

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

I'm going to suggest something quite different here. That approach is doomed to failure once your application gets out "in the wild".

Let's say your application is a huge success and now you have dozens of people all using it at the same time, and lets assume currently 1000 is the highest department number.

Now we have 20 people all at roughly the same time doing:

SELECT MAX(department_id) 
INTO max_dept 
FROM departments;

They will all get 1000 as a result, and they will all then try insert 1010 into the table. One of two things will then happen

a) all except of one them will get an error due a primary key violation, b) you have will multiple rows all with dept=1010

Either of these obviously is not great. This is why we have a thing called a sequence that can guarantee to give you unique values. You just do:

create sequence DEPT_SEQ;

and then do your inserts:

INSERT INTO departments (department_id,department_name,location_id)
VALUES(dept_seq.nextval,dept_name,NULL);

There are even easier mechanisms (google for "oracle identity column") but this heopfully explains the way forward and will save you from the problems with your current approach.

Upvotes: 5

Related Questions