Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    71

    Unanswered: Exporting Oracle Data into Excel File

    There is a table whse data needs to be exported to excel file automatically at certain intervals.

    How can this be done??

    Thanks
    Reema

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Exporting Oracle Data into Excel File

    Spool to file in CSV format like this:

    set linesize 100 pagesize 0 feedback off trimspool on

    select empno||','||ename||','||sal
    from emp

    spool emp.csv
    /
    spool off

    Then open in Excel as CSV file type.

    If any of your columns may contain commas then you need to enclose in double quotes like this:

    select empno||',"'||ename||'",'||sal
    from emp

  3. #3
    Join Date
    Oct 2003
    Posts
    71

    Re: Exporting Oracle Data into Excel File

    Thanks for your prompt reply,
    How do I automate this task?

    Originally posted by andrewst
    Spool to file in CSV format like this:

    set linesize 100 pagesize 0 feedback off trimspool on

    select empno||','||ename||','||sal
    from emp

    spool emp.csv
    /
    spool off

    Then open in Excel as CSV file type.

    If any of your columns may contain commas then you need to enclose in double quotes like this:

    select empno||',"'||ename||'",'||sal
    from emp

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    On Windows use task scheduler

    On Unix use cron
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Oct 2003
    Posts
    71
    Originally posted by The_Duck
    On Windows use task scheduler

    On Unix use cron
    Can I use EM to schedule it??

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by reemagupta
    Can I use EM to schedule it??
    I don't use that crap so I have no idea.
    I only use Standalone console sometimes.

    As long as you can spool to a file when using EM then I guess so.
    I have no idea why you wouldn't just load the script onto the host server and just run task-scheduler or a cronjob
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Dec 2003
    Posts
    2
    Hi,
    Write all of the codes in a procedure and submit through DBMS_JOB to execute in a frequent intervals.
    Thanks

  8. #8
    Join Date
    Dec 2003
    Posts
    74
    yes i agree using the DBMS_JOB would be the best solution.

    here is an example

    DECLARE
    jobnum BINARY_INTEGER;
    BEGIN
    DBMS_JOB.SUBMIT(jobno, 'procedure_name;', SYSDATE, 'SYSDATE+1/24');
    END;

    This example will execute your procedure every hour


    regards

    edwin

  9. #9
    Join Date
    Dec 2003
    Posts
    74
    if you are using IAS with the web-server toolkit you can deploy an excel spreadsheet directly back to the user using


    owa_util.mime_header(ccontent_type => 'application/vnd.ms-excel');


    Regards

    edwin

Posting Permissions

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