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:

 
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
 
 

Now view your data file in C:\ Drive

[change]

CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST'
DECLARE
fileID UTL_FILE.FILE_TYPE;
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_LINE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
END;
 
/
 

ARE

R at line 2:
00901: invalid CREATE command

[change]

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

 
CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST'
DECLARE
fileID UTL_FILE.FILE_TYPE;
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_LINE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
END;
 
/
 

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:

 
SQL> CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST';
 
SQL>DECLARE
fileID UTL_FILE.FILE_TYPE;
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_LINE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
END;
 /
 

[amitpatel66]

Duplicate Threads Merged for better management of the forum

MODERATOR

[change]

RAN IT AND THE ERRORS ARE AT BOTTOM

 
1 DECLARE
2 fileID UTL_FILE.FILE_TYPE;
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_LINE ( 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_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 204
ORA-06512: at line 5
 
 

[amitpatel66]

Try This:

 
DECLARE
fileID UTL_FILE.FILE_TYPE;
 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_LINE ( FILEID ,LINE_BUFF);
 END LOOP;
UTL_FILE.FCLOSE (fileID);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Errrrrrrr');
END;
 
 

[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':

 
SQL> SET SERVEROUTPUT ON
 
 

THen run that piece of code

[amitpatel66]

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

 
SQL> SET SERVEROUTPUT ON
 
 

THen run that piece of code

Do the following:

 
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>
 
 

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

 
DECLARE
fileID UTL_FILE.FILE_TYPE;
 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_LINE ( FILEID ,LINE_BUFF);
END LOOP;
UTL_FILE.FCLOSE (fileID);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
 
 

[amitpatel66]

You can search in this forum by typing in the keyword utl_file in the SEARCH box at the top right corner of the forum windowto get some sample codes.

But you run the code that I have provided you in my previous post to check what could be the exact error and post back the error for reference.

[change]

HI RAN THE CODE AND THIS IS THE ERROR;;;


1,User-Defined Exception

[change]

HI WAS DOING SOME REDING AND CAME ACROOS::::

Here's the format of the parameter for file access in the INIT.ORA file:

utl_file_dir = <directory>


PROBLEM IS WHERE DO I DECLARE THIS IN MY CODE

[change]

scribes

am trying to use utl_file and need to declare the directory in init.ora and i went to the oracle registry folder and i cant seem to find it....help please

[amitpatel66]

HI WAS DOING SOME REDING AND CAME ACROOS::::

Here's the format of the parameter for file access in the INIT.ORA file:

utl_file_dir = <directory>


PROBLEM IS WHERE DO I DECLARE THIS IN MY CODE

If you want to change the value of utl_file_dir, then you need to change that init.ora file and bounce the database server to take in to effect the modified value

[amitpatel66]

scribes

am trying to use utl_file and need to declare the directory in init.ora and i went to the oracle registry folder and i cant seem to find it....help please

You need to assign the directory value to utl_file_dir parameter in init.ora file. The init .ora file is present in ORACLE_HOME/dbs path. Check for INIT.ORA File, assign the directory value to the utl_file_dir parameter and bounce the DB server. You can ask your DBA to do this for you.

[amitpatel66]

Duplicate Threads merged for better management of forum


MODERATOR