call procedure Oracle apex

2
votes

I have created a PL/SQL procedure like here: https://dba.stackexchange.com/questions/45016/dynamic-oracle-pivot-in-clause

Now I want to use the procedure as the Source for a Report. I am using Oracle Apex 4.0.

When I write it like this:

Declare
   x refcursor;
begin
   dynamic_pivot(x);
end;

The following error occurs:

ORA-20001: Query must begin with SELECT or WITH

However, it is possible to use a Procedure as the source as described here: Calling procedure in oracle apex

2
oracleoracle-apex
An Oracle apex report can only be based on a SQL query, or on a PL/SQL function body returning a SQL query. For the latter, your anonymous block must RETURN a string which contains the query to execute. – Jeffrey Kemp
The question you linked to doesn't demonstrate anything to do with Apex reports. – Jeffrey Kemp
I think what you may want to do is change dynamic_pivot into a function that returns a VARCHAR2, not a ref cursor. – Jeffrey Kemp

2 Answers

1
votes

You can't use a PL/SQL Procedure as a source for an interactive Report. What you can do is creating a function which you use inside your SELECT Query. E.g.:

SELECT * FROM TABLE YOUR_FUNCTION(PARM1,PARM2);

The definition may look like this:

create or replace function your_function
(
 PARM1 in integer,
 PARM2 in integer
) 
return holiday_tab pipelined
as
 v_easter_day date;
begin
...
end;

For more on pipelined take a look at: http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm#CHDJEGHC

0
votes

First you need to select region type PL/SQL dynamic content. Then call a database procedure--

declare
   vname number;
begin
   R_MENU(vname );
end;