Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    1

    Unanswered: How can I open an OS text file inside one Stored Procedure

    HI,

    I have one Operating System text file in some directory in UNIX environment . How can I open this file inside one Stored Procedure.

    Thanks in advance.


    Dillip

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb UTL_FILE

    Hello,

    to open flat files in the OS use the package SYS.UTL_FILE. You can use the functions/procedures fopen, get_line and fclose to access the datas in the ff.

    You get direct access to the file you must enter the path of the file in your initial parameter. Otherwise Oracle can not read or write the file.

    The parameter is UTL_FILE_DIR.

    Here is a short example:

    declare
    fptr utl_file.file_type;
    buff varchar2(2048);
    line_no number(10):=0;
    loc_no integer;
    begin
    fptr:=utl_file.fopen('C:\Oracle\admin\PENT\udump', 'ORA00324.TRC','R');
    utl_file.get_line(fptr,buff);
    utl_file.fclose(fptr);
    exception
    when no_data_found then
    utl_file.fclose(fptr);
    dbms_output.put_line('Number of lines parsed ='||line_no);
    when utl_file.invalid_path then
    dbms_output.put_line('invalid path');
    raise_application_error(-20100,'file error');
    when utl_file.invalid_mode then
    dbms_output.put_line('invalid_mode');
    raise_application_error(-20100,'file error');
    when utl_file.invalid_filehandle then
    dbms_output.put_line('invalid_filehandle');
    raise_application_error(-20100,'file error');
    when utl_file.invalid_operation then
    dbms_output.put_line('invalid_operation');
    raise_application_error(-20100,'file error');
    when utl_file.read_error then
    dbms_output.put_line('read_error');
    raise_application_error(-20100,'file error');
    when utl_file.write_error then
    dbms_output.put_line('write_error');
    raise_application_error(-20100,'file error');
    when utl_file.internal_error then
    dbms_output.put_line('internal_error');
    raise_application_error(-20100,'file error');
    when others then
    dbms_output.put_line('un-handled');
    raise_application_error(-20100,'file error');
    end;

    Hope that helps ?

    Regards
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

Posting Permissions

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