Hi Folks, I wanted to report back on how I resolved my problem with converting Sybase stored procedures to Oracle. But first let me thank all of the folks who responded and offered suggestions.
As it turns out, Oracle procedures do not return values, but Oracle FUNCTIONS do. The only catch is that they return one thing. This was problematic for me in several of my cases because the Sybase procs were returning the results of a multi-column select. I experimented with returning a variety of different Oracle objects and datatypes, but none of them produced the same result as I was accustomed to in Sybase. More on this later. In many of the other cases, the Sybase procs were not returning anything so for those, I was able to write an equivalent Oracle function. Those turned out to be pretty straightforward, once I figured out a couple of things: - You have to have one of these in your function if you are going to modify data: PRAGMA AUTONOMOUS_TRANSACTION; - You have to put COMMIT statements in your function after you modify something. :-) The rest is just syntax differences. For example, string literals are enclosed in single quotes in Oracle but all of my Sybase stuff used double quotes. In some of the other cases, the Sybase proc was issuing a select but it was doing some data manipulation and verification too. In many cases, I was able to move that data manipulation back into ARS workflow and then just issue the SELECT. So no need for a proc or a function at all. However, there was one proc that used the parameters that were passed to determine which select, among many, should be executed by evaluating a very long if-then-else statement. The select statement returned multiple columns and possibly multiple rows. As mentioned above, I could not discover a way to return that. I considered two different approaches to this. The first was to stuff the results of the appropriate select into an intermediate table (adding another column with a GUID) and then, upon returning to ARS, issue a select against the intermediate table, using the GUID as the selection criteria. The other option I considered was to write a simple Perl script that took all of the same parameters as the original Sybase proc and returned the appropriate select statement. Then issue the select to Oracle. This second approach was the one that I eventually decided upon. So it is all working! Sorry for the length of this post. I hope my experience will be useful to someone else. I have examples if anyone wants to see them. Thanks again for your suggestions. Larry Larry Robinson [email protected] Office of Information Technology NC State University 919-515-5432 Voice Raleigh, NC 27695-7109 919-513-0877 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"
