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?

Answer 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);

Answer 2

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