Skip to content

How to save Oracle Table data into a text file using UTL_FILE – for the new oracle developer or dba




you are a new oracle dba and you would like to know about a way to write oracle table data into text files.

 

How To Save Table Data From an Oracle DB Into a User-Readable Text File ( .txt Extension) . [ID 443395.1]  

 

  Modified 31-OCT-2007     Type HOWTO     Status PUBLISHED  

In this Document
Goal
Solution


Applies to:

PL/SQL – Version: 9.2.0.1 to 10.2.0.4
Information in this document applies to any platform.

Goal

How to save table data into a user-readable text file ( .txt extension)

Solution


CREATE or replace DIRECTORY test_dir1 AS 'C:\Home';

set serverout on;

CREATE OR REPLACE PROCEDURE WRITEFILE as
fHandle UTL_FILE.FILE_TYPE;
Vename VARCHAR2(100); 
VempNo VARCHAR2(100);
error VARCHAR2(100);
CURSOR C1 IS
SELECT ENAME, EMPNO
FROM EMP;
BEGIN
fHandle := UTL_FILE.FOPEN('TEST_DIR1','test.txt', 'W');
OPEN C1;
LOOP
FETCH C1 INTO Vename, VempNo;
EXIT WHEN C1%NOTFOUND;
UTL_FILE.PUTF(fHandle,Vename);
UTL_FILE.PUTF(fHandle,' ');
UTL_FILE.PUTF(fHandle,VempNo);
UTL_FILE.PUTF(fHandle,'\n');
DBMS_OUTPUT.PUT_LINE(Vename);
DBMS_OUTPUT.PUT_LINE(Vempno);
END LOOP;
UTL_FILE.FCLOSE(fHandle);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
WHEN OTHERS THEN
error:=SQLCODE;
DBMS_OUTPUT.PUT_LINE('other stuff'||error);
END;
/









Related


Products


  • Oracle Database Products > Oracle Database > Application Development > PL/SQL

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.