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
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
First you need to select region type PL/SQL dynamic content. Then call a database procedure--
declare
vname number;
begin
R_MENU(vname );
end;
**// call oracle procedure in php**
$return = parent::sql("call MIGRATION_MO('".$variable1."','".$variable2."')", false);
**// call oracle function in php**
$return = parent::sql("SELECT MIGRATION('".$variable1."','".$variable2."') AS STATUS FROM DUAL", false);
dynamic_pivotinto a function that returns a VARCHAR2, not a ref cursor.