Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL and Java Servlets
PL/SQL and Java Servlets [message #224153] Tue, 13 March 2007 05:16 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi guys,

This is a bit of a Java and PL\SQl question.

I've got the following code to call a PL\SQL stored procedure from a web based servlet application.

   
    CallableStatement cst = conn.getReadOnlyConnection().prepareCall({call 
PCK_PRODUCT.PROCESS_TRANSACTIONS(?,?,?)};




The stored procedure in the database is in a package called pck_product and the procedure itself is called process_transactions. The procedure goes through several text files and processes the contents on the txt files and load the results into tables in the database.


I am writing a web page (Java Servlet) which will contain a button which when clicked, will execute the above stored procedure. The problem i have is when this button is clicked and the above statement is executed, the servlet/java application waits for the stored procedure to complete before a confirmation page is displayed.

The stored procedure usually takes around 2 hrs to process all txt files so you can imagine that its not practical for the client side application to wait for the PL\SQL to complete. The stored procedure does produce its own logs which the application uses if needed.

What i would like to do is when the button is pressed, the stored procedure is executed and the client application shouldnt wait for the stored procedure to complete.

Could anyone point me in the right direction as to how i can achieve this?

The code im currently using to execute the stored procedure is shown above.

Thanks.
Re: PL/SQL and Java Servlets [message #224230 is a reply to message #224153] Tue, 13 March 2007 07:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Take a look at dbms_job (or dbms_scheduler if you are on 10g) to start the procedure asynchronously.
Re: PL/SQL and Java Servlets [message #224232 is a reply to message #224230] Tue, 13 March 2007 08:07 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi,

Many thanks for your reply. Im using Oracle v9.2.0.7.0.

Regards
D
Re: PL/SQL and Java Servlets [message #224244 is a reply to message #224232] Tue, 13 March 2007 08:33 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi, i've had a look at it and i think i can do it in Oracle 9.

One question that comes to mind is that job_queue requires a time to be specified. How do i specify the interval if i only want to run it once. I.e. whenever the button on the web page is pressed.



Thanks
Re: PL/SQL and Java Servlets [message #224257 is a reply to message #224244] Tue, 13 March 2007 09:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't specify the interval. That will start a one-off job.

oh, and don't forget to commit when submitting.. (often made mistake)

Come to think of it, I think you have to (at least) think about potential concurrency control now. It might very well be that all of a sudden more than one instance of the procedure gets run..

[Updated on: Tue, 13 March 2007 10:06]

Report message to a moderator

Re: PL/SQL and Java Servlets [message #224259 is a reply to message #224153] Tue, 13 March 2007 10:15 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Quote:

Come to think of it, I think you have to (at least) think about potential concurrency control now. It might very well be that all of a sudden more than one instance of the procedure gets run..



What will cause more than one procedure to be called? If i dont specify the interval wouldnt the job run just once.

I will also be adding a flag in a table which will be checked. The job will be added in teh job queue only if that flag shows that the job is not running. Will this sort out the concurrency problem?
Re: PL/SQL and Java Servlets [message #224260 is a reply to message #224259] Tue, 13 March 2007 10:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yep, this is (sort of) what I meant.
Prior to this, the user had no (regular) possibility to start a second instance, because his screen was frozen for 2 hours Smile
Now he can hit the button over and over.

If you flag it, how will you 'unflag' if the job somehow fails and gets interrupted before the flag is unset? But I am sure you work out a nice way; just thought I'd point you to the extra 'danger' of possible concurrent runs
Re: PL/SQL and Java Servlets [message #224262 is a reply to message #224153] Tue, 13 March 2007 10:33 Go to previous message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
I have a table with flags specifically for this purpose. The job will not be put in the queue if its already running in the queue.

There will also be another job which queries the queue from the all_jobs view. If there are no jobs in the queue then it will unflag any flagged jobs.

I woke up this morning thinking how on earth im going to sort this problem out. Now it looks like its a piece of cake!

Thanks for the help everyone.
Previous Topic: dbms_output.put_line
Next Topic: lost updates
Goto Forum:
  


Current Time: Sun Dec 04 14:50:28 CST 2016

Total time taken to generate the page: 0.22275 seconds