12 Replies Latest reply: Apr 9, 2008 2:41 AM by 631898 RSS

    PLS-00049: Bad Bind Variable

    631898
      Here is my code:
      VARIABLE g_tax_num NUMBER(5,2);
      
      BEGIN
      :g_tax_num:=0;
      END;
      /
      
      CREATE OR REPLACE PROCEDURE tax_cost_sp 
        (p_state IN bb_tax.state%type,
         p_subtotal IN bb_basket.subtotal%type,
         p_taxrate OUT bb_tax.tax%type)
        IS
       BEGIN
         IF p_state='VA' THEN :g_tax_num:=p_taxrate*p_subtotal;
          ELSIF p_state='NC' THEN :g_tax_num:=p_taxrate*p_subtotal;
          ELSIF p_state='SC' THEN :g_tax_num:=p_taxrate*p_subtotal;
          ELSE :g_tax_num:=0;
         END IF;
       DBMS_OUTPUT.PUT_LINE('The Tax Paid Is: '||:g_tax_num);
      END;
      /
      Why would I keep getting this error? It tells me the bind variable of :g_tax_num is bad. I've checked spelling and data types and everything...no avail.

      Help!
        • 1. Re: PLS-00049: Bad Bind Variable
          121256
          Why would I keep getting this error?
          You can't use bind variables inside DDL.
          • 2. Re: PLS-00049: Bad Bind Variable
            631898
            I guess that would explain it!

            Sorry, I'm still new at PL/SQL.
            • 3. Re: PLS-00049: Bad Bind Variable
              155651
              Modify the procedure

              CREATE OR REPLACE PROCEDURE tax_cost_sp
              (p_state IN bb_tax.state%type,
              p_subtotal IN bb_basket.subtotal%type,
              p_taxrate OUT bb_tax.tax%type,
              p_tax_num OUT NUMBER)
              IS
              BEGIN
              IF p_state='VA' THEN p_tax_num:=p_taxrate*p_subtotal;
              ELSIF p_state='NC' THEN p_tax_num:=p_taxrate*p_subtotal;
              ELSIF p_state='SC' THEN p_tax_num:=p_taxrate*p_subtotal;
              ELSE p_tax_num:=0;
              END IF;
              -- DBMS_OUTPUT.PUT_LINE('The Tax Paid Is: '||p_tax_num);
              END;
              /


              Pass bind variables while calling the procedure

              SQL>
              SQL> VARIABLE g_tax_num NUMBER(5,2);
              SQL> EXEC tax_cost_sp('CA', 11,11,:g_tax_num);
              SQL> PRINT g_tax_num;
              SQL>
              • 4. Re: PLS-00049: Bad Bind Variable
                631898
                That looks like it would work...my assignment (yes, I'm a student) says I need to use a host variable named G_Tax to hold the value returned by the procedure...

                Doesn't this version take away teh host variable?
                • 5. Re: PLS-00049: Bad Bind Variable
                  576659
                  i think we cant use bind variable which was declared outside inside a procedure
                  Thanks
                  Hariharan..
                  • 6. Re: PLS-00049: Bad Bind Variable
                    631898
                    Ok, so how/where would I declare it? Would it come after the procedure and before the Begin statemetn?
                    • 7. Re: PLS-00049: Bad Bind Variable
                      Billy~Verreynne
                      Just to add where you can and should be using bind variables. In SQL code and PL/SQL anonymous blocks that you create on the client side (SQL*Plus, Java, C#, etc).

                      You cannot however add bind variables into actual stored PL/SQL code on the server. This code is parsed at compile time and stored in a binary "ready-to-execute" format. To use bind variables in PL/SQL means that you need to code dynamic SQL (parsed at runtime).

                      In this case, the PL/SQL code will look similar to that used in SQL*Plus or Java or C#.

                      A basic example. A dynamic calculation in PL/SQL using a bind variable:

                      create or replace procedure Foo is
                      n number;
                      begin
                      execute immediate 'begin :var := 1 + 1; end;' using out n;
                      end;
                      In SQL*Plus:
                      SQL> var n number
                      SQL> exec begin :n := 1 + 1; end;


                      Very similar. Also interesting that PL/SQL binds using position and SQL*Plus is clever enough to bind using name.

                      Bottom line though - you very very seldom need bind variables in PL/SQL. PL/SQL will do that part for you automatically. You simply use plain PL/SQL variables in SQL statements in PL/SQL code, and it will do the bind variable thing for you.
                      • 8. Re: PLS-00049: Bad Bind Variable
                        Billy~Verreynne
                        > Doesn't this version take away teh host variable?

                        No, on the contrary Mohan gave you a perfect example of how to define a host variable (using the VAR command in SQL*Plus) and assign that to a bind variable when making an Oracle call.

                        After that call is made, the host variable (residing in the host application SQL*Plus) contains the value of the bind variable as calculated by the Oracle server.
                        • 9. Re: PLS-00049: Bad Bind Variable
                          631898
                          Thanks so much, guys!

                          Let me try this and I'll get back with you!
                          • 10. Re: PLS-00049: Bad Bind Variable
                            631898
                            I tried the following and still receive the same errors:
                            CREATE OR REPLACE PROCEDURE tax_cost_sp 
                              (p_state IN bb_tax.state%type,
                               p_subtotal IN bb_basket.subtotal%type,
                               p_taxrate OUT bb_tax.tax%type)
                              IS
                               g_tax_num  NUMBER;
                             BEGIN
                            
                              EXECUTE IMMEDIATE 'begin :g_tax_num:=0; END;'USING OUT g_tax_num;
                            
                               IF p_state='VA' THEN :g_tax_num:=p_taxrate*p_subtotal;
                                ELSIF p_state='NC' THEN :g_tax_num:=p_taxrate*p_subtotal;
                                ELSIF p_state='SC' THEN :g_tax_num:=p_taxrate*p_subtotal;
                                ELSE :g_tax_num:=0;
                               END IF;
                             DBMS_OUTPUT.PUT_LINE('The Tax Paid Is: '||:g_tax_num);
                            END;
                            /
                            Did I do this incorrectly?
                            • 11. Re: PLS-00049: Bad Bind Variable
                              631898
                              Also tried thsi:
                              CREATE OR REPLACE PROCEDURE tax_cost_sp 
                                (p_state IN bb_tax.state%type,
                                 p_subtotal IN bb_basket.subtotal%type,
                                 p_taxrate OUT bb_tax.tax%type)
                                IS
                                 g_tax_num  NUMBER;
                               BEGIN
                              
                                EXECUTE IMMEDIATE 'begin :var:=0; END;'USING OUT g_tax_num;
                              
                                 IF p_state='VA' THEN :g_tax_num:=p_taxrate*p_subtotal;
                                  ELSIF p_state='NC' THEN :g_tax_num:=p_taxrate*p_subtotal;
                                  ELSIF p_state='SC' THEN :g_tax_num:=p_taxrate*p_subtotal;
                                  ELSE :g_tax_num:=0;
                                 END IF;
                               DBMS_OUTPUT.PUT_LINE('The Tax Paid Is: '||:g_tax_num);
                              END;
                              /
                              • 12. Re: PLS-00049: Bad Bind Variable
                                631898
                                Duh...I was placed the colon when it wasn't needed.