Oracle sql, passing parameters into a procedure problem 1

[xrodge]

I have an sql query that hopefully someone will have time to look at:

I want to pass a series of numbers into a procedure, ie 1,3,5,6,7,8 and then be able to use them to access a cursor.
Unfortunately, it keeps giving me an invalid number error when I try to run it.
So, I tried to pass them in as '1','2'... but still get the same error.

My procedure code is:

PROCEDURE ken_acc_sql (p_listitems IN VARCHAR2)
IS
/* Variables */
p_txt VARCHAR2 (200);
v_listid listitems.listid%TYPE; --number(10)
CURSOR listid_curs
IS
SELECT listid
FROM listitems
WHERE listitemid IN (p_listitems); -- Line 271
--WHERE listitemid IN (1,2,3,4);
BEGIN
dbms_output.put_line ('p_list = ' || p_listitems);
OPEN listid_curs;
LOOP
FETCH listid_curs INTO v_listid; -- Line 277
EXIT WHEN listid_curs%NOTFOUND;

INSERT INTO ken2 VALUES (v_listid);
END LOOP;
CLOSE listid_curs;

END ken_acc_sql;

and I have tried to run it by:

exec ken_main.ken_acc_sql('1,2,3,4')

and

exec ken_main.ken_acc_sql('''1'',''2'',''3'',''4''')

I get the error:

SQL> exec ken_main.ken_acc_sql('''1'',''2'',''3'',''4''')
p_listitems = '1','2','3','4'
begin ken_main.ken_acc_sql('''1'',''2'',''3'',''4'''); end;

*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "EPROJECT3.KEN_MAIN", line 271
ORA-06512: at "EPROJECT3.KEN_MAIN", line 277
ORA-06512: at line 1

I have indicated lines 271 and 277 above.
The lines before this are just other procedures within a package I have created.
If anyone could get a few minutes and point out the very silly thing I am doing, then it would be much appreciated.

 

[sem]

You need dynamic sql:

PROCEDURE ken_acc_sql (p_listitems IN VARCHAR2)
IS
..
type rc is ref cursor;
listid_curs rc;

BEGIN
.....
open listid_curs for
'SELECT listid FROM listitems WHERE listitemid IN ('
||p_listitems
||')';
.....
END;

And invoke it as
exec ken_main.ken_acc_sql('1,2,3,4')

The problem is that compiler does not know that varcharchar2 variable is a list and there is no way to make it think so. But with dynamic sql the statement parsed does contain a list.

 

[ija]

The 'in' statement will look like:
WHERE listitemid IN ('1,2,3,4')
or like
WHERE listitemid IN (''1','2','3','4'')
In neither case it is what you wanted (the red parts are always a string). You should have a look in documentation or ask somebody about variable number of parameters. I have never used it, but I know that it exists.

 

[xrodge]

Yep, I could see that it was being passed in as a string. I was working on the assumption that it would execute if I could just build up the string as if I typed it directly into a sql editor.

Sem, your idea seems to have done the trick. It's running nicely and I can tell my boss that he can have the functionality he wants.

Thanks for getting back to me so quickly folks!