Introduction

After contributing few articles on Microsoft SQL Server, I thought it would be a good time to do something Oracle specific. This first Oracle article of mine describes some basic tasks but extends to more advanced features.

So the purpose of this article is two-fold: To create a basis for a toolkit collection for developers and administrators to extend and use and also to describe some other techniques how Oracle functionality can be extended. The main areas covered are:

• Creation of a package
• Defining constants and publishing them
• Creating deterministic functions
• Using AUTHID
• Creating a function that returns a result set
• Creating a pipelined function

Creating the Schema for Utilities

The first thing is to create a new schema for the utility package. I decided to create a new schema because of few reasons:

• Security: The owner of the utilities can be granted such permissions which wouldn’t normally be granted to end-users.
• Administration: When all the utilities are in a single, separated schema, administrative tasks such as schema compilations, exports etc. become easier.

Creating the user is basically simple. In your installation, just check that the default and temporary tablespaces are correct. However, since this new user will be granted privileges to sys user’s tables, SYSDBA privileges are required.

CREATE USER ToolkitUser 
   IDENTIFIED BY Hammer
   DEFAULT TABLESPACE USERS
   TEMPORARY TABLESPACE TEMP;
/
 
GRANT CREATE SESSION TO ToolkitUser;
/

GRANT RESOURCE TO ToolkitUser;
/

GRANT SELECT ON sys.v_$session TO ToolkitUser;
/ 

Creating the Package

The downloadable scripts include the package header and body in a single file along with the types needed in this example. For convenience reasons, a public synonym is created for the package at the end of the script. The public synonym guarantees that the package can be called without specifying the schema as long as necessary privileges are granted to the caller.

The package contains several simple examples of functions just to get to know the basics. These functions won’t be discussed in more detail but few things could be pointed out.

• Even though some of the functions are just short ‘aliases’ to native Oracle functions, I wanted to create a bit more intuitive versions.
• Most of the functions are defined as DETERMINISTIC meaning that the function always produces the same result if the input values are the same. So these functions can be used in queries, but also in indexes.

The simple functions included are listed below. I decided to create mostly date-related functions since they often seem to cause headache.

• Day: Returns the number of the day for the given date.
• Month: Returns the number of the month for the given date.
• Year: Returns the number of the month for the given date.
• FirstDayOfMonth: Returns the first day of month for the given date.
• FirstDayOfYear: Returns the first day of year for the given date.
• LastDayOfMonth: Returns the last day of month for the given date.
• DaysInMonth: Returns the number of days in month for the given date.
• DaysLeftInYear: Returns the number of days left in year for the given date.
• WorkingDaysLeftInYear: Returns the number of working days left in the year for the given date.

Both Holiday and Toolkit-package scripts are meant to be run under ToolkitUser so after creating the user, get a new connection using ToolkitUser‘s credentials and execute the scripts. If you use SQL*Plus (or equivalent), add the necessary slashes (/) at the end of package header and body.

Publishing Constants

Constants can be added to the package header and then be used throughout the package. However, a constant cannot be referenced in a SQL statement from outside the package. In the Toolkit-package, I’ve defined a constant for PI and I could use it inside a function or a stored procedure, but in order to use it I have to create a small wrapper function for the constant. In this example, the PI_CONSTANT is published using the PI-function.

The constant declaration is:

PI_CONSTANT CONSTANT NUMBER := 3.14159265358979323846264338327950288419;

and the publishing function:

FUNCTION Pi RETURN NUMBER DETERMINISTIC IS
BEGIN
   RETURN ( PI_CONSTANT );
END;

Now I can use the value of the PI to calculate for example the area of a 30 centimeter pizza:

SELECT Toolkit.PI * POWER(30/2, 2) FROM Dual;

Security, AUTHID DEFINER Example

Since the package can be defined to run either using callers or definers privileges, it can be used to encapsulate operations which otherwise wouldn’t be allowed to the caller. This is the same situation as with stand-alone procedures and functions.

