Re: Utl_file within Oracle

A copy of this was sent to mklondon1311_at_my-deja.com (if that email address didn't require changing) On Mon, 25 Oct 1999 19:00:36 GMT, you wrote:

>Help!!!
>
>I'm having trouble setting utl_file to pick up a variable.
>it will only work if I explicitly call the file.
>
>here's a little piece of what I'm working on.
> Please write if you can help.
>
> for v_weather_files in c_weather_files loop
>
> If utl_file.is_open(v_wth_file_handler) then
> utl_file.fclose(v_wth_file_handler);
> End If;
>
> v_cur_file_dir := v_weather_files.file_directory;
> v_cur_file_name := v_weather_files.file_name;
>
>
>{the following line with dashes is the one that doesn't work, the line
>after that works, because the file is called explicitly}
>
>
>-- v_wth_file_handler := utl_file.fopen
> (v_cur_file_dir,v_cur_file_name, 'r');
> v_wth_file_handler := utl_file.fopen ('/home/weather/',
> 'hta102504.wth', 'r');
>

what error are you getting?
either your v_cur_file_dir is not correct (eg: it is *not* "/home/weather/") or your filename is invalid in some way. put these globals in your declare section:

g_invalid_path_msg constant varchar2(255) default 'INVALID_PATH: File location or filename was invalid.'; g_invalid_mode_msg constant varchar2(255) default 'INVALID_MODE: The open_mode parameter in FOPEN was invalid.'; g_invalid_filehandle_msg constant varchar2(255) default 'INVALID_FILEHANDLE: The file handle was invalid.'; g_invalid_operation_msg constant varchar2(255) default 'INVALID_OPERATION: The file could not be opened or operated on as requested.'; g_read_error_msg constant varchar2(255) default 'READ_ERROR: An operating system error occurred during the read operation.'; g_write_error_msg constant varchar2(255) default 'WRITE_ERROR: An operating system error occurred during the write operation.'; g_internal_error_msg constant varchar2(255) default 'INTERNAL_ERROR: An unspecified error in PL/SQL.';

    l_output utl_file.file_type;

and then have an exception block around the utl_file call:

exception

    when utl_file.invalid_path then

        raise_application_error(-20001,g_invalid_path_msg || '"' || v_cur_file_dir || '","' || v_cur_file_name || '"');

    when utl_file.invalid_mode then

        raise_application_error(-20002,g_invalid_mode_msg || '"' || v_cur_file_dir || '","' || v_cur_file_name || '"');

    when utl_file.invalid_filehandle then

        raise_application_error(-20002,g_invalid_filehandle_msg|| '"' || v_cur_file_dir || '","' || v_cur_file_name || '"');

    when utl_file.invalid_operation then

        raise_application_error(-20003,g_invalid_operation_msg || '"' || v_cur_file_dir || '","' || v_cur_file_name || '"');

    when utl_file.read_error then

        raise_application_error(-20004,g_read_error_msg || '"' || v_cur_file_dir || '","' || v_cur_file_name || '"');

    when utl_file.write_error then

        raise_application_error(-20005,g_write_error_msg || '"' || v_cur_file_dir || '","' || v_cur_file_name || '"');

    when utl_file.internal_error then

        raise_application_error(-20006,g_internal_error_msg || '"' || v_cur_file_dir || '","' || v_cur_file_name || '"'); end;

>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Oct 25 1999 - 15:17:55 CDT