How do I write to a local file with PL/SQL?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How do I write to a local file with PL/SQL?

  1. #1
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67

    How do I write to a local file with PL/SQL?

    Is there a way I can write to a local file (such as C:\test.txt) within a PL/SQL procedure?

    Can someone post an example if it can be done?

    Thanks,

    P

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    From Oracle metalink Note:123862.1

    - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
    DECLARE
    fHandle UTL_FILE.FILE_TYPE;
    vText varchar2(10);
    BEGIN
    fHandle := UTL_FILE.FOPEN('c:\','utlfile.txt','w');
    vText := 'TEST';
    UTL_FILE.PUTF(fHandle,vText);
    UTL_FILE.FCLOSE(fHandle);
    EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
    RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
    WHEN UTL_FILE.INVALID_MODE THEN
    RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
    WHEN UTL_FILE.INVALID_OPERATION then
    RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
    WHEN UTL_FILE.INVALID_FILEHANDLE then
    RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
    WHEN UTL_FILE.WRITE_ERROR then
    RAISE_APPLICATION_ERROR(-20104,'Write Error');
    WHEN UTL_FILE.READ_ERROR then
    RAISE_APPLICATION_ERROR(-20105,'Read Error');
    WHEN UTL_FILE.INTERNAL_ERROR then
    RAISE_APPLICATION_ERROR(-20106,'Internal Error');
    WHEN OTHERS THEN
    UTL_FILE.FCLOSE(fHandle);
    END;
    /

    - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Also you can look into metalink Note:119644.1
    which has more examples.

  4. #4
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67
    Thank you all..

    Now can someone post a similar example using the text_io package? Or is text_io for Oracle forms only??

    I want to write to a local file from PL/SQL? (ie c:\test.txt).

    What's the best way to do this?

    Thanks again.

    Paul
    Last edited by mrpaulwass; 05-01-2003 at 02:18 PM.

  5. #5
    Join Date
    Jun 2002
    Location
    Denver
    Posts
    54

    text_io

    As far as I know text_io is available through Forms only. utl_file will write on the special server folders only and will never write on locla machine. Syntax for text_io is exactly the same as UTL file except that destination folder can be anywhere on client and need not be specified in the system parameters.

  6. #6
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67
    Will utl_file only write out to the server side??

  7. #7
    Join Date
    Jun 2002
    Location
    Denver
    Posts
    54

    Oops

    yes, at least for the version I work on. I work on 7.3.2 and I faced this problem. I did see a clever solution where they had to geenrate file on server but it was through forms only. They opened the server fiel for reading using utl_file and wrote it line by line using text_io.

Posting Permissions

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



Click Here to Expand Forum to Full Width