1 2 Previous Next 16 Replies Latest reply: Jan 9, 2013 3:16 AM by 983133 RSS

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

    983133
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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