Home » SQL & PL/SQL » SQL & PL/SQL » How to call user defined function in SQL statement
How to call user defined function in SQL statement [message #6777] Tue, 06 May 2003 03:46 Go to next message
Jayasri
Messages: 28
Registered: July 2000
Junior Member
Hi,
Could anyone tell me how to call user defined function in a SQL statement. I've heard that this can be done if it satisfies the 'Purity levels'. What are those purity levels ?

Regards
Jayasri.
Re: How to call user defined function in SQL statement [message #6780 is a reply to message #6777] Tue, 06 May 2003 04:16 Go to previous messageGo to next message
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
Re: How to call user defined function in SQL statement [message #6784 is a reply to message #6780] Tue, 06 May 2003 05:14 Go to previous messageGo to next message
Jayasri
Messages: 28
Registered: July 2000
Junior Member
Thanks a lot Barbara. That was great !
Re: How to call user defined function in SQL statement [message #11899 is a reply to message #6780] Wed, 21 April 2004 08:57 Go to previous messageGo to next message
j w
Messages: 1
Registered: April 2004
Junior Member
can anyone tell how I sould be able to to see the structure of a function - i.e I know one one exists and what its called but want to see the SQL that is called when the function is used.
Re: How to call user defined function in SQL statement [message #11922 is a reply to message #11899] Thu, 22 April 2004 04:45 Go to previous message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
SELECT text
FROM all_source
WHERE owner = UPPER ('&owner_of_function')
AND name = UPPER ('&name_of_function')
ORDER BY line;
Previous Topic: availability of utl_smtp
Next Topic: Sum by date within a rolling 52 week period.
Goto Forum:
  


Current Time: Fri Apr 26 23:55:01 CDT 2024