I run a few stored procs from filters in a Direct SQL action CALL hyd_ldap_reset_flags_proc()
PROCEDURE HYD_LDAP_UPDATE_INX_PROC IS BEGIN -- many lines of comments -- many statements -- a few commits END; From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Garrison, Sean (Norcross) Sent: Friday, September 23, 2011 10:37 AM To: [email protected] Subject: Re: Oracle stored procedure call ... ** I get this "ARERR [552] Failure during SQL operation to the database : ORA-00911: invalid character" when I do the following: {CALL Test_SP2();} {CALL Test_SP2;} {CALL Test_SP2} CALL Test_SP2; CALL Test_SP2 Let me know if you have better luck than I do. Thanks Sean From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Cecil, Ken Sent: Friday, September 23, 2011 10: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 _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ ******************************************************************* This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept for the presence of computer viruses. www.Hubbell.com - Hubbell Incorporated** _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ ******************************************************************* This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept for the presence of computer viruses. www.Hubbell.com - Hubbell Incorporated** _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