Let’s create a small function that returns 1 if a transaction is active for the caller. In order to do this, we’ll need access to v$session to get the address of the transaction state object (if any). Earlier in this article, when the user was created, it was granted SELECT access to sys.v_$session. This is the actual dynamic view containing the information about sessions. Now, since the user owning the package (ToolkitUser) has access to the view, we can define the function in the package as:

FUNCTION TransactionActive RETURN NUMBER IS 
   nActive NUMBER;
BEGIN
   SELECT DECODE( vs.TADDR, NULL, 0, 1)
   INTO nActive
   FROM v$session vs 
   WHERE vs.SID = SYS_CONTEXT(‘USERENV‘, ‘SID‘);
 
   RETURN ( nActive );
END;

If the TADDR is NULL, then there’s currently no transaction. In the function, we need to know from what session we’re querying the state so we need to get the SID for the caller. This is done using SYS_CONTEXT –function.

The package itself was created using definers rights:

CREATE OR REPLACE PACKAGE Toolkit
AUTHID DEFINER
 ...

Now the caller doesn’t have to have access to sys.v_$session, just to the Toolkit –package in order to know if there’s an active transaction for the session. To get the result, just try:

SELECT Toolkit.TransactionActive FROM Dual;

Function Returning a Result Set

Functions can also be used to return result sets. To test this, let’s create a function that returns the numbers between given limits. First, we have to define a type for a single row and then a table type containing the data. Both of these statements are included in the package script.

---------------------------------------------------------------------
-- Type and table for number values
---------------------------------------------------------------------
CREATE OR REPLACE TYPE numberRow AS OBJECT ( 
   value NUMBER 
);
CREATE OR REPLACE TYPE numberTable IS TABLE OF numberRow;

After these are defined, we can create the actual function in the package:

FUNCTION NumbersBetween(startValue NUMBER, endValue NUMBER ) RETURN numberTable IS
   tabNumbers numberTable := numberTable();
   nCounter NUMBER;
BEGIN
   FOR nCounter IN startValue..endValue LOOP
      tabNumbers.EXTEND;
      tabNumbers(tabNumbers.COUNT) := numberRow(nCounter);
   END LOOP;
 
   RETURN tabNumbers;
END;

The function uses a FOR loop to generate the numbers. In every iteration, the number table is extended by 1 element and a new row containing the number is added to the table. When the function exits, the table of numbers is returned to the caller.

In order to use the function, TABLE statement has to be used. So getting for example numbers between 1 and 5, the statement would be:

SELECT * FROM TABLE( Toolkit.NumbersBetween(1, 5));

And the result is:

VALUE
----------
1
2
3
4
5

Creating a Pipelined Function 

Pipelined function also returns result sets much like we saw in the previous example. The difference is that a pipelined function doesn’t return the whole set at the end of the execution of the function. In a pipelined function,PIPE ROW statement is used to send the result to the caller immediately. The concept is quite similar to yield return structure in C#.

Let’s create the same NumbersBetween –function, but this time pipelined. The package has a table type definition which is used as the return type for the pipelined function. The type is named numberTable2 so that it won’t get mixed with the previously defined type.

TYPE numberTable2 IS TABLE OF NUMBER;

Now the function looks like:

FUNCTION NumbersBetweenPiped(startValue NUMBER, endValue NUMBER ) RETURN numberTable2 
   PIPELINED IS
   nCounter NUMBER;
BEGIN
   FOR nCounter IN startValue..endValue LOOP
      PIPE ROW ( nCounter );
   END LOOP;
END;

The idea is the same as before, but this time we’re not gathering the result to a single set and returning it. Instead, each value is returned separately at the PIPE ROW statement. Using the function looks exactly the same as before:

SELECT * FROM TABLE( Toolkit.NumbersBetweenPiped(1, 5) );

