"IN" operator - invalid number error 1

[hazelsisson]

Hello,

I have a stored sql query used in a report. Parameters are passed to it and all work apart from this line:

and e.org_id IN (:p_orgs)

e.org_id is a numeric column in the table.

_orgs is a string that can be blank, or can contain any number of integers separated by commas (e.g. "18, 20, 21, 22, 24, 25").

The problem is that if _orgs is not blank, I get the error ORA-01722: invalid number.

I kind of understand that it is because _orgs is a string and it doesn't automatically convert the string to a list of integers.

Do you have any ideas on how can I overcome this problem, either by changing the value of _orgs (which is generated by a function) or altering the stored query?

Many thanks,
Hazel

 

[Dagon]

You can't use bind variables as parameters for IN lists. You will have to generate a dynamic SQL statement and append your list of IN conditions to that.

declare
   type t_cursor is ref cursor;
   v_cursor t_cursor;
   in_list varchar2(500);
begin
   in_list := '1,2,3,4';
   open v_cursor for 'select numarg from 
   (select 1 as numarg from dual) where numarg in 
   ('||in_list||')';
end;

If you don't want to create a hard-coded list like that, the only alternatives would be to put the list in a temporary table and join back to that.

 

[hazelsisson]

Hi Dagon,

thanks for the advice - I will re-think my approach; the ref cursor looks like the best idea.

Thanks again,
Hazel

 

[hazelsisson]

PS - I will give you a star from home; my work firewall forbids it!