Oracle PL/SQL: Calling a dynamic procedure

My head hurts.
Is ther any way to pass a procedure or function  and have another routine run it?
For example (simplistic logic - not syntactically correct):
X = "My Package."
X = X + "My_Routine"
...
...
Function Do_it  ( x as ??)
  Run routine X
end function

Is something like this possible in PL/SQL?
 
GNOVAKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
The short answer is yes.

You need to use an EXECUTE IMMEDIATE syntax.

However, you have to be sure that your USING and INTO clauses match up.

This method would only work if all possible packages/procedures have the same number and type of parameters, etc.  If this is not the case, then you need to look at the DBMS_SQL package, that should be able to do everything you need.
0
sdstuberCommented:
here's a simple example...

note, you can't simply call a procedure with execute immediate, you have to invoke a pl/sql block, which means you need to add some additional syntax (semicolon, begin/end)  to make it work
CREATE OR REPLACE PROCEDURE proc_x
IS
BEGIN
    DBMS_OUTPUT.put_line('This is Proc_X');
END;

CREATE OR REPLACE PROCEDURE test_dynamic_plsql(p_routine IN VARCHAR2)
IS
BEGIN
    EXECUTE IMMEDIATE 'BEGIN ' || p_routine || '; END;';
END;


BEGIN
    test_dynamic_plsql('proc_x');
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
First example in the docs shows a more complete scenario including in/out parameters


http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/dynamic.htm#BHCEBBAI
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GNOVAKAuthor Commented:
Thanks everyone - excellent solution.
Appreciate it!
0
GNOVAKAuthor Commented:
what if I wish to pass parameters - can this be done?
Say in the example provided, I wished to pass what it prints out - I want to pass 'Hello' or a var instead of a hard coded 'This is Proc_X'
I've tried a number of concantenations and single quoted strings and have a problem getting it to work.
Is this even possible?
0
sdstuberCommented:
example  7-1 in the link above shows parameter usage
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.