Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Unanswered: File I/O in PL/SQL


    Please help!
    I need to write a stored procedure that will replace a word in a number of files in a directory.

    I am new to PL/SQL and will really appreciate the help. I have just learned about UTL_FILE.FOPEN thing and is not able to write it properly.

    Thanks in advance for your help.


  2. #2
    Join Date
    Dec 2003
    Frankfurt germany
    Hi arun1581,

    First of all: using PL/SQL for manipulating texts in an plain text-file is not the tool I would use. Much better (and easier to handle) form my expierinece is unsing Perl.

    But if you prefere to use PL/SQL you must first make sure sure, that your have set the init.ora-parameter 'utl_file_dir' to a directory, which you can use. This is not dynamic, so you have to reboot the Instance after changing it.

    furthermore read the documtenation of how to use the built-in package utl_file:

    basically you need to take care for:

    1. havind a utl_file_dir defined and acces-rights to it
    2. a file-handle
    3. the open/close functions of utl_file package
    4. the read/write operations from that package

    hope it helps LaoDe

  3. #3
    Join Date
    Nov 2003
    Bangalore, INDIA

    Thumbs up


    Example :

    set echo on
    !mkdir /tmp/public_access

    connect sys/change_on_install as sysdba;
    drop user tcopy01 cascade;
    grant connect, resource to tcopy01 identified by tcopy01;
    grant select_catalog_role to tcopy01;

    create or replace directory public_access as '/tmp/public_access';
    grant read on directory public_access to public;

    connect tcopy01/tcopy01
    create table tcopy01_out (line varchar2(500), i number);
    create procedure tcopy01_p as errbuf varchar2(50);
    dir varchar2(512) := 'PUBLIC_ACCESS';
    f1 utl_file.file_type;
    type t_files is table of utl_file.file_type index by binary_integer;
    files t_files;
    i number := 0;
    ok boolean := TRUE;
    pos number;
    len number;
    blk number;

    procedure insertoutput (line varchar2) is
    insert into tcopy01_out values (line, i);
    i := i+1;
    end insertoutput;

    f1 := utl_file.fopen('PUBLIC_ACCESS', 'tcopy01.dat', 'w'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 1.'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 2.'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 3.'); utl_file.fclose(f1);

Posting Permissions

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