Re: Fixing error ORA-06571

If you are using the package as part of a SQL statement, you have to issue a pragma to tell the compiler if you are updating any database tables or variables etc. See any good oracle book for details.

Change your package spec to look like...

CREATE OR REPLACE PACKAGE pk_rep_terr
IS
  FUNCTION current_alignment RETURN number;   pragma restrict_references (current_alignment, WNDS, RNDS); END pk_rep_terr;

You may not need the RNDS pragma, try it without and see if it works.

Mark

>/

PMG wrote in message <376F20B6.38E224FA_at_2xtreme.net>...
>Using Oracle 7.3.3, I have created the following package and function,
>and when I say
> select pk_rep_terr.current_alignment from dual
>I get an error message ---
>ORA-06571: Function CURRENT_ALIGNMENT does not guarantee not to update
>database.
>
>The documentation says
> *Cause: There are two possible causes for this message:
> * A SQL statement references a packaged, PL/SQL function
> that does not contain a pragma that prevents the database
> from being updated.
> * A SQL statement references a stand-alone, PL/SQL function
> that contains an instruction to update the database.
> *Action: If the referenced function is a packaged, PL/SQL function:
> Recreate the PL/SQL function with the required pragma; be
> certain to include the 'Write No Database State' (WNDS)
> argument in the argument list of the pragma.
> If the referenced function is a stand-alone, PL/SQL function:
> Do not use the function.
>
>
>Any suggestions?
>
>Here is what I have got:
>
>CREATE OR REPLACE PACKAGE pk_rep_terr
>IS
> FUNCTION current_alignment
> RETURN number;
>END pk_rep_terr;
>/
>
>CREATE OR REPLACE PACKAGE BODY pk_rep_terr
>IS
>FUNCTION current_alignment
> RETURN number
>IS
> cur_al NUMBER;
> BEGIN
> /* Determine the current alignment number */
> SELECT
> MAX(rv_low_value)
> INTO
> cur_al
> FROM
> hs_ref_codes
> WHERE
> rv_domain = 'ALIGNMENT';
> RETURN cur_al;
> END current_alignment;
>END pk_rep_terr;
>/
>
Received on Tue Jun 22 1999 - 03:09:44 CDT