Home » SQL & PL/SQL » SQL & PL/SQL » How to "synchronize" procedures in PL/SQL (Oracle 10gR2, Unbreakable linux)
How to "synchronize" procedures in PL/SQL [message #317040] Tue, 29 April 2008 05:12 Go to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi! I need to flag some procedures and functions so that once they start they don't stop until they are finished. Because if there is more than one access at a time it could f*** up the integrity of our system.
I am looking for something similar to "synchronized" in Java. I wouldn't mind using Java stored procedures if it's necessary but only as a last resort
Anybody knows how to do that?
Greetings, Xokas
Re: How to "synchronize" procedures in PL/SQL [message #317044 is a reply to message #317040] Tue, 29 April 2008 05:23 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link. I think this is what you are after.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#g43931

Regards

Raj
Re: How to "synchronize" procedures in PL/SQL [message #317045 is a reply to message #317040] Tue, 29 April 2008 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to flag some procedures and functions so that once they start they don't stop until they are finished

How a procedure could exit if you don't tell it to finish or if you encounter an exception?

Quote:
Because if there is more than one access at a time it could f*** up the integrity of our system.

Don't you think about stopping that and rewriting the procedures?

You could use DBMS_ALERT or DBMS_LOCK to syncrhonize your procedures.

Regards
Michel
Re: How to "synchronize" procedures in PL/SQL [message #317050 is a reply to message #317045] Tue, 29 April 2008 05:43 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Michel It seems like I didn't make myself clear enough.
I am working as a PLSQL developer serving data to a PHP developer.

And there is a step in our system where we only want one user at a time calling a stored procedure. Because if it is called more than one time with the same parameters then it could mean trouble for the system.
Anyway I think that the documentation provided by youtself and Raj is what I'm looking for.

Have a nice day, Joaquin
Re: How to "synchronize" procedures in PL/SQL [message #317193 is a reply to message #317050] Tue, 29 April 2008 21:02 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
This is why I love this site - its humbling to see how much there is for me to learn. I've been, as i put it, stuck in the "back end caves" of pl/sql - doing straight backend apps development for large volume ETL processing so i am novice in experience to concepts of interacting with front end processes.

I took a look at that very knowledgable site reference - and decided to throw in my "uncouth" 2 cents of how i first envisioned the problem and my first thoughts on what i guess now could be called a "duct-tape" approach.

I have had to manage processes to "do work when there is work and to do no work when there is no work, and to know when to stop doing work for some circumstance" and had good success via snp processes (visa vis oracle job execution via dbms_jobs in conjunction with a "process manager" proc).

if it was feasible such that the procedure in question could not be invoked directly, but instead a 'process request' stored proc was executed every time a user wished to run the process you are trying to control for single execution then the scenario could be -

new-proc executed by users at will -simply updates a timestamp in a request process table. (with one field for last-process-timestamp)

a stored-proc (call it "polling_sp") which executes via snp_processes with this algorithm picks up the request:
#1 - unschedule itself from snp schedule via dbms_jobs package
#2 - check if a last-process-timestamp exists in the request_process_table. if yes then use dbms_jobs to reschedule itself to restart or kick off at a future interval
#3 - if no last-process-timestamp exists then check for new-request-time-stamp. if one exists then update the value to the last-process-timestamp and kick off the main stored procedure, passing the request-time-stamp in.
then reschedule itself to restart, or kickoff at a future interval

the main procedure upon completion deletes its entry from the request-process-table and commits.

"polling_sp" will then find the next request and fire the next iteration.

Probably the quick fix hack approach - but thats what backend cavemen like me are trained for!

one thing about "polling_sp" is it can set itself at any time in the dbms jobs table to "broken" (or by the main proc or any other entity) to halt the whole deal for desired situations...

just felt like sharing an old caveman trick!
-good luck
harry
Re: How to "synchronize" procedures in PL/SQL [message #317197 is a reply to message #317040] Tue, 29 April 2008 21:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>just felt like sharing an old caveman trick!
Forgive me for dismissing any such "trick" as nothing more than pumping faster on the bilge pump after shooting a hole the bulkhead below the waterline.

IMO, any well designed application would never have to resort to such bizzare contortions to avoid sinking, crashing, or burning.
Re: How to "synchronize" procedures in PL/SQL [message #317201 is a reply to message #317197] Tue, 29 April 2008 23:04 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Ouch! back to my cave i suppose Smile

the polling process was perfect for a scenario i had at an investment bank i used to work for. We'd get feeds from external vendors on corporate annoucnements that we would have to report to shareholders. consequently users would update data on the announcements manually.

I had to write the backend business rules to process the data over to our mainframe to feed downstream income and trade processing systems as well as our notification system that sent the announcements out the door.

i needed to prioritize data processed - user updates trump second source vendor data, etc, and also i needed to only send the latest info over - we could have a corporate action updated several times by a user and or vendor and were always interested in the latest data.

so i put a trigger on our master tables to input the timestamp and datasource and key id of an updated record into a staging
"to-be-processed" table.

My polling process simply looked to that table for work to do which would pile up after the nightly feeds. skip the snp processing aspect for now -

in general the polling proc took process records and checked
the master data to compare update timestamps and data source, deleting older transaction requests.

I needed to control the unit of work, so the polling proc would send a valid id to the main processing package which would obtain the latest data records from the input source, scrub and update our db as necessary, then replicate to db2, then delete the transaction record and commit.

instead of running a "looping" process - i scheduled the polling proc as a job. the algorithm was basically "stay running" - reschedule/restart or reschedule with a sleep interval until their were records to process. if there was any problem in the process - often with the db2 replication aspect, the polling proc would set itself to broken in the jobs table and, misfortunately for me, send my pager a text message (usually at 3 am) to let me know...(how nice of oracle!)

This was a perfect solution for a straight backend process that needed to control the aspects of work i mentioned, i'd love to get experience working with some client server aspects.

Do you know if there is any popularity with oracle's web apps server package and psp's pl/sql server pages or is it dead as a doornail? i have an older book on the 9iAs architecture. oracles web packages seem pretty robust for dynamic web apps in regards to html/xml but almost seemed to good to be true.
Any experience on your side with this?

in any regards - thank you for the "critique"!

best regards
-harry
Previous Topic: Alter table Modify Datatype
Next Topic: Bulk insert and commit
Goto Forum:
  


Current Time: Wed Dec 07 12:50:47 CST 2016

Total time taken to generate the page: 0.08431 seconds