Home » SQL & PL/SQL » SQL & PL/SQL » Using Collections (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit,OS: Linux)
Using Collections [message #577410] Fri, 15 February 2013 01:10 Go to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

HI i have some requirement but not able to figure it out how to achieve it. I have 4 main tables
PT_POL ->(POL_NO VARCHAR2,POL_SYS_ID NUMBER,POL_CUST VARCHAR2)
PT_SEC ->(SEC_SYS_ID NUMBER,SEC_POL_SYS_ID NUMBER,SEC_CODE VARCHAR2)
PT_RISK ->(RISK_SYS_ID NUMBER,RISK_ID VARCHAR2,RISK_SEC_SYS_ID NUMBER,RISK_POL_SYS_ID NUMBER)
PT_COVER ->(PC_SYS_ID NUMBER,PC_RISK_SYS_ID NUMBER,PC_CODE VARCHAR2,PC_SEC_SYS_ID NUMBER,PC_POL_SYS_ID NUMBER)

Link between(i.e. Forgn key) POL_SYS_ID->SEC_POL_SYS_ID->RISK_POL_SYS_ID->PC_POL_SYS_ID

SEC_SYS_ID->RISK_SEC_SYS_ID->PC_SEC_SYS_ID

RISK_SYS_ID ->PC_RISK_SYS_ID

and there are temp tables
T_PT_POL(POL_NO)
T_PT_SEC(SEC_POL_NO,SEC_CODE)
T_PT_RISK(POL_NO,SEC_CODE,RISK_ID)
T_PT_COVER(POL_NO,SEC_CODE,RISK_ID,CVR_CODE)

Now data will come from temp tables and we need to insert into MAIN TABLES.WHile inserting we need to generate the sys id from sequence and insert it.But while inserting into tables sysid should have link i.e
Suppose in pt_policy pol_sys_id is 1 (which is generated from sequence),
while inserting into pt_sec sec_sys_id 2(which is generated from sequence) but sec_pol_sys_id will be 1 and so on
while inserting into pt_risk risk_sys_id 3(which is generated from sequence) but sec_pol_sys_id will be 1 and risk_sec_sys_id will be 2
and while inserting into pt_cover pc_sys_id 4(which is generated from sequence) but pc_pol_sys_id will be 1 and pc_sec_sys_id will be 2 and pc_risk_sys_id will be 3.

How can i achieve it using Collections (To store the sys_id and then fetch from it).I know through simple Oracle tables we can achieve and tried also.But i have told by my senior to achieve using collections it.
Re: Using Collections [message #577411 is a reply to message #577410] Fri, 15 February 2013 01:20 Go to previous message
Michel Cadot
Messages: 58532
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Previous Topic: SQL Converting a varchar to a clob within an SQL union
Next Topic: Performance & tuning views
Goto Forum:
  


Current Time: Thu Jul 24 11:18:45 CDT 2014

Total time taken to generate the page: 0.23238 seconds