Home » SQL & PL/SQL » SQL & PL/SQL » Reg bind variable
Reg bind variable [message #200724] Wed, 01 November 2006 01:49 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,
i am able to pass value to any varible using "bind variable". but i can not do the same for partition values.
for instance,
declare
cnt number;
chnl number :=2;
begin
execute immediate 'select count(1) from table_name partition(D20060203) where rx_chnl_cd=:a' into cnt using chnl;
dbms_output.put_line('count :'||cnt);
end;
/

the above one is working fine.

but this is not working
declare
cnt number;
chnl number :=2;
part varchar2(10) := 'D20060203';
begin
execute immediate 'select count(1) from table_name partition(:a) where rx_chnl_cd=:b' into cnt using part,chnl,;
dbms_output.put_line('count :'||cnt);
end;
/

it is giving the following error.
declare
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 8

how to get out of this error.

Thanks,
Thangam
Re: Reg bind variable [message #200734 is a reply to message #200724] Wed, 01 November 2006 02:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't bind the partition name for exactly the same reason that you can't bind table names into queries - the optimiser needs to know what it's looking at before it starts parsing the query.
execute immediate 'select count(1) from table_name partition('||part||') where rx_chnl_cd=:b' into cnt using chnl;
Re: Reg bind variable [message #200740 is a reply to message #200734] Wed, 01 November 2006 02:40 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
oh. Thanks JRowbottom
Previous Topic: grouping query / Can someone help me writing sql (merged 4 threads)
Next Topic: CREATE PROCEDURE change in 10G.
Goto Forum:
  


Current Time: Thu Dec 08 08:26:43 CST 2016

Total time taken to generate the page: 0.11064 seconds