oracle convert string parameter of stored procedure to number array

Question

I am writing a stored procedure that needs an WHERE IN clause with an array of numbers. How can I pass this array of numbers to the SP. What I thought is sending a string like '123,234,345' and than parse it in the SP before using it. Is this a good way of doing it? If yes, how can I make the below code work?

CREATE OR REPLACE PROCEDURE sp_test2
(
    ids in varchar2, 
    cursor_ OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN cursor_ FOR

    SELECT *FROM my_table
    WHERE my_table.ID IN (
          SELECT regexp_substr(ids,'[^:]+', 1, level) AS list FROM dual
          CONNECT BY regexp_substr(ids, '[^:]+', 1, level) IS NOT NULL);
END;
/

For example (instead of the conversion code) when use like this: IN(ids) i get ORA-01722: invalid number error

Does this answer your question? [Passing an array of data as an input parameter to an Oracle procedure](https://stackoverflow.com/questions/2885575/passing-an-array-of-data-as-an-input-parameter-to-an-oracle-procedure) — Pavel Smirnov, Jan 27 '20 at 10:47
unfortunately no because I think I need an array of numbers not an array of varchar2 — Doğaç Özen, Jan 27 '20 at 10:56
Passing an array of numbers is done the same way as passing an array of varchar2. Just change `CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50)` to `CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF NUMBER` and adjust other related parts of code. — Pavel Smirnov, Jan 27 '20 at 11:07
Have you considered using [Dynamic SQL](https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/dynamic-sql.html#GUID-7E2F596F-9CA3-4DC8-8333-0C117962DB73) ? — Abra, Jan 27 '20 at 11:11
[Using the TABLE Operator with Locally Defined Types in PL/SQL](https://oracle-base.com/articles/12c/using-the-table-operator-with-locally-defined-types-in-plsql-12cr1) — Abra, Jan 27 '20 at 11:23
@PavelSmirnov I am trying. I defined the user defined type as you said. And I tried to used it like this:WHERE my_table.ID IN(SELECT * FROM table(ids)). Bu I cannot call this SP. Toad doesn't accept the parameter of '123,234'. — Doğaç Özen, Jan 27 '20 at 11:26
@Abra I really don't want to complicate things. I'm a beginner in PL/SQL. So if a VARCHAR2 parser which will generate an array of NUMBER exists, this woudl be ideal — Doğaç Özen, Jan 27 '20 at 11:28

score 1 · Accepted Answer · answered Jan 27 '20 at 11:38

1

I really don't want to complicate things

If that's ok with you to simply pass a string of numbers separated by commas as a parameter and split it afterwards, then you can do it like this:

SELECT *FROM my_table
WHERE my_table.ID IN (
      SELECT regexp_substr(ids,'[^,]+', 1, level) AS list FROM dual
      CONNECT BY regexp_substr(ids, '[^,]+', 1, level) IS NOT NULL);

However, I don't think that using a collection complicates things too much.

answered Jan 27 '20 at 11:38

Pavel Smirnov

4,611
3
18
28

If I want to call this transformation with the help of a function, what will be the return type of the function? example of usage can be like this: WHERE my_table.ID IN( my_parser(ids)) – Doğaç Özen Jan 27 '20 at 11:53
It'd be a nested table. You can find more about it here: [Nested Tables](https://docs.oracle.com/database/121/LNPLS/composites.htm#GUID-5ADB7EE2-71F6-4172-ACD8-FFDCF2787A37) – Pavel Smirnov Jan 27 '20 at 11:57

score 0 · Answer 2 · answered Jan 27 '20 at 19:31

Like I suggested, here is how to achieve it using dynamic SQL.

create or replace procedure sp_test2(IDS in varchar2, CURSOR_ out SYS_REFCURSOR)
is
  L_SQL  varchar2(400);
begin
  L_SQL := 'select * from MY_TABLE where ID in (' || IDS || ')';
  open CURSOR_ for L_SQL;
end;

oracle convert string parameter of stored procedure to number array

2 Answers2