Re: Execute Immediate

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 20 Sep 2009 20:32:41 +0200
Message-ID: <4AB67549.9070703_at_gmail.com>

The Magnet wrote:
> Hi,
>
> I am still working on this one. I have a dynamic statement that looks
> like this:
>
> EXECUTE IMMEDIATE 'SELECT count(*) FROM customers_lookup WHERE
> customer_id = :customer_id' INTO v_data USING v_customer_id;
>
> That works great, but the actual statement looks more like this:
>
> EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || v_table_name || ' WHERE
> customer_id = :customer_id ' INTO v_data USING v_customer_id;
>
> No matter how hard I play with this and the quotes and such, it seems
> to not like the fact that I'm putting the statement together in pieces
> and complains that CUSTOMER_ID bind variable is not defined. I've
> played with a number of quote combinations also with no luck.
>
> I'm still playing with it, but maybe someone else can help me reach
> the answer faster.
>
> Many thanks!

SQL> create table customers

   2 as
   3 select empno,ename,deptno customer_id from emp    4 /

Table created.

SQL> declare

   2  v_data number;
   3  v_customer_id number := &customer_id;
   4  v_table_name varchar2(30) :='customers';

   5 begin
   6 EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || v_table_name || ' WHERE

   7  customer_id = :customer_id ' INTO v_data USING v_customer_id;
   8  dbms_output.put_line('v_data: '||v_data);
   9  dbms_output.put_line('v_customer_id: '||v_customer_id);

  10 end;
  11 /
Enter value for customer_id: 10
old 3: v_customer_id number := &customer_id; new 3: v_customer_id number := 10;
v_data: 3
v_customer_id: 10

PL/SQL procedure successfully completed.

SQL> /
Enter value for customer_id: 20
old 3: v_customer_id number := &customer_id; new 3: v_customer_id number := 20;
v_data: 5
v_customer_id: 20

PL/SQL procedure successfully completed.

SQL> /
Enter value for customer_id: 30
old 3: v_customer_id number := &customer_id; new 3: v_customer_id number := 30;
v_data: 6
v_customer_id: 30

PL/SQL procedure successfully completed.

Best regards

Maxim Received on Sun Sep 20 2009 - 13:32:41 CDT