This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 9, 2013 1:16 AM by 983133 RSS

Learning to use Bind variables in a DML stainside an anonymous block

983133 Newbie
Currently Being Moderated
SET SERVEROUTPUT ON

VARIABLE deptt_id NUMBER

DECLARE

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

BEGIN

SELECT max(department_id) INTO max_deptno FROM departments;
DBMS_OUTPUT.PUT_LINE('The max department number is ' || max_deptno);
*:deptt_id := 10 + max_deptno;*

INSERT INTO departments (department_id, department_name, location_id) VALUES ( :deptt_id, dept_name, null);

DBMS_OUTPUT.PUT_LINE('Rows affected: '|| SQL%ROWCOUNT);

END;
/

SELECT * FROM departments WHERE department_id=:deptt_id;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Error starting at line 3 in command:
DECLARE
max_deptno departments.department_id%TYPE;
dept_name departments.department_name%TYPE := 'Education';
BEGIN

SELECT max(department_id) INTO max_deptno FROM departments;

DBMS_OUTPUT.PUT_LINE('The max department number is ' || max_deptno);
:deptt_id := 10 + max_deptno;

INSERT INTO departments (department_id, department_name, location_id) VALUES ( :deptt_id, dept_name, null);

DBMS_OUTPUT.PUT_LINE('Rows affected: '|| SQL%ROWCOUNT);

END;
Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 11
01400. 00000 - "cannot insert NULL into (%s)"
*Cause:   
*Action:
The max department number is 290

no rows selected
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production

I am running this script in Oracle SQL developer (3.2.20.09)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1. According to the error message the variable 'dept_id' contains a NULL value, why is it taking a NULL Value? It should take in a value of 300 (:deptt_id := 10 + max_deptno;).

Please help, i am trying to learn PL/SQL by myself.

Edited by: Null Finder on Jan 6, 2013 5:55 AM

