File handling with PL/SQL

in ; I will give a very simple example of how to read from and write to a file with PL/ SQL ...
+ Post Reply + Post New Topic
Results 1 to 1 of 1
  1. #1
    sameer's Avatar
    sameer is offline Forum Advisor
    Points: 2,240, Level: 28
    Join Date
    21 Aug 2008
    Posts
    105
    Points
    2,240

    Post File handling with PL/SQL

    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 :
    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 :
    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 :
    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.
    Learning is a path with no destination...


Other Solutions
  1. multiple cursor with date handling issue reg
    By laxman in forum SQL PL/SQL
    Replies: 5
    Last Post: 05-11-2014, 02:36 PM
  2. Computing subtotals including shipping, handling and tax
    By plngndz in forum Oracle Database
    Replies: 8
    Last Post: 12-01-2010, 03:18 PM
  3. Handling null - values in stored procedures
    By oli001 in forum SQL PL/SQL
    Replies: 1
    Last Post: 10-07-2009, 12:33 PM