I have certain numbers eg., (1,2,3,7,8) which need to be stored in an object (let's say, l_list) and this l_list will used as the value of the parameter in a function while calling a select query.

Storing data globally:

l_list := (1,2,3,7,8);

Creating a function:

create or replace function f_get_result (p_list number)
return varchar2
as 
  l_result varchar2(100);
Begin
  select l_code
  into l_result
  from table_1;

  return l_result;

end f_get_result;

Calling the function in select query:

select f_get_result(l_list) from dual; 

Till now I have tried to define the datatype of the parameter as type table and varray. But, it didn't work. Here is the code which I used:

-- creating a type ---
CREATE OR REPLACE TYPE num_array AS TABLE OF varchar2(100); 

-- the function ---
create or replace function fn_id_exp(p_branch in num_array) 
return varchar2 
   is 
     txt varchar2(1000); 
     txt_1 varchar2(1000); 
begin 
   for i in 1..p_branch.count loop 
      select branch_name into txt_1 from tbl_branch 
      where branch_code = p_branch(i); 
     
      txt := txt ||txt_1||chr(10); 
end loop; 
return txt; 
end; 

-- the select query ---

select fn_id_exp(num_array('100','200')) from dual;

expectation: select fn_id_exp(l_list) from dual; , need to use this much, num_array should not be there,

where l_list is an object which is storing certain numbers like (100,200,300).

Please, help me in this, I am bit new in this. Can somebody, give me an an appropriate example regards this?

Note: It doesn't matter where and what datatype these number are getting stored. Requirement is just to call all the numbers(1,2,3,7,8) in this:

select fn_id_exp(l_list) from dual;

CodePudding user response：

Use a collection:

CREATE TYPE number_array AS TABLE OF NUMBER;

Then:

CREATE FUNCTION fn_id_exp(
  p_branch in number_array
) return varchar2
IS
  txt varchar2(1000);
BEGIN
  SELECT LISTAGG(branch_name, CHR(10)) WITHIN GROUP (ORDER BY rn)
  INTO txt
  FROM   (SELECT ROWNUM AS rn,
                 COLUMN_VALUE AS id
          FROM   TABLE(p_branch)) i
         INNER JOIN tbl_branch b
         ON (i.id = b.branch_code);

  RETURN txt;
END;
/

Then:

select fn_id_exp(number_array(100,200)) from dual;

Given the sample data:

CREATE TABLE tbl_branch (branch_code, branch_name) AS
SELECT 100, 'Leafy Stem' FROM DUAL UNION ALL
SELECT 200, 'Big Twig' FROM DUAL;

Outputs:

However

It may be simpler to not use a function and just use a query:

SELECT branch_name
FROM   tbl_branch
WHERE  branch_code IN (100, 200)
ORDER BY branch_code;

or, if you want to use the array::

SELECT branch_name
FROM   tbl_branch
WHERE  branch_code MEMBER OF number_array(100, 200)
ORDER BY branch_code;

or, pass in an array as a bind parameter (via JDBC, etc.) then:

SELECT branch_name
FROM   tbl_branch
WHERE  branch_code MEMBER OF :your_array
ORDER BY branch_code;

Which output:

fiddle

CodePudding user response：

Another option, which doesn't require you to use your own type, is to use Oracle's built-in types.

Here's an example; function returns employees that work in departments passed as a collection.

SQL> create or replace function f_test (par_deptno in sys.odcinumberlist)
  2    return sys.odcivarchar2list
  3  is
  4    retval sys.odcivarchar2list;
  5  begin
  6    select ename
  7      bulk collect into retval
  8      from emp
  9      where deptno in (select * from table(par_deptno));
 10
 11    return retval;
 12  end;
 13  /

Function created.

Testing:

SQL> select * from table(f_test(sys.odcinumberlist(10, 20)));

COLUMN_VALUE
--------------------------------------------------------------------------------
MILLER
KING
CLARK
FORD
ADAMS
SCOTT
JONES
SMITH

8 rows selected.

SQL>

Note that such a result might be more convenient than returning a string which "mimics" rows in a table (whose values are separated by line feed character), as you'd still might need to split them into rows if you wanted to further process the result.

You said:

expectation: select fn_id_exp(l_list) from dual; , need to use this much, num_array should not be there

But - why not? What's wrong with num_array (or whichever type name you'd use)? That's the syntax, you should follow it if you want to get the result. Silly requirements, in my opinion.

Anyway: you could pass a string of e.g. comma-separated numeric values such as in this example; you'll have to split them to rows. See if it helps.

SQL> create or replace function f_test (par_deptno in varchar2)
  2    return sys.odcivarchar2list
  3  is
  4    retval sys.odcivarchar2list;
  5  begin
  6    select ename
  7      bulk collect into retval
  8      from emp
  9      where deptno in (select regexp_substr(par_deptno, '[^,] ', 1, level)
 10                       from dual
 11                       connect by level <= regexp_count(par_deptno, ',')   1
 12                      );
 13
 14    return retval;
 15  end;
 16  /

Function created.

Testing:

SQL> select * from table(f_test('10, 20'));

COLUMN_VALUE
--------------------------------------------------------------------------------
SMITH
JONES
CLARK
SCOTT
KING
ADAMS
FORD
MILLER

8 rows selected.

SQL>