Dynamic creation of procedures in PL/SQL
Every PL/SQl devloper must have tried their hands with dynamic SQl. Its an indelible part of oracle.But its generally used when we dont know at compile time about the object on which we are to perform the task. Suppose we want to insert data into a table but we get the name of the table during the execution of the procedure, in those cases we would use dynamic SQl with the table name passed as bind variable. Dynamic Sql is even used to execute DDl commands inside a PL/SQl block. A create command is not recognized by the pl/sql compiler, so we have to wrap the code as a dynamic SQl so that it bypasses the compiling stage but gets executed during the execution time.i have written a small anonymous PL/SQL block to demonstrate it.
declare begin execute immediate 'create index idx_demo on demo_tab(demo_col)'; end;
In query 2.1 i have created an index using the execute immediate command. This being a DDl script the only way to execute it is by using dynamic SQl.
This article is not about dynamic SQl but rather about an use of dynamic SQl on which i stumbled upon just out of inquisition. i tried to create a procedure dynamically just out of fun and it created and got compiled too.
create or replace procedure demo_test( p_val1 number, p_val2 varchar2 ) as l_stmt varchar2(2000); begin l_stmt := 'create or replace procedure just_test1 as v_date date; begin select sysdate into v_date from dual; DBMS_OUTPUT.ENABLE(10000); dbms_output.put_line (v_date || ''complete''); end;'; dbms_output.put_line(l_stmt); execute immediate l_stmt; EXECUTE IMMEDIATE 'ALTER PROCEDURE JUST_TEST1 COMPILE'; EXECUTE IMMEDIATE ' call just_test1'; end;
Creation of Dynamic SQl is very vital if you consider a case where the requirement is not known at the time of programming or compiling. At present I just cant think of such a case and even the above code is just a demonstration of the capability that dynamic procedure creation has.