Utl_File and OMS

[Guest_imported]

This site has saved a lot of jobs including mine.
I have a couple more that I hope I can get help on.
One, I am trying to write an utl_file package that
is intended to produce an html code.
My questions are 1, does utl_file package work with
win2000?, 2, how do I test it?
I have set up an utl_file directory in init.ora.
Please take a look at this code and tell what is wrong
with it. It is compiling but I cannot see the result
anywhere. How do I check it to see that it is doing
what I intennd it to do?

CREATE OR REPLACE PROCEDURE Cr_Movie_PRV_HTML(
P_FileName IN VARCHAR2,
p_Id IN Movie.Movie_Id%TYPE
)
AS
CURSOR C_Movie_Detail IS
SELECT MOVIE_ID,
MOVIE_NAME,
MOVIE_TYPE_FLAG,
MOVIE_LANGUAGE_FLAG,
DESCRIPTION,
DIRECTOR,
GENRE,
PLAYING_TIME,
PRODUCER,
STUDIO,
RELEASE_DATE,
Movie_RATING,
VIDEO_RELEASE_DATE,
ACTIVE_DATE,
INACTIVE_DATE
FROM Movie
WHERE Movie_Id = P_Id;
V_FileHandle UTL_FILE.FILE_TYPE; -- This will be used to store the filename
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- Open The specified file to write into and get V_FileHandle.
V_FileHandle := UTL_FILE.FOPEN('d:\',P_FileName, 'W');
EXCEPTION
WHEN Utl_File.Invalid_Path THEN
RAISE_APPLICATION_ERROR(-2000, 'Error opening'||P_FileName||'for WRITE');
-- V_FileHandle := UTL_FILE.FCLOSE('d:\',P_FileName, 'W');
-- Reopen The specified file to write into and get V_FileHandle.
V_FileHandle := UTL_FILE.FOPEN('d:\',P_FileName, 'W');
-- Let's build the header file that will be inside the file
utl_file.put_line(V_FileHandle,'#------------------------------------------------------------------');
utl_file.put_line(V_FileHandle,'# Generated on: '||to_char(sysdate, 'Mon DD, YYYY (Dy) HH:MI:SS AM'));
utl_file.put_line(V_FileHandle,'#------------------------------------------------------------------');
utl_file.put_line(V_FileHandle,' ');
-- Next we will query data from the Movie table. Only data that are relevant.
-- Using PUT_LINE, write text with the field arguments out to the file
BEGIN
FOR Movie_Rec IN C_Movie_Detail LOOP
UTL_FILE.PUT_LINE(V_FileHandle, TO_CHAR(Movie_Rec.Movie_Id)||','||Movie_Rec.Movie_Name||','||
Movie_Rec.Movie_Type_Flag||','||Movie_Rec.Movie_Language_Flag||','||
Movie_Rec.Description||','||Movie_Rec.Genre||','||Movie_Rec.Director||','||
Movie_Rec.Playing_Time||','||Movie_Rec.Producer||','||Movie_Rec.Studio||','||
Movie_Rec.Release_Date||','||Movie_Rec.Movie_Rating||','||
Movie_Rec.Video_Release_Date||','||Movie_Rec.Active_Date||','||
Movie_Rec.Inactive_Date);
END LOOP;
END;
-- Now Close the file
UTL_FILE.FCLOSE( V_FileHandle);
END Cr_Movie_PRV_HTML;
/*******************************************End Movie procedure*******************************************/
/

In a totally unrelated case, I am trying to start service
for oracle management server but I am running into error 997
which in part means that "I cannot start oms on local computer because overlapped i/o operation in progress"
Sorry folks about the long thread.
toaft

 

[sem]

1. The answer is yes, utl_file package is Oracle stored package so it works on any platform Oracle does. There are some restrictions for NT/W2k: the directory have to be local one or oracle service must be set up to run under account of a user who have access to remote directory.
2.You may delete the call to dbms_output for later you may implicitly enable it from sql*plus by set serverout on. Just create this procedure and try to call it from sql*plus. You'll see all errors if any. But I suppose the problem is that your procedure does not make the work it's developed for. Look, you'll enter the main cycle ONLY if getting Utl_File.Invalid_Path exception. Try to move your exception handler to the end of procedure, otherwise all the code after exception keyword is treated as a part of handler. Another way is to enclose cal to fopen in its own begin/end (pl/sql block) and place exception handler into this block.

 

[carp]

1. There's no need to enable DBMS_OUTPUT - you're not using it anywhere. utl_file will write whether you have dbms_output enabled or not.

2. In your fopen command, don't include the trailing slash on your directory. Instead, try

V_FileHandle := UTL_FILE.FOPEN('d:',P_FileName, 'W');

(although I would normally use a subdirectory instead of the root of the drive).
3. As sem points out, all your procedure does is open a file - everything else is subsumed as an EXCEPTION handler.
Recommend the following structure:

BEGIN
OPEN FILE
WRITE FILE HEADER
OPEN AND PROCESS THE CURSOR
CLOSE THE FILE
EXCEPTION
WHEN Utl_File.Invalid_Path THEN
RAISE_APPLICATION_ERROR ...
WHEN OTHERS THEN
CLOSE THE FILE
END;

Note the WHEN OTHERS handler - one thing I have found is that if you don't close the file before you leave the procedure, the file can be locked and you can neither access nor delete it. Most times you'll remember to close it in your main code, but forget about it in the exception handlers!

 

[Guest_imported]

You have done it again!
Thanks sem and carp!!
It worked.
toaft