-1

I have the following query

set serveroutput on;
declare store_nbr number(9,0);
Begin
Select trim(NODE_ID) into store_nbr  from HOST_STORE_PROFILE 
where sysdate between EFCV_DTE and EXPR_DTE and TO_CHAR(NODE_ID) in 
('12175','22671','16449');
dbms_output.Put_line(store_nbr);
end;

This works fine and prints a value(store_nbr).

But when I tried to replace the 'in' condition in the select statement with variable declaration, it is not working as desired.

I am doing like this:

declare store_nbr number(9,0);
ca_store_nbr varchar2(4096) := '(12175,22671,25519)';
begin
Select NODE_ID into store_nbr from HOST_STORE_PROFILE 
where sysdate between EFCV_DTE and EXPR_DTE and TO_CHAR(NODE_ID) in || 
ca_store_nbr;
dbms_output.Put_line(store_nbr);
end;

I don't get the value into store_nbr.

Whats wrong with the second query.

Santosh
  • 2,355
  • 10
  • 41
  • 64

1 Answers1

0

This needs to be a dynamic sql statement.

make a string out of the whole statement and execute with something like 

EXECUTE IMMEDIATE <sql statement>
Randy
  • 16,480
  • 1
  • 37
  • 55