11 Replies Latest reply on Sep 2, 2009 7:57 PM by David Allan-Oracle

    Export Data to a Text file

    632529
      Is there an oracle utility that one can use to automate export of oracle table data into a text file(*.txt) in a format readable by other window applications. I have seen examples using sql plus but I need some thing that can be scheduled to run late at night or early in the morning. I dont want to manually create the file everyday. If there is no utility is there a way to run sql plus in a windows batch file to create such an output.
      Thanks
      Bunmiog
        • 1. Re: Export Data to a Text file
          Centinul
          Yes you could run SQL*Plus by using a batch file to dump data. Another alternative would be to use a Oracle job and PL/SQL to generate the file you need using the UTL_FILE package.
          • 2. Re: Export Data to a Text file
            69791
            write plsql code and then schedule it in dbms_jobs
            • 3. Re: Export Data to a Text file
              Sanghai
              Hi,
              Try using spool command to export to a text file known as csv (Coma separated values).
              and sql*loader to import the text

              example

              spool c:\filename.txt

              select EMPLOYEE_ID ||', '||
              FIRST_NAME||', '||
              LAST_NAME||', '||
              EMAIL||', '||
              PHONE_NUMBER||', '||
              HIRE_DATE||', '||
              JOB_ID||', '||
              SALARY||', '||
              COMMISSION_PCT||', '||
              MANAGER_ID||', '||
              DEPARTMENT_ID
              from hr.employees
              /
              spool off

              this will create a text file with the name "hr_employees.txt" in c:\
              the output look like this

              190, Timothy, Gates, TGATES, 650.505.3876, 11-JUL-98, SH_CLERK, 2900, , 122, 50
              191, Randall, Perkins, RPERKINS, 650.505.4876, 19-DEC-99, SH_CLERK, 2500, , 122, 50
              192, Sarah, Bell, SBELL, 650.501.1876, 04-FEB-96, SH_CLERK, 4000, , 123, 50
              193, Britney, Everett, BEVERETT, 650.501.2876, 03-MAR-97, SH_CLERK, 3900, , 123, 50
              194, Samuel, McCain, SMCCAIN, 650.501.3876, 01-JUL-98, SH_CLERK, 3200, , 123, 50
              195, Vance, Jones, VJONES, 650.501.4876, 17-MAR-99, SH_CLERK, 2800, , 123, 50
              196, Alana, Walsh, AWALSH, 650.507.9811, 24-APR-98, SH_CLERK, 3100, , 124, 50
              197, Kevin, Feeney, KFEENEY, 650.507.9822, 23-MAY-98, SH_CLERK, 3000, , 124, 50

              and use this controlfile to load

              LOAD DATA
              APPEND
              INTO TABLE SCOTT.TEMPEMP
              FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
              (
              EMPLOYEE_ID INTEGER EXTERNAL,
              FIRST_NAME CHAR,
              LAST_NAME CHAR,
              EMAIL CHAR,
              PHONE_NUMBER CHAR,
              HIRE_DATE DATE,
              JOB_ID CHAR,
              SALARY INTEGER EXTERNAL,
              COMMISSION_PCT INTEGER EXTERNAL,
              MANAGER_ID INTEGER EXTERNAL,
              DEPARTMENT_ID INTEGER EXTERNAL
              )



              Thanks.
              • 4. Re: Export Data to a Text file
                Pavan DBA
                hi, OP wants just to export data into text file which can be accessed by other applications and dont want to import data anywhere.
                so sql*loader may not be useful for this requirement.

                but as you said he can spool into csv file. i dont know the exact way to that. is it same like below?

                sql> spool d:\> export.csv

                please clarify
                • 5. Re: Export Data to a Text file
                  Sanghai
                  Hi,
                  CSV is not the file extention CSV stands for comma separated values

                  i will explain with an example

                  spool c:\hr_employees.txt (This query will ctrate a file with the name hr_employees.txt)

                  select EMPLOYEE_ID ||', '||
                  FIRST_NAME||', '||
                  LAST_NAME||', '||
                  EMAIL||', '||
                  PHONE_NUMBER||', '||
                  HIRE_DATE||', '||
                  JOB_ID||', '||
                  SALARY||', '||
                  COMMISSION_PCT||', '||
                  MANAGER_ID||', '||
                  DEPARTMENT_ID
                  from hr.employees
                  /
                  spool off

                  the output look like this

                  190, Timothy, Gates, TGATES, 650.505.3876, 11-JUL-98, SH_CLERK, 2900, , 122, 50
                  191, Randall, Perkins, RPERKINS, 650.505.4876, 19-DEC-99, SH_CLERK, 2500, , 122, 50
                  192, Sarah, Bell, SBELL, 650.501.1876, 04-FEB-96, SH_CLERK, 4000, , 123, 50
                  193, Britney, Everett, BEVERETT, 650.501.2876, 03-MAR-97, SH_CLERK, 3900, , 123, 50
                  194, Samuel, McCain, SMCCAIN, 650.501.3876, 01-JUL-98, SH_CLERK, 3200, , 123, 50
                  195, Vance, Jones, VJONES, 650.501.4876, 17-MAR-99, SH_CLERK, 2800, , 123, 50
                  196, Alana, Walsh, AWALSH, 650.507.9811, 24-APR-98, SH_CLERK, 3100, , 124, 50
                  197, Kevin, Feeney, KFEENEY, 650.507.9822, 23-MAY-98, SH_CLERK, 3000, , 124, 50


                  thanks.

                  Edited by: Sanghai on Aug 21, 2009 4:40 PM
                  • 6. Re: Export Data to a Text file
                    Pavan DBA
                    ok fine i got it.
                    • 7. Re: Export Data to a Text file
                      Sanghai
                      hi,
                      Have you gone through or need some more

                      Thanks.
                      • 8. Re: Export Data to a Text file
                        632529
                        Thanks sanghai and others for your responses. I was able to a run a test using UTL_File command. For the automation of the job I will be using the UTL_File command within PL/SOL code to create the files . However for the manual creation of the test files I will use sql plus and follow the example you showed here. Have a nice day
                        Bunmiog
                        • 9. Re: Export Data to a Text file
                          652374
                          Hi Guru,
                          I saw your reply against one of the query to export data to a text file.
                          I am able to export the data in a text file, but while exporting my SQL query and the SPOOL OFF command is appearing there in the text file.
                          I want to eliminate both of them as this file will act as an input to other query. Can you please guide me how to achieve that.

                          Thanks in advance.
                          • 10. Re: Export Data to a Text file
                            599201
                            you must put yourcomands into script file and add some string

                            set feedback off
                            set echo off
                            set verify off
                            set heading off

                            and run your script
                            SQL> @file_name.sql
                            • 11. Re: Export Data to a Text file
                              David Allan-Oracle
                              Check out this able function approach too;

                              http://blogs.oracle.com/warehousebuilder/2009/08/parallel_unload_to_file.html

                              Cheers
                              David