0

Simple task

variable dept_id NUMBER

DECLARE
    max_deptno NUMBER;
    dept_name departments.department_name%TYPE := 'Education';
BEGIN
    select max(department_id)
      into max_deptno
      from departments;
    :dept_id := max_deptno + 10;
    insert into departments (department_id, department_name, location_id)
      values (:dept_id, dept_name, null);
    DBMS_OUTPUT.PUT_LINE('The maximum department id is ' || max_deptno);
    DBMS_OUTPUT.PUT_LINE('Rows made by insert: ' || SQL%ROWCOUNT);
END;

max_deptno is not NULL. Why dept_id is NULL after assignment? What am I doing wrong?

script output:

MAX(DEPARTMENT_ID)
------------------
               520 

Error starting at line 10 in command:
DECLARE
max_deptno NUMBER;
dept_name departments.department_name%TYPE := 'Education1';
BEGIN
select max(department_id)
into max_deptno
from departments;
:dept_id := max_deptno + 10;
insert into departments (department_id, department_name, location_id)
values (:dept_id, dept_name, null);
DBMS_OUTPUT.PUT_LINE('The maximum department id is ' || max_deptno);
DBMS_OUTPUT.PUT_LINE('Rows made by insert: ' || SQL%ROWCOUNT);
END;

Error report:

ORA-01400: невозможно вставить NULL в ("ANDKOM"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: на  line 9
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:    
*Action:
Improve this question
4
  • How do you know that MAX_DEPTNO is NOT NULL? Can you show us the results of SELECT MAX(department_id) FROM andkom.departments? Commented Nov 18, 2011 at 23:49
  • OK. Can you cut and paste from a single SQL*Plus session where you 1) define the host variable DEPT_ID, 2) set serveroutput on, 3) SELECT MAX(department_id) from andkom.departments, and then 4) run the PL/SQL block you posted? Something doesn't make sense. Commented Nov 19, 2011 at 0:00
  • OK. Then run those statements as a script (F5) and copy and paste the script output. Commented Nov 19, 2011 at 1:11
  • @justin-cave I have updated question with this info (in the end) Commented Nov 20, 2011 at 21:43

2 Answers 2

Reset to default
6

The host variable DEPT_ID is not NULL after the assignment. The problem is that your INSERT statement is using the local variable NEW_DPT in the INSERT and that local variable has not been assigned a value. If you really want to use a host variable in PL/SQL (you would essentially never do this in real life) you would need to use that in your PL/SQL block

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    max_deptno NUMBER;
  3    new_dpt NUMBER;
  4    dept_name departments.department_name%TYPE := 'Education';
  5  BEGIN
  6    select max(department_id)
  7      into max_deptno
  8      from departments;
  9    :dept_id := max_deptno + 10;
 10    insert into departments (department_id, department_name, location_id)
 11      values (:dept_id, dept_name, null);
 12    DBMS_OUTPUT.PUT_LINE('The maximum department id is ' || max_deptno);
 13    DBMS_OUTPUT.PUT_LINE('Rows made by insert: ' || SQL%ROWCOUNT);
 14* END;
SQL> /
The maximum department id is 270
Rows made by insert: 1

PL/SQL procedure successfully completed.

SQL> select :dept_id from dual;

  :DEPT_ID
----------
       280

More realistic, however, would be to ditch the substitution variable entirely

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    max_deptno NUMBER;
  3    new_dpt NUMBER;
  4    dept_name departments.department_name%TYPE := 'Education';
  5  BEGIN
  6    select max(department_id)
  7      into max_deptno
  8      from departments;
  9    new_dpt := max_deptno + 10;
 10    insert into departments (department_id, department_name, location_id)
 11      values (new_dpt, dept_name, null);
 12    DBMS_OUTPUT.PUT_LINE('The maximum department id is ' || max_deptno);
 13    DBMS_OUTPUT.PUT_LINE('Rows made by insert: ' || SQL%ROWCOUNT);
 14* END;
SQL> /
The maximum department id is 280
Rows made by insert: 1

PL/SQL procedure successfully completed.
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Justin, I have one minor quibble with what you said. dept_id, in his example, is in fact a bind variable, not a substitution variable. A substitution variable would be prefixed with a '&' not a ':', and substitution would happen in SQL*Plus, before the statement was passed to the server.
@MarkBobak - Quite right, it's not a substitution variable. I've updated my answer to call it a host variable, though, rather than a bind variable (which it also is) just because all the local variables in the INSERT statement are bind variables as well in the context of what people usually mean when they talk about bind variables.
Sorry for incorrect code in my question. But I still get null in dept_id. Maybe I should turn on something:) Sorry for stupid questions. Just started to learn pl/sql
@andryuhell - Can you cut and paste from a SQL*Plus session that shows exactly what you are running and exactly what output you are getting? I ran my examples while logged in as HR with the full HR sample schema in place. I'm assuming that's what you're doing as well?
0

I've asked the same question in oracle forum and got the answer:

Hi,

This is a known issue and is dependent on a JDBC bug which has never been resolved: https://forums.oracle.com/forums/thread.jspa?messageID=9456891&#9456891

If your specific case does not lend itself to setting a bind variable in one anonymous block and reading it in another, then the only workaround (which is also considered good coding practice) is to replace the SQL*Plus variable with a PL/SQL variable.

Regards, Gary SQL Developer Team

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.