Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    6

    Unanswered: Write to Excel from package

    Hello!
    I'm trying to write some records to a CSV file from my pl/sql package (so that it can be viewed in Excel).

    I've found lots of information on how to do this from SQL*Plus using the spool command, but I'm not sure how to get it working from my procedure.

    This is my effort so far:
    Code:
    execute immediate (' set colsep ","
                                     set heading off
    		 spool c:\exportTest.csv
    		 select * from TIS_MAPPING;
    		 spool off
    		 ');
    This works OK in SQL*Plus (without the execute immediate command of course) but when I run my procedure I get the error 922: Missing or Invalid Option.

    It can't be that hard but I can't find anything to tell me how to do it!

    Thanks,
    Hazel

  2. #2
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Write to Excel from package

    You can not do that in that wau because this:
    set colsep ","
    set heading off
    spool c:\exportTest.csv
    select * from TIS_MAPPING;
    spool off
    is SQL*plus commands.

    if you wany to generate a file from PL/SQL you can use UTL_FILE package.
    Joel Pérez

  3. #3
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Write to Excel from package

    UTL_FILE
    With the UTL_FILE package, your PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

    UTL_FILE I/O capabilities are similar to standard operating system stream file I/O (OPEN, GET, PUT, CLOSE) capabilities, but with some limitations. For example, you call the FOPEN function to return a file handle, which you use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When file I/O is done, you call FCLOSE to complete any output and free resources associated with the file.


    --------------------------------------------------------------------------------
    Note:
    The UTL_FILE package is similar to the client-side TEXT_IO package currently provided by Oracle Procedure Builder. Restrictions for a server implementation require some API differences between UTL_FILE and TEXT_IO. In PL/SQL file I/O, errors are returned using PL/SQL exceptions.

    http://download-west.oracle.com/docs...e.htm#ARPLS069
    Joel Pérez

  4. #4
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    6
    Thanks for your responses.

    I looked in to using UTL_FILE but then discounted it as it only lets you write to the server, and I need to write to a client machine. I understand there is a file you can make a change in to enable writing to the client but I do not have access to it.

    I'll have a look for another solution, let me know if you have any ideas! (Maybe I could use TEXT_IO?)

    Thanks again,
    Hazel

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    Originally posted by hsisson
    Thanks for your responses.

    I looked in to using UTL_FILE but then discounted it as it only lets you write to the server, and I need to write to a client machine. I understand there is a file you can make a change in to enable writing to the client but I do not have access to it.

    I'll have a look for another solution, let me know if you have any ideas! (Maybe I could use TEXT_IO?)

    Thanks again,
    Hazel
    TEXT_IO is a package only available in Forms to write/read files in the client. As you mentioned UTL_FILE only writes in the server.

    What interface uses your application ? JSP, PSP, Forms, etc
    Joel Pérez

  6. #6
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    6
    I'm developing portlets for an Oracle portal application using PL/SQL.

Posting Permissions

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