SQL & PL/SQL

Hi Guys,

I wonder could I get an opinion, I have a script which runs fine, but when i put the same script into a package it cant find a Directory, what up?

error: ORA-29282: invalid file ID
Utl_File.Invalid_Filehandle

Thanks
Charlie



Create or replace package body DCEE.ax_debuger is

v_line varchar2(32767);
c_location constant varchar2(80) := 'UTL_FILE_TEST'
/* new in 9.2.0 - use a DIRECTORY */;
c_filename constant varchar2(80) := 'test.txt';
v_handle Utl_File.File_Type;


procedure Show_Is_Open is begin
case Utl_File.Is_Open ( file => v_handle )
when true then Dbms_Output.Put_Line ( 'open' );
else Dbms_Output.Put_Line ( 'closed' );
end case;

end Show_Is_Open;

procedure Put_Line is begin
Utl_File.Put_Line (
file => v_handle,
buffer => 'Hello world66',
autoflush => false );

end Put_Line;

begin
-- Oracle recommends that you always use max_linesize => 32767
-- This is a "magic number" which prevents error if the
-- linesize exceeds max_linesize (character file) or if the file size
-- exceeds max_linesize (binary file).
--

-- If the file is treated as character data, then max_linesize must be
-- no smaller than the longest line. Else exception when a line
-- is attempted read whose length exceeds max_linesize
-- UNLESS max_linesize is 32767
--
-- If the file is treated as binary data, then max_linesize must be
-- no smaller than the size of the file
-- UNLESS max_linesize is 32767


v_handle := Utl_File.Fopen (
location => c_location,
filename => c_filename,
open_mode => 'w' /* write over any existing file with this name */,
max_linesize => 32767 );
-- max_linesize => 32768 ) /* uncomment to see Utl_File.Invalid_Maxlinesize */;

-- Show_Is_Open;
Put_Line;
Utl_File.Fclose ( file => v_handle );

-- Show_Is_Open;
--Put_Line /* uncomment to see Utl_File.Invalid_Filehandle */;

exception
when
-- ORA-29287: invalid maximum line size
Utl_File.Invalid_Maxlinesize
then
-- Fclose_All closes all open files for this session.
-- It is for cleanup use only. File handles will not be cleared
-- (Is_Open will still indicate they are valid)

Utl_File.Fclose_All;
Raise_Application_Error ( -20000, 'Invalid_Maxlinesize trapped' );

when
-- ORA-29282: invalid file ID
Utl_File.Invalid_Filehandle
then
Utl_File.Fclose_All;
Raise_Application_Error ( -20000, 'Invalid_Filehandle trapped' );
end;
/