Wednesday, February 25, 2009

Use Oracle Table Function to Encapsulate and Modularize your Data Layer

I finally ran into a situation where I wanted the functionality of a Table Function so I was forced to learn the PL/SQL way to do this. In this case, the benefits of accepting parameters made the Table Function a clear winner. To start off you need a table:

CREATE TABLE cool_people (
cp_id NUMBER(8,0),
first_name VARCHAR2(30),
last_name VARCHAR2(30))

Next you need a new TYPE that describes a single “row” of your Table Function’s output. In my contrived example, I’m only returning a formatted name:

create or replace TYPE PEOPLE_OBJ AS OBJECT(

PERSON_NAME VARCHAR(100));

Now that we have created the new type of OBJECT, we can declare a TABLE of that new type:

create or replace TYPE PEOPLE_TABLE AS TABLE OF PEOPLE_OBJ;

Now we can create our function that accepts a parameter of CHAR:

CREATE OR REPLACE FUNCTION
TFN_GET_COOL_PEOPLE (formatName CHAR)
RETURN PEOPLE_TABLE PIPELINED
IS
fName VARCHAR(30);
lName VARCHAR(30);
p1 PEOPLE_OBJ := PEOPLE_OBJ(NULL);
CURSOR C1 IS
SELECT FIRST_NAME,
LAST_NAME
FROM COOL_PEOPLE;

BEGIN
OPEN C1;
LOOP
FETCH C1
INTO fName,
lName;

EXIT
WHEN C1%NOTFOUND;
IF formatName = 'Y' THEN
p1.PERSON_NAME := lName
|| ', '
|| fName;
ELSE
p1.PERSON_NAME := fName
|| ' '
|| lName;
END IF;
PIPE ROW (p1);
END LOOP;
RETURN;
END TFN_GET_COOL_PEOPLE;

You can then use the function in a FROM clause:


SELECT * FROM TABLE(TFN_GET_COOL_PEOPLE('N'));

Monday, February 23, 2009

Private stored procedures in an Oracle package

I never knew you could write a procedure in a package that isn’t declared in the package declaration.  It’s kind of like a private function/subroutine since it can’t be called by anything outside of the package…which is how I discovered this “feature”.  I was trying to call an “undeclared” procedure from a different package.  I had already written another procedure that called it from its own package, so I was mighty confused about the error I was getting: “MY_PROC must be declared” but now it makes perfect sense. 

One other discovery I made is that you can pass NULL as a parameter to another procedure instead of declaring a null valued variable.