Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Stored Procedure with Multiple Response to Calling Program (Oracle 11.2.0.1.0)
Oracle Stored Procedure with Multiple Response to Calling Program [message #651189] Thu, 12 May 2016 09:01 Go to next message
dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US
Junior Member
We have a requirement where the Oracle stored procedure is being called by the webservice. Here the procedure generates a sequence number for (quote number) immediately after the call and return back to the calling webservice to display the Quote number in the screen. Meanwhile, the procedure will move forward and process the rest of the code to populate few tables (during this time the user clicks on the quote number and go through the quote details and select one. This is basically to buy time during the process of generating the quote details. Please help.
Re: Oracle Stored Procedure with Multiple Response to Calling Program [message #651193 is a reply to message #651189] Thu, 12 May 2016 09:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't. And really you shouldn't need to, populating a few tables should take no more than a second or two.
If it's a serious problem and you can't speed up the code (which is the first thing you should try) then split the procedure into two seperate ones - 1 to generate the sequence number and 1 to do the rest and have the webservice call them sequentially.
Re: Oracle Stored Procedure with Multiple Response to Calling Program [message #651196 is a reply to message #651193] Thu, 12 May 2016 09:20 Go to previous messageGo to next message
dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US
Junior Member
Thanks cookiemonster. Splitting the procedures is one of the last options we can think of, as this is a code procedure being used in lot many places and should be tested across the system. Also, By populating few tables i mean at the end of the procedure it does populate the tables and don't need to return back any output to the webservice call.
Is there a way of using Queues for this req you can think of, Just in case?
Re: Oracle Stored Procedure with Multiple Response to Calling Program [message #651199 is a reply to message #651196] Thu, 12 May 2016 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could use dbms_scheduler to kick off the table population but that would be my absolute last choice. I'd seriously look at why the table population is taking so long - how many tables? How many rows?
Re: Oracle Stored Procedure with Multiple Response to Calling Program [message #651200 is a reply to message #651199] Thu, 12 May 2016 09:46 Go to previous message
dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US
Junior Member
I thought of dbms_scheduler but as this is a real time process even i did not want to take chances with the scheduler just in case it goes to wait in queue. Also, its not just a simple table population only. Maybe i should have been more clear in this case. There are a lot of business logic and validations involved after the sequence generation which ultimately loads into the tables.
Previous Topic: create dummy rows
Next Topic: create stored procedure with cursor as an out argument
Goto Forum:
  


Current Time: Thu Mar 28 03:38:20 CDT 2024