Home » Developer & Programmer » Forms » I can't rollback changes done by stored procedures
I can't rollback changes done by stored procedures [message #293857] Tue, 15 January 2008 05:36 Go to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
Hi,

I have 2 stored procedures to insert some data into some tables.

I am calling these two procedures in a single form, one after the another.

In runtime, when i commit the form, the first procedure is called and the data inserted into some tables.

But in second procedure if there is any problem occurs, then how to rollback the changes done by first procedure.

Please help me...

Regards
Mahaveer
Re: I can't rollback changes done by stored procedures [message #293873 is a reply to message #293857] Tue, 15 January 2008 07:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A short answer is: you can not. What is committed, can not be rolled back.

Not easily, that is.

Depending on your database version, there might be an option to recover a database to a point before commit was done. Check some of them in this OraFAQ Forum topic; as stated, these options should be performed by someone who exactly knows what has to be done. Otherwise, you might lose more than you'll get.

Just being curious: why don't you FIRST call these procedures, and commit at THE END? Any error in between will rollback the changes automatically (unless there's a COMMIT in the procedure, or ...).
Re: I can't rollback changes done by stored procedures [message #293976 is a reply to message #293873] Tue, 15 January 2008 17:06 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
As a 'rule of thumb' NEVER code 'commit' into anything expect your Form or a PLL procedure whose only action is to preform some special processing associated with doing a 'commit'.

David
Re: I can't rollback changes done by stored procedures [message #294798 is a reply to message #293873] Sat, 19 January 2008 04:59 Go to previous messageGo to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
I am not committing after first procedure.

my code is as below. It the below procedure I have called 2 Stored procedures one after the other.

Before committing the form I will call the below procedure. Here, the first procedure is working fine, it will insert some data into some tables (Still not committed anywhere). Now it comes to second procedure i.e Recovered_Process here if it fails then the changes made in the first procedure, must rolled back, whcih is not happening.

I thing now you got the idea.

Please help



PROCEDURE Commit_N_Recovered IS

M_Rec_No Number(5);
Result Number;
M_Int_Amount Number(12,2) := 0;
M_To_Int_Amount Number(12,2) := 0;
M_Bal_Amount N_Recovered.Recovered_Amount%Type;
BEGIN

--
---Start : Interest Calculation
--

