Dynamic creation of procedures in PL/SQL

articles: 

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.

query 2.1

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.

query 2.2

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.

Comments

fantastic..

excellent

When i Do
execute demo_test(1,'a');

it gives me
BEGIN demo_test(1,'a'); END;

*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
ORA-06512: at "TEST_USER.DEMO_TEST", line 16
ORA-06512: at line 1

can u tell me the problem

rozy

Hi,

I feel that you are trying to do something that you shouldn't. You can create the dynamic code by using conditional compilation, external code (ex. java or perl) or anything else. But I would avoid creation of abstract method factory that is creating concrete implementations of a method - most probably you can do this in more optimal way.
Additionally using DBMS_OUTPUT is a bad habit.
Let me show some example solution that might be useful under some circumstances:
CREATE PROCEDURE my_proc IS
BEGIN
INSERT INTO $$table_name VALUES (1);
COMMIT;
END;
Now you need to set conditional compilation variable table_name and compile the procedure. No need to write useless dynamic-creator code.