Home » SQL & PL/SQL » SQL & PL/SQL » Tran
Tran [message #264916] Wed, 05 September 2007 00:48 Go to next message
dbpatil01
Messages: 9
Registered: September 2007
Junior Member
Hi,
I have a query regarding the transaction control..

There is a temporary table which contains few records about a person...

A record contains personal info... address details... nominee details... bank info .... and licence details...

Once we get this record in the temporary table now this record has to be split and transfered to the respective tables i.e. address details, personal info., nominee details...

I need to ensure that all the data which is split up pertaining to a single record is inserted in the respective tables....

Commit should only happen when the data from a particular record is inserted in all the respective tables..... the complete transaction is to be rollback if it fails inserting in even 1 table....

Please help..
Razz
Re: Tran [message #264918 is a reply to message #264916] Wed, 05 September 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the problem?
I mean where are you struck, what did you try, what is your Oracle version, what are the table descriptions, what is the input, from where do you get the data (file, other table...).

Please read and follow OraFAQ Forum Guide, including "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Tran [message #264919 is a reply to message #264916] Wed, 05 September 2007 00:55 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Please help..
Did you read & follow ANY posting guidelines as stated in STICKY posts at top of this forum?
Your post is so generic it could apply to MYSQL, SQLServer, DB2, or any RDBMS.
Please post your homework question in a more appropriate forum.

Re: Tran [message #264973 is a reply to message #264916] Wed, 05 September 2007 03:02 Go to previous messageGo to next message
dbpatil01
Messages: 9
Registered: September 2007
Junior Member
Hi,

Thanks Michel for ur suggestion. I will reformat my question.

I am working on Oracle 9i.

I have a table which is in a denormalized form.

It holds records about a person which contains details such as personal info., address, nominee details, bank details and license details.

Now when i have to insert this record into different tables. Like the personal data has to go into the personal record table, address data has to go into the address record table....

Now if this denormalized record has to be transformed into normalized tables there has to be some check.

In case if it fails to be inserted in the denormalized table it needs to roll back.

The following procedure is used to upload data from temporary table (in denormalized form) to normalized table. In case the insert is not taken place in one table it should rollback.

cursor C is
select * from temporary
where status = 'passed';
begin


for z in C
LOOP
  INSERT INTO IM_DETAIL
 (       
 INTERMEDIARY_Id,
 TITLE           ,
 SURNAME,
 FIRSTNAME,
 MIDDLENAME,
 IM_DOB,
 IM_PAN,
 IM_PAN_STATUS,
 im_GENDER,
 im_status,
 INTERMEDIARY_TYPE,
 INTERMEDIARY_CATEGORY_ID,
 IM_CLUSTER_ID,
 OM_ID,
 PATHFINDER_ID,
 PREMIA_ID,
 IM_ROPS_LOCATION,
 IM_AS_COR_EM_ID,
 IM_AS_COR_EM_IRDA_NO,
 IM_AS_AGENT_ID,
 IM_AMADEUS_ID,
 IM_CCR_NO,
 IM_LEVEL_DESC_ID
  )
VALUES
(
'XXX',
'XXX',
000,
'26/DEC/07',
Z.intermediary_id,
z.im_initial,
z.Intermediary_Last_Name,
Z.INTERMEDIARY_FIRST_NAME,
z.Intermediary_Middle_Name,
Z.IM_DOB,
Z.IM_PAN,
z.IM_Pan_Status,
z.IM_sex ,
z.IM_Status ,
Z.INTERMEDIARY_TYPE,
z.Intermediary_Category_ID,
z.IM_Cluster_ID,
z.OM_ID ,
z.PathFinder_IM_ID ,
z.Premia_IM_ID ,
z.IM_ROPS_Location,
z.IM_Asscorp_Emp_ID ,
z.IM_Asscorp_Emp_IRDA_License_No ,
z.IM_Associated_Agent_ID  ,
z.IM_Amadeus_Id ,
z.IM_CCR_No,
z.IM_Level_Description_ID
);


INSERT INTO [B]im_address[/B]
(
 im_con_NAME,
 IM_CON_TELE_ISD_NUM1,
 IM_CON_TELE_STD_NUM1,
 IM_CON_TELE_NUM1,
 IM_CON_TELE_ISD_NUM2,
 IM_CON_TELE_STD_NUM2,
 IM_CON_TELE_NUM2,
 IM_FAX_NUMBER_ISD,
 IM_FAX_NUMBER_STD,
 faxnuMBER,
 IM_CONTACT_MOBILE_NUMBER_ISD,
 MOBILENUMBER,
 IM_PRE_MAIL_ADD_LINE1,
 IM_PRE_MAIL_ADD_LINE2,
 E_MAIL1        ,
 IM_PRE_STATE,
 IM_PRE_CITY_DISTRICT,
 IM_PRE_AREA_VILLAGE,
 IM_PRE_PIN_CODE,
 IM_PRE_COUNTRY,
 IM_PERM_MAIL_ADD_LINE1,
 IM_PERM_MAIL_ADD_LINE2,
  IM_PERM_AREA_VILLAGE,
 IM_PERM_COUNTRY,
nom_CON_TELE_ISD_NUM1,
 NOM_CON_TELE_STD_NUM1,
 NOM_CON_TELE_NUM1,
 NOM_CON_TELE_ISD_NUM2,
 NOM_CON_TELE_STD_NUM2,
NOM_CON_TELE_NUM2,
 NOM_FAX_NUMBER_ISD,
 NOM_FAX_NUMBER_STD,
 NOM_FAX_NUMBER,
 NOM_CONTACT_MOBILE_NUMBER_ISD,
 NOM_CONTACT_MOBILE_NUMBER,
 NOM_MAIL_ADD_LINE1,
 NOM_MAIL_ADD_LINE2,
 NOM_STATE,
 NOM_CITY_DISTRICT,
 NOM_AREA_VILLAGE,
 NOM_PIN_CODE,
 NOM_COUNTRY,
IM_PERM_STATE,
IM_PERM_CITY_DISTRICT,
im_perm_pincode,
nom_first_name
)
values
(
z.IM_Contact_person_Name,
z.IM_Cont_Telph_ISDcode_No1,
z.IM_Cont_Telph_STDcode_No1,
z.IM_Cont_Telph_No1,
z.IM_Cont_Telph_ISDcode_No2,
z.IM_Cont_Telph_STDcode_No2,
z.IM_Cont_Telph_No2,
z.IM_Fax_No_ISDcode ,
z.IM_Fax_No_STDcode ,
z.IM_Fax_No ,
z.IM_Cont_Mob_No_ISDcode,
z.IM_Cont_Mob_No ,
z.IM_Present_MailAddrLine1  ,
z.IM_Present_MailAddrLine2 ,
z.IM_Email_Id ,
z.IM_Present_State  ,
z.IM_Present_City_Dist  ,
z.IM_Present_Area_Vil ,
z.IM_Present_PinCode  ,
z.IM_Present_Country ,
z.IM_perm_risk_MailAddrLine1  ,
z.IM_perm_risk_MailAddrLine2 ,
z.IM_perm_risk_Area_Vil  ,
z.IM_perm_risk_Country ,
z.Nom_Cont_Telph_ISDcode_No1 ,
z.Nom_Cont_Telph_STDcode_No1 ,
z.Nom_Cont_Telph_No1 ,
z.Nom_Cont_Telph_ISDcode_No2 ,
z.Nom_Cont_Telph_STDcode_No2 ,
z.Nom_Cont_Telph_No2 ,
z.Nom_Fax_No_ISDcode ,
z.Nom_Fax_No_STDcode ,
z.Nom_Fax_No ,
z.Nom_Cont_MobNo_ISDcode ,
z.Nom_Cont_MobNo ,
z.Nom_MailAddrLine1 ,
z.Nom_MailAddrLine2 ,
z.Nom_State  ,
z.Nom_City_Dist  ,
z.Nom_Area_Vil  ,
z.Nom_PinCode  ,
z.Nom_Country,
z.IM_perm_risk_State  ,
z.IM_perm_risk_City_Dist,
z.IM_perm_risk_PinCode  ,
z.Nom_First_Name
);



INSERT INTO [B]im_license_det[/B]

(
IM_Lic_ExpDate ,
IM_Lic_Type,
im_effec_date,
im_irda_no
  )
values
(
z.IM_Lic_ExpDate,
z.IM_Lic_Type,
z.IM_Lic_Eff_Date,
z.IM_IRDA_License_no
);

insert into [B]im_nom_det[/B]

(
Nom_PAN_Number ,
Nom_Pan_Status ,
Nom_Sub_ID
)
values
(
 z.NOM_PAN_NUMBER,
 z.NOM_PAN_STATUS,
 z.NOM_SUB_ID
);

insert into im_login_det
(
COUNTRYCODE,
PERSONID,
PASSWORD,
ROLEID,
EFFECTIVEDATE,
EXPIRATIONDATE         ,
PASSWORDEXPIRATIONDATE ,
STATUS                 ,
CREATIONDATE           ,
CREATERUSERID          ,
TBLREFLOGINUSERS_VER   ,
DM_LSTUPDDT            ,
 IM_CREATION_TIME ,
IM_UPDATION_TIME
)
values
(
'xxx',
'xxx',
'xxx',
'xxx',
'xxx',
100,
'26/jul/2007',
'26/jul/2017',
'26/jul/2017',
'a',
'26/jul/2007',
'abc1',
1,
'26/jul/2007',
z.IM_Creation_Time ,
z.IM_Updation_Time
);

end loop;
Exception
   WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('THERE IS NO DATA IN THE TEMP2 TABLES');  
end pr_imfinalamsupload;

[Updated on: Wed, 05 September 2007 03:08] by Moderator

Report message to a moderator

Re: Tran [message #264976 is a reply to message #264973] Wed, 05 September 2007 03:09 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
EXCEPTION handler will never be executed as there's no SELECT statement which might raise NO-DATA-FOUND error.

You might check how many records are inserted (actually, as it is a loop, only one should be inserted at a time so this 'count' will be 1 (one)) using SQL%ROWCOUNT. Something like this:
INSERT INTO some_table VALUES (...);

IF SQL%ROWCOUNT = 0 
THEN
   raise an exception;
END IF;
Re: Tran [message #264986 is a reply to message #264976] Wed, 05 September 2007 03:33 Go to previous messageGo to next message
dbpatil01
Messages: 9
Registered: September 2007
Junior Member
I guess even this can work out.....

INSERT INTO some_table VALUES (...);

IF SQL%ROWCOUNT = 0
THEN
rollback;
else
commit;
END IF;
Re: Tran [message #264989 is a reply to message #264986] Wed, 05 September 2007 03:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why would rowcount ever be zero if you supply values for the record?
Re: Tran [message #264997 is a reply to message #264989] Wed, 05 September 2007 04:02 Go to previous messageGo to next message
dbpatil01
Messages: 9
Registered: September 2007
Junior Member
If i m not wrong then rowcount can be zero if the no record has been updated or inserted in the table.

Please correct me if i m wrong Razz
Re: Tran [message #265012 is a reply to message #264997] Wed, 05 September 2007 04:28 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; it is my fault here as I suggested this "= 0" thing. As all values are collected by a cursor, even though all of them are NULLs for a certain INSERT statement, SQL%ROWCOUNT would still return 1.

SQL%ROWCOUNT would be 0 in such a case:
BEGIN
  INSERT INTO DEPT (deptno, dname, loc)
    SELECT deptno, dname, loc
    FROM DEPT WHERE 1 = 2;
END;
Re: Tran [message #265030 is a reply to message #264997] Wed, 05 September 2007 05:11 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
dbpatil01 wrote on Wed, 05 September 2007 11:02
If i m not wrong then rowcount can be zero if the no record has been updated or inserted in the table.

Please correct me if i m wrong Razz

Ok, here the correction comes:
If you do a "insert .. values..", (not accounting for stupid triggers) either there will be 1 row inserted, or there will be an exception.
In both cases program flow will NEVER go into the if rowcount=0 part.
Previous Topic: regarding mutating error
Next Topic: doubt in rownum
Goto Forum:
  


Current Time: Wed Dec 07 18:23:00 CST 2016

Total time taken to generate the page: 0.09979 seconds