Oracle Functions
Version 12.1.0.1

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Note: This page does not include Pipelined Table Functions they are linked at page bottom
Data Dictionary Objects
ALL_ARGUMENTS CDB_PLSQL_OBJECT_SETTINGS ERROR$
ALL_ERRORS_AE CDB_PROCEDURES OBJ$
ALL_OBJECT_SIZE CDB_SOURCE_AE SOURCE$
ALL_PLSQL_OBJECT_SETTINGS DBA_ARGUMENTS USER_ARGUMENTS
ALL_PROCEDURES DBA_ERRORS_AE USER_ERRORS_AE
ALL_SOURCE_AE DBA_OBJECT_SIZE USER_OBJECT_SIZE
CDB_ARGUMENTS DBA_PLSQL_OBJECT_SETTINGS USER_PLSQL_OBJECT_SETTINGS
CDB_ERRORS_AE DBA_PROCEDURES USER_PROCEDURES
CDB_OBJECT_SIZE DBA_SOURCE_AE USER_SOURCE_AE
Object Privileges GRANT execute ON <function_name> TO <user_name>;

Privileges to tables and views granted through roles may not be valid within a function. See the section on AUTHID under PROCEDURES.
Related System Privileges
ALTER ANY PROCEDURE CREATE PROCEDURE DROP ANY PROCEDURE
CREATE ANY PROCEDURE DEBUG ANY PROCEDURE EXECUTE ANY PROCEDURE
Special Restrictions Functions called from SQL have special restrictions
  • Stored in database
  • Must own or have EXECUTE privilege
  • When used in SELECT statement - cannot contain DML
  • When used in UPDATE or DELETE - cannot SELECT or perform DML on the same table
 
Functions Without Parameters
Simple Function Creation CREATE OR REPLACE FUNCTION <function_name> RETURN <variable_type> IS
 <variable declarations>
BEGIN
  <code_here>;
END <function_name>;
/
CREATE OR REPLACE FUNCTION simple RETURN VARCHAR2 AUTHID DEFINER IS
BEGIN
  RETURN 'Simple Function';
END simple;
/

desc user_source

SELECT name, type
FROM user_source;

SELECT name, COUNT(*)
FROM user_source
GROUP by name;

SELECT text
FROM user_source
WHERE name = 'SIMPLE'
ORDER BY line;

desc user_object_size

-- very slow
SELECT name, source_size, parsed_size, code_size, error_size
FROM user_object_size;

SELECT simple FROM dual;
Function Without Parameters Used In A SELECT Clause SELECT <function_name>
FROM <table_name>;
-- function creation
CREATE OR REPLACE FUNCTION getosuser RETURN user_users.username%TYPE AUTHID DEFINER IS
 -- explain use of %TYPE
 vOSUser user_users.username%TYPE;
 -- explain INTO and return
BEGIN
  SELECT osuser
  INTO vOSUser
  FROM gv$session
  WHERE sid = (
    SELECT sid
    FROM v$mystat
    WHERE rownum = 1);

  RETURN vOSUser;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'UNK';
END getosuser;
/

-- test getosuser function
SELECT getosuser FROM dual;
Simple Function Used In An INSERT Statement CREATE TABLE my_stuff (
col_values VARCHAR2(10),
insert_by  VARCHAR2(30));

INSERT INTO my_stuff VALUES ('ABCDEFG', getosuser);
INSERT INTO my_stuff VALUES ('ABCDEFG', 'Dan Morgan');
INSERT INTO my_stuff VALUES ('ABCDEFG', getosuser);
INSERT INTO my_stuff VALUES ('ABCDEFG', 'Connor McDonald');
INSERT INTO my_stuff VALUES ('ABCDEFG', getosuser);
COMMIT;

SELECT * FROM my_stuff;
Simple Function Used In A WHERE Clause SELECT * FROM my_stuff
WHERE insert_by = getosuser;
Simple Function Used In A View CREATE OR REPLACE VIEW my_stuff_view AS
SELECT * FROM my_stuff
WHERE insert_by = getosuser;

