|
Re: How to call user defined function in SQL statement [message #6780 is a reply to message #6777] |
Tue, 06 May 2003 04:16 |
|
Barbara Boehmer
Messages: 9090 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
PRAGMA RESTRICT_REFERENCES is used to check purity levels within packaged functions of versions of Oracle prior to 8i. By purity we mean that it does not violate certain rules like writing to the database, writing to the package, and so forth. These are only required if you are using an old unsupported version of Oracle and are optional in newer versions.
Please click on the link below for Oracle on-line documentation on the subject:
Here is a simple example of creation of a user-defined packaged function that uses PRAGMA RESTRICT_REFERENCES to check the purity level, followed by an example of its usage in a sql select statement:
SQL> CREATE OR REPLACE PACKAGE test_pkg
2 AS
3 FUNCTION format_phone
4 (p_phone IN VARCHAR2)
5 RETURN VARCHAR2;
6 PRAGMA RESTRICT_REFERENCES (format_phone, 'WNDS', 'WNPS', 'RNDS');
7 END test_pkg;
8 /
Package created.
SQL> SHOW ERRORS
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg
2 AS
3 FUNCTION format_phone
4 (p_phone IN VARCHAR2)
5 RETURN VARCHAR2
6 IS
7 v_phone VARCHAR2 (14);
8 BEGIN
9 v_phone := '('
10 || SUBSTR (p_phone, 1, 3)
11 || ') '
12 || SUBSTR (p_phone, 4, 3)
13 || '-'
14 || SUBSTR (p_phone, 7);
15 RETURN v_phone;
16 END format_phone;
17 END test_pkg;
18 /
Package body created.
SQL> SHOW ERRORS
No errors.
SQL> SELECT test_pkg.format_phone ('1234567890') AS formatted_phone FROM DUAL
2 /
FORMATTED_PHONE
--------------------------------------------------------------------------------
(123) 456-7890
|
|
|
|
|
|