When at all possible I try to utilize a stored function in Oracle. This way I can use a simple select Test_SP2() from Dual statement
Fred From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Cecil, Ken Sent: Friday, September 23, 2011 9:03 AM To: [email protected] Subject: Re: Oracle stored procedure call ... ** Call is portable and works for both Oracle and Microsoft for me. Here is the language reference for each in case you want to try it. http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_4008.htm#i2119950 http://msdn.microsoft.com/en-us/library/aa198007%28v=sql.80%29.aspx You may need to have braces around instead: {CALL Test_SP2} I'll check our syntax when I get back in the office. From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Garrison, Sean (Norcross) Sent: Friday, September 23, 2011 9:25 AM To: [email protected] Subject: Re: Oracle stored procedure call ... ** "call" probably works in MS SQL but doesn't work for Oracle. "Execute" works if you run it via SQLPlus but doesn't work in a filter. From what I can tell you must have a begin and end (for oracle) and it must be on one line. Just putting it in the ARSList archives because I know I will forget this and wonder why in the world my stored procedure isn't working .... Thanks, Sean From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Cecil, Ken Sent: Friday, September 23, 2011 1:53 AM To: [email protected] Subject: Re: Oracle stored procedure call ... ** Doesn't this work and do what you want or is there more to it than that? call TEST_SP2; Ken. From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Garrison, Sean (Norcross) Sent: Thursday, September 22, 2011 5:23 PM To: [email protected] Subject: Oracle stored procedure call ... ** This is kind of weird but I have been debugging why an oracle stored procedure call wasn't working for me. We created a simple stored procedure called "TEST_SP2" and it would not work. What we found was you had to put it all on one line: declare begin TEST_SP2; end; This did not work: Declare Begin TEST_SP2; end; Anyone notice similar behavior? It used to work that way in 7.0.1 but I might be mistaken. We are currently on 7.5 patch 6. Do I need to do something different? Thanks, Sean _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
