Re: Calling a function from a view

Your problem is not view related. In order to use a user function from SQL it can not modify any database objects. Your function calls a stored procedure which in its turn calls Oracle supplied package DBMS_PIPE. One of the differences between standalone and packaged function/procedure is that standalone function/procedure is compiled every time you reference it. Therefore, when it is called from SQL Oracle knows to check if it is changing database state. Packaged are stored already compiled. Therefore, if you intend to use packaged function/procedure from SQL, you need to indicate it in package specification by using RESTRICT_REFERENCES pragma (Application Developer's Guide, Chapter 6: Using Procedures and Packages, Section: Calling Stored Functions from SQL Expressions). Unfortunately, all Oracle provided packages do not include PRAGMA RESTRICT_REFERENCES for any function/procedure that does not "write any database state". As a result you can not reference any Oracle provided packages in a function you intend to use from SQL. I filed this issue to Oracle support about a year ago and response was Oracle 8. I guess we have to wait couple more months to find out if it is the case.

Solomon.Yakobson_at_entex.com

In article <33544B6E.17A8_at_deakin.edu.au>,   lesp_at_deakin.edu.au wrote:
>
>
> 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.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet