ORA-06503 Function returned without Value

From: Gerry West <gwest_at_skyconnect.com>
Date: Sat, 20 Mar 1999 14:19:53 -0700
Message-ID: <7d13el$f1q$1@news1.rmi.net>

Hi All,

This is my first function.
Can someone tell me why I would be getting an ORA- 06503 PL\SQL: Function returned without value. I created a function that would initialize a file handle to use with UTL_FILE
functionality. I'm calling this function from a procedure which then uses it to PUTF and FFLUSH.

Thanks in advance.
Gerry West

CREATE OR REPLACE FUNCTION openConfigLogfile( i_log_handle IN OUT UTL_FILE.FILE_TYPE )
RETURN UTL_FILE.FILE_TYPE IS
BEGIN
/* Make sure the file is closed first. */
    IF UTL_FILE.IS_OPEN(i_log_handle) THEN

        UTL_FILE.FCLOSE(i_log_handle);
    END IF;
/* Open the file for writing. */

    i_log_handle := UTL_FILE.FOPEN('/usr/oracle8/install', 'nvod_config.log', 'a');

EXCEPTION

    WHEN UTL_FILE.INVALID_PATH THEN
        RAISE_APPLICATION_ERROR(-20100, 'Reset: Invalid Path');
    WHEN UTL_FILE.INVALID_MODE THEN
        RAISE_APPLICATION_ERROR(-20101, 'Reset: Invalid Mode');
    WHEN UTL_FILE.INVALID_OPERATION THEN
        RAISE_APPLICATION_ERROR(-20101, 'Reset: Invalid Operation');

RETURN i_log_handle;
END openConfigLogfile;
/

CREATE OR REPLACE PROCEDURE logUsrs( i_ctable  IN VARCHAR2,
                                     i_caction IN VARCHAR2,
                                     i_user_id IN usrs_users.user_id%TYPE,
                                     i_user_name IN
usrs_users.user_name%TYPE,
                                     i_password IN

usrs_users.password%TYPE ) AS
v_log_handle UTL_FILE.FILE_TYPE;
v_osuser sys.v_$session.osuser%TYPE;
BEGIN     SELECT osuser INTO v_osuser
    FROM sys.v_$session
    WHERE audsid = userenv('sessionid');

/* Initialize the File Handle */

    v_log_handle := openConfigLogfile( v_log_handle);

/* Write data to file and flush handle */
    UTL_FILE.PUTF (v_log_handle, 'Date: %s OSUser: %s Table: %s DML: %s ',

               SYSDATE, v_osuser, i_ctable, i_caction );     UTL_FILE.FFLUSH(v_log_handle);
    UTL_FILE.PUTF (v_log_handle, 'FIELDS: ID %s UserName %s Password %s \n',

                              i_user_id, i_user_name, i_password );

    UTL_FILE.FFLUSH(v_log_handle);

EXCEPTION
    WHEN UTL_FILE.INVALID_OPERATION THEN

      RAISE_APPLICATION_ERROR(-20102,
                              'logUsrs: Invalid Operation, a or w not

designated.');

    WHEN UTL_FILE.INVALID_FILEHANDLE THEN

      RAISE_APPLICATION_ERROR(-20103,
                              'logUsrs: Invalid Path/File Location

Handle.');

    WHEN UTL_FILE.WRITE_ERROR THEN

      RAISE_APPLICATION_ERROR(-20104,
                              'logUsrs: Write Error, OS error during write

operation.');

/* Close the File Handle */

    closeConfigLogfile(v_log_handle);

END logUsrs;
/ Received on Sat Mar 20 1999 - 15:19:53 CST