title: How to insert data into one table from multiple table using oracle procedure
body: <p>I have 6 different tables which has dependency with each other. I am trying to insert selected columns from all these 6 tables into a single table. </p>
  
  <p>I have to pass a parameter to select the data from one table based on the output of the table reaming tables data's are fetched. If no records match then null should be passed.</p>
  
  <p>I am trying to write a procedure using 6 cursor and pass the values of one cursor to others.</p>
  
  <p>Somebody Pls help me achieve this.</p>
  
  <p>Here is my simplifed verison of my table</p>
  
  <pre><code> Table Name                         table type
   ==========                         ==========
   CRM_CLAIM_INT_DETAILS_VIEW         Linked_Table
   INTRFC_MOTOR_NOTIFICATION_VIEW     Linked_Table
   Intrfc_Motor_Office_Master         Linked_Table
   INTRFC_MOTOR_RISK_VIEW             Linked_Table
   Intrfc_Policy_view                 Linked_Table
   MO_CLAIM_MASTER                    Table
  
  Selected Fields
  ===============
  CRM_ACC_DATE_TIME,CRM_GEN_REP_NAME,CRM_SYSDATE
  policy_number
  Parent_office_name
  CHASSIS_NO,ENGINE_NO,MAKE,MODEL,REGISTRATION_NO
  AGENT_NAME,Branch_Office Description,Insured_Address,Insured_Name,PRODUCT_CODE,RISK_START_DATE,RISK_START_TIME
  claim_no,DATE_OF_NOTIFICATION,notified_by,VEHICLE_TYPE
  
  Condition
  =========
  mo_claim_master.crm_sl_no = CRM_CLAIM_INT_DETAILS_VIEW.crm_sl_no
  INTRFC_MOTOR_NOTIFICATION_VIEW.claim_no = mo_claim_master.claim_no
  intrfc_policy_view.branch_office_code = Intrfc_Motor_Office_Master.office_code
  INTRFC_MOTOR_RISK_VIEW.refernce_num = intrfc_policy_view.num_reference_num
  intrf_notification_view.refernce_num = intrfc_policy_view.num_reference_num
  claim_ref_no=:claim_ref_no
  </code></pre>
  
  <p>INSERT INTO CFT_Table</p>
  
  <hr>
  
  <p>i have created a procedure for the same but still i am not able to get the null values.
  Is this a right way to get this output pl. suggest me.</p>
  
  <pre><code>      CREATE OR REPLACE
   PROCEDURE TEST(
       para_claim_ref_no IN VARCHAR2 )
   AS
     CURSOR c1
     IS
       SELECT claim_no,
         DATE_OF_NOTIFICATION,
         VEHICLE_TYPE,
         notified_by,
         notif_crm_sl_no
       FROM mo_claim_master
       WHERE claim_ref_no=para_claim_ref_no;
     t1 c1%rowtype;
     claim_num_var       VARCHAR2(50);
     notif_crm_sl_no_var NUMBER;
     CURSOR c2
     IS
       SELECT policy_num,
         reference_num
       FROM INTRFC_MOTOR_NOTIFICATION_VIEW
       WHERE claim_no=claim_num_var;
     t2 c2%rowtype;
     num_reference_num_var NUMBER;
     CURSOR c3
     IS
       SELECT BRANCH_OFFICE_CODE,
         Branch_Office_Desc,
         Insured_Name,
         Insured_Address,
         RISK_START_DATE,
         RISK_START_TIME,
         PRODUCT_CODE,
         AGENT_NAME
       FROM intrfc_policy_view
       WHERE num_reference_number=num_reference_num_var;
     t3 c3%rowtype;
     branch_office_code_var VARCHAR2(30);
     CURSOR c4
     IS
       SELECT OFFICE_CODE
       FROM INTRFC_MOTOR_OFFICE_MASTER
       WHERE office_code = branch_office_code_var;
     t4 c4%rowtype;
     CURSOR c5
     IS
       SELECT MAKE,
         MODEL,
         ENGINE_NO,
         CHASSIS_NO,
         REGISTRATION_NO
       FROM INTRFC_MOTOR_RISK_VIEW
       WHERE reference_num=num_reference_num_var;
     t5 c5%rowtype;
     CURSOR c6
     IS
       SELECT CRM_ACC_DATE_TIME,
         CRM_GEN_REP_NAME
       FROM CRM_CLAIM_INT_DETAILS_VIEW
       WHERE crm_slno=notif_crm_sl_no_var;
     t6 c6%rowtype;
   BEGIN
     OPEN c1;
     FETCH c1 INTO t1;
     CLOSE c1;
     claim_num_var       := t1.claim_no;
     notif_crm_sl_no_var := t1.notif_crm_sl_no;
     OPEN c2;
     FETCH c2 INTO t2;
     CLOSE c2;
     num_reference_num_var := t2.reference_num;
     OPEN c3;
     FETCH c3 INTO t3;
     CLOSE c3;
     branch_office_code_var := t3.BRANCH_OFFICE_CODE;
     OPEN c4;
     FETCH c4 INTO t4;
     CLOSE c4;
     OPEN c5;
     FETCH c5 INTO t5;
     CLOSE c5;
     OPEN c6;
     FETCH c6 INTO t6;
     CLOSE c6;
     dbms_output.put_line(t1.claim_no);
     dbms_output.put_line(t1.DATE_OF_NOTIFICATION);
     dbms_output.put_line(t1.VEHICLE_TYPE);
     dbms_output.put_line(t1.notified_by);
     dbms_output.put_line(t2.policy_num);
     dbms_output.put_line(t3.Branch_Office_Desc);
     dbms_output.put_line(t3.Insured_Name);
     dbms_output.put_line(t3.Insured_Address);
     dbms_output.put_line(t3.RISK_START_DATE);
     dbms_output.put_line(t3.RISK_START_TIME);
     dbms_output.put_line(t3.PRODUCT_CODE);
     dbms_output.put_line(t3.AGENT_NAME);
     dbms_output.put_line(t4.office_code);
     dbms_output.put_line(t5.MAKE);
     dbms_output.put_line(t5.MODEL);
     dbms_output.put_line(t5.ENGINE_NO);
     dbms_output.put_line(t5.CHASSIS_NO);
     dbms_output.put_line(t5.REGISTRATION_NO);
     dbms_output.put_line(t6.CRM_ACC_DATE_TIME);
     dbms_output.put_line(t6.CRM_GEN_REP_NAME);
   END TEST;
  </code></pre>