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  |
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 #294798 is a reply to message #293873] |
Sat, 19 January 2008 04:59  |
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 #295259 is a reply to message #295236] |
Mon, 21 January 2008 22:18 |
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 #295919 is a reply to message #295265] |
Wed, 23 January 2008 22:40 |
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 #295942 is a reply to message #295934] |
Wed, 23 January 2008 23:50 |
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 #295952 is a reply to message #295945] |
Thu, 24 January 2008 00:34 |
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 #295989 is a reply to message #295983] |
Thu, 24 January 2008 02:12 |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
Goto Forum:
Current Time: Mon Mar 10 19:08:23 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
