Oracle dynamic PL/SQL procedures Oracle Database Tips by Donald Burleson

This Oracle forum thread discusses a novel approach to creating a stored procedure "on-the-fly", based on changing database row values:

---

I have 5 stored procedures inside a package. There is a Visual Basic dot-net (VB .net) program that creates one of these stored procedures whenever some values in a table changes. Basically it's like a dynamic stored procedure where some formulas that are stored in a table makeup the logic for the stored procedure.

Basically the stored procedure is a huge case statement in the select statement. When the value that I am selecting is a specific value I apply a specific formula to it. ie. If a location is 'Erie' I use the formula defined to 'Erie' in a table. This formula references column names in it, so that when the text is put into the stored procedure it makes sense.

In my case I have a branch table and a formula table. The branch table is below, and it basically branches different records to different formulas based on some criteria. The formula table is just some calcs on the columns. What is really cool, is in some forumals, I have subqueryies querying the same table to get some data back to do a formula on.

City | Formula | Another Formula ID
-----------------------------------------------------------
Erie | When TEMP >= 60 Then | 0
Erie | When TEMP < 60 Then | 1

So TEMP is an actual column name in the table I'm querying.

The resulting stored procedure which just builds an insert query would look something like: (the below cases were all dynamically created by the Visual Basic (VB)app)

Insert Into insert_table
Select City, Temp_Date, Temp_Hour,
    Case City
        When 'Erie' Then
            Case
                WHEN TEMP >= 50 AND TEMP <= 70 THEN
                    TEMP
                WHEN TEMP < 50 THEN
                    CASE WHEN WIND_SPEED > 10 THEN (TEMP - .5 * (WIND_SPEED - 10)) ELSE TEMP END
                WHEN TEMP > 70 THEN
                    (.55 * TEMP) + (.02 * DEW_POINT) + 17.5
            End
        When 'Atlantic City' Then
            Case
                WHEN TEMP >= 55 AND TEMP <= 70 THEN
                    TEMP
                WHEN TEMP < 55 THEN
                    CASE WHEN WIND_SPEED > 10 THEN (TEMP - .5 * (WIND_SPEED - 10)) ELSE TEMP END
                WHEN TEMP > 70 THEN
                    (.55 * TEMP) + (.02 * DEW_POINT) + 17.5
            End

So as you can see, it's like passing each value into a formula pipeline and storing the results into a table. So if they want to change a formula, we don't have to alter any code (ie. procedure), we just change the table entry and rerun the vb.net app to generate the new stored procedure.

The entire idea was flexibility. The user who is using this used to be a programmer but moved to an analyst field. So he has access to the formula tables. If he wants to tweak something he just makes a change in the table and runs the Visual Basic (VB) applicatrion to rebuild the procedure.
 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.