Writing data to file using UTL_FILE package

[change]

scribes
i would like to write /export the results of a query to a text file
logic
1)execute query and
2) export to a text file

[debasisdas]

Try using the SPOOL command.

[amitpatel66]

Try something like this:

[code=oracle]

SQL> SET FEEDBACK OFF
SQL> SET VERIFY OFF
SQL> SET TERMOUT OFF
SQL> SET HEADING OFF
SQL> SPOOL C:/data.txt
SQL> SELECT * FROM emp;
SQL> SPOOL OFF

[/code]

Now view your data file in C:\ Drive

[change]

wtiting to file

[code=oracle]
CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST'
DECLARE
fileID UTL_FILE.FILE_T YPE;
LINE_BUFF VARCHAR2(1000);
BEGIN
fileID := UTL_FILE.FOPEN (LOGG_DIR, 'emp.dat', 'W');
FOR emprec IN (SELECT * FROM SO_SERVICES)
LOOP
LINE_BUFF :=TO_CHAR (emprec.ACCT_NO ) ;
UTL_FILE.PUT_LI NE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
END;

/
[/code]

ARE

R at line 2:
00901: invalid CREATE command

[change]

hi am using code like ths and get the error at the bottom

[code=oracle]

CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST'
DECLARE
fileID UTL_FILE.FILE_T YPE;
LINE_BUFF VARCHAR2(1000);
BEGIN
fileID := UTL_FILE.FOPEN (LOGG_DIR, 'emp.dat', 'W');
FOR emprec IN (SELECT * FROM SO_SERVICES)
LOOP
LINE_BUFF :=TO_CHAR (emprec.ACCT_NO ) ;
UTL_FILE.PUT_LI NE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
END;

/
[/code]

ARE

R at line 2:
00901: invalid CREATE command

Please enclose your source code in [CODE] tags for better readability

[amitpatel66]

Try something like this:

[code=oracle]

SQL> CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST';

SQL>DECLARE
fileID UTL_FILE.FILE_T YPE;
LINE_BUFF VARCHAR2(1000);
BEGIN
fileID := UTL_FILE.FOPEN (LOGG_DIR, 'emp.dat', 'W');
FOR emprec IN (SELECT * FROM SO_SERVICES)
LOOP
LINE_BUFF :=TO_CHAR (emprec.ACCT_NO ) ;
UTL_FILE.PUT_LI NE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
END;
/
[/code]

[amitpatel66]

Duplicate Threads Merged for better management of the forum

MODERATOR

[change]

RAN IT AND THE ERRORS ARE AT BOTTOM
[code=oracle]

1 DECLARE
2 fileID UTL_FILE.FILE_T YPE;
3 LINE_BUFF VARCHAR2(1000);
4 BEGIN
5 fileID := UTL_FILE.FOPEN ('C:\TEST', 'emp.dat
6 FOR emprec IN (SELECT * FROM SO_SERVICES)
7 LOOP
8 LINE_BUFF :=TO_CHAR (emprec.ACCT_NO ) ;
9 UTL_FILE.PUT_LI NE ( FILEID ,LINE_BUFF);
10 END LOOP;
11 UTL_FILE.FCLOSE (fileID);
12* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FI LE", line 120
ORA-06512: at "SYS.UTL_FI LE", line 204
ORA-06512: at line 5

[/code]

[amitpatel66]

Try This:

[code=oracle]

DECLARE
fileID UTL_FILE.FILE_T YPE;
LINE_BUFF VARCHAR2(1000);
BEGIN
fileID := UTL_FILE.FOPEN ('C:\TEST', 'emp.dat
FOR emprec IN (SELECT * FROM SO_SERVICES)
LOOP
LINE_BUFF :=TO_CHAR (emprec.ACCT_NO ) ;
UTL_FILE.PUT_LI NE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT _LINE('Errrrrrr r');
END;

[/code]

[change]

get :PL/SQL procedure successfully completed but the data aint there in the specified file.

[amitpatel66]

get :PL/SQL procedure successfully completed but the data aint there in the specified file.

Do this and let me know does it print 'Errrrrr':

[code=oracle]

SQL> SET SERVEROUTPUT ON

[/code]

THen run that piece of code

[amitpatel66]

Do this and let me know does it print 'Errrrrr':

[code=oracle]

SQL> SET SERVEROUTPUT ON

[/code]

THen run that piece of code

Do the following:

[code=oracle]

SQL> CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST';

SQL> GRANT READ,WRITE ON DIRECTORY LOGG_DIR TO PUBLIC;

SQL> SET SERVEROUTPUT ON

SQL> <The piece of Code that I have given in my previous Post>

[/code]

Post back what happens when you do these steps?

[change]

Did As U Said And Printed Errrrrrrrrrr

[change]

Can U Get A Dummy One Working For Me Man Am In Need

[amitpatel66]

To get the exact error message, please run this code and post back the error message for reference

[code=oracle]

DECLARE
fileID UTL_FILE.FILE_T YPE;
LINE_BUFF VARCHAR2(1000);
BEGIN
fileID := UTL_FILE.FOPEN ('C:\TEST', 'emp.dat
FOR emprec IN (SELECT * FROM SO_SERVICES)
LOOP
LINE_BUFF :=TO_CHAR (emprec.ACCT_NO ) ;
UTL_FILE.PUT_LI NE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT _LINE(SQLCODE|| ','||SQLERRM);
END;

[/code]