Clash Royale CLAN TAG#URR8PPP

How to execute a multiple value parameter function in Oracle

I am trying to execute a function which has a parameter with multiple value.

SELECT *
FROM TABLE(MyFunction('1,2'));


This is how I created function

create or replace FUNCTION MyFunction
(
-- Add the parameters for the function here
Ids varchar2
)

--
//Extra bit and pieces


If I create single value function,

create or replace FUNCTION MyFunction
(
-- Add the parameters for the function here
p_party_id IN NUMBER
)


And execute like this

SELECT *
FROM TABLE(MyFunction(1));


Then above query works fine for me.

2 Answers
2

The function you created with

create or replace FUNCTION MyFunction
(
-- Add the parameters for the function here
Ids varchar2
)


still only takes one parameter. If you want to pass in multiple parameters you need to declare each one, as in:

create or replace FUNCTION MyFunction
(
-- Add the parameters for the function here
Id_1 varchar2,
Id_2 varchar2
)


Best of luck.

IN

Aha, so you're passing a comma-separated values as a parameter. If that's so, you'll have to split it into rows and do something with separate values.

Here's an example which returns the input string as rows and joins then with the DEPT table (owned by Scott). You might want to do something different with it.

DEPT

SQL> create or replace function myfunction(par_ids in varchar2)
2 return sys.odcinumberlist
3 is
4 t_id sys.odcinumberlist := sys.odcinumberlist();
5 begin
6 select regexp_substr(par_ids, '[^,]+', 1, level)
7 bulk collect into t_id
8 from dual
9 connect by level <= regexp_count(par_ids, ',') + 1;
10
11 return t_id;
12 end;
13 /

Function created.

SQL> select x.column_value, d.dname, d.loc
2 from dept d join table(myfunction('10,20,30')) x on x.column_value = d.deptno;

COLUMN_VALUE DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO

SQL>


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.