Oracle Stored Procedure

[amit prajapati]

Hi,

I have oracle procedure which run every one hour though cron job. But during night time when procedure is executed it do not generate proper data. I am not able to find the exact reason for this

The Procedure generate data in the below format

9938446473,20130822120458,2496,1
8233566279,20130822120525,3080,1
8233175625,20130822120527,2498,1

But in the night, i find some of rows were not generated properly in text file.

67,1


PLSQL PROC :

CREATE OR REPLACE PROCEDURE NP_GUI."NP_MEDIATION_DELTA" (filename IN VARCHAR2,last_exe_time varchar2,from_exe_time varchar2) AS BEGIN DECLARE v_file UTL_FILE.FILE_TYPE; TN VARCHAR2(15); TimeStamp1 VARCHAR2(15); LRN VARCHAR2(6); ActiveFlag CHAR(1); CURSOR GET_TRANSACTIONS IS SELECT TN, to_char(LAST_MODIFIED_TIME, 'yyyymmddhhmiss'), decode(rn_status, 'A', RN,''), decode(RN_STATUS,'A','1','D','0') from MAIN.NP_INFO where (to_char(last_modified_time,'yyyymmddhh24miss') > to_char(from_exe_time)) and (to_char(last_modified_time,'yyyymmddhh24miss') <= to_char(last_exe_time)) and (RN_STATUS = 'A' or RN_STATUS = 'D'); BEGIN v_file := UTL_FILE.FOPEN('MED_DIR',filename,'w'); IF NOT GET_TRANSACTIONS%ISOPEN THEN OPEN GET_TRANSACTIONS; END IF; LOOP FETCH GET_TRANSACTIONS INTO TN,TimeStamp1,LRN,ActiveFlag; EXIT WHEN GET_TRANSACTIONS%NOTFOUND; UTL_FILE.PUT_LINE(v_file, TN || ',' || TimeStamp1 || ',' || LRN || ',' || ActiveFlag); END LOOP; UTL_FILE.FCLOSE(v_file); IF GET_TRANSACTIONS%ISOPEN THEN CLOSE GET_TRANSACTIONS; END IF; EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.'); WHEN UTL_FILE.INVALID_MODE THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.'); WHEN UTL_FILE.INVALID_OPERATION THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.'); WHEN UTL_FILE.READ_ERROR THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.'); WHEN UTL_FILE.WRITE_ERROR THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.'); WHEN UTL_FILE.INTERNAL_ERROR THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.'); WHEN UTL_FILE.CHARSETMISMATCH THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' || 'operations use nonchar functions such as PUTF or GET_LINE.'); WHEN UTL_FILE.FILE_OPEN THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.'); WHEN UTL_FILE.INVALID_MAXLINESIZE THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20009, 'The MAX_LINESIZE value for FOPEN() is invalid; it should ' || 'be within the range 1 to 32767.'); WHEN UTL_FILE.INVALID_FILENAME THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.'); WHEN UTL_FILE.ACCESS_DENIED THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.'); WHEN UTL_FILE.INVALID_OFFSET THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' || 'it should be greater than 0 and less than the total ' || 'number of bytes in the file.'); WHEN UTL_FILE.DELETE_FAILED THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.'); WHEN UTL_FILE.RENAME_FAILED THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.'); WHEN OTHERS THEN UTL_FILE.FCLOSE(v_file); RAISE; END; END; /