SELECT * FROM my_stuff;
SELECT * FROM my_stuff_view;
 
Functions With Parameters
Function to determine if a string is a has the format of a valid social security number CREATE OR REPLACE FUNCTION <function_name> (
<parameters> [IN | OUT | IN OUT] [NOCOPY] <data_type>)
RETURN <data_type> [AUTHID <CURRENT_USER | DEFINER>] IS

<constant, exception, and variable declarations>

BEGIN
  <code_here>;
END <function_name>;
/
CREATE OR REPLACE FUNCTION is_socsecno(string_in IN VARCHAR2) RETURN BOOLEAN
AUTHID DEFINER IS
-- validating ###-##-#### format
 incorrect EXCEPTION;
 delim     CHAR(1);
 part1     NUMBER(3,0);
 part2     NUMBER(2,0);
 part3     NUMBER(4,0);
BEGIN
  IF LENGTH(string_in) <> 11 THEN
    RAISE incorrect;
  END IF;

  part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999');

  delim := SUBSTR(string_in,4,1);
  IF delim <> '-' THEN
    RAISE incorrect;
  END IF;

  part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99');

  delim := SUBSTR(string_in,7,1);
  IF delim <> '-' THEN
    RAISE incorrect;
  END IF;

  part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999');

  RETURN TRUE;
EXCEPTION
  WHEN incorrect THEN
    RETURN FALSE;
  WHEN OTHERS THEN
    RETURN FALSE;
END is_socsecno;
/

set serveroutput on

BEGIN
  IF is_socsecno('123-45-6789') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/

BEGIN
  IF is_socsecno('123-A5-6789') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/

BEGIN
  IF is_socsecno('123=45-6789') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/

BEGIN
  IF is_socsecno('123-A5-67890') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
Candy --note IN and AS
CREATE OR REPLACE FUNCTION ssn_candy(str_in IN VARCHAR2) RETURN BOOLEAN AUTHID DEFINER AS
-- validating ###-##-#### format
BEGIN
  IF TRANSLATE(str_in, '0123456789A','AAAAAAAAAAB') = 'AAA-AA-AAAA' THEN
    RETURN TRUE;
  END IF;
  RETURN FALSE;
END ssn_candy;
/

set serveroutput on

BEGIN
  IF ssn_candy('123-45-6789') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/

BEGIN
  IF ssn_candy('123-A5-6789') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/

BEGIN
  IF ssn_candy('123=45-6789') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/

BEGIN
  IF ssn_candy('123-A5-67890') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
