How do I get the Job Id for a URL submitted report?

[randall2nd]

I am submitting jobs to the Report Server to generate a report. This is working fine. However, some the reports can take a while.

I would like to be able to check on whether or not the report has finished. I can use the showjobs "function" and get a list of all the jobs with their Job Id and Status. Once the Report is finished, I would then set a flag that allows the users to download the report.

Is there a way to get the "Job Id" when the request is submitted?

All sugestions appreciated.

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.

 

[HimanB]

Hi,
Are you running the report on the WEB or Clinet/Server?
What built-in are you using to run the report?

You may make use of RUN_REPORT_OBJECT built-in to run your report & then use REPORT_OBJECT_STATUS built-in to check the status of the report.

DECLARE 
	repid REPORT_OBJECT; 
	v_rep  VARCHAR2(100); 
	rep_status varchar2(20); 
BEGIN 
	repid := find_report_object('report4'); 
	v_rep := RUN_REPORT_OBJECT(repid); 
	rep_status := REPORT_OBJECT_STATUS(v_rep); 
 
	if rep_status = 'FINISHED' then 
		message('Report Completed'); 
		copy_report_object_output(v_rep,'d:\temp\local.pdf'); 
		host('netscape d:\temp\local.pdf'); 
	else 
		message('Error when running report.'); 
	end if; 
END;

HTH
Regards
Himanshu

 

[randall2nd]

First, thank you for your response.

Actually I am submitting the report over the URL, so I guess that would be on the WEB.

Here is the skinny:
I need to submit a report to be generated in a trigger, so that when a table is updated the report is generated. Once the report is generated I need to update the table to reflect this.

Problem:
Unfortunately our current Unix version does not support event driven publishing, so I cannot use those APIs.

The only way to perform the desired action is to use UTL_HTTP to call a URL on the Web Server. The URL being the rwcgi60 and the parameters to submit it to the Report Server. (Just to note my database is on one machine and the Web/Report Server are on another).

DECLARE
  V_RESULTS VARCHAR2(2000);
BEGIN

  V_RESULTS:= UTL_HTTP.REQUEST( '[URL unfurl="true"]http://reprts.webserver.com/cgi-bin/rwcgi60?userid=scott/tiger@orcl+server=repserv+report=rep1.rdf+destype=file+desname=test_001.txt+param1=2703'[/URL] );


  DBMS_OUTPUT.PUT_LINE( V_RESULTS );

END;

Some reports could take 5+ minuets to run, so I need some way of checking if the report is finished. Otherwise an attempt to retrieve a report that is processing or still in the queue will fail.

The showjobs function lists all requested reports and thier status. So, I know the information is kept somewhere, but how do I get at it from a trigger/stored procedure. Even that does me no good if I do not have the JobID.

So, it comes full circle back to "how do I get the JobId for the report that I submitted through the URL".

Is there a parameter I need to pass in to get it to return in the HTML response?
Can I specify a JobID when I submit?

All assisstance appreciated, I am willing to try almost anything.

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.

 

[HimanB]

Hi Randall2nd,
This is possible by making a slight modification to your code.Here is a sample code which I tested from SQL prompt:

SQL> ed
Wrote file afiedt.buf

  1  declare
  2   L_STRING varchar2(5000):=null;
  3   L_TEMP VARCHAR2(4000);
  4  begin
  5   L_STRING := '[URL unfurl="true"]http://wesco.polaris.co.in/cgi-bin/rwcgi60.cmd?server=orepsn002'||[/URL]
  6              '+userid=als/fwp@dbn005+report=alsr5020+desformat=html+destype=cache';
  7    Select utl_http.request(l_string)
  8          INTO L_TEMP from dual;
  9        dbms_output.put_line('Report generated');
 10      EXCEPTION
 11        WHEN OTHERS THEN
 12         dbms_output.put_line(sqlerrm||' report not  generated);
 13         dbms_output.put_line(L_temp);
 14* end;
SQL> /
Report generated

PL/SQL procedure successfully completed.

Note that Report generated. meesage was not displayed until report was generated on the Server.

HTH
Regards
Himanshu

 

[randall2nd]

Thank you.

I have recently come to this realization as well. I read the documentation to mean that the report was submitted to the queue and then reutrns. In actuality it submits it as a new job and the response waits for the report to be completely processed.

This is not a desired effect, as my intention is to put this on an after update trigger, this could lock the table. I am told some reports could take over 5 to 10 minuets to run. This would be a very bad thing.

I have tested it by updating 10 rows, each running a report that took about 30 seconds, and then refereshed the "showjobs" page for the report server. Sure enough, only one processed at a time and the table was locked for update.

So, back to the drawing board, unless anyone knows a parameter to include for it to queue up the reports and not wait for them to finish. In which I would also once again need a way to get the Job ID.

Himanshu, thank you so much for your assistance.

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.