PL/SQL Bind Variable in an Insert Statement ,

Question

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:

Do any (committed) rows already exist in the table? What does the max() query return if run manually? — Alex Poole
– Alex Poole, Commented Jun 11, 2019 at 11:53
Like Alex, I figure there are no rows there. maxt_dept + 10 becomes NULL if max_dept is NULL. Use a sequence or an identity column. — Tad Harrison
– Tad Harrison, Commented Jun 11, 2019 at 14:20
Stick a DBMS_OUTPUT just before the INSERT to see what is really in both :dept_id and max_dept. I did a quick test of this (using EMP/DEPT) and found that with an uncaught exception Oracle nulls out the bind variable, causing the AUTOPRINT to not show anything. — Tad Harrison
– Tad Harrison, Commented Jun 13, 2019 at 15:32
Tried, it gives 270 for max_dept and 280 for :dept_id. But inside INSERT it became null. — Sasheena
– Sasheena, Commented Jun 17, 2019 at 4:43

Connor McDonald · Accepted Answer · 2019-06-11 12:03:31Z

5

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.

answered Jun 11, 2019 at 12:03

Connor McDonald

11.8k1 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

APC Over a year ago

Surely that should be create sequence DEPT_SEQ start with 10 increment by 10;? To respect the canon :)

Connor McDonald Over a year ago

hee hee...that sounds like a BASIC program - so we can go back and use multiples of "5" later :-)

Sasheena Over a year ago

Thanks for your suggestion. my ultimate goal is not to insert a new dept, I want to add new dept id into that bind variable and use it later. The issue is with the bind variable.

Connor McDonald Over a year ago

Empty table, means first "max" returns null, and null + anything is null. Use NVL

Sasheena Over a year ago

No, it's not null, it's the primary key of departments table.

Collectives™ on Stack Overflow

PL/SQL Bind Variable in an Insert Statement ,

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related