title: Select data from table by reading fetch condition from another table in Oracle
body: <p>I need to write a plsql code which should read channel_id, retailer_id, start_date and end_date column from table multiple_retailer .
  Then pass these column data as fetch condition in Register table to retrieve data and finally create a csv file out of it.</p>
  <p>Table: Multiple_retailer
  <a href="https://i.stack.imgur.com/hgLV4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hgLV4.png" alt="enter image description here" /></a></p>
  <p>For example:</p>
  <pre><code>select *
      from register
      where channel_id=563454 and retailer_id=53
      and read_time between '30/08/2018 00:00:00' and '30/08/2018 23:00:00:00';
  </code></pre>
  <p>create a csv file for above result.</p>
  <p>Again go to 2nd row of multiple_retailer, pass these columns in register table, fetch the data  and append the result in csv file.</p>
  <p>Please help me on this.</p>
  <p>Edited:
  I am able to create single file with all retailer_id. How can i split them into multiple files based on Retailer_id.</p>
  <p>For example: As shown in below image, i want to create 2 files : 1 with BUSTREAM-R and 1 with CASTLE-R</p>
  <p><a href="https://i.stack.imgur.com/2W38P.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2W38P.png" alt="enter image description here" /></a></p>
  <p>Below is my code</p>
  <pre><code>declare
  CURSOR C IS 
  
  select dsc.name Retailer_Name, 
         ch.UDC_ID Meter_Unique_Identifier, 
         dpf.value Frequency,
         --ch.channel_id,m.start_date,m.end_date,rr.read_time,
         m.retailer_id ,m.insert_time,
         TO_CHAR(m.start_date, 'YYYY')||TO_CHAR(m.start_date, 'MM')||TO_CHAR(m.start_date, 'DD')||TO_CHAR(m.start_date, 'HH24')|| TO_CHAR(m.start_date,'MI')||TO_CHAR(m.start_date,'SS')Start_Date, 
         TO_CHAR(m.end_date, 'YYYY')||TO_CHAR(m.end_date, 'MM')||TO_CHAR(m.end_date, 'DD')||TO_CHAR(m.end_date, 'HH24')|| TO_CHAR(m.end_date,'MI')||TO_CHAR(m.end_date,'SS') End_Date, 
         TO_CHAR(rr.Read_time, 'YYYY')||TO_CHAR(rr.Read_time, 'MM')||TO_CHAR(rr.Read_time, 'DD')||TO_CHAR(rr.Read_time, 'HH24')|| TO_CHAR(rr.Read_time,'MI')||TO_CHAR(rr.Read_time,'SS') Meter_Read_Timestamp, 
         TO_CHAR(rr.read_value, '9999999.9') Meter_Read,
         dp.value Meter_Mode,
         svc.value SPID
         --,dense_rank() over ( ORDER BY m.retailer_id )  AS rnk
  from multiple_retailer m 
  left join (select d.mfg_serial_num,d.udc_id, d.id device_id,c.id as channel_Id, c.name,c.type,dcr.eff_end_time from device d
             join device_channel_rel dcr on( d.id=dcr.device_id) and dcr.eff_end_time is null
             join channel c on (dcr.channel_id=c.id) and c.type='Register'
             ) ch on ch.mfg_serial_num=m.mfg_serial_num
  left join mudr.register_read rr on rr.read_time between m.start_date and m.end_date and rr.channel_id=ch.channel_id
  left join data_svc_class dsc on dsc.id=m.retailer_id
  left join device_param dp on dp.device_id=ch.device_id 
  left join device_param dpf on dpf.device_id=ch.device_id 
  left join svc_pt_device_rel SDR on SDR.device_id=ch.device_id
  left join (select * from svc_pt_param where name='Water SPID') SVC on SVC.SVC_PT_ID=SDR.SVC_PT_ID
  where m.status='pending'
        and dp.name='Connection Mode'
        and dpf.name='Read Frequency' 
        and dp.eff_end_time is null and dpf.eff_end_time is null
        order by dsc.name,ch.UDC_ID,rr.read_time
        --and dsc.name like 'DTS Class for%'
        ;
          
   
      TYPE rec_list_I IS TABLE OF C%ROWTYPE;
      recs_I  rec_list_I;
      V_CODE NUMBER;
      V_ERRM VARCHAR2(1000);
      v_file  UTL_FILE.FILE_TYPE;
      v_out_file VARCHAR2(1000) ;
      ldir VARCHAR2(100) := 'ET_MDMSTEST';
      f_exists BOOLEAN;
      F_Size PLS_INTEGER;
      f_blk_size PLS_INTEGER;
      --v_Retailer_id varchar2(100);
    
  BEGIN 
     --MeterReadsForPAENERGY_20201009042001
      SELECT 'MeterReadsFor'||  EXTRACT(year FROM sysdate)||TO_CHAR(sysdate, 'MM')|| TO_CHAR(sysdate, 'DD')||TO_CHAR(SYSDATE,'HH24MISS')||'.CSV' into v_out_file from dual;
    
      
      v_file := UTL_FILE.FOPEN( ldir,v_out_file ,'w',32000);
    
      
      OPEN C;
          LOOP
              FETCH C BULK COLLECT INTO recs_I;
              IF C%ROWCOUNT &gt;0 THEN
              
              UTL_FILE.PUT_LINE(v_file, '&quot;Retailer ID&quot;,&quot;Applicant Company Name&quot;,SPID,&quot;Meter Unique Identifier&quot;,Frequency,&quot;Start Date&quot;,&quot;End Date&quot;,&quot;Meter Read Timestamp&quot;,&quot;Meter Read&quot;,&quot;Meter Mode&quot;,Comments' );  -- inserting header into  file
              FOR i IN recs_I.FIRST .. recs_I.LAST
                  LOOP
        
                      UTL_FILE.PUT_LINE(v_file,
                           '&quot;'|| recs_I(i).Retailer_Name               || '&quot;,' ||
                          recs_I(i).SPID                      || ',' ||
                          recs_I(i).Meter_Unique_Identifier   || ',' ||
                          '&quot;'||recs_I(i).Frequency                 || '&quot;,' ||
                          recs_I(i).Start_Date                || ',' ||
                          recs_I(i).END_DATE                  || ',' ||
                          recs_I(i).Meter_Read_Timestamp      || ',' ||
                          recs_I(i).Meter_Read                || ',' ||
                          recs_I(i).Meter_Mode                || ',' ||
                          '');
                         
                       
                       
      
                  END LOOP;
                  UTL_FILE.PUT_LINE(v_file,'row_count='|| C%ROWCOUNT);
              END IF; 
              
              exit when C%notfound;
           
          END LOOP;
          
          UTL_FILE.FCLOSE(v_file);
      CLOSE C;
  
      
      utl_file.fgetattr(ldir,  v_out_file , f_exists, f_size, f_blk_size);
           -- dbms_output.put_line(f_size);
      if f_size = 0 then
          utl_file.fremove(ldir,  v_out_file);
      end if;
  
   EXCEPTION
           WHEN NO_DATA_FOUND THEN
                      
                      UTL_FILE.FCLOSE(v_file);
                      
          WHEN OTHERS THEN
                     
                      UTL_FILE.FCLOSE(v_file);
  
     
    --DBMS_OUTPUT.PUT_LINE('An EXECUTION!! occured during the process of insert' );
  
      V_CODE := SQLCODE;
      V_ERRM := SUBSTR(SQLERRM, 1,64);
      DBMS_OUTPUT.PUT_LINE('PROCESS TERMINATED!! An Exception occured due to '||V_CODE||'  ' ||V_ERRM);
                  
  END;
  /
  </code></pre>