Hello Everyone,
The scenario is we are providing an interface (via Forms 10g) to execute different queries. These queries have fixed columns and tables (i.e. we have same SELECT and FROM clauses for each query). However, WHERE clause is unique for each query. So, I am trying to write a dynamic cursor but DYNAMIC CURSORS are not clear to me. I am posting my code for taking guidance from experts from the forum.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Load_Rows EXEC_SQL.CursType;
New_Query varchar2(5000) = '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_CLAUSE;
begin
Load_Rows := Exec_SQL.Open_cursor;
EXEC_SQL.PARSE(Load_Rows, New_Query);
EXEC_SQL.DEFINE_COLUMN(Load_Rows,1, v_colchar1, 30);
EXEC_SQL.DEFINE_COLUMN(Load_Rows,2, v_colchar2, 15);
EXEC_SQL.DEFINE_COLUMN(Load_Rows,3, v_colchar3, 30);
v_exec := EXEC_SQL.EXECUTE(Load_Rows);
WHILE EXEC_SQL.FETCH_ROWS(Load_Rows) > 0 LOOP
EXEC_SQL.COLUMN_VALUE(Load_Rows,1,v_colchar1);
EXEC_SQL.COLUMN_VALUE(Load_Rows,2,v_colchar2);
EXEC_SQL.COLUMN_VALUE(Load_Rows,3,v_colchar3);
END LOOP;
EXEC_SQL.CLOSE_CURSOR(Load_Rows);
end;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I have to integrate above written DYNAMIC cursor in the code below that was written by using a simple cursor:
BEGIN
--— File Path
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
Row_Count :=0;
FOR i IN Load_Rows LOOP
--COUNT:=Load_Rows%ROWCOUNT; -----counter
Row_Count := Row_Count+1;
client_text_io.Put_Line(lv_File, i.DATA);
IF Row_Count=5000 THEN
Synchronize;
Row_Count:=0;
END IF;
END LOOP;
--–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;