Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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
                WHEN TEMP >= 50 AND TEMP <= 70 THEN
                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
        When 'Atlantic City' Then
                WHEN TEMP >= 55 AND TEMP <= 70 THEN
                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

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 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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.