Edited by: Null Finder on Jan 6, 2013 6:01 AM
  • 1. Re: Learning to use Bind variables in a DML stainside an anonymous block
    Etbin Guru
    Currently Being Moderated
    Are you after http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011

    Regards

    Etbin
  • 2. Re: Learning to use Bind variables in a DML stainside an anonymous block
    user503635 Explorer
    Currently Being Moderated
    Just use plsql variables will do. They are actually bind variables.

    Declare

    V_dep_id number

    Begin

    ...
    v_dep_id := 10 + ...
    ...
    Insert... Values (v_dep_id, ...)
    ...

    End;
  • 3. Re: Learning to use Bind variables in a DML stainside an anonymous block
    Manik Expert
    Currently Being Moderated
    Just trying to understand :

    Are you trying to do this?
    INSERT INTO dept
       (SELECT (SELECT MAX (dept_id) + 10 FROM dept) department_id,
               'Education' department_name,
               NULL location_id
          FROM DUAL);
    
    commit;
    Cheers,
    Manik.
  • 4. Re: Learning to use Bind variables in a DML stainside an anonymous block
    983133 Newbie
    Currently Being Moderated
    Thank you for your reply.

    I dont think the above script requires a Dynamic SQL. If i were to replace the variable 'deptt_id' with an actual number or a variable declared inside the block, the SQL statement executes without any problem. The deptt_id for some reason gets stored with a NULL value. The column departments.departments_id has the not null constraint tagged to it and hence i think it throws an error.

    Please correct me if i am wrong.
  • 5. Re: Learning to use Bind variables in a DML stainside an anonymous block
    983133 Newbie
    Currently Being Moderated
    Bind variables are created in the environment and not in the declarative section of a PL/SQL block. Variables declared in the block can only be used in the block. I want to use the variable 'deptt_id' outside the block as well.

    So unfortunately this will not help me.

    Thank you for your reply.
  • 6. Re: Learning to use Bind variables in a DML stainside an anonymous block
    983133 Newbie
    Currently Being Moderated
    What i am trying to do is

    1.Declaring a Bind variable(in the SQL environment i.e. outside the block)

    VARIABLE deptt_id NUMBER
    DECLARE
    max_deptno departments.department_id%TYPE;

    2.Assign a value to this variable

    *:deptt_id := 10 + max_deptno;*

    where max_deptno is a local variable which already has a value say 280
    so deptt_id will have a vale 290(10 + 280)

    3.Now i want to use the Bind Variable 'deptt_id' in a DML statement

    INSERT INTO departments (department_id, department_name, location_id) VALUES ( *:deptt_id*, dept_name, null);

    4.Now from outside the block i am trying to execute a SQL query using the same 'deptt_id' variable.

    END;
    /

    SELECT * FROM departments WHERE department_id=*:deptt_id*;

    Thank You.

    Please advice.
  • 7. Re: Learning to use Bind variables in a DML stainside an anonymous block
    Etbin Guru
    Currently Being Moderated
    You're right.
    The problem is <tt>:deptt_id := 10 + max_deptno;</tt> particulary <tt>:deptt_id</tt> more particulary the leading :
    The Dynamic SQL bind variables act as placeholders - values cannot be assigned the way you're trying to do - they're assigned in the using clause.
    If you don't need Dynamic SQL then use <tt>deptt_id := 10 + max_deptno;</tt> as others have suggested already.
    I don't have a Database at hand to play with. Looking at it I'd expect an error of some kind to be thrown, but looks like the statement was simply ignored.

    Regards

    Etbin
  • 8. Re: Learning to use Bind variables in a DML stainside an anonymous block
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Null Finder wrote:
    SET SERVEROUTPUT ON

    VARIABLE deptt_id NUMBER

    DECLARE

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

    BEGIN

    SELECT max(department_id) INTO max_deptno FROM departments;
    DBMS_OUTPUT.PUT_LINE('The max department number is ' || max_deptno);
    *:deptt_id := 10 + max_deptno;*

    INSERT INTO departments (department_id, department_name, location_id) VALUES ( :dept_id, dept_name, null);
    Be careful! The line above has :dept_id with only 1 "T"; the other places have :deptt_id with 2 "T"s.
    DBMS_OUTPUT.PUT_LINE('Rows affected: '|| SQL%ROWCOUNT);

    END;
    /

    SELECT * FROM departments WHERE department_id=:deptt_id;

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Error starting at line 3 in command:
    DECLARE
    max_deptno departments.department_id%TYPE;
    dept_name departments.department_name%TYPE := 'Education';
    BEGIN

    SELECT max(department_id) INTO max_deptno FROM departments;

    DBMS_OUTPUT.PUT_LINE('The max department number is ' || max_deptno);
    :deptt_id := 10 + max_deptno;

    INSERT INTO departments (department_id, department_name, location_id) VALUES ( :deptt_id, dept_name, null);

    DBMS_OUTPUT.PUT_LINE('Rows affected: '|| SQL%ROWCOUNT);

    END;
    Error report:
    ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
    ORA-06512: at line 11
    01400. 00000 - "cannot insert NULL into (%s)"
    *Cause:   
    *Action:
    The max department number is 280
    Post a complete script that people can use to re-create the problem and test their ideas. Include CREATE TABLE and INSERT staemements for your table as it exists before running the anonymous block.
    See the forum FAQ {message:id=9360002}
    no rows selected
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production

    I am running this script in Oracle SQL developer (3.2.20.09)

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1. According to the error message the variable 'dept_id' contains a NULL value, why is it taking a NULL Value? It should take in a value of 300 (:deptt_id := 10 + max_deptno;).
    Why should it get a value of 300? If the largest department_id is 280 before running the anonymous block, wouldn't the new row get a value of 10 + 280 = 290?

    I suspect you declared another bind variable, dept_id, with only 1 "T", and never gave it a value.
  • 9. Re: Learning to use Bind variables in a DML stainside an anonymous block
    Etbin Guru
    Currently Being Moderated
    Take a look at http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/fundamentals.htm#LNPLS99945
    the variable declared outside the block cannot be "seen" by the block where the value gets assigned unles the latter is contained by the block where the declaration is made.

    Regards

    Etbin
  • 10. Re: Learning to use Bind variables in a DML stainside an anonymous block
    983133 Newbie
    Currently Being Moderated
    I have created a simplified example of my problem.

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------
    CREATE TABLE t1
    (t1_id number(10) PRIMARY kEY); --I have created a simple table with one column 't1_id' as a Primary Key.

    VARIABLE temp NUMBER --I have declared a Bind Variable/Host Variable.
    BEGIN
    :temp:=100; --I have now assigned a value to the declared variable.
    INSERT INTO t1 (t1_id) VALUES (:temp); --Now i try to insert a value a row into into the table using the Bind variable.
    END;
    /
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------

    Error starting at line 5 in command:
    BEGIN
    :temp:=100;
    INSERT INTO t1 (t1_id) VALUES (:temp);
    END;
    Error report:
    ORA-01400: cannot insert NULL into ("HR"."T1"."T1_ID")
    ORA-06512: at line 3
    01400. 00000 - "cannot insert NULL into (%s)"
    *Cause:   
    *Action:

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------
    Now to narrow down the problem i altered the script a little

    CREATE TABLE t1
    (t1_id number(10) PRIMARY kEY); --I have created a simple table with one column 't1_id' as a Primary Key.

    VARIABLE temp NUMBER --I have declared a Bind Variable.
    BEGIN
    :temp:=100; --I have now assigned a value to the declared variable.
    INSERT INTO t1 (t1_id) VALUES (:temp);   I have commented the DML statement.
    END;
    /
    Print temp

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

    anonymous block completed
    TEMP
    ---
    100

    This means that the assignment happens correctly.
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------

    I do not know why i am getting an error message when i am trying to insert the row using the the bind variable declared ouside the block?

    Please advice.
    Result
    Etbin, the variable that i am using is declared outside the PL/SQL block(host variable)
  • 11. Re: Learning to use Bind variables in a DML stainside an anonymous block
    983133 Newbie
    Currently Being Moderated
    CREATE TABLE t1
    (t1_id number(10) PRIMARY kEY); --I have created a simple table with one column 't1_id' as a Primary Key.

    VARIABLE temp NUMBER --I have declared a Bind Variable.
    BEGIN
    :temp:=100; --I have now assigned a value to the declared variable.
    INSERT INTO t1 (t1_id) VALUES (:temp); --Now i try to insert a value a row into into the table using the Bind variable.
    END;
    /
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------

    Error starting at line 5 in command:
    BEGIN
    :temp:=100;
    INSERT INTO t1 (t1_id) VALUES (:temp);
    END;
    Error report:
    ORA-01400: cannot insert NULL into ("HR"."T1"."T1_ID")
    ORA-06512: at line 3
    01400. 00000 - "cannot insert NULL into (%s)"
    *Cause:   
    *Action:

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------
    Now to narrow down the problem i altered the script a little

    CREATE TABLE t1
    (t1_id number(10) PRIMARY kEY); --I have created a simple table with one column 't1_id' as a Primary Key.

    VARIABLE temp NUMBER --I have declared a Bind Variable.
    BEGIN
    :temp:=100; --I have now assigned a value to the declared variable.
    INSERT INTO t1 (t1_id) VALUES (:temp);   I have commented the DML statement.
    END;
    /
    Print temp

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

    anonymous block completed
    TEMP
    ---
    100

    This means that the assignment happens correctly.
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------

    I do not know why i am getting an error message when i am trying to insert the row using the the bind variable declared ouside the block?

    Please advice.


    Thank you for responding and sorry about the typo in the script that you noticed.
  • 12. Re: Learning to use Bind variables in a DML stainside an anonymous block
    Etbin Guru
    Currently Being Moderated
    Guessing (no Database available to verify - although ther's no reason to not believe you)
    CREATE TABLE t1
    (t1_id number(10) PRIMARY kEY); --I have created a simple table with one column 't1_id' as a Primary Key.
    
    VARIABLE temp NUMBER --I have declared a Bind Variable.
    
    BEGIN
    :temp := 100; --I have now assigned a value to the declared variable.
    --INSERT INTO t1 (t1_id) VALUES (:temp); --I have commented the DML statement.
    /* but either */
      INSERT INTO t1 (t1_id) VALUES (temp);
    /* or */
      execute immediate 'INSERT INTO t1 (t1_id) VALUES (:temp)' using temp;
    /* would successfully complete */
    END;
    Suggesting that <tt>:temp := 100;</tt> is equivalent to <tt>temp := 100;</tt>
    and the <tt>:temp</tt> within <tt>INSERT INTO t1 (t1_id) VALUES (:temp);</tt> is somehow another <tt>:temp</tt> variable.

    Regards

    Etbin
  • 13. Re: Learning to use Bind variables in a DML stainside an anonymous block
    user503635 Explorer
    Currently Being Moderated
    I sort of not agree with you, you may read this
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7832114438832

    In the above doc, it mentiones and proves
    1. All PLSQL variables are bind variables
    2. Static SQL has better performance than dynamic SQL with so called bind variables.
  • 14. Re: Learning to use Bind variables in a DML stainside an anonymous block
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Null Finder wrote:
    CREATE TABLE t1
    (t1_id number(10) PRIMARY kEY); --I have created a simple table with one column 't1_id' as a Primary Key.

    VARIABLE temp NUMBER --I have declared a Bind Variable.
    BEGIN
    :temp:=100; --I have now assigned a value to the declared variable.
    INSERT INTO t1 (t1_id) VALUES (:temp); --Now i try to insert a value a row into into the table using the Bind variable.
    END;
    /
    The code above works fine for me. Actually, I put the comments on separate lines except in PL/SQL. That is, I ran:
    CREATE TABLE t1
    (t1_id number(10) PRIMARY kEY); 
    --I have created a simple table with one column 't1_id' as a Primary Key.
    
    VARIABLE temp NUMBER 
    --I have declared a Bind Variable.
    
    
    BEGIN
        :temp := 100; --I have now assigned a value to the declared variable.
        INSERT INTO t1 (t1_id) VALUES (:temp); --Now i try to insert a value a row into into the table using the Bind variable.
    END;
    /
    I get errors if I have comments after the semi-colon at the end of SQL statements.
    What front end are you using? I tried it in SQL*Plus 10.2.0.1.0 (My database is also 10.2.0.1.0, but I don't think that matters in this case.)
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------

    Error starting at line 5 in command:
    BEGIN
    :temp:=100;
    INSERT INTO t1 (t1_id) VALUES (:temp);
    END;
    If you're getting an error on line 5, then I don't believe the code above (which has only 4 lines) is causing it.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points