11 Replies Latest reply on Aug 20, 2013 3:09 PM by GTS (DBA)

    Getting error for bind variables

    GTS (DBA)

      Hello Experts ;

       

      SQL> DECLARE

        2  l_eid  number;

        3  l_name  varchar2(30);

        4  BEGIN

        5  l_eid:=1000;

        6  select name into l_name from tab1 where no = l_eid;

        7  l_eid:=9999;

        8  select name into l_name from tab1 where no = l_eid;

        9  l_eid:=299999;

      10  select name into l_name from tab1 where no = l_eid;

      11* END;

             /

      PL/SQL procedure successfully completed.

       

      when  creating bind variables  i am getting error :

       

      SQL> variable b1 number;

      SQL> exec :b1 :='select name from tab1 where no = :b1';

      BEGIN :b1 :='select name from tab1 where no = :b1'; END;

      * ERROR at line 1:

      ORA-06502: PL/SQL: numeric or value error: character to number conversion error

      ORA-06512: at line 1

        • 1. Re: Getting error for bind variables
          Solomon Yakobson

          And what

           

          exec :b1 :='select name from tab1 where no = :b1';

           

          is supposed to mean? In any case, when you use PL/SQL variables in SQL statement, PL/SQL automatically does binding for you.

           

          SY.

          • 2. Re: Getting error for bind variables
            GTS (DBA)

            @ SY


            Can you please  point out my exact error ?

            • 3. Re: Getting error for bind variables
              Solomon Yakobson

              If you want to use bind variables you:

               

              variable b1 number

              variable b2 varchar2(30)

              exec :b1 := 1000;

              begin

                  select  name

                    into  :b2

                    from tab1

                    where no = :b1;

              end;

              /

              print :b2

               

              For example:

               

              SQL> variable b1 number
              SQL> variable b2 varchar2(30)
              SQL> exec :b1 := 7839;

              PL/SQL procedure successfully completed.

              SQL> begin
                2      select  ename
                3        into  :b2
                4        from  emp
                5        where empno = :b1;
                6  end;
                7  /

              PL/SQL procedure successfully completed.

              SQL> print :b2

              B2
              --------------------------------
              KING

              SQL>

               

              SY.

              • 4. Re: Getting error for bind variables
                GTS (DBA)

                @ SY

                 

                Thanks  SY.  

                • 5. Re: Getting error for bind variables
                  GTS (DBA)

                  @Sy


                    Could you please consider my exact query ?


                  >>  Executable part >>


                     l_eid:=1000;

                       select name into l_name from tab1 where no = l_eid;

                     l_eid:=9999;

                       select name into l_name from tab1 where no = l_eid;

                    l_eid:=299999;

                    select name into l_name from tab1 where no = l_eid;


                  Actually i was trying to execute  " without  passing  input  " record_no"  from  binary  value

                  because that value is assigned  in executable part.


                  My goal  is

                  =========

                  1)  Avoiding hard parse again.

                  2)  input value should not passed   from bind variable.


                  Note :  i'm getting  error  "what i have posted in my first reply "


                  • 6. Re: Getting error for bind variables
                    Solomon Yakobson

                    I already told you - when you use PL/SQL variables in SQL statement, PL/SQL automatically does binding for you. Statement:

                     

                    select name into l_name from tab1 where no = l_eid;

                     

                    will become:

                     

                    select name into l_name from tab1 where no = :b1;

                     

                    and Oracle will bind PL/SQL variable l_eid value each time you execute the above statement. Therefore, it doesn't matter what values you assign to l_eid - it will be hard parse first time and soft parse thereafter.

                     

                    SY.

                    1 person found this helpful
                    • 7. Re: Getting error for bind variables
                      GTS (DBA)

                      @ SY;

                       

                      Sorry SY ,

                      I was planning to get output different method that method not workable for me.

                      Can you provide some easy links to understand bind variables and cursor sharing !

                      • 8. Re: Getting error for bind variables
                        Solomon Yakobson

                        You need to explain "method not workable for me". I have no idea what method you are talking about and what are you trying to achieve.

                         

                        SY.

                        • 9. Re: Getting error for bind variables
                          GTS (DBA)

                          @ SY

                          SolomonYakobson wrote:

                          You need to explain "method not workable for me". I have no idea what method you are talking about and what are you trying to achieve.

                          SY.


                          SQL>DECLARE

                              v_value  number;

                              v_dispaly varchar2(30);

                              BEGIN

                             v_value :=100000;

                              select name into v_display from tab1 where no = :v_value;

                              DBMS_OUTPUT.PUT_LINE(v_display);

                              v_value :=20000;

                              select name into v_display from tab1 where no = :v_value;

                              DBMS_OUTPUT.PUT_LINE(v_display);

                               v_value :=250000;

                              select name into v_display from tab1 where no = :v_value;

                              DBMS_OUTPUT.PUT_LINE(v_display);

                            * END;

                          RS>/

                          PL/SQL procedure successfully completed.

                           

                          >>  This is not workable for me ,  Here getting error  >>

                           

                          SQL> DECLARE

                            2  variable  b1  number;

                            3  exec  :b1;

                            4  BEGIN

                            5  select  name  from  tab1  where no =: b1;

                            6* END;

                          RS>/

                          variable b1 number;

                                      *

                          ERROR at line 2:

                          ORA-06550: line 2, column 13:

                          PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:

                          := . ( @ % ; not null range default character


                          Thanks !

                          • 10. Re: Getting error for bind variables
                            Solomon Yakobson

                            First of all variable SQL*PLus, not PL/SQL statement. You can't use it inside PL/SQL block.  Same way exec (short of execute) is SQL*PLus, not PL/SQL statement and can't be used inside PL/SQL block. And anyway, what exec  :b1; is supposed to do? There is no value assigned to b1. And your select lacks into clause. I believe I already showed you.  Anyway:

                             

                            VARIABLE v_value NUMBER

                            DECLARE

                                v_dispaly varchar2(30);

                            BEGIN

                                :v_value := 100000;

                                select  name

                                  into  v_display

                                  from  tab1

                                  where no = :v_value;

                                DBMS_OUTPUT.PUT_LINE(v_display);

                                :v_value := 20000;

                                select  name

                                  into  v_display

                                  from  tab1

                                  where no = :v_value;

                                DBMS_OUTPUT.PUT_LINE(v_display);

                                :v_value := 250000;

                                select  name

                                  into  v_display

                                  from  tab1

                                  where no = :v_value;

                                DBMS_OUTPUT.PUT_LINE(v_display);

                            END;

                            /

                             

                             

                            SY.

                            1 person found this helpful
                            • 11. Re: Getting error for bind variables
                              GTS (DBA)

                              @ SY

                               

                              Now i understood. Thanks