Determine filePath/Name from UTL_FILE.FILE_TYPE? [message #664995] |
Wed, 16 August 2017 14:02 |
|
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 |
|
Michel Cadot
Messages: 68693 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 #665006 is a reply to message #665001] |
Thu, 17 August 2017 09:20 |
|
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.
|
|
|