Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically Execute a Procedure
Dynamically Execute a Procedure [message #292355] Tue, 08 January 2008 13:32 Go to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Hello,

I will first explain what I am needing to do, then how I was going to go about it. I need to execute a procedure and see if it returns any data, if it does, then I want to exit. If it does not return any data, I need to check another procedure. So, I laid something out that has a table with the list of procedures I want to execute (two columns in the table, checkProcedure and successProcedure). I build a cursor that contains the list of the procedures. Then I want to loop through the list and execute the checkProcedure. Check to see if the checkProcedure returns any data, if it does return data, exit the loop and then execute the successProcedure. If the checkProcedure does not return any data, then I am going to go execute the next checkProcedure.

I tried to use the EXECUTE IMMEDIATE command, but it seems as though that is only for SQL statements. I am trying to make the procedure that runs this easy to add additional procedures in to be checked, that is why I am storing the procedures in a table. I can probably change procedures being stored in the table to functions, if that would help, and then execute the function dynamically.

Any help is appreciated.

Thank you,
Shane
Re: Dynamically Execute a Procedure [message #292356 is a reply to message #292355] Tue, 08 January 2008 13:35 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Post your code.....

By
Vamsi
Re: Dynamically Execute a Procedure [message #292357 is a reply to message #292355] Tue, 08 January 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I tried to use the EXECUTE IMMEDIATE command, but it seems as though that is only for SQL statements.

Wrong it can execute any PL/SQL block.

Regards
Michel
Re: Dynamically Execute a Procedure [message #292358 is a reply to message #292355] Tue, 08 January 2008 13:43 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
I was doing some testing, before coding everything, to see how I could dynamically execute a procedure. What I tried for that was:

-- This one will work
PROCEDURE p_profile_check (next_url VARCHAR2)
IS
testProcedure VARCHAR2 (1024);
BEGIN
testProcedure := 'SELECT * FROM FS_ACCT_INFO';
EXECUTE IMMEDIATE testProcedure;
END p_profile_check;

-----------------------------------------------------------
-- This one does not work (error: ORA-00900: invalid SQL statement ORA-06512)
PROCEDURE p_profile_check (next_url VARCHAR2)
IS
testProcedure VARCHAR2 (1024);
BEGIN
testProcedure := 'p_new_procedure(''testing'')';
EXECUTE IMMEDIATE testProcedure;
END p_profile_check;

PROCEDURE p_new_procedure (from_loc VARCHAR2)
IS
BEGIN
HTP.p (from_loc);
END p_new_procedure;

[Updated on: Tue, 08 January 2008 13:44]

Report message to a moderator

Re: Dynamically Execute a Procedure [message #292359 is a reply to message #292358] Tue, 08 January 2008 13:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check EXECUTE IMMEDIATE.

By
Vamsi
Re: Dynamically Execute a Procedure [message #292360 is a reply to message #292359] Tue, 08 January 2008 13:50 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
That doesn't really show about executing a procedure or function though. It only shows about executing a sql statement.

- Shane
Re: Dynamically Execute a Procedure [message #292361 is a reply to message #292358] Tue, 08 January 2008 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 7 Performing SQL Operations with Native Dynamic SQL
Section Using the EXECUTE IMMEDIATE Statement in PL/SQL
Just read the documentation.

Regards
Michel
Re: Dynamically Execute a Procedure [message #292363 is a reply to message #292361] Tue, 08 January 2008 14:17 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
I changed my procedure to a function. Then executed it using:
strSql := 'Select f_name(:1) from dual';
EXECUTE IMMEDIATE strSql INTO returnVal USING 'parm1';

[Updated on: Tue, 08 January 2008 14:44]

Report message to a moderator

Re: Dynamically Execute a Procedure [message #292364 is a reply to message #292363] Tue, 08 January 2008 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then?

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Dynamically Execute a Procedure [message #292365 is a reply to message #292364] Tue, 08 January 2008 14:45 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Sorry about not being clear. When I changed the procedure to a function and then call the function in an SQL Statement, it works without any errors. Thank you for your help.
Re: Dynamically Execute a Procedure [message #292473 is a reply to message #292365] Wed, 09 January 2008 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It is perfectly possible to call procedures using execute immediate. You will have to us an anonymous block.
BUT.. You should ask yourself why you need dynamic sql to call a procedure. Most of the time this indicates a flaw in design or a flaw in thoughts by the coder.
Re: Dynamically Execute a Procedure [message #292614 is a reply to message #292473] Wed, 09 January 2008 06:56 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Based on what I am trying to do, do you have any other suggestions of how to accomplish it without using dynamic sql? I come from a background of building my sql statements and then executing them, rather than just calling the sql statements directly. I would love to hear other ways to go about this, so I can code the best way possible.

- Shane
Re: Dynamically Execute a Procedure [message #292619 is a reply to message #292614] Wed, 09 January 2008 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have a list of tasks (procedures) to execute inside a table there is no other way than to use dynamic SQL.
It could also be a list of task numbers, then you call a generic procedure passing the task number and that do something like
if param = 1 then proc1
elsif param = 2 then proc2
...
In this case, all is static.

Regards
Michel
Re: Dynamically Execute a Procedure [message #292621 is a reply to message #292619] Wed, 09 January 2008 07:21 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Thank you for the suggestion. If performance is hindered too much, then I will change to the static approach you suggested. I appreciate everyone's help and comments/suggestions.

- Shane
Re: Dynamically Execute a Procedure [message #292635 is a reply to message #292621] Wed, 09 January 2008 08:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why can't you just code the whole thing in a single procedure? You know upfront what procedures you will be calling and in what order. Just code them all in order in a single procedure and add if-statements to exit the procedure if you hit a success.
Nothing dynamic about that
Re: Dynamically Execute a Procedure [message #292640 is a reply to message #292635] Wed, 09 January 2008 08:49 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
I do not actually know the procedures up front. When a user logs into our system, I need to run through the list of procedures in the table (sometimes there may be none for that user, other times they may have 5 procedures). So, the procedures being called could change and the number of procedures being called could also change.

When one of the procedures is called, it checks for required information and if it finds the information, then it will redirect the user to a different location than they would normally arrive at. I originally had it coded so that it was static (because there was only one procedure being called). Since then, they specs have changed and another procedure is being added for checking. I can see the specs changing to allow even more procedures, so instead of having to go back and recode/modify the main procedure, I just want to be able to add them to a table and have the main procedure reference them that way.

I hope that explains better why I am going about this in a dynamic fashion. I don't like to have to constantly modify a procedure, when I could possibly have the procedure read from a table to determine what it should do.

Thank you,
Shane
Re: Dynamically Execute a Procedure [message #292643 is a reply to message #292640] Wed, 09 January 2008 08:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah ok. Missed that part.

Re: Dynamically Execute a Procedure [message #292654 is a reply to message #292640] Wed, 09 January 2008 09:21 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Shane,

What you have described is the perfect case for using dynamic SQL. Whenever possible it should be avoided, but there are some valid situations to use it.

I have a similiar business case where based on certain criteria one or more procedure name(s) are extracted from a table and then run dynamically.

Good luck.
Previous Topic: Case statement in Where clause
Next Topic: Database Links
Goto Forum:
  


Current Time: Sat Dec 10 18:58:56 CST 2016

Total time taken to generate the page: 0.07218 seconds