Using ORACLE SYS_REFCURSOR in TD

General discussion forum about all databases other than SqlBase.
arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

Using ORACLE SYS_REFCURSOR in TD

Post by arbrenes » 08 Sep 2011, 19:29

I created this silly sample in Oracle to explain better.

Having the following SP:

CREATE OR REPLACE PROCEDURE /*dbo.*/usp_GetChildren
(
v_ParentLN IN CHAR DEFAULT NULL
)
AS

cv_1 SYS_REFCURSOR;

BEGIN
OPEN cv_1 FOR
SELECT ChildName
FROM tFamilies
WHERE ParentName = v_ParentLN
AND RcdSts = 'A';
END;


If I want to bring children names based on Parent last name. There could be more than 1 results.

As the SqlPLSQLCommand and the SqlOraPLSQLPrepare is not giving me an option to get back an 'into' list when calling the function, how can I get the info contained in the SYS_REFCURSOR using TD functions ? Thanks in advance !

jmcnamara

Re: Using ORACLE SYS_REFCURSOR in TD

Post by jmcnamara » 09 Sep 2011, 21:38

Your result cursor needs to be an out parameter. Can't help you with how go get the cursor from Oracle to TD though.

arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

Re: Using ORACLE SYS_REFCURSOR in TD

Post by arbrenes » 11 Sep 2011, 01:04

Thanks JMcnamara for your response. I thought the same thing (output parameter) but according to somebody that replied to a similar question it can't be done thru TD functions(see last response in post https://support.guptatechnologies.com/supportforum/viewtopic.php?f=40&t=7146).

Anybody from Unify can give some feedback ? thanks

Jeff Luther

Re: Using ORACLE SYS_REFCURSOR in TD

Post by Jeff Luther » 13 Sep 2011, 01:39

A user who cannot access the forum via his work PC (Brian Howard) provided some info. that may be of use to you.

ARBRENES -- Kindly only reply to this forum thread. You started another one earlier and now with this one it is confusing to all of us to have parallel threads. I am putting Brian's info. in this thread because this thread is newer. Here is what he wrote:
In the TD 5.2 forum there are a couple of questions regarding result sets from Oracle stored procedures.

This info. is for this topic and duplicate/related topic: https://support.guptatechnologies.com/supportforum/viewtopic.php?f=40&t=7146

There is a very good white paper on this written by Suren Behari, it can be found at
https://samples.tdcommunity.net/samples ... ProcRS.pdf

Basically you need to use the Oracle OLEDb provider.

Further to this, the limitation is that you can only return a predefined number of result sets. i.e. If the number of result sets is dynamic then you are toast. When we came across this limitation we hassled Oracle and were given the attached sql, which I never got around to proving would work, as our oracle project was dropped ( political ), but it looks like it would allow for a dynamic result set list. The limitation of this method, is that all result sets are built before the procedure returns, thus you cannot use this to monitor progress on a long running procedure.

Anyway, you have always been helpful to us, so I hope this helps some of your customers.
He also attached a sample SQL file. Thanks, Brian!!!
You do not have the required permissions to view the files attached to this post.

arbrenes
Costa Rica
Posts: 17
Joined: 08 Dec 2019, 23:30
Location: New Jersey

Re: Using ORACLE SYS_REFCURSOR in TD

Post by arbrenes » 13 Sep 2011, 14:58

Thank you for the info.

I apologize for opening another thread. I am under a very tight corporate delivery deadline and did not get a fast response on my last request on previous post I opened in this forum. I guess I am very frustrated at this point. Sorry.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests