Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    5

    Exclamation Unanswered: oracle table to excel file (very urgent)!!!

    pls anbybdoy tell me how to extract an oracle dtabase table to an excel file. can i do it from sql prompt. if so pls lemme know the command with syntax

    rgds

    zahir

  2. #2
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: oracle table to excel file (very urgent)!!!

    even I don't know a way to export to an excel-file, i'd suggest you create a csv file: disable all header and status output in sql*plus, set an apropriate width, spool to a file and run a
    select col1 | ';'| col2 | ';' | ... | coln from Table
    ^/\x

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    or use a tool called pl/sql developer in which you can right click the results and export to excel ....

  4. #4
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    5

    WHAT ABOUT TEXT FORMAT ?

    Originally posted by robert xr4x4
    or use a tool called pl/sql developer in which you can right click the results and export to excel ....
    OK...IS THERE ANY WAY TO EXPORT THE TABLE TO TXT FORMAT ???

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    /** **/
    DECLARE
    lfFilelog UTL_FILE.FILE_TYPE;

    lspath varchar2(50) := 'c:\';
    lsfile varchar2(50) := 'file';

    BEGIN
    lfFilelog := UTL_FILE.FOPEN(lspath,lsfile||'.txt','w');

    FOR r IN (SELECT FROM table) LOOP

    UTL_FILE.PUT_LINE(lfFilelog ,r.row);

    END LOOP;

    UTL_FILE.FCLOSE_ALL;
    EXCEPTION
    WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
    UTL_FILE.PUT_LINE(lfFilelog,' Invalid File open Operation');
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20051,'Invalid File open Operation');

    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
    UTL_FILE.PUT_LINE(lfFilelog,' Invalid File Name');
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20052,'Invalid File Name');

    WHEN UTL_FILE.READ_ERROR THEN
    UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
    UTL_FILE.PUT_LINE(lfFilelog,' Read Error');
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20053,'Read Error');

    WHEN OTHERS THEN
    UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
    UTL_FILE.FCLOSE_ALL;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
    END;

  6. #6
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    5

    ???

    HEY PLS DON'T SCARE ME GIVING CODES LIKE THESE
    CAN I DO IT FROM SQL PROMPT ? IF SO WHAT'S THE COMMAND WITH SYNTAX ?

    JUST TELL ME HOW TO EXTRACT THE RECORDS TO TEXT FILE

  7. #7
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    you cant!

  8. #8
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    5

    ???

    ok now what i am trying to do is... displaying the results in sql itself and just copy the records to excel. but i am not able to display the entire results. the pages is getting cut. how can i display full results
    what's the command for setting buffer and pagesize ??

    pls ... i am new to oracle and sql ...

  9. #9
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Use spool

    Do this at the sql prompt

    SQL> spool c:\temp\empdata.txt
    SQL> select * from emp;
    SQL> spool off

    open the empdata.txt and you will find the data there.

    DO execute the last statement 'spool off' to view the data.
    Oracle can do wonders !

  10. #10
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: ???

    justs try

    set headings off
    set linesize 999 (or whatever will be the max. count of characters)
    set pagesize 999
    spool filename

    select * from table

    spool off
    ^/\x

  11. #11
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    5

    thanks a lot cmasharma

    Originally posted by cmasharma
    Use spool

    Do this at the sql prompt

    SQL> spool c:\temp\empdata.txt
    SQL> select * from emp;
    SQL> spool off

    open the empdata.txt and you will find the data there.

    DO execute the last statement 'spool off' to view the data.

    thanks a lot cmasharma. atlast i am able to do that
    once again thank u very much
    kmzahir@eim.ae

  12. #12
    Join Date
    Oct 2004
    Posts
    60
    SQL Developer which is a free tool we use often.

Posting Permissions

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