CODEWITHSUNDEEP
oracle-databaseplsql
Pete
Pete

Reputation: 6723

Getting results from an Oracle package function returning a table

Our database has a package that returns a type t_daily_array_table.

This type is defined as: 

  TYPE t_daily_array_table IS TABLE OF r_daily_array_rec

and r_daily_array_rec is defined as a record with a number of fields.

We have a package called schedule_pkg with the function f_daily_array_table which returns the t_daily_array_table. The function takes a single Number parameter.

I've tried calling it a number of ways, but I can't seem to get it to work (I know this function works. It's called from an COBOL app that functions just fine).

I've tried:

select schedule_pkg.f_daily_array_table(74501) from dual;

When I do that, I get

SQL Error: ORA-00902: invalid datatype

I've tried:

select * from schedule_pkg.f_daily_array_table(74501)

That gives me:

SQL Error: ORA-00933: SQL command not properly ended

I've tried using code (C#) and calling in various ways using OleDb, OracleClient, and ODBC and I have yet to find a way I can call this, other than using COBOL.

Upvotes: 2

Views: 2263

Answers (2)

Alex Poole
Alex Poole

Reputation: 191285

If you want to query the values from an SQL context but have a PL/SQL collection type, you can create a pipelined wrapper function, either within the package or as a standalone object for testing/debuggging. Something like this should work:

create function f_daily_array_pipe(p_id IN number)
return schedule_pkg.t_daily_array_table pipelined is
  l_daily_array_table schedule_pkg.t_daily_array_table;
begin
  l_daily_array_table := schedule_pkg.f_daily_array_table(p_id);
  for i in 1..l_daily_array_table.count loop
    pipe row (l_daily_array_table(i));
  end loop;
end f_daily_array_pipe;
/

select * from table(f_daily_array_pipe(74501));

This calls your original function, assigns the result to a local collection, iterates over it, and pipes each record in turn back to the caller; which uses the SQL table() collection expression to convert it to something you can query from.

SQL Fiddle demo.

If you are using an OCI or JDBC client you can use an array descriptor and retrieve the data as an array, but that won't work from plain SQL.

Upvotes: 2

KevinKirkpatrick
KevinKirkpatrick

Reputation: 1456

I think the syntax you're looking for is:

select * from table(schedule_pkg.f_daily_array_table(74501));

Upvotes: 0

Related Questions