--(P_Farmer_Id, P_Season_Cd, Season_To_Recover, P_Int_To_Date, P_Ded_Limit_Amount, P_Login_Cd, Out-P_Alert, Out-P_Alert_Message

Interest_Calculation (:N_Cash_Paid_Farmer_Transfer.Farmer_Id, :Global.Season_Cd, 99999,
:N_Cash_Paid_Farmer_Transfer.Int_To_Date, :N_Cash_Paid_Farmer_Transfer.Ded_Limit_Amount,
:Global.Login_Cd, :N_Cash_Paid_Farmer_Transfer.P_Alert, :N_Cash_Paid_Farmer_Transfer.P_Alert_Message);

--
---End : Temporary Interest Calculation
--

--Company_Cd, Farmer_Id, To_Farmer_ID, Season_Cd, Season_To_Recover, Payable_Amount, Login_Cd, Recovered_Bscil_Tran_No, Alert, Alert_Message

Recovered_Process (:Global.Company_Cd, :N_Cash_Paid_Farmer_Transfer.Farmer_Id, :N_Cash_Paid_Farmer_Transfer.To_Farmer_Id,
:Global.Season_Cd, 99999, :N_Cash_Paid_Farmer_Transfer.Ded_Limit_Amount, :Global.Login_Cd, M_Bal_Amount,
:N_Cash_Paid_Farmer_Transfer.Recovered_Bscil_Tran_No, :N_Cash_Paid_Farmer_Transfer.P_Alert, :N_Cash_Paid_Farmer_Transfer.P_Alert_Message);

END;

Re: I can't rollback changes done by stored procedures [message #294820 is a reply to message #293857] Sat, 19 January 2008 09:47 Go to previous messageGo to next message
akramrabaya
Messages: 41
Registered: November 2007
Member
may you can insert all ur changes in a temporary table and commit all togther once all ur procedures passed successfully

that will affect ur run time performance .. but it is just an idea u can use it till u find the sutable solution

akram
Re: I can't rollback changes done by stored procedures [message #295236 is a reply to message #294798] Mon, 21 January 2008 19:22 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please post the code for 'Interest_Calculation' and 'Recovered_Process' and PLEASE format the code and use code tags.

David
Re: I can't rollback changes done by stored procedures [message #295259 is a reply to message #295236] Mon, 21 January 2008 22:18 Go to previous messageGo to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
PROCEDURE Interest_Calculation (P_Farmer_Id In Varchar2, P_Season_Cd In Number, P_Season_To_Recover In Number,
P_Int_To_Date In Date, P_Ded_Limit_Amount In Number,
P_Login_Cd In Number, P_Alert Out Varchar2, P_Alert_Message Out Varchar2) IS
BEGIN

-- Interest Calculation Recovery
-- Take Bill from Recovery_Recovered whose Balance Amt > 0 Transaction Type = 'BILL'
-- Process Int Bill wise & Product wise ( Per Record)

Declare
Cursor C1 Is Select
Bscil_Tran_No, Bscil_Bill_Season_Cd, Bill_Season_Cd, Company_Cd, Main_Crop_Cd, Financial_Year_Cd, Farmer_Id, Bill_No, Product_Cd,
Interest_Rate + Service_Charge Interest_Service_Rate,
Int_To_Date Int_From_Date, Start_Recovery_Season_Cd, Sum(Balance_Amount) Balance_Amount
From
N_Recovery_Recovered
Where
Farmer_Id = P_Farmer_Id
And Start_Recovery_Season_Cd <= P_Season_To_Recover
And (Interest_Rate > 0 OR Service_Charge > 0)
And Balance_Amount > 0
Group By
Bscil_Tran_No, Bscil_Bill_Season_Cd, Bill_Season_Cd, Company_Cd, Main_Crop_Cd, Financial_Year_Cd, Farmer_Id, Bill_No, Product_Cd,
Interest_Rate, Service_Charge, Int_To_Date, Start_Recovery_Season_Cd, Bill_Date
Order By
Bill_Date, Bscil_Tran_No, Product_Cd;

M_Int_Bill_Seq_ID Number(5) := 11;
M_Bscil_Tran_No_Seq_Id Number(5) := 8;

M_Days Number(5) :=0;
M_int_Amount Number(15,2) :=0;
M_Fin_Year_Cd Financial_Year_Dir.Financial_Year_Cd%Type;
M_Bill_No N_Bill_Head.Bill_No%Type;
M_Bscil_Tran_No N_Bill_Head.Bscil_Tran_No%Type;

M_Ded_Limit_Amount N_Bill_Head_Tail.Amount%Type := P_Ded_Limit_Amount;

M_First_Time Char(1) := 'Y';
T_Bscil_Tran_No N_Bill_Head.Bscil_Tran_No%Type;

Begin
For Rec in C1 loop
M_Days := 0;

If M_Ded_Limit_Amount > 0 And Rec.Int_From_Date < P_Int_To_Date Then

M_Days := ( P_Int_To_Date - Rec.Int_From_Date ) + 1;
M_Int_Amount := Round( ((Rec.Balance_Amount * Rec.Interest_Service_Rate)/36500) * M_Days,0);
M_Ded_Limit_Amount := M_Ded_Limit_Amount - M_Int_Amount;

If P_Alert Is Null And M_Int_Amount > 0 Then

--Get_Fin_Year_Cd(Sysdate, M_Fin_Year_Cd);

If (M_First_Time = 'Y' OR T_Bscil_Tran_No <> Rec.Bscil_Tran_No) Then
M_First_Time := 'N';
T_Bscil_Tran_No := Rec.Bscil_Tran_No;

Get_Sequence_No(Rec.Company_Cd, P_Season_Cd, M_Bscil_Tran_No_Seq_Id, SysDate, M_Bscil_Tran_No, P_Alert, P_Alert_Message);
--Get_Sequence_No(Rec.Company_Cd, P_Season_Cd, M_Int_Bill_Seq_Id, SysDate, M_Bill_No, P_Alert, P_Alert_Message);

Insert Into N_Bill_Head (Bscil_Tran_No, Bscil_Bill_Season_Cd, Company_Cd, Main_Crop_Cd, Bill_Season_Cd, Financial_Year_Cd, Transaction_Type,
Bill_No, Bill_Date, Farmer_Id, Installments, Season_To_Recover, Remarks, Inserted_Through, KK_Bill_No, Add_By, Add_Date, Bill_Interest) Values
(M_Bscil_Tran_No, P_Season_Cd, Rec.Company_Cd, Rec.Main_Crop_Cd, Rec.Bill_Season_Cd, Rec.Financial_Year_Cd, 'NEW BILL', Rec.Bill_No, SYSDATE,
Rec.Farmer_Id, 1, Rec.Start_Recovery_Season_Cd, Null, 'SYSTEM', Null, P_Login_Cd, SYSDATE, 'INTEREST');
End If;

Insert Into N_General_Bill_Tail (Bscil_Tran_No, Product_Cd, Amount, Discount_Amount, Net_Amount, Interest_Rate,
Service_Charge, Ref_Bscil_Tran_No) Values
(M_Bscil_Tran_No, Rec.Product_Cd, M_Int_Amount, 0, M_Int_Amount, 0, 0, Rec.Bscil_Tran_No);

Insert Into N_Interest_Calculation (Bscil_Tran_No, Ref_Bscil_Tran_No, Ref_Product_Cd, Int_Season_Cd,
Principle_Amount, Int_Calculated_Date, Int_Calculated_Amount, Int_Calculated_Days,
Int_From_Date, Int_To_Date, Add_By, Add_Date) Values
(M_Bscil_Tran_No, Rec.Bscil_Tran_No, Rec.Product_Cd, P_Season_Cd,
Rec.Balance_Amount, Sysdate, M_Int_Amount, M_Days, Rec.Int_From_Date, P_Int_To_Date, P_Login_Cd, Sysdate);

Elsif P_Alert Is Not Null Then
Exit;
End If;

End If;
End Loop;


Exception
When Others Then
Rollback;
End;

END;

------------------------------------

PROCEDURE Recovered_Process (P_Company_Cd In Number, P_Farmer_Id In Varchar2, P_To_Farmer_Id In Varchar2,
P_Season_Cd In Number, P_Season_To_Recover In Number, P_Payable_Amount In Number, P_Login_Cd In Number,
P_Bal_Amount Out Number, P_Recovered_Bscil_Tran_No Out Number, P_Alert Out Varchar2, P_Alert_Message Out Varchar2) IS

--
-- P_Season_Cd - While inserting record in N_Recovered, Recovered_Season
-- P_Recovered_Bscil_Tran_No - This parameter is returned because, this no is saved in Farmer transfer OR Cane Payment Table as a link.
-- P_To_Farmer_id - This parameter is passed only from Farmer transfer prohgram, it will generate bills on To_Farmer_Id.
--

Cursor C1 Is Select
Bscil_Tran_No, Financial_Year_Cd, Bill_Season_Cd, Bill_No, Bill_Date, Farmer_Id, Company_Cd, Product_Cd, Installment_No, Balance_Amount, Recovery_Hierarchy,
Start_Recovery_Season_Cd, Main_Crop_Cd, Interest_Rate, Service_Charge, Bill_Interest
From
N_Recovery_Recovered
Where
Farmer_Id = P_Farmer_Id
And Season_To_Recover <= P_Season_To_Recover
And Balance_Amount > 0
Order By
Recovery_Hierarchy, Bill_Date, Installment_No, Bscil_Tran_No, Product_Cd;

M_Bill_Seq_ID Number(5) := 1;
M_Bscil_Tran_No_Seq_Id Number(5) := 8;
M_Recd_Bscil_Tran_No_Seq_Id Sequence_Dir.Sequence_Id%Type := 10;

M_Bal_Payable_Amount N_Recovery.Amount%Type := P_Payable_Amount;
M_Recovered_Amount N_Recovered.Recovered_Amount%Type;

M_Bscil_Tran_No N_Bill_Head.Bscil_Tran_No%Type;
-- M_Fin_Year_Cd Financial_Year_Dir.Financial_Year_Cd%Type;
-- M_Bill_No N_Bill_Head.Bill_No%Type;
Begin
Get_Sequence_No(P_Company_Cd, P_Season_Cd, M_Recd_Bscil_Tran_No_Seq_Id, SysDate, P_Recovered_Bscil_Tran_No, P_Alert, P_Alert_Message);
-- Get_Fin_Year_Cd(Sysdate, M_Fin_Year_Cd);

If P_Alert Is Null Then
For Rec in C1 loop
If M_Bal_Payable_Amount > 0 Then

If M_Bal_Payable_Amount >= Rec.Balance_Amount Then
M_Recovered_Amount := Rec.Balance_Amount;
M_Bal_Payable_Amount := M_Bal_Payable_Amount - Rec.Balance_Amount;
Elsif M_Bal_Payable_Amount < Rec.Balance_Amount Then
M_Recovered_Amount := M_Bal_Payable_Amount;
M_Bal_Payable_Amount := 0;
End If;

Insert Into N_Recovered (Bscil_Tran_No, Product_Cd, Installment_No, Recovered_Season_Cd, Recovered_Date, Recovered_Amount,
Add_By, Add_Date, Recovered_Bscil_Tran_No) Values (Rec.Bscil_Tran_No, Rec.Product_Cd, Rec.Installment_No, P_Season_Cd, Sysdate,
M_Recovered_Amount, P_Login_Cd, Sysdate, P_Recovered_Bscil_Tran_No);

If P_To_Farmer_Id Is Not Null Then

--Get_Sequence_No(P_Company_Cd, P_Season_Cd, M_Bill_Seq_Id, SysDate, M_Bill_No, P_Alert, P_Alert_Message);
Get_Sequence_No(P_Company_Cd, P_Season_Cd, M_Bscil_Tran_No_Seq_Id, SysDate, M_Bscil_Tran_No, P_Alert, P_Alert_Message);

Insert Into N_Bill_Head (Bscil_Tran_No, Bscil_Bill_Season_Cd, Company_Cd, Main_Crop_Cd, Bill_Season_Cd, Financial_Year_Cd, Transaction_Type, Bill_No,
Bill_Date, Farmer_Id, Installments, Season_To_Recover, Remarks, Inserted_Through, KK_Bill_No, Add_By, Add_Date, Bill_Interest) Values
(M_Bscil_Tran_No, P_Season_Cd, Rec.Company_Cd, Rec.Main_Crop_Cd, Rec.Bill_Season_Cd, Rec.Financial_Year_Cd, 'FARMER TRANSFER', Rec.Bill_No, SYSDATE,
P_To_Farmer_Id, 1, Rec.Start_Recovery_Season_Cd, Null, 'SYSTEM', Null, P_Login_Cd, SYSDATE, Rec.Bill_Interest);

Insert Into N_General_Bill_Tail (Bscil_Tran_No, Product_Cd, Amount, Discount_Amount, Net_Amount, Interest_Rate,
Service_Charge, Ref_Bscil_Tran_No) Values (M_Bscil_Tran_No, Rec.Product_Cd, M_Recovered_Amount, 0, M_Recovered_Amount,
Rec.Interest_Rate, Rec.Service_Charge, Rec.Bscil_Tran_No);
End If;

Elsif M_Bal_Payable_Amount = 0 Then
Exit;
End If;
End Loop;
End If;

P_Bal_Amount := M_Bal_Payable_Amount;
/*
Exception
When Others Then
Rollback;
Raise_Application_Error(-20000, 'There is problem in Recovered_Process Procedure.');
*/
END ;

Re: I can't rollback changes done by stored procedures [message #295265 is a reply to message #295259] Mon, 21 January 2008 23:05 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I said to format the code and place it between code tags!!

What is the code in 'Get_Sequence_No'?

David
Re: I can't rollback changes done by stored procedures [message #295919 is a reply to message #295265] Wed, 23 January 2008 22:40 Go to previous messageGo to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
Hi David,

Lets take a simple example.

I have 2 tables Emp & Dept.

There are 2 stored procedures, one for to insert data into Emp and another to insert data into Dept.

In a form I am calling these two procedures.

First procedure 'Emp_Insert' is woring fine.

Then it will go to second procedure 'Dept_Insert' in this if any error occurs then it will raise an error.

But the data inserted through first procedure are committed.

How do i rollback that data.



Re: I can't rollback changes done by stored procedures [message #295934 is a reply to message #295919] Wed, 23 January 2008 23:39 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What part of ALL of the responses is not getting into your head?? If you 'commit' you have committed the data. If you want to be able to 'go back' then DON'T commit the first set of data changes.

David
Re: I can't rollback changes done by stored procedures [message #295937 is a reply to message #295934] Wed, 23 January 2008 23:40 Go to previous messageGo to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
Still I have not committed any where,
Re: I can't rollback changes done by stored procedures [message #295940 is a reply to message #295937] Wed, 23 January 2008 23:44 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I repeat "What is the code in 'Get_Sequence_No'?"

David
Re: I can't rollback changes done by stored procedures [message #295942 is a reply to message #295934] Wed, 23 January 2008 23:50 Go to previous messageGo to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
One more question

I have 2 database triggers on one table
triggers are like After Insert on the table.

Lets take Test table,

After Insert to 'TEST' table

1st triggers

Insert Into DEPT (DeptNo, DeptName) Values (:New.Dept_No, :New.Dept_Name);

There is not problem with this trigger.

2nd Trigger

Insert Into EMP (EmpNo, EmpName, DeptNo) Values
(:New.Emp_No, :New.Emp_Name, :New.Dept_No);

If any error occurs here (like unique constraint).
Then the data inserted through 1st trigger into DEPT table are committed, but i have not committed any where. Now how do i roll back.

I amy be not clear with the fundas, but please help me to understand it.

I appriciate your patience.

Re: I can't rollback changes done by stored procedures [message #295943 is a reply to message #295940] Wed, 23 January 2008 23:52 Go to previous messageGo to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
Get_Sequence

This a procedure, which will generate the next number for given parameters.

Works like a sequence.

Re: I can't rollback changes done by stored procedures [message #295945 is a reply to message #295943] Thu, 24 January 2008 00:04 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
@mahaveer,

For the third and final time, "What is the code in 'Get_Sequence_No'?"

If you do not respond with this information I will not respond further to this thread.

David
Re: I can't rollback changes done by stored procedures [message #295952 is a reply to message #295945] Thu, 24 January 2008 00:34 Go to previous messageGo to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
Sorry for that,

PROCEDURE Get_Sequence_No (P_Company_Cd In Number, P_Season_Cd In Number, P_Sequence_Id In Number, P_Seq_Date In Date,
P_Sequence_No Out Varchar2, P_Alert_Type OUT VARCHAR2, P_Alert_Message OUT VARCHAR2) IS

M_SEQ_START_NO Sequence_Ini.SEQ_START_NO%Type;
M_SEQ_END_NO Sequence_Ini.SEQ_END_NO%Type;
M_INCREMENT_BY Sequence_Ini.INCREMENT_BY%Type;
M_PREFIXED_BY Sequence_Ini.PREFIXED_BY%Type;
M_PREFIX_DIVIDER Sequence_Ini.PREFIX_DIVIDER%Type;
M_SUFFIXED_BY Sequence_Ini.SUFFIXED_BY%Type;
M_SUFFIX_DIVIDER Sequence_Ini.SUFFIX_DIVIDER%Type;
M_Season_Wise Char(1);
BEGIN
Begin
Select Season_Wise Into M_Season_Wise From Sequence_Dir Where Sequence_Id = P_Sequence_Id;

Begin
If M_Season_Wise = 'Y' Then
Select Sequence_No, SEQ_START_NO, SEQ_END_NO, INCREMENT_BY, PREFIXED_BY, PREFIX_DIVIDER, SUFFIXED_BY,
SUFFIX_DIVIDER Into P_Sequence_No, M_SEQ_START_NO, M_SEQ_END_NO, M_INCREMENT_BY, M_PREFIXED_BY, M_PREFIX_DIVIDER,
M_SUFFIXED_BY, M_SUFFIX_DIVIDER From Sequence_Ini Where Company_Cd = P_Company_Cd And Season_Cd = P_Season_Cd
And Sequence_Id = P_Sequence_Id --And P_Seq_Date Between Seq_Start_Date And Seq_End_Date
For Update NOWAIT;
Elsif M_Season_Wise = 'N' Then
Select Sequence_No, SEQ_START_NO, SEQ_END_NO, INCREMENT_BY, PREFIXED_BY, PREFIX_DIVIDER, SUFFIXED_BY,
SUFFIX_DIVIDER Into P_Sequence_No, M_SEQ_START_NO, M_SEQ_END_NO, M_INCREMENT_BY, M_PREFIXED_BY, M_PREFIX_DIVIDER,
M_SUFFIXED_BY, M_SUFFIX_DIVIDER From Sequence_Ini Where Company_Cd = P_Company_Cd
And Sequence_Id = P_Sequence_Id And P_Seq_Date Between Seq_Start_Date And Seq_End_Date For Update NOWAIT;
End If;

If P_Sequence_No <> 0 Then
P_Sequence_No := P_Sequence_No + M_Increment_By;
Else
P_Sequence_No := M_Seq_Start_No;
End If;

If M_Season_Wise = 'Y' Then
Update Sequence_Ini Set Sequence_No = P_Sequence_No Where Company_Cd = P_Company_Cd And Season_Cd = P_Season_Cd
And Sequence_Id = P_Sequence_Id; --And P_Seq_Date Between Seq_Start_Date And Seq_End_Date ;
Elsif M_Season_Wise = 'N' Then
Update Sequence_Ini Set Sequence_No = P_Sequence_No Where Company_Cd = P_Company_Cd
And Sequence_Id = P_Sequence_Id And P_Seq_Date Between Seq_Start_Date And Seq_End_Date ;
End If;

Commit;

If M_Prefixed_By Is Not Null Then
P_Sequence_No := M_Prefixed_By||M_Prefix_Divider||P_Sequence_No;
End If;

If M_Suffixed_By Is Not Null Then
P_Sequence_No := P_Sequence_No||M_Suffix_Divider||M_Suffixed_By;
End If;
Exception
When No_Data_Found Then
P_Alert_Type := 'BSCIL_STOP';
P_Alert_Message := 'Sequence initialization not yet done.';
End;

Exception
When No_Data_Found Then
P_Alert_Type := 'BSCIL_STOP';
P_Alert_Message := 'Sequence initialization not yet done.';
End;

END;

[EDITED by LF: I hope you see a COMMIT now]

[Updated on: Thu, 24 January 2008 00:53] by Moderator

Report message to a moderator

Re: I can't rollback changes done by stored procedures [message #295956 is a reply to message #295952] Thu, 24 January 2008 00:39 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Can you see the statement 'commit' in the code that you just posted? That's why you can't 'rollback' your changes.

Remove it and see what happens.

David
Re: I can't rollback changes done by stored procedures [message #295983 is a reply to message #295956] Thu, 24 January 2008 01:34 Go to previous messageGo to next message
mahaveer
Messages: 16
Registered: July 2000
Junior Member
Does it affect to another stored procedure.

I thought the scope of this Commit is only for that stored procedure.

Re: I can't rollback changes done by stored procedures [message #295989 is a reply to message #295983] Thu, 24 January 2008 02:12 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If it were an autonomous transaction (search the net for this pragma), then COMMIT issued in this stored procedure would affect only its inserts/updates/deletes. Otherwise, it affects it all.

If you think it is a good idea (once you read more about autonomous transaction), think twice. It is useful in error logging procedures (for example), but in many cases it just means that you have (or will have) serious problems with data integrity.
Previous Topic: hw to change value of one field s soon as other field changes
Next Topic: Dealing with forms and LOV
Goto Forum:
  


Current Time: Sat Dec 10 09:06:05 CST 2016

Total time taken to generate the page: 0.07735 seconds