Resultset from Oracle

[mutley]

Hi

Somebody have a examples How can I to return Resultset from Oracle to Visual Basic.
I trying with Ref Cursor, but I need to do others call to others Tables and Return data to a Recordset of the VB

[RhinoBull]

Do you need to return records from stored procedure or simply by sending sql statement?

[RhinoBull]

Please do not dbl post. Thank you.

[Shuja Ali]

Well if you rprocedure returns record then just execute it from VB and set the return value to a recordset.

Have I misunderstood what You are trying to say?

[mutley]

thanks
RhinoBull
I neeed return a Resultset for VB, for to open with recordset
Shuja Ali
Do You have a example ?

[mutley]

Sorry

I do not to knew where I to post

Sorry again

[Shuja Ali]

A simple example would be using a Command Object of the ADO library and execute the procedure using that.

I guess a simple search on this forum will give you lot of examples.

[GaryMazzone]

Look in the Database section FAQ (this is the thread: http://www.vbforums.com/showthread.php?t=393457)). I have posed examples using VB6 and VB.Net for this.

[mutley]

Thanks

But I need Passing Data from 2 or 3 Cursors, as below

cursor c1 (v1 in integer....) is
select a, b, c
from Table A, TAble B
WHERE ANYthing

cursor c2 (v2 in integer...etc) is
select x1,x2 , x3
from tableX, TableY
where ANYTHING
FOR R_C1 IN C1 (V1.....Vn) loop
FOR R_C2 IN C1 (V1.....Vn) loop
I NEED TO MOUNT A RESULT SET WITH some data from c1 cursor
and other data from c2 cursor


end loop

end loop

How can to build a Resultset like above ?

[GaryMazzone]

Record sets can contain more the on result right. So create another input/output parameter for the SP and Return the results from the other cursors to the parameters,

[mutley]

Hi

I do not understand , because I have 2 Cursors nested, where the second use data from first, but I need return some data from 2 Cursors

Example

LOop cursor c1
loop cursor c2 with data from c1

end loop c2
end loop cursor c1

I want return a cursor C to VB with some data from c1 and some data from cursor c2

[GaryMazzone]

As I said create two reference cursors. set the results of the SP cursors to the two refecursors and return the two ref cursors to the application in on record set. You can then use the record set to break up the results.

[mutley]

I do not to do

[MrNorth]

If u r referring to vb.net it is easy.

Just declare a procedure like this:

craete or replace procedure P_TEST(param1 in varchar2, recordset out cursor)

open recordset for

select * from whatever

end procedure P_TEST;


I wrote this by heart... but I hope you get a general idea.

What I did to make things easier was to encapsulate the cursor into a TYPE. I can show u the code when I get back to office...

/Henrik

[GaryMazzone]

When you have the results back in VB then use the rs.nextresult command.

[mutley]

tks

for a simple SELECT this work fine, but I have 2 or 3 Cursors for return in only recordset

[mutley]

tks

How can I read The first row from first recordset and first row from Second recordset

After

read The Second Row from first recordset and Second row from Second Recordset ?

[Hack]

Please do not dbl post. Thank you.

Duplicate Threads merged.

[GaryMazzone]

You must process each record set to completion then move to the next.

[mutley]

Ok

But I need first row from first cursor together with first row from second cursor

[GaryMazzone]

Then I think we need to look at chaning the way data is gathered. Why draw the data you need from 2 cursors in Oracle and not just one? Or return the return the results from the first cursor and then call a second SP with data in the result set from the first SP (in a resultset).

[mutley]

Do you have a example ?

[GaryMazzone]

Since I don't know what you're tying to accomplish no. Why not write two SPs. The first one returns the result set bound in our first cursor of the original SP. Now once back into the app call a second SP that has the reults based on the results of the first, and return that set.

[mutley]

Tks

I will go try

[mutley]

I did not have success

[GaryMazzone]

What did you not have success at?

[mutley]

to do a procedure that return in only cursor, with call for other procedure with other 2 cursors

I need to build a procedure with only cursor, because I to return to VB or Crystal reports

[evidal]

Hello,

I'm not sure I've understood exactly what mutley was trying to do, but I'm also trying to get, in my VB project, 2 or more REF CURSORS from a packaged procedure in Oracle, and it gives me the following error:

Error Type:
ADODB.Recordset (0x800A0CB3)
Current provider does not support returning multiple recordsets from a single execution.

Here's a bit of the code:
----------------
SQL = "{? = call PK.procXPTO({resultset 0, refCursor1},{resultset 0, refCursor2},?)}
(...)
.CommandType = adCmdText
.NamedParameters = True
Set objParam = .CreateParameter("result", 131, 2)
.Parameters.Append objParam
Set objParam = .CreateParameter("paramIN", 131, 1, 255, paramIn)
.Parameters.Append objParam
(...)
---------------

I'm using MSDAORA as Provider, Oracle 9.2 and VB6.

Any help is utterly appreciated.

Many thanks,

E. V.