1 2 Previous Next 20 Replies Latest reply on Jul 16, 2010 5:26 PM by InoL

    run a report from PL/SQL


      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
          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:
          • 2. Re: run a report from PL/SQL
            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 */


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

            RETURN mystatus.statustext;

            WHEN OTHERS THEN
            RETURN 'An unhandled exception occured, please send the report manually';
            • 3. Re: run a report from PL/SQL
              Hi Sanki!
              Im using the 10g versions for both oracle and developer;
              the procedure is running in the database . . .
              • 4. Re: run a report from PL/SQL
                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 . . .

                • 5. Re: run a report from PL/SQL

                  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
                    Sanki, I just mentioned that this package is related to reports builder, please note that . . .
                    • 7. Re: run a report from PL/SQL
                      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

                        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
                          Ok! Thanks for both of you . . .

                          I will try that . . .
                          • 10. Re: run a report from PL/SQL
                            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

                            • 11. Re: run a report from PL/SQL
                              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
                                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')

                                • 13. Re: run a report from PL/SQL
                                  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
                                    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?

                                    1 2 Previous Next