Oracle and PL/SQL Recipes: A Problem-Solution Approach

8-7. Executing a Dynamic Block of PL/SQL

Problem

You want to execute a specific stored procedure based upon events that occur within your application. Therefore, you need to provide the ability for your application to execute procedures that are unknown until runtime. In short, you want to execute PL/SQL in the same dynamic manner as SQL.

Solution #1

Native dynamic SQL can be used to create and execute a block of code at runtime. This strategy can be used to create a dynamic block of code that executes a given procedure when an event occurs. In this example, a procedure is created that accepts an event identifier. An event handler within the application can call upon this procedure passing an event identifier, and subsequently a procedure that ...