This content has been marked as final. Show 20 replies
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:
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
/* 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);
WHEN OTHERS THEN
RETURN 'An unhandled exception occured, please send the report manually';
Im using the 10g versions for both oracle and developer;
the procedure is running in the database . . .
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 . . .
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
Sanki, I just mentioned that this package is related to reports builder, please note that . . .
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.
The SRW package and code above sits in the database, all the code above is in the database and called from trigger.
Ok! Thanks for both of you . . .
I will try that . . .
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
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?
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')
I am also looking for the same package...I need to 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?