Follow up
Shivani Gupta, November 28, 2015 - 6:59 pm UTC
Hello Connor,
Thanks for the response I tried to implement the solution as you directed but still getting errors:
create or replace procedure myproc1 as
begin
dbms_output.put_line('Called Prc');
end;
create or replace procedure myproc2(procname varchar2) is
begin
execute immediate 'begin ' ||procname||' ;' ||' end;';
end;
Getting error after I run below code:
begin
myproc2(myproc1);
end;
Error starting at line : 8 in command -
begin
myproc2(myproc1);
end;
Error report -
ORA-06550: line 2, column 9:
PLS-00222: no function with name 'MYPROC1' exists in this scope
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
November 30, 2015 - 3:26 am UTC
myproc2('myproc1');
Just reiterating what a bad bad idea this is
Shivani Gupta, November 30, 2015 - 3:34 am UTC
Hi,
I know there was very good chances of SQL injection but this one of my my project requirement.Also I am able to implement now:
vsql := 'begin ' || pprocname || ';end;';
execute immediate vsql;
Thanks,
Dynamic method dispatch
Gerard, December 02, 2015 - 8:57 pm UTC
One under-utilized alternative to dynamically executed PL/SQL is to make use of the dynamic method dispatch mechanism implicit in Oracle OBJECT types... for example:
create or replace type OMyInterface
is object
(
dummy varchar2(10),
member procedure MyProc(self in OMyInterface, aArgs in varchar2)
)
not instantiable
not final
/
create or replace type OMyImplementation_1
under OMyInterface
(
overriding member procedure MyProc(self in OMyImplementation_1, aArgs in varchar2)
)
/
create or replace type body OMyImplementation_1
is
overriding member procedure MyProc(self in OMyImplementation_1, aArgs in varchar2)
is
begin
dbms_output.put_line('Called OMyImplementation_1.MyProc with ' || aArgs);
end;
end;
/
create or replace type OMyImplementation_2
under OMyInterface
(
overriding member procedure MyProc(self in OMyImplementation_2, aArgs in varchar2)
)
/
create or replace type body OMyImplementation_2
is
overriding member procedure MyProc(self in OMyImplementation_2, aArgs in varchar2)
is
begin
dbms_output.put_line('Called OMyImplementation_2.MyProc with ' || aArgs);
end;
end;
/
create or replace procedure RunMyProc(aImpl in OMyInterface, aArgs in varchar2)
is
begin
aImpl.MyProc(aArgs);
end;
/
exec RunMyProc(new OMyImplementation_1('Impl 1'), 'arg1');
exec RunMyProc(new OMyImplementation_2('Impl 2'), 'arg2');
December 03, 2015 - 2:08 am UTC
Thanks for the contribution