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
|