PLSQL - Return a collection of codes

0
votes

I am trying to write a function to return a list of code.

In the package spec I have declared

type codes_t is table of number;

and

function getCodes(
   acc_id in Account.id%type
)
return codes_t;

In the body I have

function getCodes(
  acc_id in Account.id%type
)
return codes_t
is
  v_codes codes_t := codes_t();
begin
   for row in (select ...) loop
     v_codes.extend();
     v_codes(v_codes.count) := row.code;
   end loop;
return codes_t;

The package compilers without errors but when I try to call the function with

select pkg.getCodes(123) from dual;

I get ORA-00902: Invalid datatype

Anything obviously wrong?

2
plsql
"Invalid datatype" at line? – hotfix
@hotfix I've updated the question. The line in the error doesn't refer to the function itself but to the call to it. – algiogia

2 Answers

0
votes

Since you're trying to call the function in SQL, and it returns an array, you need to do two things:

  1. Create the type as a database type (i.e. create type ...) so that the SQL engine can know about the datatype
  2. cast the function's output to a table and select from that, e.g. select * from table(<function>)

Doing those two things should do the trick for you.

0
votes

Or it can be pipelined. Demo

CREATE OR REPLACE PACKAGE pkg
is

type codes_t is table of number;

function getCodes(
   acc_id in NUMBER
)
return codes_t PIPELINED;
end pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg
is
  function getCodes(
    acc_id in NUMBER
  ) return codes_t PIPELINED
  is
    v_codes codes_t := codes_t();
  begin
     for r in (select acc_id as code from dual
                union 
                 select acc_id+1 from dual) loop
              PIPE row(r.code);
     end loop;
  end getCodes;
end pkg;
/