RE: a PL/SQL design question.
Title: RE: a PL/SQL design question. Jeremy, as I wholeheartedly agree that dbms_job is a good thing, here is my recent experience ... One of the development group recently posed us the question Can we use a db trigger to fire a dbms_job to be executed only once? we reluctantly agreed only on the condition that this job will not be a repeating job as 9012 has had its own problems with dbms_job (the server sometimes forgets that there a jobs to run ...). The dev team tested it in ACPT and okayed it to go. That night I was on call and computer room called me to say that the system is very slow and one of the support person called to say that they were getting ora-4030 errors on simple selects. Well I logged on, looked at the system, it showed some load, but then I looked at dba_job queue and boy there were 14000 jobs sitting waiting to be run. bottom line: I shut off job_queue_processes to zero, disabled the triggers on the tables that submitted these jobs, gave all the details to the developer and his manager after waking them up at 2am and received a promise that they will fix the code tomorrow before 12noon. They did. The reason, the development team didn't anticipate that there will be so many changes so they didn't optimize their code. I am all for AQ solution ... though I like dbms_job and they do work as advertised unless of course you are using 901x where thee are some bugs. My $0.02 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re:RE: a PL/SQL design question.
DBMS jobs are good things for stuff that can occur totally within the database and on a scheduled basis only. It sounds, RAJ, like your developers made a real mess. What would have been a better idea would have been to have the trigger update a table that the job on a scheduled basis checked for what it needed to do. Dick Goulet Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 12/10/2002 4:38 AM Jeremy, as I wholeheartedly agree that dbms_job is a good thing, here is my recent experience ... One of the development group recently posed us the question Can we use a db trigger to fire a dbms_job to be executed only once? we reluctantly agreed only on the condition that this job will not be a repeating job as 9012 has had its own problems with dbms_job (the server sometimes forgets that there a jobs to run ...). The dev team tested it in ACPT and okayed it to go. That night I was on call and computer room called me to say that the system is very slow and one of the support person called to say that they were getting ora-4030 errors on simple selects. Well I logged on, looked at the system, it showed some load, but then I looked at dba_job queue and boy there were 14000 jobs sitting waiting to be run. bottom line: I shut off job_queue_processes to zero, disabled the triggers on the tables that submitted these jobs, gave all the details to the developer and his manager after waking them up at 2am and received a promise that they will fix the code tomorrow before 12noon. They did. The reason, the development team didn't anticipate that there will be so many changes so they didn't optimize their code. I am all for AQ solution ... though I like dbms_job and they do work as advertised unless of course you are using 901x where thee are some bugs. My $0.02 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 META NAME=Generator CONTENT=MS Exchange Server version 5.5.2654.45 TITLERE: a PL/SQL design question./TITLE /HEAD BODY PFONT SIZE=2Jeremy, as I wholeheartedly agree that dbms_job is a good thing, here is my recent experience .../FONT /P PFONT SIZE=2One of the development group recently posed us the question quot;Can we use a db trigger to fire a dbms_job to be executed only once?quot; we reluctantly agreed only on the condition that this job will not be a repeating job as 9012 has had its own problems with dbms_job (the server sometimes forgets that there a jobs to run ...). /FONT/P PFONT SIZE=2The dev team tested it in ACPT and okayed it to go. That night I was on call and computer room called me to say that the system is very slow and one of the support person called to say that they were getting ora-4030 errors on simple selects./FONT/P PFONT SIZE=2Well I logged on, looked at the system, it showed some load, but then I looked at dba_job queue and boy there were 14000 jobs sitting waiting to be run. /FONT/P PFONT SIZE=2bottom line: I shut off job_queue_processes to zero, disabled the triggers on the tables that submitted these jobs, gave all the details to the developer and his manager after waking them up at 2am and received a promise that they will fix the code tomorrow before 12noon. They did./FONT/P PFONT SIZE=2The reason, the development team quot;didn't anticipatequot; that there will be so many changes so they didn't optimize their code./FONT/P PFONT SIZE=2I am all for AQ solution ... though I like dbms_job and they do work as advertised unless of course you are using 901x where thee are some bugs./FONT/P PFONT SIZE=2My $0.02/FONT /P PFONT SIZE=2Raj/FONT BRFONT SIZE=2__/FONT BRFONT SIZE=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp ;nbsp; MIS, ESPN Inc./FONT BRFONT SIZE=2Rajendra dot Jamadagni at ESPN dot com/FONT BRFONT SIZE=2Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. /FONT BRFONT SIZE=2QOTD: Any clod can have facts, but having an opinion is an art!/FONT /P /BODY /HTML This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: RE: a PL/SQL design question.
Title: RE: RE: a PL/SQL design question. Dear Richard, These are a bunch of developers who think they are GOD (at-least their managers think so) ... we have learned that it is better to let them try and fail themselves rather than trying to explain them the right thing ... it works all the time. It is their database, and they own it, so if it has any problems, they suffer ... and learn. Lather .. rinse .. repeat. Yes, they could have done something better, but sometimes they need output in a fraction of a second ... because when bottomline or clock gets updated during a live broadcast, having 1 to 1.5 second delay is unacceptable... Most other times these developers are good ... they fight if their queries take longer than 500ms ... they try to tune and tune the code to make it right. It is only sometimes something like this happens. Plus we never could reliably run dbms_jobs on 9012 on AIX 433 .. all dba related actions rely on cron .. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 9:13 AM To: Jamadagni, Rajendra; Multiple recipients of list ORACLE-L Subject: Re:RE: a PL/SQL design question. DBMS jobs are good things for stuff that can occur totally within the database and on a scheduled basis only. It sounds, RAJ, like your developers made a real mess. What would have been a better idea would have been to have the trigger update a table that the job on a scheduled basis checked for what it needed to do. Dick Goulet This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: RE: a PL/SQL design question.
Title: RE: RE: a PL/SQL design question. I'd like to add a point here as well... Because DBMS_JOBS are asynch, ie not transaction bound and not read-consistent, by design they should not be allowed to be fired from triggers or other event-driven mechanisms _if_ they are to accomplish some transaction-specific action. I can dream up all kinds of goofy crap that developers could accidentally do if they use this. I know, I'm one of them... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 6:14 AM To: Multiple recipients of list ORACLE-L Subject: Re:RE: a PL/SQL design question. DBMS jobs are good things for stuff that can occur totally within the database and on a scheduled basis only. It sounds, RAJ, like your developers made a real mess. What would have been a better idea would have been to have the trigger update a table that the job on a scheduled basis checked for what it needed to do. Dick Goulet Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 12/10/2002 4:38 AM Jeremy, as I wholeheartedly agree that dbms_job is a good thing, here is my recent experience ... One of the development group recently posed us the question Can we use a db trigger to fire a dbms_job to be executed only once? we reluctantly agreed only on the condition that this job will not be a repeating job as 9012 has had its own problems with dbms_job (the server sometimes forgets that there a jobs to run ...). The dev team tested it in ACPT and okayed it to go. That night I was on call and computer room called me to say that the system is very slow and one of the support person called to say that they were getting ora-4030 errors on simple selects. Well I logged on, looked at the system, it showed some load, but then I looked at dba_job queue and boy there were 14000 jobs sitting waiting to be run. bottom line: I shut off job_queue_processes to zero, disabled the triggers on the tables that submitted these jobs, gave all the details to the developer and his manager after waking them up at 2am and received a promise that they will fix the code tomorrow before 12noon. They did. The reason, the development team didn't anticipate that there will be so many changes so they didn't optimize their code. I am all for AQ solution ... though I like dbms_job and they do work as advertised unless of course you are using 901x where thee are some bugs. My $0.02 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 META NAME=Generator CONTENT=MS Exchange Server version 5.5.2654.45 TITLERE: a PL/SQL design question./TITLE /HEAD BODY PFONT SIZE=2Jeremy, as I wholeheartedly agree that dbms_job is a good thing, here is my recent experience .../FONT /P PFONT SIZE=2One of the development group recently posed us the question quot;Can we use a db trigger to fire a dbms_job to be executed only once?quot; we reluctantly agreed only on the condition that this job will not be a repeating job as 9012 has had its own problems with dbms_job (the server sometimes forgets that there a jobs to run ...). /FONT/P PFONT SIZE=2The dev team tested it in ACPT and okayed it to go. That night I was on call and computer room called me to say that the system is very slow and one of the support person called to say that they were getting ora-4030 errors on simple selects./FONT/P PFONT SIZE=2Well I logged on, looked at the system, it showed some load, but then I looked at dba_job queue and boy there were 14000 jobs sitting waiting to be run. /FONT/P PFONT SIZE=2bottom line: I shut off job_queue_processes to zero, disabled the triggers on the tables that submitted these jobs, gave all the details to the developer and his manager after waking them up at 2am and received a promise that they will fix the code tomorrow before 12noon. They did./FONT/P PFONT SIZE=2The reason, the development team quot;didn't anticipatequot; that there will be so many changes so they didn't optimize their code./FONT/P PFONT SIZE=2I am all for AQ solution ... though I like dbms_job and they do work as advertised unless of course you are using 901x where thee are some bugs./FONT/P PFONT SIZE=2My $0.02/FONT /P PFONT SIZE=2Raj/FONT BRFONT SIZE=2__/FONT BRFONT SIZE=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp ;nbsp;nbsp;nbsp ;nbsp; MIS, ESPN Inc./FONT BRFONT SIZE
RE: a PL/SQL design question.
Hi, The other option that you have is to use Advance Queuing. You can insert the row and a message on a queue within the same transaction. Your procedure will be a consumer of the messages in the queue. If the transaction that does the insert is rolled back then the message is never placed on the queue and your procedure is never executed. The dequeue of the message is also transactional so if your procedure fails the message will be left on the queue and redelivered. You need to handle the redelivery case is a sensible manner - that is, you do not want the message to be redelivered continually if it is going to fail all the time. Cheers, Craig. -Original Message- Sent: Friday, 29 November 2002 5:04 AM To: Multiple recipients of list ORACLE-L Andrey Bronfin wrote: Dear gurus! I'm looking for a solution to the following problem: I need a way to run a certain stored procedure as soon as a record is inserted into a certain table. A trigger is not feasible for this, since I do not want the execution of the procedure to be a part of the transaction that inserts a row into the table. I want the insertion to be visible to all the users (i.e. committed) as soon as the insertion is done, and then, as a separate transaction of its own, to run the stored procedure. Suggestions , please ? Thanks a lot ! Keyword = AUTONOMOUS TRANSACTION -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Craig Munday INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: a PL/SQL design question.
Title: RE: a PL/SQL design question.
An easy way to do this is using DBMS_JOB. That way you can get asynchronous execution, and it isn't tied to the transaction.
This is clipped from a fix I just did for a customer. It ain't complete, but hopefully you can follow the logic:
v_variables_in_table INTEGER;
v_job_num INTEGER;
v_job_started INTEGER;
BEGIN
BEGIN
SELECT job_num INTO v_job_num
FROM job_number_storage
WHERE job_name = 'stored_proc_name';
SELECT 1 INTO v_job_started
FROM user_jobs
WHERE job=v_job_num;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_job_started := 0;
END;
IF v_job_started = 1 THEN
DBMS_JOB.REMOVE(v_job_num);
END IF;
DELETE FROM job_number_storage
WHERE job_name = 'stored_proc_name';
-- start the job
-- insert into jdp_temp values ('starting job here',sysdate);
DBMS_JOB.SUBMIT(v_job_num,'stored_proc_name;',sysdate,'sysdate+1');
INSERT INTO job_number_storage (job_num,job_name)
VALUES (v_job_num,'stored_proc_name');
COMMIT;
END;
/
-Original Message-
From: Craig Munday [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 09, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: a PL/SQL design question.
Hi,
The other option that you have is to use Advance Queuing.
You can insert
the row and a message on a queue within the same transaction. Your
procedure will be a consumer of the messages in the queue.
If the transaction that does the insert is rolled back then
the message is
never placed on the queue and your procedure is never
executed. The dequeue
of the message is also transactional so if your procedure
fails the message
will be left on the queue and redelivered. You need to handle the
redelivery case is a sensible manner - that is, you do not
want the message
to be redelivered continually if it is going to fail all the time.
Cheers,
Craig.
-Original Message-
Sent: Friday, 29 November 2002 5:04 AM
To: Multiple recipients of list ORACLE-L
Andrey Bronfin wrote:
Dear gurus!
I'm looking for a solution to the following problem:
I need a way to run a certain stored procedure as soon as a
record is
inserted into a certain table.
A trigger is not feasible for this, since I do not want the
execution of
the
procedure to be a part of the transaction that inserts a
row into the
table.
I want the insertion to be visible to all the users (i.e.
committed) as
soon
as the insertion is done, and then, as a separate
transaction of its own,
to
run the stored procedure.
Suggestions , please ?
Thanks a lot !
Keyword = AUTONOMOUS TRANSACTION
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Craig Munday
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: a PL/SQL design question.
Title: RE: a PL/SQL design question.
Just
so you know, you should be able to manually "acknowledge" the enqueue of the
message on the queue which will make it availableto the
consumerbefore the transaction containing the insert is
commited.
-Original Message-From: Jeremy Pulcifer
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, 10 December 2002
10:09 AMTo: Multiple recipients of list ORACLE-LSubject:
RE: a PL/SQL design question.
An easy way to do this is using DBMS_JOB. That way you can get
asynchronous execution, and it isn't tied to the transaction.
This is clipped from a fix I just did for a customer. It ain't
complete, but hopefully you can follow the logic:
v_variables_in_table INTEGER;
v_job_num
INTEGER; v_job_started INTEGER;
BEGIN
BEGIN
SELECT job_num INTO
v_job_num
FROM
job_number_storage
WHERE job_name =
'stored_proc_name';
SELECT 1 INTO
v_job_started
FROM user_jobs
WHERE
job=v_job_num; EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_job_started :=
0; END;
IF v_job_started =
1 THEN
DBMS_JOB.REMOVE(v_job_num);
END IF;
DELETE FROM
job_number_storage
WHERE job_name =
'stored_proc_name';
-- start the
job --
insert into jdp_temp values ('starting job here',sysdate);
DBMS_JOB.SUBMIT(v_job_num,'stored_proc_name;',sysdate,'sysdate+1');
INSERT INTO
job_number_storage (job_num,job_name)
VALUES
(v_job_num,'stored_proc_name');
COMMIT;
END; /
-Original Message-
From: Craig Munday [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 09, 2002 2:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: a PL/SQL design question.
Hi, The other option
that you have is to use Advance Queuing.
You can insert the row and a message on a queue
within the same transaction. Your procedure
will be a consumer of the messages in the queue.
If the transaction that does the insert is rolled
back then the message is never placed on the queue and your procedure is never
executed. The dequeue of the message is also transactional so if your procedure
fails the message
will be left on the queue and redelivered. You need to handle the
redelivery case is a sensible manner - that is, you do
not want the message
to be redelivered continually if it is going to fail all the time.
Cheers, Craig.
-Original Message- Sent: Friday, 29 November 2002 5:04 AM To: Multiple recipients of list ORACLE-LAndrey
Bronfin wrote: Dear gurus! I'm looking for a solution to the following problem:
I need a way to run a certain stored procedure as
soon as a record is
inserted into a certain table. A
trigger is not feasible for this, since I do not want the execution of the procedure to be a part of the transaction that inserts a
row into the
table. I want the insertion to be visible to
all the users (i.e. committed) as
soon as the insertion
is done, and then, as a separate transaction of
its own, to run
the stored procedure. Suggestions , please
? Thanks a lot !Keyword
= AUTONOMOUS TRANSACTION -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult
INET: [EMAIL PROTECTED] Fat City Network
Services -- 858-538-5051 http://www.fatcity.com San Diego, California --
Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an
E-Mail message to: [EMAIL PROTECTED] (note
EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may also send the HELP command for other
information (like subscribing). --
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- Author: Craig
Munday INET:
[EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego,
California -- Mailing list and web
hosting services
-
To REMOVE yourself from this mailing list, send an
E-Mail message to: [EMAIL PROTECTED] (note
EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may also send the HELP command for other
information (like subscribing).
RE: a PL/SQL design question.
Thanks a lot ! -Original Message- Sent: Thursday, November 28, 2002 8:04 PM To: Multiple recipients of list ORACLE-L Andrey Bronfin wrote: Dear gurus! I'm looking for a solution to the following problem: I need a way to run a certain stored procedure as soon as a record is inserted into a certain table. A trigger is not feasible for this, since I do not want the execution of the procedure to be a part of the transaction that inserts a row into the table. I want the insertion to be visible to all the users (i.e. committed) as soon as the insertion is done, and then, as a separate transaction of its own, to run the stored procedure. Suggestions , please ? Thanks a lot ! Keyword = AUTONOMOUS TRANSACTION -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: a PL/SQL design question.
Hi ! The problem with this solution is that the row, inserted into my table, will be committed (i.e. seen by other users) only after the completion of the stored procedure called by the trigger. Thanks a lot ! -Original Message- Sent: Thursday, November 28, 2002 9:09 PM To: Multiple recipients of list ORACLE-L Andrey Please forgive me if this sounds like a theoretical solution, since I don't have the time to try it out relevant to your situation. But what about using the trigger to call a procedure which is an autonomous transaction ? You do this by placing the following anywhere in the declare section of the SP: pragma AUTONOMOUS_TRANSACTION This will allow the operation to take place and commit, completely independent of the master transaction. My 2 cents' worth. HTH. Ferenc Mantfeld -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Friday, November 29, 2002 4:34 AM To: Multiple recipients of list ORACLE-L Subject:a PL/SQL design question. Dear gurus! I'm looking for a solution to the following problem: I need a way to run a certain stored procedure as soon as a record is inserted into a certain table. A trigger is not feasible for this, since I do not want the execution of the procedure to be a part of the transaction that inserts a row into the table. I want the insertion to be visible to all the users (i.e. committed) as soon as the insertion is done, and then, as a separate transaction of its own, to run the stored procedure. Suggestions , please ? Thanks a lot ! File: ATT3.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mantfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: a PL/SQL design question.
Similarly, if you only want the procedure to be run IF the base transaction does a commit then you can use dbms_job (because the job submission process is part of the same txn) hth connor --- Stephane Faroult [EMAIL PROTECTED] wrote: Andrey Bronfin wrote: Dear gurus! I'm looking for a solution to the following problem: I need a way to run a certain stored procedure as soon as a record is inserted into a certain table. A trigger is not feasible for this, since I do not want the execution of the procedure to be a part of the transaction that inserts a row into the table. I want the insertion to be visible to all the users (i.e. committed) as soon as the insertion is done, and then, as a separate transaction of its own, to run the stored procedure. Suggestions , please ? Thanks a lot ! Keyword = AUTONOMOUS TRANSACTION -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
a PL/SQL design question.
RE: a PL/SQL design question.
here is my answer. -Original Message-From: Andrey Bronfin [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 28, 2002 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: a PL/SQL design question.
Re: a PL/SQL design question.
Andrey Bronfin wrote: Dear gurus! I'm looking for a solution to the following problem: I need a way to run a certain stored procedure as soon as a record is inserted into a certain table. A trigger is not feasible for this, since I do not want the execution of the procedure to be a part of the transaction that inserts a row into the table. I want the insertion to be visible to all the users (i.e. committed) as soon as the insertion is done, and then, as a separate transaction of its own, to run the stored procedure. Suggestions , please ? Thanks a lot ! Keyword = AUTONOMOUS TRANSACTION -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: a PL/SQL design question.
Andrey Please forgive me if this sounds like a theoretical solution, since I don't have the time to try it out relevant to your situation. But what about using the trigger to call a procedure which is an autonomous transaction ? You do this by placing the following anywhere in the declare section of the SP: pragma AUTONOMOUS_TRANSACTION This will allow the operation to take place and commit, completely independent of the master transaction. My 2 cents' worth. HTH. Ferenc Mantfeld -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Friday, November 29, 2002 4:34 AM To: Multiple recipients of list ORACLE-L Subject:a PL/SQL design question. Dear gurus! I'm looking for a solution to the following problem: I need a way to run a certain stored procedure as soon as a record is inserted into a certain table. A trigger is not feasible for this, since I do not want the execution of the procedure to be a part of the transaction that inserts a row into the table. I want the insertion to be visible to all the users (i.e. committed) as soon as the insertion is done, and then, as a separate transaction of its own, to run the stored procedure. Suggestions , please ? Thanks a lot ! File: ATT3.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mantfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
