I have seen this topic discussed several times before but, unfortunately, have never seen a solution. Oracle stored procs don't return data in record sets. Sybase and SQL Server do (not sure about Informix or DB2).
On Wed, Oct 21, 2009 at 9:46 PM, Brittain, Mark <[email protected]> wrote: > Hi Larry, > > I would say the solution depends on the output. If it is to fill in a field > or present a list to a user, then is a Select Field, Read Value for Field > From: SQL, add the select statement there and map to the fields. > > If you want to populate all of the results into a field then you would need a > view, a table and guide to make it work. Sounds like a lot but it is straight > forward. > > Any questions, let me know > > Mark > Mark Brittain > Remedy Developer > NaviSite Inc. > [email protected] > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[email protected]] On Behalf Of Garrison, Sean (Norcross) > Sent: Wednesday, October 21, 2009 12:50 PM > To: [email protected] > Subject: Re: Stored procedures in Sybase vs Oracle > > When I do something like this I usually create a view in oracle then create a > "View Form" in remedy that uses the view I created in Oracle. But have you > tried returning a CURSOR ??? Honestly I doubt it would work but in theory it > might. Let me know if you are able to get it to work cause it would be > valuable knowledge for me as well. > > Thanks, > > Sean > > > > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[email protected]] On Behalf Of L G Robinson > Sent: Tuesday, October 20, 2009 2:41 PM > To: [email protected] > Subject: Re: Stored procedures in Sybase vs Oracle > > Sorry... I forgot my sig. > > Larry Robinson [email protected] > Remedy Developer/Admin > Office of Information Technology > NC State University 919-515-5432 Voice > Raleigh, NC 27695-7109 919-513-1893 FAX > > > On Oct 20, 2009, at 12:16 PM, L G Robinson wrote: > >> Hi Folks, >> >> I am in the early stages of a migration from Sybase to Oracle and I >> am struggling with stored procedures. >> >> One of the nice things about Sybase is the ease with which one could >> return data to the ARS system using stored procedures. I am finding >> this much more difficult in the Oracle environment. When using a >> SetFields operation in an Active Link, one can invoke a Sybase >> stored procedure that includes a SELECT statement and the results of >> the select are presented back to ARS as $1$, $2$, etc. If multiple >> rows are returned, you can display them and let the user choose the >> record they want. Simple. >> >> In Oracle, I have discovered that stored procedures do not return >> anything. However, Oracle Functions return one "thing. The "thing" >> can be a singe value or something more complex such as a row or a >> table or some other user-defined Oracle object. The trouble I am >> having is understanding how to define the Oracle "thing" that I want >> to return so that it behaves the same way that it does with Sybase. >> Is it a RECORD, an OBJECT or an array or a table? Or something else? >> I know my way around Sybase pretty well, but this is my first >> exposure to Oracle. >> >> If anyone has any experience in this area and could nudge me in the >> right direction, I would greatly appreciate it. I am willing to put >> in the time to learn whatever I need to learn... I just need some >> direction. References to helpful documentation or a working example >> would be most helpful. >> >> ARS 5.1.2 >> Sybase 12.5.2 >> Oracle 10.2.0.4.0 >> >> Thanks. >> Larry >> >> _______________________________________________________________________________ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> Platinum Sponsor:[email protected] ARSlist: "Where the >> Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" > > This e-mail is the property of NaviSite, Inc. It is intended only for the > person or entity to which it is addressed and may contain information that is > privileged, confidential, or otherwise protected from disclosure. > Distribution or copying of this e-mail, or the information contained herein, > to anyone other than the intended recipient is prohibited. > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
