Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle PL/SQL: Calling a dynamic procedure

Posted on 2011-10-04
6
Medium Priority
?
322 Views
Last Modified: 2012-05-12
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?
 
0
Comment
Question by:GNOVAK
  • 3
  • 2
6 Comments
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 800 total points
ID: 36911723
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1200 total points
ID: 36911855
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36911891
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:GNOVAK
ID: 36912296
Thanks everyone - excellent solution.
Appreciate it!
0
 

Author Comment

by:GNOVAK
ID: 36920201
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36920229
example  7-1 in the link above shows parameter usage
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question