Dynamic runtime evaluation of the PL/SQL expressions
Quite frequently the people who have to work with PL/SQL and who have some experience with other programming languages (like me) are looking for familiar mechanisms to solve their problems. One of these mechanism is dynamic evaluation of the language expressions commonly used in Unix shell, Perl and other languages. It actually turns out that it is quite easy to evaluate a PL/SQL expression to execute some dynamically generated code. You can also pass the parameters and even extract any number of resulting values.
Lets consider the following Oracle PL/SQL example:
set serveroutput on;
declare
expression varchar2(64);
a number := 2;
b number := 4;
result number;
begin
expression := 'begin :out_var := ' || a ||
' + :comp2; end;';
execute immediate expression using out result, in b;
dbms_output.put_line(expression ||
' => ' || result);
end;
/
quit
In this example we build a string expression ":out_var := 2 + :comp2;" to execute. In order to be a valid PL/SQL code block we have to surround it with "begin" and "end;". "execute immediate" statement allows us to bind any variables to the code being executed dynamically. The trick is to specify the "out" or "in out" mode for the variables that we will use to transfer the execution results into our code. In the given example we will use the PL/SQL variable "result" to store the result of the expression and we will pass the second component of the sum using variable "b".
Running this script would produce the following result:
sqlplus -SL user/pwd@dbname @test.sql begin :out_var := 2 + :comp2; end; => 6 PL/SQL procedure successfully completed.
Using this approach you can add some dynamic behavior to your code, like storing the expressions that need to be executed directly in the database, dynamically generating the expressions and even stored procedures or functions.
blog comments powered by Disqus