2 Replies Latest reply: Feb 15, 2013 3:00 PM by rp0428 RSS

    Using Collections

    Chandan.Rattan
      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.
        • 1. Re: Using Collections
          Karthick_Arp
          You have temp table with data, that does not have any relation, i.e. Primary and Foreign keys are not defined and key columns do not even exist.

          Now you want to load them into a bunch of table which have primary and foreign key defined. For generating these keys you need to define the relation between the rows of different tables.

          For example lets take 2 tables

          <pre>
          PT_POL

          Column Name Data Type Key
          ---------------------------------------------------
          POL_NO VARCHAR2
          POL_SYS_ID NUMBER Primary Key
          POL_CUST VARCHAR2

          PT_SEC

          Column Name Data Type Key
          ---------------------------------------------------
          SEC_SYS_ID NUMBER Primary Key
          SEC_POL_SYS_ID NUMBER Foreign Key
          SEC_CODE VARCHAR2
          </pre>

          In the temp table you don't have data for POL_SYS_ID, SEC_SYS_ID, SEC_POL_SYS_ID.

          Now tell me how do you determine which are the SEC_SYS_ID records that fall in the same SEC_POL_SYS_ID. That is what are the child records for PT_POL.POL_SYS_ID? This is a business rule that need to be defined first. Once you have that first we would be able to proceed.

          Note: You have not provided necessory details to answer your question. So i would suggest you read {message:id=9360002} to understand how to ask and post a question in this forum.
          • 2. Re: Using Collections
            rp0428
            >
            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.
            >
            As already suggested you first need to determine how the records relate to each other and how you plan to do the inserts: in bulk or row-by-row.

            You can return the SEQUENCEs generated (and other column info) using the RETURNING INTO clause

            See the PL/SQL Language doc
            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#CIHIEBJC
            >
            The INSERT, UPDATE, and DELETE statements can include a RETURNING clause, which returns column values from the affected row into a PL/SQL record variable. This eliminates the need to SELECT the row after an insert or update, or before a delete.

            By default, you can use this clause only when operating on exactly one row. When you use bulk SQL, you can use the form RETURNING BULK COLLECT INTO to store the results in one or more collections.