Friday, 27 March 2009

Oracle - Testing UTL_FILE

CREATE OR REPLACE PROCEDURE
utl_file_test_write (PATH IN VARCHAR2,
filename IN VARCHAR2,
firstline IN VARCHAR2,
secondline IN VARCHAR2
)
AUTHID CURRENT_USER
IS
output_file UTL_FILE.file_type;
v_path all_directories.directory_path%TYPE;
BEGIN
SELECT directory_path
INTO v_path
FROM all_directories
WHERE UPPER (directory_name) = UPPER (PATH) AND ROWNUM = 1;

DBMS_OUTPUT.put_line ('Opening file ' || RTRIM (v_path, '/') || '/' || filename);
output_file := UTL_FILE.fopen (PATH, filename, 'W');

DBMS_OUTPUT.put_line ('Writing the 1st record "' || firstline || '"');
UTL_FILE.put_line (output_file, firstline);

DBMS_OUTPUT.put_line ('Writing the 2nd record "' || secondline || '"');
UTL_FILE.put_line (output_file, secondline);

DBMS_OUTPUT.put_line ('Closing the file');
UTL_FILE.fclose (output_file);

DBMS_OUTPUT.put_line ('Complete!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;

/

No comments:

Post a Comment