CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [208 users online]    © 2010 psoug.org