Re: Execute Immediate

From: The Magnet <art_at_unsu.com>
Date: Sun, 20 Sep 2009 11:47:06 -0700 (PDT)
Message-ID: <8d3296d0-8821-4c4a-90d5-29225b5ecd1a_at_p36g2000vbn.googlegroups.com>

On Sep 20, 1:32 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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

Many Many thanks....... Received on Sun Sep 20 2009 - 13:47:06 CDT