Select data from table by reading fetch condition from another table in Oracle

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.

Table: Multiple_retailer

For example:

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';

create a csv file for above result.

Again go to 2nd row of multiple_retailer, pass these columns in register table, fetch the data and append the result in csv file.

Please help me on this.

Edited: I am able to create single file with all retailer_id. How can i split them into multiple files based on Retailer_id.

For example: As shown in below image, i want to create 2 files : 1 with BUSTREAM-R and 1 with CASTLE-R

Below is my 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 >0 THEN
            
            UTL_FILE.PUT_LINE(v_file, '"Retailer ID","Applicant Company Name",SPID,"Meter Unique Identifier",Frequency,"Start Date","End Date","Meter Read Timestamp","Meter Read","Meter Mode",Comments' );  -- inserting header into  file
            FOR i IN recs_I.FIRST .. recs_I.LAST
                LOOP
      
                    UTL_FILE.PUT_LINE(v_file,
                         '"'|| recs_I(i).Retailer_Name               || '",' ||
                        recs_I(i).SPID                      || ',' ||
                        recs_I(i).Meter_Unique_Identifier   || ',' ||
                        '"'||recs_I(i).Frequency                 || '",' ||
                        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;
/

Answer

Added my comment as an answer. Please tick it is you’re happy with it as a solution…

If you order your cursor by Retailer_ID then you should be able to use logic in your LOOP to open a new file (and close the previous file) every time the value of Retailer_ID changes

Advertisement

Answer