How to Pass multiple values to a procedure in plsql

Question

I want to execute this query inside a procedure:

PROCEDURE ConvertNo ()
IS
CURSOR get IS
select *
from tab
where xx in (2,3,5,6);
..
..

How should I pass these 2,3,5,6 values to the procedure?

Answer 1

You could pass an array of numbers and then use that array in your query. In order to be able to use an array in SQL. The type of that array must exist in your schema, so you would need to create a new database type -if it doesn't already exist-:

CREATE TYPE t_number IS TABLE OF NUMBER;

Then you could just pass an array of numbers to your procedure and use that array as if it were just another database table with the TABLE function:

CREATE OR REPLACE PROCEDURE ConvertNo (p_numbers IN t_number) IS
  CURSOR get IS
    SELECT *
    FROM tab
    WHERE xx IN (SELECT column_value FROM TABLE(p_numbers));
  ...
BEGIN
  ...
END;

You would call this procedure like this:

DECLARE
  v_numbers t_number := t_number(2,3,5,6);
BEGIN
  ConvertNo(v_numbers);
  ...
END;

Alternatively, you could use the pre-defined type sys.odciNumberList instead of creating a new one.