This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jul 16, 2010 10:26 AM by InoL RSS

run a report from PL/SQL

640031 Newbie
Currently Being Moderated
Hi

Can a report be run from within PL/SQL?
If yes, how the parameters are passed?

Thank you!
  • 1. Re: run a report from PL/SQL
    147458 Newbie
    Currently Being Moderated
    What version of the Oracle and Developer Suite you are using?

    And where is the PL/SQL? is that running on Client or Database?

    Message was edited by:
    sankionline
  • 2. Re: run a report from PL/SQL
    440705 Newbie
    Currently Being Moderated
    We have just done some work on this. You can use the SRW package that can be installed (scripts are in the as10gr2 directory structure. The reports manual has thorough documentation on it (we used the report distribution stuff to automatically e-mail the resulting PDF to a recipent that is looked up). The code we came up looks something like this (this is still in dev phase):

    FUNCTION run_distribution_report(a_ord_id IN wpe_orders.ord_id%TYPE,
    a_distribution_xml IN VARCHAR2,
    a_send_to_requestor IN VARCHAR2,
    a_report_name IN VARCHAR2 DEFAULT 'wo.rdf') RETURN VARCHAR2 IS

    myplist srw_paramlist;
    myident srw.job_ident;
    mystatus srw.status_record;
    v_distribution_xml VARCHAR2(200);

    v_email people.peo_email%TYPE;
    v_date DATE;

    /* Cursors to lookup email adresses, server settings etc */

    BEGIN



    srw.start_debugging;
    myplist := srw_paramlist(srw_parameter('', ''));

    srw.add_parameter(myplist, 'GATEWAY', 'http://123.dummy.com/reports/rwservlet');
    srw.add_parameter(myplist, 'SERVER', 'REP_SERVERNAME');
    srw.add_parameter(myplist, 'REPORT', 'd:\123\reports\' || a_report_name); --path to where report is
    srw.add_parameter(myplist, 'USERID', g_username || '/' || g_password || '@' || g_tns);
    srw.add_parameter(myplist, 'DESTYPE', 'FILE');
    srw.add_parameter(myplist, 'DESFORMAT', 'PDF');
    srw.add_parameter(myplist, 'DESTINATION', g_filepath || '\' || v_distribution_xml);--path to where xml file
    srw.add_parameter(myplist, 'DISTRIBUTE', 'YES');
    srw.add_parameter(myplist, 'P_TO', v_email); --bespoke parameters for report
    srw.add_parameter(myplist, 'P_FILENAME', 'Work Order Report' || a_ord_id);
    srw.add_parameter(myplist, 'P_ORD_ID', a_ord_id);

    myident := srw.run_report(myplist);
    mystatus := srw.report_status(myident);
    srw.stop_debugging;

    RETURN mystatus.statustext;

    EXCEPTION
    WHEN OTHERS THEN
    RETURN 'An unhandled exception occured, please send the report manually';
    END;
  • 3. Re: run a report from PL/SQL
    640031 Newbie
    Currently Being Moderated
    Hi Sanki!
    Im using the 10g versions for both oracle and developer;
    the procedure is running in the database . . .
    Thanks
  • 4. Re: run a report from PL/SQL
    640031 Newbie
    Currently Being Moderated
    Thnak you Chris for that!

    But the srw is a package in reports builder, I need to run my procedure in the database and not on a client . . .

    null
  • 5. Re: run a report from PL/SQL
    147458 Newbie
    Currently Being Moderated
    Hmmm,

    Unfortunately I don’t have srw package installed and can not do it on the current databases I have.

    Thanks for the script as it taught me a new thing
  • 6. Re: run a report from PL/SQL
    640031 Newbie
    Currently Being Moderated
    Sanki, I just mentioned that this package is related to reports builder, please note that . . .
  • 7. Re: run a report from PL/SQL
    147458 Newbie
    Currently Being Moderated
    Hi Raj,

    Can't you use rwrun.exe to run the report??

    You can call that from PL/SQL using HOST/SHELL command. Have you tried this workaround ??

    ***********
    And Raj, The SRW package you can install on your database and kick your reports from there. That's what the above thread talks, i think. You can try the scripts available in C:\DevSuite10gR2\repadm61\UTL folder. :) It is also a built-in for Reports.
  • 8. Re: run a report from PL/SQL
    440705 Newbie
    Currently Being Moderated
    Hi,

    The SRW package and code above sits in the database, all the code above is in the database and called from trigger.
  • 9. Re: run a report from PL/SQL
    640031 Newbie
    Currently Being Moderated
    Ok! Thanks for both of you . . .

    I will try that . . .
  • 10. Re: run a report from PL/SQL
    574682 Explorer
    Currently Being Moderated
    there is some package like htp or something which allows you to invoke or call a html page from procedure. You an pass the reports url to this and it should work.

    Alternatively if you are running on client server then from the plsql if you can create a procedure with host and pass the rwrun along with the parameters then it will work in client server

    Rajesh
  • 11. Re: run a report from PL/SQL
    512818 Newbie
    Currently Being Moderated
    I can not for the life of me figure out where that package is so I can install it. Can you point me to the right directory and file?
  • 12. Re: run a report from PL/SQL
    574682 Explorer
    Currently Being Moderated
    Its htp package. search in google/metalink. You will get so many hits.

    Alternatively create a simple sql script and inside that you can use a host(iexplore.exe 'construct the whole url')

    Rajesh
  • 13. Re: run a report from PL/SQL
    649614 Newbie
    Currently Being Moderated
    I am also looking for the same package...I need to run a report from PL/SQL.
    Please help.
  • 14. Re: run a report from PL/SQL
    512818 Newbie
    Currently Being Moderated
    I found a work around for the SRW solution using the UTL_HTTP package.

    The SRW package looks like a solution made just for 10g and higher... I tried installing it into my database and it told me that the version was wrong... im using a 10g AS but 9i DB... should I be running the 9i AS version of SRW?

    Now im trying to figure out if its possible to run a report and store the results directly into the database in a blob... anyone done this before?

    Mo
1 2 Previous Next