PROCEDURE usp_OUTPUT_ToFileAscii(p_Path IN VARCHAR2, p_FileName IN VARCHAR2, p_Input IN refCursor, p_Header in VARCHAR2, p_Footer IN VARCHAR2, p_WriteMode VARCHAR2) IS
vLine VARCHAR2(30000);
vFile UTL_FILE.file_type;
vExists boolean;
vLength number;
vBlockSize number;
BEGIN
UTL_FILE.fgetattr(p_path, p_FileName, vExists, vLength, vBlockSize);
FETCH p_Input INTO vLine;
IF p_input%ROWCOUNT > 0
THEN
IF vExists THEN
vFile := UTL_FILE.FOPEN_NCHAR(p_Path, p_FileName, p_WriteMode);
ELSE
--even if the append flag is passed if the file doesn't exist open it with W.
vFile := UTL_FILE.FOPEN(p_Path, p_FileName, 'W');
END IF;
--GET HANDLE TO FILE
IF p_Header IS NOT NULL THEN
UTL_FILE.PUT_LINE(vFile, p_Header);
END IF;
UTL_FILE.PUT_LINE(vFile, vLine);
DBMS_OUTPUT.PUT_LINE('Record count > 0');
--LOOP THROUGH CURSOR VAR
LOOP
FETCH p_Input INTO vLine;
EXIT WHEN p_Input%NOTFOUND;
UTL_FILE.PUT_LINE(vFile, vLine);
END LOOP;
IF p_Footer IS NOT NULL THEN
UTL_FILE.PUT_LINE(vFile, p_Footer);
END IF;
CLOSE p_Input;
UTL_FILE.FCLOSE(vFile);
ELSE
DBMS_OUTPUT.PUT_LINE('Record count = 0');
END IF;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE ('invalid_path');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE ('invalid_mode');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE ('invalid_filehandle');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE ('invalid_operation');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('read_error');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('write_error');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('internal_error');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('other write error');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END;
703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
