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'));

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.