- Category: Oracle PL/SQL
I need to refer to how to create a function in Oracle PL/SQL and sites on the net just attempt to overcomplicate everything and have forgotten how it is to be new to Oracle. I need a function in it's simplest form and if I want to torture my successors, I'll complicate the function myself.
Functions are supposed to return a single value, which is all I need in this case. In my example, I need to submit an Active Directory (AD) username and receive a student ID number instead, all with the aim to improve performance on some SSRS queries which accept either an ID number or an AD name as user parameters.
CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] RETURN return_datatype IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [function_name];
Example: My example accepts an Active Directory (AD) username and returns the student ID:
CREATE OR REPLACE FUNCTION fn_get_sref_from_adname (ad_username IN varchar2) RETURN number IS student_no number; cursor c1 is select student_reference from student_accounts_table where student_ad_account = trim(lower(ad_username)); BEGIN open c1; fetch c1 into student_no; if c1%notfound then student_no := 0; end if; close c1; RETURN student_no; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END fn_get_sref_from_adname;
To run this:
SELECT fn_get_sref_from_adname('f2574687') from dual where rownum<=1; -- I use dual as the table but this can be any table. -- I specify rownum because it was returning rows with just the same value.
Accessible by other users?
CREATE PUBLIC SYNONYM my_function FOR base_table_owner.my_function; GRANT EXECUTE ON my_function TO another_user;