Executing procedure with no results 1

[Jami]

I am just diving into pl/sql and have compliled a number of procedures without errors and executed them successfully, yet nothing specified in my procedure is actually occurring. Is there something I am missing? Here is the most basic of my procedures just to test its success and still an insert, dbms_output or file write did not occur and no errors were generated.
(And I have specified utl_file_dir=directory path in my init.ora file as well.)

create or replace procedure test_file

as

-------------------Storage Variables --------------------------
n number :=0;
error_msg VARCHAR2(300) := SQLERRM;


---------------------Utl File Variables-------------------------
p_file_name VARCHAR2(20);
p_file_dir VARCHAR2(30);
v_file_handle UTL_FILE.FILE_TYPE;



-------------------------Main Program Logic-----------------

BEGIN

p_file_name := ‘itt_leads.txt’;
p_file_dir := ‘/export/home/u01/app/oracle/product/8.1.6’;
v_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, ‘A’);

insert into
dev.test (address1, address2)
values (‘street1’, ‘street2’);


DBMS_OUTPUT.ENABLE(50000);
SYS.DBMS_OUTPUT.PUT_LINE(‘Working’);
UTL_FILE.PUT_LINE(v_file_handle, ‘Is this working’);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(v_file_handle);
DBMS_OUTPUT.PUT_LINE(‘File write: Invalid Path’);

WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(v_file_handle);
DBMS_OUTPUT.PUT_LINE(‘File write: Invalid Mode’);

WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(v_file_handle);
DBMS_OUTPUT.PUT_LINE(‘File write: Invalid Operation’);

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(v_file_handle);
DBMS_OUTPUT.PUT_LINE(‘File write: Invalid File_Handle’);

WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(v_file_handle);
DBMS_OUTPUT.PUT_LINE(‘File write: Write Error’);

WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file_handle);
DBMS_OUTPUT.PUT_LINE(‘file write others’ || error_msg);

END;
/

I would be so happy to get these procedures running. Thanks in advance!

 

[Jami]

Ok, my dbms_output was not working from sql*plus because I had to invoke the following:

set serveroutput on;

Now, the only thing not working is utl_file.put_line. My filed is created with fopen, but not ever written to after that.???

 

[karluk]

As far as I can see the only mistake you made is not giving yourself credit for being able to write error-free code. The only place you ever do a utl_file.fclose is in your exception routine. Since the procedure completes without error the file never gets closed and you don't see any output.

 

[Jami]

Karluk,
That was exactly the problem. Thanks so much for replying!

Jami