PL/SQL and Object-Oriented Programming

A little known fact about PL/SQL is that Oracle has been introducing ever-increasing support for Object-Oriented Programming (OOP) ever since version 8. I have some examples below that will help illustrate some of the OOP concepts. (Note: The following examples are run using Oracle 10g, you'll have to try for yourself to see if they work with a different version.)

To get started, lets review some terminology and I'm going to assume you are familiar with Java or C++. Most terms are the same, for instance in Java class methods are still methods in PL/SQL. However, class variables or fields are called attributes in pl/sql. Enough with the talking, time for code.

1. CREATE OR REPLACE TYPE user_object AS OBJECT  
2. (  
3. id number,  
4. first_name varchar2(250),  -- attribute  
5. last_name varchar2(250),  
6. email_address varchar2(1000),  
7. -- constructor specification  
8. CONSTRUCTOR FUNCTION user_object  
9. (  
10. id number,  
11. first_name varchar2,  
12. last_name varchar2,  
13. email_address varchar2)  
14. RETURN SELF AS RESULT,  
15. -- member procedure  
16. MEMBER PROCEDURE set_first_name  
17. (first_name varchar2),  
18. -- member function  
19. MEMBER FUNCTION get_first_name  
20. RETURN varchar2)  
21. INSTANTIABLE NOT FINAL;  
22.   
23.   
24. CREATE OR REPLACE TYPE BODY user_object AS  
25. CONSTRUCTOR FUNCTION user_object  
26. (  
27. id number,  
28. first_name varchar2,  
29. last_name varchar2,  
30. email_address varchar2)  
31. RETURN SELF AS RESULT IS  
32. BEGIN  
33.   self.id := id;  
34.   self.first_name := first_name;  
35.   self.last_name := last_name;  
36.   self.email_address := email_address;  
37.   RETURN;  
38. END user_object;  
39.   
40. MEMBER PROCEDURE set_first_name (first_name varchar2)  
41. is  
42. BEGIN  
43.   self.first_name := first_name;  
44. END set_first_name;  
45.   
46. MEMBER FUNCTION get_first_name   
47. RETURN varchar2  
48. IS  
49. BEGIN  
50.   RETURN self.first_name;  
51. END get_first_name;  
52.   
53. END;  

Okay, so what are we looking at here? We're looking at an OOP representation of a data structure. Thank you, Captain Obvious. The top statement is the object specification, notice I've specified 4 attributes, a constructor and two member methods. The constructor takes 4 parameters, one for each attribute. The member methods "set" and "get" the first name attribute.

So your next question, wow, this is great and all, but how can this be useful to me. The answer is simple, lets say I have a function that I need to return several pieces of information. You can then declare your function to return an object type.

This is a huge subject, however if there are some specifics that you are wondering about, just leave a comment and I'll try to answer them.

PL/SQL - Using a function as a table

Occasionally, there is a need to output the data of a function in such a way it can be used as a table. In a recent project, we were wrapped the output of a "c" external procedure using this feature. For instance:

1. select * from some_func();  

The syntax there isn't exactly correct, but that is the jist of what we want. To accomplish the following, you need two things. A user defined type of type table and a pipelined function. Below is a very simple example:

1. create type num_as_table as table of number;  
2.   
3. create or replace function work_as_table  
4. return num_as_table pipelined  
5. is  
6. begin  
7.   pipe row(1);  
8.   return;  
9. end;  

Now the above function, while not very interesting does demonstrate the point. I created a new type called, "num_as_table" which basically represents a number. I then created a pipelined function that simply pipes the number 1. Now I can simply query my function using the following select statement:

1. select * from table(work_as_table());  

As we would expect from that snippet, the output will be one. Obviously, this is a very powerful tool as you can do some heavy duty data manipulations then pipe it out as if it were a table. (Note: I have only done extensive testing of this on Oracle 10g, however it will work on 9i as well.)

PL/SQL - Constants Package

Occasionally, there is a need in PL/SQL to have a package of constants. In one such project I had, there were some drop down values that we stored in the database and we allowed the users to change the text. We had to write some logic based on the drop down value, but couldn't count on the text remaining the same. So I wrote a constants package:

create or replace package CONSTANTS
as
TYPE constants is table of NUMBER(38) INDEX BY VARCHAR2(100);
enum constants;
enum_size = 4;
function getContstant(in_val varchar2) return number;
end;

create or replace package body CONSTANTS
as
PROCEDURE load_data IS
BEGIN
  enum('CONST1') := 50;
  enum('CONST2') := 51;
  enum('CONST3') := 52;
  enum('CONST4') := 53;
end load_data;

function getConstant(in_val varchar2) return number
is
begin
  if enum.COUNT <> enum_size THEN
    load_data();
  end if;
  return enum(upper(in_val));
  EXCEPTION WHEN OTHERS THEN RETURN -1;
end getConstant;
end;

-- usage
select * from some_table where constant_id = CONSTANTS.getConstant('const1');

You may ask and rightfully so, why not just create a package with a bunch of variables that represent that value? Good question. The key to the package is the load_data function. It can be modified to support any implementation of the way you save your constants. In the example above, I hard coded them in the package, but I could have just as easily read them from the database to avoid my constants table from getting a lot of database hits. Hopefully, you'll be able to use the above to your advantage. (Note: The following has only been compiled and tested on Oracle 10g.)

JPA - Column annotation tip

Being an Oracle guy, I always have enjoyed getting into the guts of the SQL language. Recently I have been using JPA quite a bit recently and was frustrated by a particular problem. I needed to include a column that was generated off of other columns. For instance:

1. SELECT a, b, a+b FROM uninteresting_table;

In JPA-land, I started by writing my own fetch query and added a dummy field to the database table to accomplish this. Class, let's say this together, "This sucks.". However, I did perform a little bit of JPA trickery to get what I want, with the custom fetch query and no dummy data field. In your persistent bean you'll simply annotate your column as follows:

1. // Java code above....




3. @Column(name="a+b", insertable=false, updatable=false)


4. Long myNewColumn;




6. //Java code below....

In the example above, you'll see I just injected my sql I want as that column name and the rest JPA handles for you. Other examples include:

1. @Column(name="myFunc(a)", insertable=false, updatable=false)


2. String functionResult;




4. @Column(name="select count(*) from other_table where id = a", insertable=false, updateable = false)


5. String queryResult;

Spring - Create bean of type Class

I had a need recently to create a bean using Spring of type Class (java.lang.Class). I also wanted to be able to specify the actual class. So for instance I wanted a bean called "productClass" and wanted to pass in my class name "com.example.product.Product" and let Spring instantiate the bean for me. To accomplish this, I created the bean in the context xml file using the following:

1. <bean id="productClass" class="java.lang.Class"  
2. factory-method="forName">  
3. <constructor-arg value="com.example.product.Product"/>  
4. </bean>  

This worked great for my purposes, I received my com.example.product.Product Class object.