Oracle Functions Pl/SQL SQL

Search the Reference Library pages:

Oracle Functions

Version 11.1

General

Note: This page does not include Pipelined Table Functions. They are linked, along with related topics at bottom of the page.

Related Data Dictionary Objects

source$
error$

DBA	ALL	USER
dba_arguments	all_arguments	user_arguments
dba_errors	all_errors	user_errors
dba_object_size	all_object_size	user_object_size
dba_procedures	all_procedures	user_procedures
dba_source	all_source	user_source

System Privileges Related To Functions

alter any procedure
create any procedure
create procedure
debug any procedure
drop any procedure
execute any procedure

Object Privileges

GRANT execute ON <function_name> TO <schema_name>;

Privileges to tables and views granted through roles may not be valid within a function. See the section on AUTHID under PROCEDURES.

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 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 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 gv$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 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 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 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 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 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>)
DETERMINISTIC
RETURN <data_type> [AUTHID <CURRENT_USER | DEFINER>] IS

<constant, exception, and variable declarations>

BEGIN
<code_here>;
END <function_name>;
/

CREATE OR REPLACE PACKAGE df_demo 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;

set timing on

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

exec df_demo.td := TO_DATE('01-JUL-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 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 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 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 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

CREATE OR REPLACE FUNCTION is_digit (chr_in VARCHAR2) RETURN BOOLEAN
IS

BEGIN
IF (SUBSTR(chr_in, 1, 1) IN ('0','1','2','3','4','5','6','7','8','9')) THEN
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END is_digit;
/

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 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 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 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 VARCHAR2) RETURN NUMBER 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 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;
--============================
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;
--============================
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 determine the difference between times

CREATE OR REPLACE FUNCTION tn_time_diff(DATE_1 IN DATE, DATE_2 IN DATE)
RETURN NUMBER 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 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
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 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 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 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 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
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

Anonymous Blocks

DBMS_METADATA

Packages

Pipelined Table Functions

Pragma Inline

Procedures

-----