Function with OUT parameter CREATE OR REPLACE FUNCTION out_func (outparm OUT VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
BEGIN
  outparm := 'out param';
  RETURN 'return param';
END out_func;
/

set serveroutput on

DECLARE
  retval VARCHAR2(20);
  outval VARCHAR2(20);
BEGIN
  retval := out_func(outval);
  dbms_output.put_line(outval);
  dbms_output.put_line(retval);
END;
/
Function with IN OUT parameter CREATE OR REPLACE FUNCTION inout_func (outparm IN OUT VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
BEGIN
  outparm := 'Coming out';
  RETURN 'return param';
END inout_func;
/

set serveroutput on

DECLARE
  retval VARCHAR2(20);
  ioval  VARCHAR2(20) := 'Going in';
BEGIN
  dbms_output.put_line('In: ' || ioval);
  retval := inout_func(ioval);
  dbms_output.put_line('Out: ' || ioval);
  dbms_output.put_line('Return: ' || retval);
END;
/
Parallel Enabled CREATE OR REPLACE FUNCTION pe_demo RETURN VARCHAR2 PARALLEL_ENABLE AUTHID DEFINER IS
BEGIN
  RETURN 'Success';
END pe_demo;
/

SELECT pe_demo FROM dual;
 
Functions Deterministic
Deterministic functions do not reference tables and always return the same result, based upon input, every time they are called CREATE OR REPLACE FUNCTION <function_name> (
<parameters> [IN | OUT | IN OUT] [NOCOPY] <data_type>)
RETURN <data_type> [AUTHID <CURRENT_USER | DEFINER>]
[DETERMINISTIC] IS
 <constant, exception, and variable declarations>
BEGIN
  <code_here>;
END <function_name>;
/
CREATE OR REPLACE PACKAGE df_demo AUTHID DEFINER IS
 td DATE;

 FUNCTION get_date RETURN DATE;
 FUNCTION get_date_determ RETURN DATE DETERMINISTIC;
END df_demo;
/

CREATE OR REPLACE PACKAGE BODY df_demo IS
--===================================================
 FUNCTION get_date RETURN DATE IS
 BEGIN
   RETURN df_demo.td;
 END get_date;
--===================================================
 FUNCTION get_date_determ RETURN DATE DETERMINISTIC IS
 BEGIN
   RETURN df_demo.td;
 END get_date_determ;
--===================================================
END df_demo;
/

CREATE TABLE t AS
SELECT *
FROM dba_objects_ae;

set timing on

SELECT COUNT(*)
FROM t
WHERE created > TO_DATE('01-JAN-2008','DD-MON-YYYY');

exec df_demo.td := TO_DATE('01-JAN-2008','DD-MON-YYYY');

SELECT COUNT(*)
FROM t
WHERE created > df_demo.get_date;

SELECT COUNT(*)
FROM t
WHERE created > df_demo.get_date_determ;
 
Alter Function
Debug mode CREATE OR REPLACE FUNCTION <function_name>
[COMPILE [DEBUG] [parameter_name = [parameter_value>] REUSE SETTINGS;
CREATE OR REPLACE FUNCTION test (inparm IN NUMBER) RETURN NUMBER AUTHID DEFINER IS
BEGIN
  RETURN inparm;
END test;
/

desc user_plsql_object_settings

col plsql_debug format a15

SELECT name, type, plsql_debug
FROM user_plsql_object_settings;

ALTER FUNCTION test COMPILE DEBUG;
Recompile CREATE OR REPLACE FUNCTION <function_name>
[COMPILE [DEBUG] [parameter_name = [parameter_value>]
[REUSE SETTINGS];
CREATE OR REPLACE FUNCTION test (inparm IN NUMBER) RETURN NUMBER AUTHID DEFINER IS
BEGIN
  RETURN inparm;
END test;
/

ALTER FUNCTION test COMPILE;

SELECT name, type, plsql_debug
FROM user_plsql_object_settings;
 
Drop Function
Drop a function DROP FUNCTION <function_name>;
DROP FUNCTION test;
 
Function Demos
Days Between Function CREATE OR REPLACE FUNCTION date_diff (max_date STRING, min_date STRING)
RETURN PLS_INTEGER AUTHID DEFINER IS
BEGIN
  RETURN TO_DATE(max_date) - TO_DATE(min_date);
EXCEPTION
  WHEN OTHERS THEN
  RETURN NULL;
END date_diff;
/

SELECT date_diff('31-MAR-2004', '20-FEB-2003') FROM dual;

-- alternative version with date rather than strings as the input

CREATE OR REPLACE FUNCTION date_diff (max_date DATE, min_date DATE) RETURN PLS_INTEGER
AUTHID DEFINER IS
BEGIN
 RETURN max_date - min_date;
EXCEPTION
  WHEN OTHERS THEN
  RETURN NULL;
END date_diff;
/

SELECT object_name, date_diff(last_ddl_time, created) FROM user_objects;
Function to determine if the first character of a string is a digit.

Thanks to Colin 't Hart for the demo.
CREATE OR REPLACE FUNCTION starts_with_digit(chr_in IN VARCHAR2) RETURN BOOLEAN
AUTHID DEFINER IS
BEGIN
  RETURN SUBSTR(chr_in, 1, 1) IN ('0','1','2','3','4','5','6','7','8','9');
END starts_with_digit;
/

set serveroutput on

BEGIN
  IF starts_with_digit('ZABCD') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

BEGIN
  IF starts_with_digit('3ABCD') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
Use an anonymous block to test the function set serveroutput on

BEGIN
  IF is_digit('ZABCD') = TRUE THEN
    DBMS_OUTPUT.PUT_LINE('TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('FALSE');
  END IF;
END;
/

Use this technique, replacing the function and the value passed to it to test other functions that return Booleans.
Function to determine if a number is even CREATE OR REPLACE FUNCTION is_even(num_in NUMBER) RETURN BOOLEAN AUTHID DEFINER IS
BEGIN
  IF MOD(num_in, 2) = 0 THEN
    RETURN TRUE;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END is_even;
/
Function to determine if a number is odd CREATE OR REPLACE FUNCTION is_odd(num_in NUMBER) RETURN BOOLEAN AUTHID DEFINER IS
BEGIN
  RETURN MOD(num_in, 2) = 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END is_odd;
/
Function to determine if a string is numeric CREATE OR REPLACE FUNCTION is_number(char_in VARCHAR2) RETURN BOOLEAN AUTHID DEFINER IS
 n NUMBER;
BEGIN
  n := TO_NUMBER(char_in);
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END is_number;
/

DECLARE
 x BOOLEAN;
BEGIN
  IF is_number('ABC') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

DECLARE
 x BOOLEAN;
BEGIN
  IF is_number('123') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
Function to determine if a string is numeric (a variation) CREATE OR REPLACE FUNCTION is_number(char_in IN VARCHAR2) RETURN NUMBER AUTHID DEFINER IS
BEGIN
  FOR x IN 1 .. LENGTH(char_in) LOOP
    -- remove , & .
   
IF SUBSTR(char_in,x,1) in (',' , '.' , ' ') THEN
      RETURN 0;
    END IF;
  END LOOP;

  IF TO_NUMBER(char_in,'9999999') > -1000000 THEN
    RETURN 1 ;
  END IF;
EXCEPTION
  WHEN invalid_number THEN
    RETURN 0;
  WHEN OTHERS THEN
    RETURN 0;
END is_number;
/
Function to convert numbers to hex CREATE OR REPLACE FUNCTION hex(v_num IN BINARY_INTEGER) RETURN VARCHAR2 AUTHID DEFINER IS
 v_tmp7 BINARY_INTEGER; v_tmp6 BINARY_INTEGER;
 v_tmp5 BINARY_INTEGER; v_tmp4 BINARY_INTEGER;
 v_tmp3 BINARY_INTEGER; v_tmp2 BINARY_INTEGER;
 v_tmp1 BINARY_INTEGER; v_tmp0 BINARY_INTEGER;
 v_buf BINARY_INTEGER;
 --============================
 FUNCTION hexchr(v_c in BINARY_INTEGER) RETURN VARCHAR2 AS
 BEGIN
   IF v_c BETWEEN 0 AND 9 THEN
     RETURN to_char(v_c);
   ELSIF v_c= 10 THEN
     RETURN 'A';
   ELSIF v_c=11 THEN
     RETURN 'B';
   ELSIF v_c=12 THEN
     RETURN 'C';
   ELSIF v_c=13 THEN
     RETURN 'D';
   ELSIF v_c=14 THEN
     RETURN 'E';
   ELSIF v_c=15 THEN
     RETURN 'F';
   END IF;
 END hexchr;
 --============================
 FUNCTION div(i IN BINARY_INTEGER, j IN BINARY_INTEGER) RETURN BINARY_INTEGER AS
  v_buf BINARY_INTEGER := i;
  a     BINARY_INTEGER := 0;
 BEGIN
   WHILE v_buf>j LOOP
     a := a + 1;
     v_buf := v_buf - j;
   END LOOP;
   RETURN a;
 END div;
--============================
BEGIN
  v_buf:=v_num;
  v_tmp7:=div(v_buf,268435456); v_buf:=v_buf-268435456*v_tmp7;
  v_tmp6:=div(v_buf,16777216); v_buf:=v_buf-16777216*v_tmp6;
  v_tmp5:=div(v_buf,1048576); v_buf:=v_buf-1048576*v_tmp5;
  v_tmp4:=div(v_buf,65536); v_buf:=v_buf-65536*v_tmp4;
  v_tmp3:=div(v_buf,4096); v_buf:=v_buf-4096*v_tmp3;
  v_tmp2:=div(v_buf,256); v_buf:=v_buf-256*v_tmp2;
  v_tmp1:=div(v_buf,16); v_buf:=v_buf-16*v_tmp1;
  v_tmp0:=v_buf;

  RETURN hexchr(v_tmp7) || hexchr(v_tmp6) || hexchr(v_tmp5) ||
  hexchr(v_tmp4) || hexchr(v_tmp3) || hexchr(v_tmp2) || hexchr(v_tmp1)
  || hexchr(v_tmp0);
END hex;
/
Function to convert various data types to RAW CREATE OR REPLACE FUNCTION display_raw (rawval IN RAW, vType IN VARCHAR2) RETURN VARCHAR2 AUTHID DEFINER IS
 cc CHAR(32);
 cd DATE;
 cnv NVARCHAR2(32);
 cn NUMBER;
 cr ROWID;
 cvc VARCHAR2(32);
BEGIN
  IF (vType = 'NUMBER') THEN
    dbms_stats.convert_raw_value(rawval, cn);
    RETURN TO_CHAR(cn);
  ELSIF (vType = 'VARCHAR2') THEN
    dbms_stats.convert_raw_value(rawval, cvc);
    RETURN TO_CHAR(cvc);
  ELSIF (vType = 'DATE') THEN
    dbms_stats.convert_raw_value(rawval, cd);
    RETURN TO_CHAR(cd);
  ELSIF (vType = 'NVARCHAR2') THEN
    dbms_stats.convert_raw_value(rawval, cnv);
    RETURN TO_CHAR(cnv);
  ELSIF (vType = 'ROWID') THEN
    dbms_stats.convert_raw_value(rawval, cr);
    RETURN TO_CHAR(cnv);
  ELSIF (vType = 'CHAR') THEN
    dbms_stats.convert_raw_value(rawval, cc);
    RETURN TO_CHAR(cc);
  ELSE
    RETURN 'UNKNOWN DATATYPE';
  END IF;
END;
/
Function to determine the difference between times CREATE OR REPLACE FUNCTION tn_time_diff(DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER
AUTHID DEFINER IS
 NDATE_1   NUMBER;
 NDATE_2   NUMBER;
 NSECOND_1 NUMBER(5, 0);
 NSECOND_2 NUMBER(5, 0);
BEGIN
  -- Get Julian date number from first date (DATE_1)
  NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

  -- Get Julian date number from second date (DATE_2)
  NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

  -- Get seconds since midnight from first date (DATE_1)
  NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

  -- Get seconds since midnight from second date (DATE_2)
  NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

  RETURN (((NDATE_2 - NDATE_1)*86400)+(NSECOND_2 - NSECOND_1));
END tn_time_diff;
/
Function converting McKesson software dates CREATE TABLE test(testcol varchar2(7));

INSERT INTO test VALUES ('2003300');
INSERT INTO test VALUES ('2004300');
INSERT INTO test VALUES ('2005300');
COMMIT;

CREATE OR REPLACE FUNCTION makedate(strin IN VARCHAR2) RETURN DATE AUTHID DEFINER IS
 dy CHAR(3);
 yr CHAR(4);
 janone DATE;
BEGIN
  dy := SUBSTR(strin, 5);
  yr := SUBSTR(strin,1,4);
  janone := TO_DATE('01-JAN-' || yr, 'DD-MON-YYYY')+TO_NUMBER(dy)-1;

  RETURN janone;
END makedate;
/

SELECT testcol, makedate(testcol) FROM test;

SELECT TO_DATE('01-JAN-' || SUBSTR(testcol,1,4), 'DD-MON-YYYY') + TO_NUMBER(SUBSTR(testcol, 5)-1)
FROM test;
Function that selects a value from a table based on a single input CREATE OR REPLACE FUNCTION get_customer(deliv_date DATE) RETURN VARCHAR2 AUTHID DEFINER IS
 x airplanes.customer_id%TYPE;
BEGIN
  SELECT customer_id
  INTO x
  FROM airplanes
  WHERE delivered_date BETWEEN deliv_date AND deliv_date + 1;

  RETURN x;
EXCEPTION
  WHEN TOO_MANY_VALUES
    RETURN 'More Than One';
  WHEN OTHERS THEN
    RETURN 'None Found';
END get_customer;
/
Table and Data For IF Statement Function Demo CREATE TABLE discounts (
prodname VARCHAR2(20),
prodprice NUMBER(5),
proddisc NUMBER(2));

INSERT INTO discounts
(prodname, prodprice, proddisc)
VALUES
('Diamond', 1000, 10);

INSERT INTO discounts
(prodname, prodprice, proddisc)
VALUES
('Ruby', 850, 15);

INSERT INTO discounts
(prodname, prodprice, proddisc)
VALUES
('Sapphire', 600, 25);

INSERT INTO discounts
(prodname, prodprice, proddisc)
VALUES
('Emerald', 2000, 20);

INSERT INTO discounts
(prodname, prodprice, proddisc)
VALUES
('Topaz', 400, 30);

COMMIT;
IF Statement Function CREATE OR REPLACE FUNCTION sale_price(pProd VARCHAR2) RETURN PLS_INTEGER AUTHID DEFINER IS
 tabPrice discounts.prodprice%TYPE;
 tabDisc  discounts.proddisc%TYPE;
 i        PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM discounts
  WHERE prodname = pProd;

  IF i <> 0 THEN
    SELECT prodprice, proddisc
    INTO tabPrice, tabDisc
    FROM discounts
    WHERE prodname = pProd;

    RETURN tabPrice - (tabPrice * tabDisc/100);
  ELSE
    RETURN 0;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END sale_price;
/

SELECT sale_price('Diamond') FROM dual;
SELECT sale_price('Ruby') FROM dual;
SELECT sale_price('Topaz') FROM dual;
SELECT sale_price('Emerald') FROM dual;
SELECT sale_price('Zzyzx') FROM dual;
Nested Functions Demo CREATE OR REPLACE FUNCTION nested(some_date DATE) RETURN VARCHAR2 AUTHID DEFINER IS
 yrstr VARCHAR2(4);

-- beginning of nested function in declaration section
FUNCTION turn_around (
  year_string VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  yrstr := TO_CHAR(TO_NUMBER(year_string)*2);
  RETURN yrstr;
END;
-- end of nested function in declaration section

-- beginning of named function

BEGIN
  yrstr := TO_CHAR(some_date, 'YYYY');
  yrstr := turn_around(yrstr);
  RETURN yrstr;
END nested;
/
String Between Demo CREATE OR REPLACE FUNCTION StringBetween (
 teststr VARCHAR2, startpos PLS_INTEGER, endpos INTEGER)
 RETURN VARCHAR2 AUTHID DEFINER IS
BEGIN
  RETURN SUBSTR(teststr, startpos, endpos-startpos-1);
END StringBetween;
/
Function that determines whether a table contains all of the values in a string CREATE TABLE user_domain_map (
user_id   NUMBER(5),
domain_id NUMBER(5));

INSERT INTO user_domain_map VALUES (121, 200);
INSERT INTO user_domain_map VALUES (121, 201);
INSERT INTO user_domain_map VALUES (121, 207);
COMMIT;

CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION contains_all (
useridin user_domain_map.user_id%TYPE, stringin VARCHAR2,
checkint INTEGER) RETURN INTEGER AUTHID DEFINER IS
i         PLS_INTEGER;
my_table  dbms_utility.uncl_array;
stringary InStrTab;
BEGIN
  -- convert stringin of domain ids into a PL/SQL table
  dbms_utility.comma_to_table(stringin, i, my_table);
  -- initialize a collection
  stringary := InStrTab('');
  -- extend the collection to the size of the PL/SQL table
  stringary.EXTEND(my_table.COUNT);

  -- for each element in the PL/SQL table
  FOR j IN 1 .. my_table.COUNT
  LOOP
    -- remove the double-quotes
    my_table(j) := TRANSLATE(my_table(j), 'A"', 'A');
    -- assign it to an element in the array
    stringary(j) := my_table(j);
  END LOOP;

  -- check the count of array elements found in the user_domain_map table
  SELECT COUNT(*)
  INTO i
  FROM zuser_domain_map
  WHERE user_id = useridin
  AND domain_id IN (
    SELECT column_value
    FROM TABLE(CAST(stringary AS InStrTab)));

  -- compare the number found agains the checksum
  IF i >= checkint THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END contains_all;
/

SELECT contains_all(121, '"200","201","207"',3)
FROM dual;

SELECT contains_all(121, '"200","201","206"',3)
FROM dual;
Calculate distances from latitude and longitude CREATE OR REPLACE FUNCTION calc_distance(
 pLat1 NUMBER,
 pLon1 NUMBER,
 pLat2 NUMBER,
 pLon2 NUMBER)
RETURN NUMBER AUTHID DEFINER IS

-- r is the spherical radius of earth in Kilometers
cSpherRad CONSTANT NUMBER := 6367;
-- the spherical radius of earth in miles is 3956
a        NUMBER;
vLat     NUMBER;
vLat1Rad NUMBER;
vLat2Rad NUMBER;
vLon     NUMBER;
vLon1Rad NUMBER;
vLon2Rad NUMBER;

BEGIN
  /*
  Most computers require the arguments of trigonometric functions to be
  expressed in radians. To convert lon1, lat1 and lon2,lat2 from
  degrees,minutes, seconds to radians, first convert them to decimal
  degrees. To convert decimal degrees to radians, multiply the number
  of degrees by pi/180 = 0.017453293 radians/degrees.
  */


  vLat1Rad := pLat1 * 0.017453293;
  vLat2Rad := pLat2 * 0.017453293;
  vLon1Rad := pLon1 * 0.017453293;
  vLon2Rad := pLon2 * 0.017453293;

  vLon := vLon2Rad - vLon1Rad;
  vLat := vLat2Rad - vLat1Rad;

  a := POWER(SIN(vLat/2),2) + COS(vLat1Rad) * COS(vLat2Rad) *
  POWER(SIN(vLon/2),2);

  /*
  The intermediate result c is the great circle distance in radians.
  Inverse trigonometric functions return results expressed in radians.
  To express c in decimal degrees, multiply the number of radians by 180/pi = 57.295780 degrees/radian.
  The great circle distance d will be in the same units as r.
  */


  RETURN ROUND(cSpherRad * 2 * ATAN2(SQRT(a), SQRT(1-a)),1);
EXCEPTION
  WHEN OTHERS THEN
    RETURN 999;
END calc_distance;
/
 
Function Related SQL Statements
Retrieve Function Metadata SELECT object_name, argument_name, position, data_type, data_length
FROM user_arguments
WHERE object_name = <function_name>
ORDER BY object_name;
Retrieve Function Source Code SELECT text
FROM user_source
WHERE name = <function_name>;

Related Topics
Accessible By Clause
Anonymous Blocks
DBMS_METADATA
DBMS_RESULT_CACHE
Exception Handling
Hadoop
Packages
Pipelined Table Functions
PL/SQL Object Settings
PL/SQL_Warnings
Pragma Inline
Procedures

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved