from http://psoug.org/reference/functions.html
General | |||||||||||||||||||
Note: This page does not include Pipelined Table Functions they are linked at page bottom | |||||||||||||||||||
Related Data Dictionary Objects | source$ error$
| ||||||||||||||||||
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
| ||||||||||||||||||
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 BOOLEANAS -- 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>; |