[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

utl_file.invalid_path

Posted on 2005-05-16
3
Medium Priority
?
937 Views
Last Modified: 2008-01-16
I am facing problem in executing the package
ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at "SYS.UTL_FILE", line 534

ORA-06512: at "SYSTEM.MYDEBUG", line 56

ORA-06512: at "SYSTEM.MYDEBUG", line 66

ORA-06512: at line 3

 
My package is
============
CREATE OR REPLACE PACKAGE Mydebug AS
   
        -- Define a variable to hold the file id for all debugging actions.
        FileId utl_file.file_type;
     
     
     -- This procedure will initialise the debugging system by opening the debugging file. You
     -- could add more stuff here if you wish.
     PROCEDURE Initialise(iPath IN VARCHAR2, iFile IN VARCHAR2, iBuffer IN NUMBER := 256);
   
    -- This procedure terminates the debugging system - simply by closing whichever file is open.
    -- Although UTL_FILE allows a number of files to be open at any time, this is a simple
    -- system and only has a single debugging file.
    PROCEDURE TERMINATE;
   
    -- And the guts of the package is this simple procedure to send whatever is passed to it,
    -- straight out to the logging file. The option to flush the buffer contents is provided.
    PROCEDURE debug_out(iText IN VARCHAR2, iFlush IN BOOLEAN := FALSE);
     PROCEDURE Test(File IN VARCHAR2);
   
   END;
/
CREATE OR REPLACE PACKAGE BODY Mydebug AS
       -- Define a variable to hold the file id for all debugging actions.
        -- FileId utl_file.file_type;
     
     
     -- This procedure will initialise the debugging system by opening the debugging file. You
     -- could add more stuff here if you wish.
     PROCEDURE Initialise(iPath IN VARCHAR2, iFile IN VARCHAR2, iBuffer IN NUMBER := 256)
    AS
    BEGIN
       Dbms_Output.put_line('ipath = '||iPath ||'File '||iFile);
       Mydebug.FileId := utl_file.fopen(iPath, iFile, 'W', iBuffer);
       Mydebug.debug_out('Debugging session begins at ' || TO_CHAR(SYSDATE));    
    EXCEPTION
         WHEN utl_file.invalid_path THEN
           Dbms_Output.put_line('utl_file.invalid_path');
           --RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
         WHEN utl_file.invalid_mode THEN
           --RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
           DBMS_OUTPUT.put_line('utl_file.invalid_mode');
         WHEN utl_file.invalid_filehandle THEN
            DBMS_OUTPUT.put_line('utl_file.invalid_filehandle');
           --RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
         WHEN utl_file.invalid_operation THEN
           --RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
           DBMS_OUTPUT.put_line('utl_file.invalid_operation');
         WHEN utl_file.read_error THEN
           --RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
           DBMS_OUTPUT.put_line('utl_file.read_error');
         WHEN utl_file.write_error THEN
           --RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
           DBMS_OUTPUT.put_line('utl_file.write_error');
         WHEN utl_file.internal_error THEN
           --RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
           DBMS_OUTPUT.put_line('utl_file.internal_error');
         WHEN OTHERS THEN
           --RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
           DBMS_OUTPUT.put_line('utl_file.other_error');
       END;
   
    -- This procedure terminates the debugging system - simply by closing whichever file is open.
    -- Although UTL_FILE allows a number of files to be open at any time, this is a simple
    -- system and only has a single debugging file.
    PROCEDURE TERMINATE
    AS
    BEGIN
       Mydebug.debug_out('Debugging session terminated at ' || TO_CHAR(SYSDATE));
       utl_file.fclose(Mydebug.FileId);
    END;
   
    -- And the guts of the package is this simple procedure to send whatever is passed to it,
    -- straight out to the logging file. The option to flush the buffer contents is provided.
    PROCEDURE debug_out(iText IN VARCHAR2, iFlush IN BOOLEAN := FALSE)
    AS
    BEGIN
       utl_file.put_line(Mydebug.FileId, iText);
       IF (iFlush) THEN
         utl_file.fflush(Mydebug.FileId);
       END IF;
    END;
    PROCEDURE Test(File IN VARCHAR2)
    AS
    BEGIN
         Mydebug.INITIALISE('e:\TrueUpData\',File);
       FOR something IN 1..100 LOOP
         Mydebug.DEBUG_OUT('Currently processing record : ' || TO_CHAR(something));
       END LOOP;
 
       Mydebug.TERMINATE;
   END;    
   
   END;
/

My test file is
=========
declare
  -- Local variables here
  i integer;
begin
  Mydebug.Test('test.log');
  end;


My package is pretty simple.
I have the directory and i have the permissions.
I am on Win2000 server.

Can anyone help me with this.

Thanks
Deepak
0
Comment
Question by:dgupta70
1 Comment
 
LVL 25

Accepted Solution

by:
jrb1 earned 2000 total points
ID: 14015763
You can't write directly to a path.  You have to first create a directory within oracle.

create or replace directory temp_dir as 'c:\temp';

Then, when you go to write to that directory, you

f := utl_file.fopen('temp_dir', 'filename.txt', 'w');

Some more examples here:

http://www.adp-gmbh.ch/ora/sql/create_directory.html

Do this:

select * from all_directories;

Do you get anything?  If not, you don't have any directories created.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question