BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







  Oracle Tips by Burleson

PL/SQL Functions

A function is a PL/SQL named block that returns a value.  It is commonly used to convert or assign values. Whereas a procedure is executed, a function is called, as in the example below:

  n_area := calc_area(10,20);

In the code fragment above, the area is calculated by a procedure named get_area and a function names calc_area.  The procedure was passed three values and it copied the calculated area into the n_area variable when the procedure exited.  The next line uses a function that is passed two values, calculates the area, and returns that value, which is assigned to the n_area variable.  Notice that the function is used directly in the assignment operation.  A function is defined in the format below.

create or replace function <Name>
  (<variable list>) return <datatype>
as (or is) 

  local variable declaration
  code section

This is similar to the procedure definition except that it uses the return definition.  A function returns a datatype, not a variable. 

SQL> create or replace function calc_area
  2    (n_length in number,
  3     n_width  in number)
  4    return number
  5  as
  6  begin
  7    return n_length*n_width;
  8  end;/
Function created.

Above the function calc_area is defined so that it returns a number.  In the function body there must be a RETURN statement defining what is returned.  In the example above, the RETURN statement is in line 4.

A function name can be very descriptive with up to 32 characters and the function is always created in the schema of the user that creates the function.  As with the procedure, a function can declare any number of values in the declaration section, limited only by the usability of the function.  However, unlike a procedure, a function can not be passed variables in mode OUT or INOUT.  A function can only return a datatype.  If a function is defined with an OUT or INOUT variable, the function will compile but will raise an exception when executed.

ORA-06572: Function <name> has out arguments

An example used earlier in the book converted a temperature in Fahrenheit to Celsius.  This is a perfect example of a function, which takes a value and returns a number.

SQL> create or replace function f2c
  2    (n_faren IN number)
  3    return number
  4  as
  5    n_cel number := 0;
  6  begin
  7    n_cel := (5/9)*(n_faren -32);
  8    return n_cel;
  9  end;
 10  /

Function created.

The function f2c takes a number in mode IN, calculates the values in Celsius and returns the Celsius value.  If the function does not compile, SQL*Plus will display the errors with the “show errors” command.

SQL> create or replace function broken
  2    (n_faren IN number)
  3  as
  4    n_cel number := 0;
  5  begin
  6    n_cel := (5/9)*(n_faren -32);
  7    return n_cel;
  8  end;
  9  / 

Warning: Function created with compilation errors.

SQL> show errors

-------- ------------------------------------------------3/1      PLS-00103: Encountered the symbol "AS" when expecting one of the following: return

5/1      PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:  end function package

pragma private procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor

The function broken will not compile.  Reading PL/SQL errors  is a little different than reading errors in other programming languages.  In PL/SQL, we focus with the top error.  This is because most of the following errors will be caused by the first error.  In this example, the first error states that the compiler found the AS key word when it expected something else, namely the RETURN clause.  A look at the code shows that the function definition does not define a return datatype.   

As with procedures, to find the function in the database, we query the user_objects view, as shown below.

SQL> set pages 999
SQL> column object_name format a30
SQL> select
  2    object_name,
  3    object_type,
  4    status
  5  from user_objects

OBJECT_NAME                    OBJECT_TYPE        STATUS
------------------------------ ------------------ -------AUTHOR                         TABLE              VALID
BOOK                           TABLE              VALID
BOOK_AUTHOR                    TABLE              VALID
BROKEN                         FUNCTION           INVALID
CALC_AREA                      FUNCTION           VALID
EMP                            TABLE              VALID
F2C                            FUNCTION           VALID
GET_AREA                       PROCEDURE          VALID
GET_AREA2                      PROCEDURE          VALID
JOB                            TABLE              VALID
NUM_CHECK                      PROCEDURE          VALID
PUBLISHER                      TABLE              VALID
SALES                          TABLE              VALID
STORE                          TABLE              VALID

14 rows selected.

To remove the function broken from the database, we can drop it with the “drop function” command.

SQL> drop function broken;

Function dropped.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 


Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.