CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperoracle-apps
Farooq
Farooq

Reputation: 23

How to call RDF report in oracle PL/SQL Procedure?

I want to call RDF report inside the oracle pl/sql procedure with dynamic parameter.And send that report output to email. I am using oracle EBS 12.1.3.

Upvotes: 1

Views: 3669

Answers (1)

TenG
TenG

Reputation: 4004

If the reports are on the same server as the database, you can set up a DBMS_SCHEDULER job that runs a shell script that calls rwrun, and invoke this in your PLSQL.

The rwrun command has options to send the result via email:

rwrun report=myrep.rdf userid=un/pw@db desformat=pdf DESTYPE=mail DESNAME="[email protected], [email protected]" cc="[email protected]" bcc="[email protected]" replyto="[email protected]" from="[email protected]"

Other options are to use UTL_HTTP or similar to issue a rwcgi request to run the report.

See here for available options for running a Report:

https://docs.oracle.com/cd/B14099_19/bi.1012/b14048/pbr_cla.htm#i634293

https://docs.oracle.com/cd/A97329_03/bi.902/a92102/pbr_cla.htm#634395

Edited to show how to create via DBMS_SCHEDULER.

DBMS_SCHEDULER is a highly flexible and configurable feature with Oracle, with lots of options and often a offering a few ways to do things so the following is a generic example to get you started.

First create a shell script which runs the report:

run_rdf.sh:

rwrun report=myrep.rdf ...

Now script to create the DBMS_SCHEDULER job:

BEGIN
   DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name=> 'RUN_RDF',
      program_type=> 'EXECUTABLE',
      program_action  => '/bin/bash run_rdf.sh',
      enabled=> TRUE,
      comments=> 'Run RDF report'
   );
END;
/

You can add arguments to this program that would then get passed to the script:

BEGIN
 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
   program_name            => 'RUN_RDF',
   argument_position       => 1,
   argument_name           => 'arg_name',
   argument_type           => 'VARCHAR2',
   default_value           => '');
END;
/

So you must decide whether the parameters/arguments will be hardcoded into teh shell script, as passed in directly as command line args when defining program_action above, or set dynamically via the DEFINE_PROGRAM_ARGUMENT feature (see what I mean about there being several ways to use this?).

Set this as a runable job but do not specify times:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name     => 'RUN_RDF_JOB',
      program_name => 'RUN_RDF',
      start_date => NULL,
      repeat_interval => NULL,
      end_date => NULL,
      enabled => FALSE,
      auto_drop => FALSE,
      comments => '');
END;
/

To run:

BEGIN
   -- Set arguments values
   DBMS_SCHEDULER.set_job_argument_value('RUN_RDF_JOB',1,'arg1 value');
   DBMS_SCHEDULER.ENABLE_JOB('RUN_RDF_JOB'); -- Might need this
   DBMS_SCHEDULER.RUN_JOB('RUN_RDF_JOB');
END;
/

Monitor the job via the views (ALL/USER/DBA as necessary):

ALL_SCHEDULER_JOB_RUN_DETAILS
ALL_SCHEDULER_JOB_LOG

Upvotes: 1

Related Questions