Oracle Bind Variable giving error

Question

SET SERVEROUTPUT ON

VARIABLE dept_id NUMBER

DECLARE

  max_deptno NUMBER(3);

  dept_name departments.department_name%TYPE :='Education';

BEGIN

  SELECT MAX(department_id)

  INTO max_deptno 

  FROM departments;

  DBMS_OUTPUT.PUT_LINE ('The maximum department no is : '  || max_deptno);

  :dept_id:=(max_deptno+10);

  INSERT INTO departments (department_name, department_id,location_id)

  VALUES(dept_name,  :dept_id, NULL);

  DBMS_OUTPUT.PUT_LINE ('The number of rows affected : '  || SQL%ROWCOUNT);


END;

/

Error report: ORA-01400: cannot insert NULL into ("SYSTEM"."DEPARTMENTS"."DEPARTMENT_ID") ORA-06512: at line 10 01400. 00000 - "cannot insert NULL into (%s)" *Cause:
*Action: The maximum department no is : 190

I am getting this error while trying to execute the bind variable in oracle statment. But if i put some value instead of bind variable, i get this insert statement right. What am I doing wrong here?

no the bind variable(:dept_id) is giving me problem. INSERT INTO departments (department_name, department_id,location_id) VALUES('jim', 200, NULL); works fine... — need_the_buzz
– need_the_buzz, Commented Apr 8, 2011 at 4:40
Add the following for troubleshooting: DBMS_OUTPUT.PUT_LINE ('The department id is : ' || dept_id); — squawknull
– squawknull, Commented Apr 8, 2011 at 4:45
My bad. :dept_id:=(max_deptno+10) should just be dept_id = (max_deptno+10). The prefixing of colon is not needed while variable declaration. — billygoat
– billygoat, Commented Apr 8, 2011 at 4:48
Is there any particular reason why you're using a SQL*Plus VARIABLE? — Jeffrey Kemp
– Jeffrey Kemp, Commented Apr 8, 2011 at 6:18

redcayuga · Accepted Answer · 2011-04-12 14:39:39Z

1

I think the value of the bind variable is only set when the pl/sql block is finished. And it probably has to terminate normally.

One solution is to use max_deptno+10 in the insert insead of :dept_if. A better solution is to create another pl/sql variable and use that in the insert statement.

new_dept_id := max_deptno+10;
:dept_id := new_dept_id;

You also have to change the INSERT statement:


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

edited Apr 12, 2011 at 14:39

answered Apr 8, 2011 at 16:08

redcayuga

1,2517 silver badges5 bronze badges

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

2 Comments

need_the_buzz Over a year ago

As you said i have changed the query like this

SET SERVEROUTPUT ON VARIABLE dept_id NUMBER DECLARE max_deptno NUMBER(3); dept_name departments.department_name%TYPE :='Education'; sql_stmt varchar2(100); new_dept_id NUMBER(3); BEGIN SELECT MAX(department_id) INTO max_deptno FROM departments; new_dept_id := max_deptno+10; DBMS_OUTPUT.PUT_LINE ('added : ' || new_dept_id); :dept_id := new_dept_id; INSERT INTO departments (department_name,department_id,location_id) VALUES(dept_name,:dept_id,NULL); END;

/...............But still error

redcayuga Over a year ago

You also have the change the VALUES list in the INSERT statement. That's why I suggested a new variable. The bind variable is only set when the block terminates. I modified my answer to add the INSERT statement.

Andreas Louv · Accepted Answer · 2012-10-27 20:50:05Z

0

I think this error is obtained because you use bind variable without using set autoprint on in start program.

edited Oct 27, 2012 at 20:50

Andreas Louv

47.3k14 gold badges109 silver badges126 bronze badges

answered Jan 6, 2012 at 13:05

THE BT

1

Collectives™ on Stack Overflow

Oracle Bind Variable giving error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related