Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Data Export oracle query

    Can anyone send me the Oracle Query to Export the data from a table to Excel file.


    Note - Its should not be a manual export. i need Oracle Query to execute.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There are different options available. The simplest one is - from SQL*Plus - to spool result into a CSV file. Another option uses the same principle, but from GUI tools that are capable of saving result as CSV (for example, TOAD can do that). Furthermore, result of a report can be saved in Excel file format. Moreover, an Apex application (in version 4.x for sure, can't tell for earlier versions) offers Excel download. PL/SQL can utilize UTL_FILE and create a CSV file which can be opened by Excel.

    Saying that "it should not be a manual report" means ... what? That you should schedule it at night and just pick up the result in the morning? No problem; DBMS_SCHEDULER can do that. Your operating system's scheduler can do the same as well.

    So, you have quite a few options. Which one will you use depends on Oracle database version you use, tools you use to access it, operating system and, possibly, some other information you'd want to share with us.

  3. #3
    Join Date
    May 2012
    Posts
    3

    Spooling issue

    Hi ,
    My requirement is stated as below.

    1. I need to create a batch file to Login to Oracle database.
    2. After successfull logon, It should execute a select query.
    3. After execution, Resultset needs to be exported into an Excel file.

    Above 3 steps needs to be processed in one batch file.


    i use below batch script. But i am getting spooling error. Like "spool' not recognized by internal or external keyword

    @Echo off
    C:\ORACLE\10.2.0\bin\sqlplus.exe uname/pwd@DBInstance
    set feed off markup html on spool on;
    spool @C:\testExcel.csv;
    select * from TABLE where Building='Block 50';
    spool off;
    set markup html off spool off;
    Echo
    Pause
    End
    Exit;

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    create file called excel.sql that contains lines below:
    Code:
    set feed off markup html on spool on;
    spool C:\testExcel.csv;
    select * from TABLE where Building='Block 50';
    spool off;
    set markup html off spool off;
    Echo
    Pause
    End
    Exit;
    then bat script should contain the following:

    C:\ORACLE\10.2.0\bin\sqlplus.exe uname/pwd@DBInstance @excel.sql
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2012
    Posts
    3

    Issue in Echo command

    Hi,
    I have tried the above script which you have shared. Its got executed and returned the resultset in SQL*Plus.

    But i am getting the error in the Line 'ECHO' and the excel file is not exported as well.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Is "ECHO" a valid sqlplus command?
    I just used what you posted; which may have included errors.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Just a small change to anacedent's code:

    PHP Code:
    Changespool C:\testExcel.csv;
        
    Tospool C:\testExcel.xml
    Also remove the invalid commands.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •