?
Solved

Bind Variable with Dynamic SQL

Posted on 2003-03-03
7
Medium Priority
?
789 Views
Last Modified: 2013-12-12
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
Comment
Question by:betster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 8058876
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
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 8058969
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
 

Author Comment

by:betster
ID: 8059060
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
Independent Software Vendors: 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!

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 8059509
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
 
LVL 15

Expert Comment

by:andrewst
ID: 8059714
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
 
LVL 4

Accepted Solution

by:
stemu2000 earned 200 total points
ID: 8061754
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
 

Author Comment

by:betster
ID: 8061873
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question