Tuesday, September 30, 2014
   
Text Size
Login

Basic Oracle Function Structure

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:

  1.  CREATE [OR REPLACE] FUNCTION function_name 
  2.      [ (parameter [,parameter]) ] 
  3.   
  4.      RETURN return_datatype 
  5.   
  6.  IS | AS 
  7.      [declaration_section] 
  8.   
  9.  BEGIN 
  10.      executable_section 
  11.   
  12.  [EXCEPTION 
  13.      exception_section] 
  14.   
  15.  END [function_name]; 

Example: My example accepts an Active Directory (AD) username and returns the student ID:

  1.  CREATE OR REPLACE FUNCTION fn_get_sref_from_adname 
  2.      (ad_username IN varchar2) 
  3.      RETURN number 
  4.  IS 
  5.      student_no number; 
  6.   
  7.      cursor c1 is 
  8.      select student_reference 
  9.        from student_accounts_table 
  10.        where student_ad_account = trim(lower(ad_username)); 
  11.  BEGIN 
  12.      open c1; 
  13.      fetch c1 into student_no; 
  14.   
  15.      if c1%notfound then 
  16.          student_no := 0; 
  17.      end if; 
  18.   
  19.      close c1; 
  20.   
  21.  RETURN student_no; 
  22.   
  23.  EXCEPTION 
  24.  WHEN OTHERS THEN 
  25.        raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
  26.   
  27.  END fn_get_sref_from_adname; 

To run this:

  1.  SELECT fn_get_sref_from_adname('f2574687') from dual where rownum<=1; 
  2.   
  3.  -- I use dual as the table but this can be any table. 
  4.  -- I specify rownum because it was returning rows with just the same value. 

Additional:
Accessible by other users?

  1.  CREATE PUBLIC SYNONYM my_function FOR base_table_owner.my_function; 
  2.  GRANT EXECUTE ON my_function TO another_user; 
Add Comment

Name:

Email:

Website:

Message:


Latest Posts