OracleBlog

Consider the situation where you are writing a stored procedure that takes a column name as a variable, and then does some work based on a query that uses that column name. How would you do it?

Let's consider a hypothetical situation. Say you have a table with all your employees. Some of the columns are responsible for their pay. Employees can get paid in different ways, for example: base salary, hourly wage, bonus, dividend, etc. You have made each one of these a separate column in the table. (Note: all these columns are of the same type).

You have a number of stored procedures that access these tables. They all share some things in common, so you have decided to make some common "helper" procedures for all the "master" procedures to use.

Your "helper" procedure would have to take the column name from the "master" procedure, and then perform the common queries and data manipulations for that given column.

1. Dynamic SQL

One way of doing that is with dynamic SQL. Observe:

CREATE OR REPLACE PROCEDURE HelperProc (
in_col_name IN VARCHAR2,
out_val OUT NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE 'SELECT MAX(' || in_col_name || ') FROM EMP' INTO out_val;
END;

SET SERVEROUTPUT ON;

DECLARE
out_val NUMBER;
BEGIN
HelperProc('EMPNO', out_val);
DBMS_OUTPUT.PUT_LINE(out_val);
END;

It works very well:
- It's a single line, no matter how many possible columns are used
- You don't need to know the column names in advance
- You don't need to change it after a DDL change

However, there are drawbacks to Dynamic SQL. Among others, there is extra parsing and (most seriously) vulnerabilities to SQL injection. I won't go into more detail on Dynamic SQL, but I promise to blog on it soon.

2. Static SQL

The obvious recourse is to use something like IF or CASE or (my favourite) DECODE.

CREATE OR REPLACE PROCEDURE HelperProc (
in_col_name IN VARCHAR2,
out_val OUT NUMBER)
IS
BEGIN
SELECT MAX(DECODE(in_col_name, 'EMPNO', EMPNO, 'MGR', MGR, 'SAL', SAL, 'COMM', COMM, 'DEPTNO', DEPTNO, NULL))
INTO out_val
FROM EMP;
END;

Essentially this is like looking at the column name, and doing something different depending on what it is. That's practically all you can do with static SQL, by definition. This almost defeats the purpose of having a common "helper" procedure, but there are still two reasons it would still make sense:
1. Modularity (and abstraction) is generally a good thing
2. Any extra work done on out_val will justify the "helper" procedure.

3. Revised Data Model

There is an even more important consideration. Whenever you are struggling to do something clever, take a step back and consider your data model. It could be ill-suited for your needs.

In this case, what could we do?

We could break this information into separate tables. For example: EmpBaseSalary, EmpHoury, EmpBonus, etc. Then we could join them to the Emp table by employee id. Of course, that just makes the table name variable instead of the column, so that doesn't really help us, so instead:

We could elongate the employee table, making something like this:

ID;...;SALARY;HOURLY;BONUS;DIVIDEND
1;...;60;NULL;NULL;NULL
2;...;100;NULL;NULL;20

into a separate table mapped by ID:

ID;VALUE;TYPE
1;60;'SALARY'
2;100;'SALARY'
2;20;'DIVIDEND'

That would effectively move the "column name" into the WHERE clause. That would certainly make the task easier. That is sort of a "reverse pivot."

Also, that opens the door to add extra columns for effective start and end dates. We could even do this with views if we wanted to leave the data model alone.

http://thinkoracle.blogspot.com/2005/07/use-views.html
http://thinkoracle.blogspot.com/2005/09/pivot-and-crosstab-queries.html

That is just one example, but it shows how you need to take a step back and consider the real-world application.

Here is a link to the Dizwell discussion forum where we discussed this, and where most of this came from:
http://www.phpbbserver.com/phpbb/viewtopic.php?t=450&mforum=dizwellforum