1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

File handling with PL/SQL

Discussion in 'SQL PL/SQL' started by sameer, Aug 28, 2008.

  1. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    I will give a very simple example of how to read from and write to a file with PL/SQL code block.

    WRITE TO A FILE

    UTL_FILE package is used for writing to a file. To begin writing to a file of a specific directory first you will need to have permission from your DBA.

    If you are a DBA, you can create a directory as below and give permission to "user" as below

    Code (Text):

    CONNECT / AS DBA
    CREATE OR REPLACE DIRECTORY STUDENT AS '/tmp';
    GRANT read, write ON DIRECTORY STUDENT TO user;
    GRANT EXECUTE ON UTL_FILE TO user;
     
    Once permission is granted to you, just follow the code below to write to a file.
    Code (Text):

    DECLARE
      fHandler UTL_FILE.FILE_TYPE;
    BEGIN
      fHandler := UTL_FILE.FOPEN('STUDENT', 'Details', 'w');
      UTL_FILE.PUTF(fHandler, Hello sir! How are you? \n');
      UTL_FILE.FCLOSE(fHandler);
    EXCEPTION
      WHEN utl_file.invalid_path THEN
         raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
    END;
    /
     
    READ FROM A FILE

    UTL_FILE package is again used for reading from a file. To begin reading from a file of a specific directory you will again need to have permission from DBA in the same manner as shown above.

    Once you have read permission follow the code below to read from a file.

    Code (Text):

    DECLARE
      fHandler UTL_FILE.FILE_TYPE;
      buf      varchar2(4000);
    BEGIN
      fHandler := UTL_FILE.FOPEN('STUDENT', 'Details', 'r');
      UTL_FILE.GET_LINE(fHandler, buf);
      dbms_output.put_line('DATA FROM FILE: '||buf);
      UTL_FILE.FCLOSE(fHandler);
    EXCEPTION
      WHEN utl_file.invalid_path THEN
         raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
    END;
    /
     
    Note how FOPEN and FCLOSE are used to open and close the file and GET_LINE and PUTF are used to read from and write to a file.