How to execute a multiple value parameter function in Oracle

Question

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.

Bob Jarvis - Слава Україні · Accepted Answer · 2018-08-12 23:57:42Z

1

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.

answered Aug 12, 2018 at 23:57

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sonam Mohite Over a year ago

I am struggling to execute multiple value parameter like select * from emp where emp_id in (1,2,3,4,5); In the given function above i still need one parameter only but it's a multiple value parameter. Hope that make sense. :)

Bob Jarvis - Слава Україні Over a year ago

Ah, I see. It appears you're trying trying to pass a list of values in a string that you will then use in an IN clause. Put simply, you can't do that. Sorry.

Littlefoot Over a year ago

I'd say you can, @Bob - takes a little bit of typing, but - you can do that. Have a look at my example in this discussion, splitting that comma-separated values list into rows. Such a query can then be used as a subquery for the IN clause.

Littlefoot · Accepted Answer · 2018-08-13 06:04:34Z

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.

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>

Collectives™ on Stack Overflow

How to execute a multiple value parameter function in Oracle

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related