Hi everyone,
I want to export records from database block to an Excel file. Initially, I did it with OBL however it took too long as the number of record increases in the database block.
Then I tried csv file. Its working fine with my queries. However if I run my worst case the form crashes after 3 minutes. My max number of records are around 5 lac.
Here is the code:
DECLARE
lv_File client_Text_IO.File_Type;
lv_File_Name VarChar2(500);
lv_wait timer;
user_nam varchar2(1000);
Cursor Load_Rows is
SELECT
IRR_TAMPERED_OK||','||
IRR_DATE_DISPATCHED||','||
IRR_DISPATCHED_NO||','||
IRR_REG_ID||','||
IRR_CONS_NO||','||
CON_NAM||','||
CON_ADR||','||
IRR_SR||','||
EMC_EVC_NO||','||
IRR_TNO||','||
IRR_MTR_TYPE||','||
IRR_MTR_DIC||','||
MTR_RED_COR||','||
MTR_RED_UNCOR||','||
MTR_RED||','||
IRR_FP_SIGN||','||
IRR_FP_OPEN||','||
IRR_FP_SIGN_CHK||','||
IRR_FP_CHK||','||
IRR_20PSIG_SIGN||','||
IRR_20_PSIG_VALUE||','||
IRR_40_PSIG_SIGN||','||
IRR_40_PSIG_VALUE||','||
IRR_60_PSIG_SIGN||','||
IRR_60_PSIG_VALUE||','||
IRR_80_PSIG_SIGN||','||
IRR_80_PSIG_VALUE||','||
DTE_REM||','||
DTE_REC||','||
IRR_CALL_REMARKS||','||
IRR_SEAL_COND||','||
IRR_G_OBSER||','||
IRR_REMARKS data
FROM
inproving_result_remarks,MTR_RET_CRD
WHERE MTR_SR = IRR_SR AND MTR_ITNO = IRR_TNO;
path_part varchar(50);
BEGIN
user_nam:= WebUtil_ClientInfo.Get_User_Name();
path_part := to_char(sysdate,'DD-MM-YYYY (HH-MI-SS)');
lv_File_Name :='C:\Users\'||user_nam||'\CMS_MIR_'||path_part||'.csv';
---Open file in write mode
lv_File := client_text_io.FOpen(lv_File_Name, 'W');
--— Write header columns
client_text_io.Put_Line(lv_File,'IRR_TAMPERED_OK, IRR_DATE_DISPATCHED,IRR_DISPATCHED_NO,IRR_REG_ID,IRR_CONS_NO,CON_NAM,CON_ADR,IRR_SR,EMC_EVC_NO,IRR_TNO,IRR_MTR_TYPE,IRR_MTR_DIC,MTR_RED_COR,MTR_RED_UNCOR,MTR_RED,IRR_FP_SIGN,IRR_FP_OPEN,IRR_FP_SIGN_CHK,IRR_FP_CHK,IRR_20PSIG_SIGN,IRR_20_PSIG_VALUE,IRR_40_PSIG_SIGN,IRR_40_PSIG_VALUE,IRR_60_PSIG_SIGN,IRR_60_PSIG_VALUE,IRR_80_PSIG_SIGN,IRR_80_PSIG_VALUE,DTE_REM,DTE_REC,IRR_CALL_REMARKS,IRR_SEAL_COND,IRR_G_OBSER,IRR_REMARKS');
--–Write rows into the file
FOR i IN Load_Rows LOOP
client_text_io.Put_Line(lv_File, i.DATA);
END LOOP;
--–Close File
client_text_io.FClose(lv_File);
--–Open file in read mode
lv_File := client_text_io.FOpen(lv_File_Name,'R');
--–Close file
client_text_io.FClose(lv_File);
Message('Meter Inspection Report has been generated at:C:\Users\'||user_nam||'\CMS_MIR_'||path_part);
Message('Meter Inspection Report has been generated at:C:\Users\'||user_nam||'\CMS_MIR_'||path_part);
END;
-----------------------------------------------------------------------------
Looking for best and efficient solution from the experts.
Regards,
Misbah.