CODEWITHSUNDEEP
oracle-databaseoracle-apex
Muhammad Sarmad Mahmood Malik
Muhammad Sarmad Mahmood Malik

Reputation: 437

call procedure Oracle apex

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

Upvotes: 2

Views: 2676

Answers (3)

Emanuel Borges
Emanuel Borges

Reputation: 1

**// 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);

Upvotes: 0

user12701630
user12701630

Reputation: 1

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

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

Upvotes: 0

rarspace01
rarspace01

Reputation: 419

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

Upvotes: 2

Related Questions