469,290 Members | 1,898 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

Writing data to file using UTL_FILE package

26
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
Mar 12 '08 #1
21 13905
debasisdas
8,127 Expert 4TB
Try using the SPOOL command.
Mar 12 '08 #2
amitpatel66
2,367 Expert 2GB
Try something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET FEEDBACK OFF
  3. SQL> SET VERIFY OFF
  4. SQL> SET TERMOUT OFF
  5. SQL> SET HEADING OFF
  6. SQL> SPOOL C:/data.txt
  7. SQL> SELECT * FROM emp;
  8. SQL> SPOOL OFF
  9.  
  10.  
Now view your data file in C:\ Drive
Mar 12 '08 #3
change
26
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST'
  2. DECLARE
  3. fileID UTL_FILE.FILE_TYPE;
  4. LINE_BUFF VARCHAR2(1000);
  5. BEGIN
  6. fileID := UTL_FILE.FOPEN (LOGG_DIR, 'emp.dat', 'W');
  7. FOR emprec IN (SELECT * FROM SO_SERVICES)
  8. LOOP
  9. LINE_BUFF :=TO_CHAR (emprec.ACCT_NO) ;
  10. UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);
  11. END LOOP;
  12. UTL_FILE.FCLOSE (fileID);
  13. END;
  14.  
  15. /
  16.  
ARE

R at line 2:
00901: invalid CREATE command
Mar 12 '08 #4
change
26
hi am using code like ths and get the error at the bottom

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST'
  3. DECLARE
  4. fileID UTL_FILE.FILE_TYPE;
  5. LINE_BUFF VARCHAR2(1000);
  6. BEGIN
  7. fileID := UTL_FILE.FOPEN (LOGG_DIR, 'emp.dat', 'W');
  8. FOR emprec IN (SELECT * FROM SO_SERVICES)
  9. LOOP
  10. LINE_BUFF :=TO_CHAR (emprec.ACCT_NO) ;
  11. UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);
  12. END LOOP;
  13. UTL_FILE.FCLOSE (fileID);
  14. END;
  15.  
  16. /
  17.  
ARE

R at line 2:
00901: invalid CREATE command

Please enclose your source code in [code] tags for better readability
Mar 12 '08 #5
amitpatel66
2,367 Expert 2GB
Try something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST';
  3.  
  4. SQL>DECLARE
  5. fileID UTL_FILE.FILE_TYPE;
  6. LINE_BUFF VARCHAR2(1000);
  7. BEGIN
  8. fileID := UTL_FILE.FOPEN (LOGG_DIR, 'emp.dat', 'W');
  9. FOR emprec IN (SELECT * FROM SO_SERVICES)
  10. LOOP
  11. LINE_BUFF :=TO_CHAR (emprec.ACCT_NO) ;
  12. UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);
  13. END LOOP;
  14. UTL_FILE.FCLOSE (fileID);
  15. END;
  16.  /
  17.  
Mar 12 '08 #6
amitpatel66
2,367 Expert 2GB
Duplicate Threads Merged for better management of the forum

MODERATOR
Mar 12 '08 #7
change
26
RAN IT AND THE ERRORS ARE AT BOTTOM
Expand|Select|Wrap|Line Numbers
  1.  
  2. 1 DECLARE
  3. 2 fileID UTL_FILE.FILE_TYPE;
  4. 3 LINE_BUFF VARCHAR2(1000);
  5. 4 BEGIN
  6. 5 fileID := UTL_FILE.FOPEN ('C:\TEST', 'emp.dat
  7. 6 FOR emprec IN (SELECT * FROM SO_SERVICES)
  8. 7 LOOP
  9. 8 LINE_BUFF :=TO_CHAR (emprec.ACCT_NO) ;
  10. 9 UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);
  11. 10 END LOOP;
  12. 11 UTL_FILE.FCLOSE (fileID);
  13. 12* END;
  14. SQL> /
  15. DECLARE
  16. *
  17. ERROR at line 1:
  18. ORA-06510: PL/SQL: unhandled user-defined exception
  19. ORA-06512: at "SYS.UTL_FILE", line 120
  20. ORA-06512: at "SYS.UTL_FILE", line 204
  21. ORA-06512: at line 5
  22.  
  23.  
Mar 12 '08 #8
amitpatel66
2,367 Expert 2GB
Try This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. fileID UTL_FILE.FILE_TYPE;
  4.  LINE_BUFF VARCHAR2(1000);
  5.  BEGIN
  6.  fileID := UTL_FILE.FOPEN ('C:\TEST', 'emp.dat
  7.  FOR emprec IN (SELECT * FROM SO_SERVICES)
  8.  LOOP
  9.  LINE_BUFF :=TO_CHAR (emprec.ACCT_NO) ;
  10.  UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);
  11.  END LOOP;
  12. UTL_FILE.FCLOSE (fileID);
  13. EXCEPTION
  14. WHEN OTHERS THEN
  15. DBMS_OUTPUT.PUT_LINE('Errrrrrrr');
  16. END;
  17.  
  18.  
Mar 12 '08 #9
change
26
get :PL/SQL procedure successfully completed but the data aint there in the specified file.
Mar 12 '08 #10
amitpatel66
2,367 Expert 2GB
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':

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET SERVEROUTPUT ON
  3.  
  4.  
THen run that piece of code
Mar 12 '08 #11
amitpatel66
2,367 Expert 2GB
Do this and let me know does it print 'Errrrrr':

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET SERVEROUTPUT ON
  3.  
  4.  
THen run that piece of code
Do the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST';
  3.  
  4. SQL> GRANT READ,WRITE ON DIRECTORY LOGG_DIR TO PUBLIC;
  5.  
  6. SQL> SET SERVEROUTPUT ON
  7.  
  8. SQL> <The piece of Code that I have given in my previous Post>
  9.  
  10.  
Post back what happens when you do these steps?
Mar 12 '08 #12
change
26
Did As U Said And Printed Errrrrrrrrrr
Mar 12 '08 #13
change
26
Can U Get A Dummy One Working For Me Man Am In Need
Mar 12 '08 #14
amitpatel66
2,367 Expert 2GB
To get the exact error message, please run this code and post back the error message for reference

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. fileID UTL_FILE.FILE_TYPE;
  4.  LINE_BUFF VARCHAR2(1000);
  5.  BEGIN
  6.  fileID := UTL_FILE.FOPEN ('C:\TEST', 'emp.dat
  7. FOR emprec IN (SELECT * FROM SO_SERVICES)
  8. LOOP
  9. LINE_BUFF :=TO_CHAR (emprec.ACCT_NO) ;
  10. UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);
  11. END LOOP;
  12. UTL_FILE.FCLOSE (fileID);
  13. EXCEPTION
  14. WHEN OTHERS THEN
  15. DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
  16. END;
  17.  
  18.  
Mar 12 '08 #15
amitpatel66
2,367 Expert 2GB
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.
Mar 12 '08 #16
change
26
HI RAN THE CODE AND THIS IS THE ERROR;;;


1,User-Defined Exception
Mar 13 '08 #17
change
26
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
Mar 13 '08 #18
change
26
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
Mar 13 '08 #19
amitpatel66
2,367 Expert 2GB
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
Mar 13 '08 #20
amitpatel66
2,367 Expert 2GB
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.
Mar 13 '08 #21
amitpatel66
2,367 Expert 2GB
Duplicate Threads merged for better management of forum


MODERATOR
Mar 13 '08 #22

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Igor Shulgin | last post: by
5 posts views Thread by Siddhant Kumar | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.