Thursday, April 24, 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

  • 301 Redirect using htaccess file

    • Tue 15-Apr-14
      Further suggestion:
      RewriteRule ^(.*/)?assets/s 2dmain.html\?/( .*/)? $2 [R=301,L]
      Webmaster  
    • Thu 10-Apr-14
      Playing with some RegEx testers
      RewriteRule ^(.*\?/)?(?:$|( .+?)(?:(\.[^.]$ )|$)) $2 [R=301,L] ...
      Webmaster  
    • Wed 09-Apr-14
      I tried to redirect links from my old site to my new site, based on what I read in this thread. The old ...
      pelle
  • Joes Revolver Map (JRM)

    • Tue 22-Apr-14
      Hi Manfred, Some downloaders have reported that the version of this module intended for Joomla 2.5.x ...
      Webmaster  
    • Sun 20-Apr-14
      Hello, is it possible to use this module also in joomla 3.2? Thanks Manfred
      Manfred V.