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:
copyraw
CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]

    RETURN return_datatype

IS | AS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [function_name];
  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:
copyraw
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;
  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:
copyraw
SELECT fn_get_sref_from_adname('f2574687') from dual where rownum
  1.  SELECT fn_get_sref_from_adname('f2574687') from dual where rownum 

Additional:
Accessible by other users?
copyraw
CREATE PUBLIC SYNONYM my_function FOR base_table_owner.my_function;
GRANT EXECUTE ON my_function TO another_user;
  1.  CREATE PUBLIC SYNONYM my_function FOR base_table_owner.my_function; 
  2.  GRANT EXECUTE ON my_function TO another_user; 
Category: Oracle PL/SQL :: Article: 409

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.