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';
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.
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 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?