CODEWITHSUNDEEP
oracle-apexoracle-apex-5
GMan1973
GMan1973

Reputation: 319

APEX Oracle Package returning a data collection

Is it possible to create a APEX interactive grid off an oracle data collection in a package. It does not need to be an interactive grid but any report. I have a pkg with an out parameter (cursor) and I would like to display it on a APEX page. If it is possible, could you point me to an example or provide some instructions on how to accomplish this? Thank you

Upvotes: 1

Views: 1539

Answers (1)

Dan McGhan
Dan McGhan

Reputation: 4659

At this time, it is not possible to use cursors as the source for APEX components. This is because the APEX engine "wraps" the provided SQL query with more SQL to apply filters, do sorting, pagination, etc.

When a stored procedure returns a cursor, the cursor is already opened and its results can not be modified by the APEX engine.

Here are a couple of workarounds you could consider. Neither would be as performant as just supplying a SQL query for the report, but if the cursor doesn't return a lot of data then either should work fine.

  1. Consider using APEX Collections. APEX Collections are not PL/SQL collections. They can be thought of as generic, temporary tables linked to a user's session. On page load, you would write PL/SQL code that gets the cursor and uses it to populate a collection. Then the SQL query for the report would be written on top of the table.
  2. Consider wrapping the API that returns a cursor with you're own "table function" that returns a PL/SQL collection. Then the SQL query for the report would be written to call your wrapper using the TABLE function (which will treat the function call like an actual table).

Here's an example of creating a view:

-- Create a simple table
create table t (
  c  number,
  c2 varchar2(10)
);

-- Add some data
insert into t (c, c2) values (1, 'one');
insert into t (c, c2) values (2, 'two');
insert into t (c, c2) values (3, 'three');

-- Create a function that returns an opened cursor
create or replace function get_cursor
  return sys_refcursor
is

  l_cursor sys_refcursor;

begin
  open l_cursor for select * from t;

  return l_cursor;
end;
/

-- Create an object type based on a row of the cursor
create type cursor_ot as object(
  c  number,
  c2 varchar2(10)
);
/

-- Create a nested table based on the object type 
create type cursor_ntt is table of cursor_ot;
/

-- Create a pipelined function to fetch the data from the cursor
create or replace function get_piped_data

  return cursor_ntt pipelined

as

  l_cursor sys_refcursor;
  l_c      t.c%type;
  l_c2     t.c2%type;

begin

  l_cursor := get_cursor();

  loop
    fetch l_cursor into l_c, l_c2;
    exit when l_cursor%notfound;

    pipe row(cursor_ot(l_c, l_c2));
  end loop;

  close l_cursor;

end;
/

-- Create a view on the pipelined function
create or replace view t_piped_v
as
select *
from table(get_piped_data());

-- Select data from the view
select *
from t_piped_v;

Upvotes: 2

Related Questions