What?
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.

How?
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.

Syntax:

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.

Additional:
Accessible by other users?

CREATE PUBLIC SYNONYM my_function FOR base_table_owner.my_function;
GRANT EXECUTE ON my_function TO another_user;

< Prev		Next >