• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 811
  • Last Modified:

Bind Variable with Dynamic SQL

I have the following procedure that tries to fill a table with the cursor data.

CREATE OR REPLACE PROCEDURE qup_cc_payment_table(
       appeal VARCHAR2,
        id     VARCHAR2) IS
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    query_str VARCHAR2(200);
    payment_date date;
    payment_amount number(8,2);
BEGIN
    query_str := 'SELECT payment_date, payment_amount    FROM ' || appeal ||
                       '.pledge_payment ' ||  
                      'WHERE id_number = :' || id;
     DELETE FROM qut_cc_temp_plg_payments;
    OPEN c FOR query_str USING id;
    LOOP
        FETCH c INTO payment_date, payment_amount;
        EXIT WHEN c%NOTFOUND;
       
         
     INSERT INTO qut_cc_temp_plg_payments(
                                                           id_number
                                                       ,payment_date
                                                       ,payment_amount)
                                             VALUES
                                                         (
                                                        id
                                                       ,payment_date
                                                       ,payment_amount);
    END LOOP;
    CLOSE c;
END;
/

It keeps giving me the error of a bind variable being "invalid" or "does not exist" and I don't quite understand this.  I just started looking at this dynamic SQL today.  Any help will be greatly appreciated.

Thanks..
0
betster
Asked:
betster
1 Solution
 
andrewstCommented:
You are not using the bind variable correctly in query_str:

query_str := 'SELECT payment_date, payment_amount    FROM ' || appeal || '.pledge_payment ' || 'WHERE id_number = :' || id;
   
This should be:

query_str := 'SELECT payment_date, payment_amount    FROM ' || appeal || '.pledge_payment ' || 'WHERE id_number = :x';

You could put :id instead of :x, but the point is that the bind variable (:x or :id) and the variable id are not the same thing - they bind variable gets set to the value of id by the USING clause.

Having said that, you will only get an error with the incorrect form you had if the value of id passed in is an invalid syntax for a bind variable name.  If the value of 'id' was '123' or 'abc' it would still work, because :123 and :abc are valid bind variable names.  But if the id values was '@' or something then since :@ is an invalid bind variable name you would get@:

ORA-01745: invalid host/bind variable name

0
 
Daniel StanleyDatabase engineerCommented:
CREATE OR REPLACE PROCEDURE qup_cc_payment_table(
      appeal VARCHAR2,
       ID     VARCHAR2) IS
   TYPE cur_typ IS REF CURSOR;
   c cur_typ;
   query_str VARCHAR2(200);
   payment_date date;
   payment_amount number(8,2);
BEGIN
   query_str := 'SELECT payment_date, payment_amount    FROM ' || appeal ||
                      '.pledge_payment ' ||  
                     'WHERE id_number = ' || ID;
    DELETE FROM qut_cc_temp_plg_payments;
   OPEN c FOR query_str USING id;
   LOOP
       FETCH c INTO payment_date, payment_amount;
       EXIT WHEN c%NOTFOUND;
       
         
    INSERT INTO qut_cc_temp_plg_payments(
                                                          id_number
                                                      ,payment_date
                                                      ,payment_amount)
                                            VALUES
                                                        (
                                                       id
                                                      ,payment_date
                                                      ,payment_amount);
   END LOOP;
   CLOSE c;
END;
/


binding is done automatically with input variables so there is no need to prefix the variable with a colon like you would if you were using placeholders.

good luck,
daniels@asix.com
0
 
betsterAuthor Commented:
Thanks so far guys,

andrewst:

your binding idea worked, but it returns no data. How does the value of x get set for the query_str??
I know you mentioned something about the 'USING' but how does that work???

drs66:

I had tried that before and that is when I get the binding error
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeOM_DBACommented:
Try this:


...
  query_str := 'SELECT payment_date, payment_amount    FROM ' || appeal ||
                                     '.pledge_payment ' ||  
                                    'WHERE id_number = :1' ;
  DELETE FROM qut_cc_temp_plg_payments;
  OPEN c FOR query_str USING id;
...etc...
Also you may want to consider qualifying the values for the insert as in:

INSERT INTO qut_cc_temp_plg_payments(
                                                                         id_number
                                                                     ,payment_date
                                                                     ,payment_amount)
                                                           VALUES
                                                                       (
                                                                      c.id
                                                                     ,c.payment_date
                                                                     ,c.payment_amount);
0
 
andrewstCommented:
betster> your binding idea worked, but it returns no data. How does the value of x get set for the query_str??  I know you mentioned something about the 'USING' but how does that work???

query_str := 'SELECT payment_date, payment_amount    FROM ' || appeal || '.pledge_payment ' || 'WHERE id_number = :x';
...
OPEN c FOR query_str USING id;

When you use bind variables like this, the names of the bind variables are irrelevant - you can use :x, :1, :id or whatever.  

The USING clause of the OPEN statement associates a value with each bind variable in the order they occur in the statement from left to right.  In this case you associate the value in parameter id with the first (and only) bind variable in the query.

I don't know why it returned no data - it depends on what was in the table, and the value of id used.

drs66> binding is done automatically with input variables so there is no need to prefix the variable with a colon like you would if you were using placeholders.

Actually that is not true with dynamic SQL.  Take your example:

query_str := 'SELECT payment_date, payment_amount    FROM ' || appeal || '.pledge_payment ' || 'WHERE id_number = ' || ID;

If appeal = 'scott' and id = 'abc' then the assignment above becomes:

query_str := 'SELECT payment_date, payment_amount    FROM scott.pledge_payment WHERE id_number = abc'

...whic will cause an error unless there is a column called abc in the table!  Even if id had been '123' the statement would then succeed but it would NOT be using a bind variable, it would be using the hardcoded numeric value 123.
0
 
stemu2000Commented:
following statement will do it in one simple go:

CREATE OR REPLACE PROCEDURE qup_cc_payment_table(
     appeal VARCHAR2,
     ID     VARCHAR2) IS
  lsql varchar2(2000);

begin

  DELETE FROM qut_cc_temp_plg_payments;

  lsql := 'insert into  qut_cc_temp_plg_payments'||
        '     (id_number,payment_date,payment_amount)'||
        ' SELECT id_number,payment_date, payment_amount'||
        '   FROM '|| appeal ||'.pledge_payment ' ||  
        '  WHERE id_number = :id';
  execute immediate lsql using id;
end;
/





Cheers, Stefan

ps: delete from qut_cc_temp_plg_payments is a really bet idea, id will give you looking problems in multiuser systems. make sure this table is defined as either a global temporary table or has a unique session key (userenv('sessionid')) in it's primary key!
0
 
betsterAuthor Commented:
Tank you very much.  This does the job and is also a lot cleaner looking.  

Thanks to everyone that answered, I learned a lot from this..
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now