Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Note: This page does not include 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;
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;
/
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;
/
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');
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-2018', '20-FEB-2017') 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;
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;
/
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
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.
*/
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>;
