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 can be determined via the identifier will be invoked.
-- Create first Procedure
CREATE OR REPLACE PROCEDURE TEST_PROCEDURE1 AS
BEGIN
DBMS_OUTPUT.PUT_LINE('YOU HAVE EXECUTED PROCEDURE 1…'),
END;
-- Create Second Procedure
CREATE OR REPLACE PROCEDURE TEST_PROCEDURE2 AS
BEGIN
DBMS_OUTPUT.PUT_LINE('YOU HAVE EXECUTED PROCEDURE 2…'),
END;
-- Create Event Handling Procedure
CREATE OR REPLACE PROCEDURE run_test(test_id IN NUMBER DEFAULT 1) AS
sql_text VARCHAR2(200);
BEGIN
sql_text := 'BEGIN ' ||
' TEST_PROCEDURE' || test_id || '; ' ||
'END;';
EXECUTE IMMEDIATE sql_text;
END;
When an event handler passes a given event number to this procedure, it dynamically creates a code block that is used to execute that procedure, passing the parameters the procedure needs. This solution provides the ultimate flexibility for creating an event handler within your applications.
Solution #2
DBMS_SQL can also be used to execute the same dynamic code. The following example demonstrates how this is done.
CREATE OR REPLACE PROCEDURE run_test(test_id IN NUMBER DEFAULT 1) AS
sql_text VARCHAR2(200);
cursor_var NUMBER := DBMS_SQL.OPEN_CURSOR;
rows NUMBER;
BEGIN
sql_text := 'BEGIN ' ||
' TEST_PROCEDURE' || test_id || '; ' ||
'END;';
DBMS_SQL.PARSE(cursor_var, sql_text, DBMS_SQL.NATIVE);
rows := DBMS_SQL.EXECUTE(cursor_var);
DBMS_SQL.CLOSE_CURSOR(cursor_var);
END;
How It Works
Native dynamic SQL allows processing of a SQL statement via the EXECUTE IMMEDIATE statement. This can be used to the advantage of the application and provide the ability to create dynamic blocks of executable code. By doing so, you can create an application that allows more flexibility, which can help ensure that your code is more easily manageable.
In the solution to this recipe, an unknown procedure name along with its parameters is concatenated into a SQL string that forms a code block. This code block is then executed using the EXECUTE IMMEDIATE statement.
Using native dynamic SQL, the array of parameters has to be manually processed to create the SQL string and assign each of the array values to the USING clause of the EXECUTE IMMEDIATE statement. This technique works quite well, but there is a different way to implement the same procedure.
As far as comparing native dynamic SQL and DBMS_SQL for dynamic code block execution, which code is better? That is up to you to decide. If you are using native dynamic SQL for all other dynamic SQL processing within your application, then it is probably a good idea to stick with it instead of mixing both techniques. However, if you are working with some legacy code that perhaps includes a mixture of both DBMS_SQL and native dynamic SQL, then you may prefer to write a dynamic code block using DBMS_SQL just to save some time and processing.