passing two or more parameyers in a single variable in plsql

Question

I am getting. ORA-1722 error while passing in one single variable more than one number like (54,55,56) to a procedure.

In the procedure cursor block I have written:

 Select * from emp where deptid in(  inputparameter);

While running the procedure the error comes in the select statement above.

Is there any way to handle this situation in a single parameter

Actually your question is a duplicate of [this SO thread](http://stackoverflow.com/q/38854554/146325) because it represents where you're at. But SO rules won't allow me to use it as the target. Your parameter is something like `1,2,3` which Oracle treats as a string. So you need to convert the string into a stream of numbers or use dynamic SQL. The answers in the indicated thread will show you how to do that. — APC, Dec 22 '16 at 08:13

Leo · Answer 1 · 2016-12-22T07:43:08.863

1) You can use dynamic sql for that statement and add "inputparameter" like a string '54,55,56'

DECLARE 
  TYPE input_cursor IS REF CURSOR;
  l_cursor input_cursor;
  l_input_parameter VARCHAR2(4000) := '52,53,43';
BEGIN
  open l_cursor for 'SELECT * FROM emp WHERE deptid in (' || l_input_parameter || ')';  
END;

2) You can create new type for scheme like

CREATE OR REPLACE TYPE "TNUMBERLIST_TABLE" AS TABLE OF NUMBER

and use it in your procedure like input parameter, for example:

DECLARE
  PROCEDURE output_list(p_table TNUMBERLIST_TABLE) IS    
  BEGIN
    FOR cur IN (SELECT * FROM TABLE(p_table)) LOOP
      dbms_output.put_line(cur.column_value);
    END LOOP;
  END;
BEGIN
  output_list(p_table => TNUMBERLIST_TABLE(1,2,3,4,5,6));
END;

for your task it would be:

Select * from emp where deptid in (SELECT * FROM TABLE(p_table));

score 0 · Answer 2 · answered Dec 22 '16 at 07:39

I am not sure what you need to do , however you can use listagg

lets say id column have value (54,55,56)

in your procedure :

create or replace procedure P_TEST
as
begin
declare  inputparameter varchar2(38);
begin
SELECT LISTAGG(COMP_CODE, ', ') WITHIN GROUP  (ORDER BY COMP_CODE) into inputparameter
FROM emp ;
end;
end;

result inputparameter = 1,3

passing two or more parameyers in a single variable in plsql

2 Answers2