One big advantage of using pipelined functions is that the result set doesn’t have to be stored until it‘s returned because the results are returned to the caller as they ready. This will have a positive memory impact and also may improve performance for example because parallel operations can be done earlier.

Other Examples of Pipelined Functions

To demonstrate few more examples for functions returning result sets, I’ve included the following (pipelined) functions to the package. 

DatesUntil 

The function is much like the NumbersBetween –function but operates on dates. The parameters are in different order, first the end date and then the start date. This is because the start date is optional. So, if the start date is not defined in the call, SYSDATE is used. The function implementation is:

FUNCTION DatesUntil(endDate DATE, startDate DATE DEFAULT SYSDATE ) RETURN dateTable 
   PIPELINED IS
   dCounter DATE := TRUNC( startDate );
BEGIN
   WHILE TRUNC( endDate ) >= dCounter LOOP
      PIPE ROW( dCounter );
      dCounter := dCounter + 1;
   END LOOP;
END;

Getting for example this day and the next 5 days would look like:

SELECT * FROM TABLE( Toolkit.DatesUntil( SYSDATE + 5 ) );

And the results:

COLUMN_VAL
----------
13.03.2011
14.03.2011
15.03.2011
16.03.2011
17.03.2011
18.03.2011

WorkingDaysLeftInYear

The Holiday -table is created as follows (note that in the scripts the Holiday -table is created before the package in order to prevent compilation errors). 

CREATE TABLE ToolkitUser.Holiday (
   Holiday     DATE          NOT NULL PRIMARY KEY,
   Description VARCHAR2(100)
);
/

CREATE PUBLIC SYNONYM Holiday FOR ToolkitUser.Holiday;
/ 

Now, we can use the DatesUntil –function to populate the Holiday-table. Let’s add some weekends to holidays.

INSERT INTO ToolkitUser.Holiday (Holiday, Description)
SELECT days.Column_Value,
       TO_CHAR(days.Column_Value, ‘DAY‘ )
FROM  TABLE( Toolkit.DatesUntil( SYSDATE + 100)) days
WHERE TO_CHAR(days.Column_Value, ‘DY‘, ‘NLS_DATE_LANGUAGE=AMERICAN‘) IN (‘SAT‘,‘SUN‘);

COMMIT; 

Now, we can select the amount of working days left this year (2011) excluding weekends:

SELECT Toolkit.WorkingDaysLeftInYear FROM Dual;

StringSplit 

One more example is a splitter function which receives a string and a delimiter for values. It breaks the stringinto parts based on the delimiter and returns each element. The functionality is much like .NET Framework‘sString.Split method. The implementation:

FUNCTION StringSplit(string VARCHAR2, delimiter VARCHAR2 DEFAULT ‘;‘) RETURN stringTable
   PIPELINED IS
   nStartPosition NUMBER := 1;
BEGIN
   FOR endPosition IN (SELECT Column_Value 
                       FROM TABLE( StringOccurences( string, delimiter ))) LOOP
      PIPE ROW( SUBSTR( string, nStartPosition, endposition.Column_Value-nStartPosition));
      nStartPosition := endposition.Column_Value + 1;
   END LOOP;
   PIPE ROW( SUBSTR( string, nStartPosition ));
END;

This function uses another pipelined function, StringOccurences, which returns all the indexes from the originalstring where the delimiter is found and then based on those results, this function splits the string. Calling the function:

SELECT NVL(Column_Value, ’— NULL –’) AS Result
FROM TABLE(Toolkit.StringSplit( ’;Sunday;Afternoon;’, ’;’));

Would return:

RESULT
-------------
- NULL -
Sunday
Afternoon
- NULL –

Final Words

Hopefully this article has demonstrated some techniques to create packaged functions in Oracle environment. I’d be grateful if you would have the extra time for comments and votes. Thank you.

History

• March 13, 2011: Article created.
• April 1, 2012: Slight modification + examples added to scripts.