Home » SQL & PL/SQL » SQL & PL/SQL » Determine filePath/Name from UTL_FILE.FILE_TYPE? (Oracle 11g)
Determine filePath/Name from UTL_FILE.FILE_TYPE? [message #664995] Wed, 16 August 2017 14:02 Go to next message
bkuhn
Messages: 3
Registered: August 2017
Junior Member
Apologies in advance if this is a dumb question.

In existing code, we have procedures that use UTL_FILE.FOPEN to open txt files that subprocedures (sometimes several levels down) PUT data into. Simple enough, works fine. The file handle from fopen (of type UTL_FILE.FILE_TYPE) is passed to subprocedures.

We are in process of adding some exception handling in subprocedures - logging exception information and handling a couple of types of exceptions directly (not letting the exception bubble up the stack in that case). If we don't handle the exception we do let the exception bubble up for logging at the top level.

Now we have need of logging which particular file is currently open when logging the error in a subprogram. Preferably the file path and filename.

I'm not seeing a way of getting the file name/path from the UTL_FILE.FILE_TYPE variable that is passed down. Is there some way of determining this short of passing the file information as another parameter?

Re: Determine filePath/Name from UTL_FILE.FILE_TYPE? [message #664996 is a reply to message #664995] Wed, 16 August 2017 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't get it from the handle or FILE_TYPE, you have to pass it to the procedure or set it in a package variable in your procedure that opens the file, something like:
SQL> create or replace package mypkg is
  2    file_name varchar2(300);
  3    procedure handle_file (location in varchar2, filename in varchar2);
  4    procedure read_file (file in out utl_file.file_type);
  5    procedure close_file (file in out utl_file.file_type);
  6  end;
  7  /

Package created.

SQL> create or replace package body mypkg is
  2    procedure handle_file (location in varchar2, filename in varchar2)
  3    is
  4      file utl_file.file_type;
  5      loc  varchar2(300);
  6    begin
  7      select directory_path into loc from all_directories where directory_name = location;
  8      file_name := loc || filename;
  9      dbms_output.put_line ('opening '||file_name);
 10      file := utl_file.fopen (location, filename, 'R');
 11      read_file (file);
 12    end;
 13    procedure read_file (file in out utl_file.file_type) is
 14      line varchar2(32767);
 15    begin
 16      dbms_output.put_line ('reading '||file_name||' lines');
 17      begin
 18        loop
 19          utl_file.get_line(file, line);
 20        end loop;
 21      exception when no_data_found then null;
 22      end;
 23      close_file (file);
 24    end;
 25    procedure close_file (file in out utl_file.file_type) is
 26    begin
 27      dbms_output.put_line ('closing '||file_name);
 28      utl_file.fclose (file);
 29      file_name := '';
 30    end;
 31  end;
 32  /

Package body created.

SQL> set serveroutput on
SQL> exec mypkg.handle_file ('MY_DIR','t.txt');
opening C:\t.txt
reading C:\t.txt lines
closing C:\t.txt

PL/SQL procedure successfully completed.
Re: Determine filePath/Name from UTL_FILE.FILE_TYPE? [message #664998 is a reply to message #664996] Wed, 16 August 2017 16:42 Go to previous messageGo to next message
bkuhn
Messages: 3
Registered: August 2017
Junior Member
I was afraid of that. We are trying to affect as few procedures as possible, and a lot of these procedures are shared among multiple other calling procedures. Packages? Hah! Not around here. That would make things too easy. It's a mess.

We'll start passing the filespec as a parameter. I can see fun times ahead - there are hundreds of related processes.

Thank you though. You confirmed my fear!
Re: Determine filePath/Name from UTL_FILE.FILE_TYPE? [message #665001 is a reply to message #664998] Thu, 17 August 2017 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note that the procedures don't need to be in package (but it is better in the programming and maintenance points of view), just the variable.

If you have to add exception handlers in your procedures you nevertheless have to modify all of them unless you already have a specific procedure to handle such cases the you only have to modify this later.

Here's the same thing with procedures outside the package:
SQL> drop package mypkg;

Package dropped.

SQL> create or replace package mypkg is
  2    file_name varchar2(300);
  3  end;
  4  /

Package created.

SQL> create or replace procedure handle_file (location in varchar2, filename in varchar2)
  2    is
  3      file utl_file.file_type;
  4      loc  varchar2(300);
  5    begin
  6      select directory_path into loc from all_directories where directory_name = location;
  7      mypkg.file_name := loc || filename;
  8      dbms_output.put_line ('opening '||mypkg.file_name);
  9      file := utl_file.fopen (location, filename, 'R');
 10      read_file (file);
 11    end;
 12  /

Procedure created.

SQL> create or replace procedure read_file (file in out utl_file.file_type) is
  2      line varchar2(32767);
  3    begin
  4      dbms_output.put_line ('reading '||mypkg.file_name||' lines');
  5      begin
  6        loop
  7          utl_file.get_line(file, line);
  8        end loop;
  9      exception when no_data_found then null;
 10      end;
 11      close_file (file);
 12    end;
 13  /

Procedure created.

SQL> create or replace procedure close_file (file in out utl_file.file_type) is
  2    begin
  3      dbms_output.put_line ('closing '||mypkg.file_name);
  4      utl_file.fclose (file);
  5      mypkg.file_name := '';
  6    end;
  7  /

Procedure created.

SQL> set serveroutput on
SQL> exec handle_file ('MY_DIR','t.txt');
opening C:\t.txt
reading C:\t.txt lines
closing C:\t.txt

PL/SQL procedure successfully completed.
Re: Determine filePath/Name from UTL_FILE.FILE_TYPE? [message #665006 is a reply to message #665001] Thu, 17 August 2017 09:20 Go to previous message
bkuhn
Messages: 3
Registered: August 2017
Junior Member
Yes. That is essentially what I have to do. A number of my subprocedures are used by multiple calling procedures - some of those are not part of our updates (shared by multiple clients). I'm going to pass the filespec as an optional parameter and default it to NULL if it isn't passed. Code that calls these procedures without the parameter will simply not be able to report the file that was in use when an error was handled. Not optimal, but that's the breaks until we can update all the calling procedures.
Previous Topic: qualification_ind value meaning (2 threads merged by bb, including one hijacked from unrelated thread http://www.orafaq.com/forum/stt/76498/)
Next Topic: Need to get the comma separated value
Goto Forum:
  


Current Time: Fri Mar 29 04:57:53 CDT 2024