ORA-06571: Function TOTAL_COMP does not guarantee not to
update database
As discussed in
Section 17.7, "Realities: Calling PL/SQL Functions in SQL", it can be very difficult
at times (and sometimes impossible) to avoid this error. In other situations, however, there is an easy
resolution (certainly do check the above list of restrictions).
Previous: 17.3
Requirements for Stored
Functions in SQL
Oracle PL/SQL
Programming, 2nd Edition
Next: 17.5 Calling
Packaged Functions in SQL
17.3 Requirements for Stored
Functions in SQL
Book Index
17.5 Calling Packaged
Functions in SQL
The Oracle Library
Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 17.4 Restrictions
on PL/SQL Functions in
SQL
Chapter 17
Calling PL/SQL Functions in
SQL
Next: 17.6 Column/
Function Name Precedence

WNDS
Writes No Database State. Asserts that the function does not modify any database tables.
WNPS
Writes No Package State. Asserts that the function does not modify any package variables.
RNDS
Reads No Database State. Asserts that the function does not read any database tables.
RNPS
Reads No Package State. Asserts that the function does not read any package variables.
Notice that only the WNDS level is mandatory in the pragma. That is consistent with the restriction
that stored functions in SQL may not execute an UPDATE, INSERT, or DELETE statement. All
other states are optional. You can list them in any order, but you must include the WNDS argument.
No one argument implies another argument. I can write to the database without reading from it. I can
read a package variable without writing to a package variable.
Here is an example of two different purity level assertions for functions in the company_financials
package:
PACKAGE company_financials
IS
FUNCTION company_type (type_code_in IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION company_name (company_id_in IN company.
company_id%TYPE)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES (company_type, WNDS, RNDS,
WNPS, RNPS);
PRAGMA RESTRICT_REFERENCES (company_name, WNDS, WNPS,
RNPS);
END company_financials;
In this package, the company_name function reads from the database to obtain the name for the
specified company. Notice that I placed both pragmas together at the bottom of the package
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

/
When I attempt to compile this package body I will get the following error:
3/4 PLS-00452: Subprogram 'COMPANY_TYPE' violates its
associated pragma
because the company_type function reads from the database and I have asserted the RNDS purity
level. If I remove that silly SELECT statement, I will then receive this error:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
11/4 PLS-00452: Subprogram 'COMPANY_NAME' violates its
associated pragma
because the company_name function updates the database and I have asserted the WNDS level. You
will sometimes look at your function and say: "Hey, I absolutely do not violate my purity level. There
is no UPDATE, DELETE, or UPDATE around." Maybe not. But there is a good chance that you are
calling a built-in package or in some other way breaking the rules.
17.5.2 Asserting Purity Level with Package Initialization Section
If your package contains an initialization section (executable statements after a BEGIN statement in
the package body), you must also assert the purity level of that section. The initialization section is
executed automatically the first time any package object is referenced. So if a packaged function is
used in a SQL statement, it will trigger execution of that code. If the initialization section modifies
package variables or database information, the compiler needs to know about that through the
pragma.
You can assert the purity level of the initialization section either explicitly or implicitly. To make an
explicit assertion, use the following variation of the pragma RESTRICT_REFERENCES:
PRAGMA RESTRICT_REFERENCES
(package_name, WNDS, [, WNPS] [, RNDS] [, RNPS])
Instead of specifying the name of the function, you include the name of the package itself, followed
by all the applicable state arguments. In the following argument I assert only WNDS and WNPS
because the initialization section reads data from the configuration table and also reads the value of a
global variable from another package (session_pkg.user_id).
PACKAGE configure
IS

section. This makes it easier for those responsible for maintaining the package to understand both
your intentions and your understanding of the package.
Previous: 17.4 Restrictions
on PL/SQL Functions in
SQL
Oracle PL/SQL
Programming, 2nd Edition
Next: 17.6 Column/
Function Name Precedence
17.4 Restrictions on PL/SQL
Functions in SQL
Book Index
17.6 Column/Function Name
Precedence
The Oracle Library
Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 17.5 Calling
Packaged Functions in SQL
Chapter 17
Calling PL/SQL Functions in
SQL
Next: 17.7 Realities:
Calling PL/SQL Functions
in SQL

17.6 Column/Function Name Precedence
If your function has the same name as a table column in your SELECT statement and it has no

Previous: 17.6 Column/
Function Name Precedence
Chapter 17
Calling PL/SQL Functions in
SQL
Next: 17.8 Examples of
Embedded PL/SQL

17.7 Realities: Calling PL/SQL Functions in SQL
The ability to call PL/SQL functions in SQL has been around since Release 2.1, but in many ways (at
least until Oracle8) it can still be considered "bleeding edge" technology. Why?
●
You must manually apply RESTRICT_REFERENCES pragmas to all of your code -- and you
have to figure out where all those pragmas need to go. This process is described in a
subsection below.
●
Functions execute outside of the read consistency model of the Oracle database (!). This issue
is also explored below in a subsection below.
●
The overhead of calling a function from SQL remains high. The exact price you pay to call a
function from within SQL (compared to, say, executing in-line SQL code) can be hard to pin
down. It varies from computer to computer and even by instance or by the function being
called; I have heard reports that range from an extra half-second to an astonishing additional
50 seconds (in that case, I suggested that they do some more analysis and debugging).
Whatever the specific amount of time, the delay can be noticeable and you need to factor it
into your design and test plans.
●
Tuning mechanisms such as EXPLAIN PLAN do not take into account the SQL that may be
called inside functions called in your SQL statement. PL/SQL functions are ignored by the
EXPLAIN PLAN facility. This makes it very difficult to come up with a comprehensive

absolutely minimal use of built-in packaged functionality. Neither an easy nor a particularly desirable
task.
17.7.2 Read Consistency Model Complications
Yes, it is hard to believe, but quite true: unless you take special precautions, it is quite possible that
your SQL query will violate the read consistency model of the Oracle RDBMS, which has been
sacrosanct territory for years at Oracle. To understand this issue, consider the following query and the
function it calls:
SELECT name, total_sales (account_id)
FROM account
WHERE status = 'ACTIVE';
FUNCTION total_sales (id_in IN account.account_id%TYPE)
RETURN NUMBER
IS
CURSOR tot_cur
IS
SELECT SUM (sales) total
FROM orders
WHERE account_id = id_in
AND year = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'));
tot_rec tot_cur%ROWTYPE;
BEGIN
OPEN tot_cur;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FETCH tot_cur INTO tot_rec;
RETURN tot_rec.total;
END;
The account table has five million active rows in it (a very successful enterprise!). The orders table
has 20 million rows. I start the query at 11 a.m.; it takes about an hour to complete. At 10:45 a.m.,
somebody with the proper authority comes along, deletes all rows from the orders table and performs
a commit. According to the read consistency model of Oracle, the session running the query should


Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 17.7 Realities:
Calling PL/SQL Functions
in SQL
Chapter 17
Calling PL/SQL Functions in
SQL
Next: V. New PL/SQL8
Features

17.8 Examples of Embedded PL/SQL
The more you think about stored functions in SQL, the more you come up with ways to put them to
use in every single one of your applications. To prod your creativity and get you started, here are a
number of examples of the ways stored functions in SQL can change the way you build Oracle-based
systems.
17.8.1 Encapsulating Calculations
In just about any and every application, you will need to perform the same calculations over and over
again. Whether it is a computation of net present value, mortgage balance, the distance between two
points on a Cartesian plane, or a statistical variance, with native SQL you have to recode those
computations in each of the SQL statements in which they are needed.
You can pay a big price for this kind of redundancy. The code that implements your business rules is
repeated throughout the application. Even if the business rule doesn't change, the way you should
implement the rule is almost sure to require modification. Worse than that, the business rule itself
might evolve, which could necessitate fairly significant alterations.
To solve this problem, you can hide or encapsulate all of your formulas and calculations into stored
functions. These functions can then be called from within both SQL statements and also PL/SQL
programs.
One fine example of the value of encapsulated calculations arose when an insurance company needed

Not only is that difficult to understand, but it required three different calls to the ADD_MONTHS
built-in. And remember that this complex SQL would have to be repeated in every SELECT list
where ADD_MONTHS was used to increment or decrement dates. You can well imagine how happy
the programmers in this company became when they installed Oracle Server Version 7.1 and were
able to use the following function inside their SQL statements (for a full explanation of the function's
logic, see
Chapter 12, Date Functions):
FUNCTION new_add_months (date_in IN DATE, months_shift IN
NUMBER)
RETURN DATE
IS
return_value DATE;
BEGIN
return_value := ADD_MONTHS (date_in, months_shift);
IF date_in = LAST_DAY (date_in)
THEN
return_value :=
LEAST (return_value,
TO_DATE (TO_CHAR (return_value, 'MMYYYY')
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
||
TO_CHAR (date_in, 'DD') ,
'MMYYYYDD'));
END IF;
RETURN return_value;
END new_add_months;
With the stored function, the SELECT statement to obtain the next payment date is simply:
SELECT new_add_months (payment_date,1)
FROM premium_payments;
The more you look through your SQL statements, the more opportunities you find for stored

CREATE VIEW dept_salary
AS
SELECT department_id, SUM (salary) total_salary
FROM employee
GROUP BY department_id;
Now, with this view, I can get at my answer with a single SQL statement as follows:
SELECT E.department_id, last_name, salary, total_salary
FROM employee E, dept_salary DS
WHERE E.department_id = DS.department_id
AND salary = (SELECT MAX (salary)
FROM employee E2
WHERE E.department_id = E2.
department_id);
This doesn't seem like such a bad solution, except that you have to create a customized view each
time you want to perform this kind of calculation. In addition, this SQL is far less than
straightforward for many programmers.
A better solution is to make use of a stored function in SQL. Instead of creating a view, create a
function that performs exactly the same calculation, but this time only for the specified department:
FUNCTION total_salary (dept_id_in IN department.
department_id%TYPE)
RETURN NUMBER
IS
CURSOR grp_cur
IS
SELECT SUM (salary)
FROM employee
WHERE department_id = dept_id_in;
return_value NUMBER;
BEGIN
OPEN grp_cur;

SELECT E.department_id, last_name, salary, total_salary
(E.department_id)
FROM employee E
WHERE salary = (SELECT MAX (salary)
FROM employee E2
WHERE E.department_id = E2.
department_id);
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The last three lines in the query contain a SELECT statement matching the department ID number for
the "inner" employee (E2) to the department ID number for the "outer" employee table (E1). The
inner query is executed once for every row retrieved in the outer query.
The correlated subquery is a very powerful feature in SQL, since it offers the equivalent of a
procedural language's nested loop capability, as in:
LOOP
LOOP
END LOOP;
END LOOP;
Two drawbacks with a correlated subquery are:
●
The logic can become fairly complicated
●
The resulting SQL statement can be difficult to understand and follow
You can use a stored function in place of a correlated subquery to address these drawbacks; in the
above example, I would want a function that calculates the highest salary in a given department:
FUNCTION max_salary (dept_id_in IN department.
department_id%TYPE)
RETURN NUMBER
IS
CURSOR grp_cur
IS

WHERE E.department_id = E2.
department_id);
and I am sure you will agree that stored functions in SQL can make your life much easier.
You may have noticed that the total_salary function from the previous section and the max_salary
from this section look very similar. The only difference between the two is that the cursor in
total_salary uses the SUM group function and the cursor in max_salary uses the MAX group
function. If you are as fanatical about consolidating your code into the smallest possible number of
distinct "moving parts," you might consider a single function that returns a different group-level
statistic for a department based on a second parameter, as follows:
FUNCTION salary_stat
(dept_id_in IN department.department_id%TYPE,
stat_type_in IN VARCHAR2)
RETURN NUMBER
IS
v_stat_type VARCHAR2(20) := UPPER (stat_type_in);
CURSOR grp_cur
IS
SELECT SUM (salary) sumsal,
MAX (salary) maxsal,
MIN (salary) minsal,
AVG (salary) avgsal,
COUNT (DISTINCT salary) countsal,
FROM employee
WHERE department_id = dept_id_in;
grp_rec grp_cur%ROWTYPE;
retval NUMBER;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN
OPEN grp_cur;
FETCH grp_cur INTO grp_rec;

by the Oracle Server. You can use the DECODE syntax to create matrix reports with a fixed number
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
of columns and also perform complex IF-THEN-ELSE logic within a query. The downside to
DECODE is that it can be difficult to write and very difficult to maintain. Consider the following
example of DECODE to determine whether a date is within the prescribed range and, if it is, add to
the count of rows that fulfill this requirement:
SELECT FC.year_number,
SUM (DECODE (GREATEST (ship_date, FC.q1_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.
q1_edate),
ship_date, 1,
0),
0)) Q1_results,
SUM (DECODE (GREATEST (ship_date, FC.q2_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.
q2_edate),
ship_date, 1,
0),
0)) Q2_results,
SUM (DECODE (GREATEST (ship_date, FC.q3_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.
q3_edate),
ship_date, 1,
0),
0)) Q3_results,
SUM (DECODE (GREATEST (ship_date, FC.q4_sdate),
ship_date,