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!

The Magnet wrote:
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

On Sep 20, 1:32*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
Many Many thanks.......