Calling a function from a view

Summary:

Cannot create a view that calls a function that writes to a pipe. The following error occurs -

ORA-06571: Function FUNCTION_X does not guarantee not to update database.

Can the view be forced to use the function?

Detailed explanation of problem:

The application we are developing has a mechanism that when an unhandled exception occurs in a stored procedure within the database, then information concerning the exception is written to a pipe. This occurs by calling a function within the exception handler (genp_log_error) in the stored procedure.

An example is shown below:

	CREATE OR REPLACE FUNCTION FUNCTION_X (p_1 VARCHAR2) 
			RETURN VARCHAR2 IS
		v_other_detail	VARCHAR2 (500);
	BEGIN


		--	

		-- General functionality	


		--	

	EXCEPTION
		WHEN OTHERS THEN
			v_other_detail := 'Parameters:' || 
				' p_1- ' || p_1; 
			genp_log_error(
				'FUNCTION_X',
				SQLCODE,
				SQLERRM,
				v_other_detail);
			RAISE;
	END;  

The raise after genp_log error will ensure the error is handled within the forms application on the clients side.

Genp_log error has the following code:

	PROCEDURE genp_log_error(
		p_program_unit_name IN VARCHAR2 ,
		p_sqlcode IN NUMBER ,
		p_sqlerrm IN VARCHAR2 ,
		p_other_detail IN VARCHAR2 )
	IS
	BEGIN
	    --- Construct message for sending
	    DBMS_PIPE.PACK_MESSAGE(p_program_unit_name);
	    DBMS_PIPE.PACK_MESSAGE(p_sqlcode);
	    DBMS_PIPE.PACK_MESSAGE(p_sqlerrm);
	    DBMS_PIPE.PACK_MESSAGE(p_other_detail);
	    DBMS_PIPE.PACK_MESSAGE(USER);
	    DBMS_PIPE.PACK_MESSAGE(SYSDATE);
	    --- Send the message and raise an exception if it fails
	    IF DBMS_PIPE.SEND_MESSAGE('sys_error_pipe') <> 0 THEN
	      RAISE_APPLICATION_ERROR(-20999,
			'GENP_LOG_ERROR: Internal error writing to pipe.' ||
		        ' SQLERRM: ' || sqlerrm);
	    END IF;
	END;

The problem is that a view cannot be created which calls the function from within the select. The following error occurs:

ORA-06571: Function FUNCTION_X does not guarantee not to update database.

If the call to genp_log_error is commented out, the view can be created. The function does not update any tables. I suspect that somewhere in the DBMS_PIPE package that global variables are being altered.

Does anyone know of how to force the view to use the function without having
to alter genp_log_error or the call in the exception handler??

Thanks in advance,

Les. Received on Wed Apr 16 1997 - 00:00:00 CDT