Getting recordset to VB from Oracle Stored Proc 2

[olichap]

I've a working Oracle stored procedure that executes a select statement and returns (as an OUT parameter) a cursor. My problem is, I've no idea how to call the proc through VB. I create a Command object and configure the two parameters (1 is a string, 2 is expecting REFCURSOR on Oracle).

If I were to execute this through Oracle (from SQL*Plus) I'd type the following:
SQL> var c refcursor;
SQL> exec mma_plan_events('000000000000087', :c);

to display the results
SQL> print c

My proc looks something like this:

CREATE OR REPLACE PROCEDURE PLAN_EVENTS
(v_ID in varchar, events_cursor out pkg_types.ref_cursor)
is

begin

open events_cursor for
select .... from ... where...= v_id;

return;
end;

The results are in "events_cursor" but I don't know how to call this from VB, passing the proper variables, to get the result.

Any help would be greatyl appreciated.

Thanks,
O.

 

[ulwitch]

The following sub calls a procedure that doesn't have input parameters. If you do have input parameters, you would need to include the following for each parameter:

Dim nParam1 As New ADODB.Parameter
'This next part should be placed right after you set the command type.
Set nParam1 =.CreateParameter(Name,Type,Direction,Sze,Value)
.Parameters.Append nParam1

If your Procedure is in a package then you need to preface the Procedure Name with the PackageName. Like this "PackageName.ProcedureName".
'**********************************************************
Private Sub ReturnCursorFromOracle()
Dim mvarConn As ADODB.Connection
Dim cmdStoredProc As ADODB.Command
Dim strCnn1 as String
Dim rs As ADODB.Recordset

strCnn1 = "Provider=MSDAORA.1; Data Source=OracleDatabaseName; User ID=WhichUser; Password=UsersPassword"
Set mvarConn = New ADODB.Connection
mvarConn.CursorLocation = adUseClient
mvarConn.Open strCnn1

Set cmdStoredProc = New ADODB.Command
With cmdStoredProc
.ActiveConnection = mvarConn
.CommandText = "ProcedureName"
.CommandType = adCmdStoredProc
End With

Set rs = cmdStoredProc.Execute
'Do whatever you need to do with the recordset.
Set rs = Nothing
Set cmdStoredProc = Nothing
End Sub

 

[olichap]

Thanks ulwitch,

I've got the basic execution code down; no problems there.
The proceedure requires a string be passed in and I want the results to be in some usable format. It is my assumption based on other examples that in order to return a resultset from an Oracle stored proceedure I must return the cursor, thus the need for the OUT parameter.

My problem is how to call this proc through the ADO Command object. Since I have two parameters I must pass two objects. One is easy, that's the string. I have to pass the OUT parameter as well, but whatever type I try returns an error that says a parameter was missing or of the incorrect type. I have two parameters added to my command object. One is adParamInput the other adParamOutput. The former is set to my string, the latter (I guess) is supposed to represent a cursor object being passed.

Another question:

Do I need to create an OUT parameter at all to do this? If I pass just the string, declare a cursor within the proceedure, and execute my select statement through ADO will the resulting cursor dataset still be passed back?

Thanks,

O.

The proc is not part or the package itself. I only created the Cursor referrence in the "pkg_types" package.

 

[ulwitch]

No, you do not need to specify the cursor. For clarification, I am going to paste a copy of a vb call to a procedure that I use everyday as well as the procedure that was called.
'****************************vb*********************
Set cmdStoredProc = New ADODB.Command
With cmdStoredProc
.ActiveConnection = mvarConn
.CommandText = "Faculty.SelectUser"
.CommandType = adCmdStoredProc

Set nParam1 = .CreateParameter(mvarUserOnyen, adVarChar, adParamInput, 64, mvarUserOnyen)
.Parameters.Append nParam1
End With

Set rs = cmdStoredProc.Execute
rs.MoveFirst
UserID = rs.Fields("faculty_id&quot.Value
UserPrivilege = rs.Fields("secgroup&quot.Value
UserLastName = rs.Fields("LAST_NAME&quot.Value
UserFirstName = rs.Fields("FIRST_NAME&quot.Value
RankID = rs.Fields("RANK_ID&quot.Value
EmailAddress = rs.Fields("EMAIL&quot.Value
Password = UCase$(rs!PASS)
Set rs = Nothing
Set cmdStoredProc = Nothing

'***********************oracle*************************
This is in the package definition.
Type FacultyInfo is Ref Cursor Return IOG_FACULTY%ROWTYPE;
PROCEDURE SelectUser
( UserOnyen IN VarChar2,
UserInfo Out FacultyInfo
);

This is in the package body.
PROCEDURE SelectUser
( UserOnyen IN VarChar2,
UserInfo Out FacultyInfo

)
IS
BEGIN
OPEN UserInfo For
SELECT *

FROM
IOG_FACULTY
WHERE
onyen = UserOnyen;
return;
End SelectUser;

 

[olichap]

Ok, now in my package (MMA_TYPES) I have the following:

TYPE ref_cursor IS REF CURSOR;
procedure Plan_Events(v_sorgid IN varchar2, events_cursor OUT ref_cursor);
end;

In the package the following:

CREATE OR REPLACE PACKAGE BODY "HCC"."MMA_TYPES" procedure Plan_Events(v_id IN varchar2, events_cursor OUT ref_cursor)
is
begin
open events_cursor for
select * from...where...=v_id;
return;
end;

Below are the errors I get when it trys to compile:

Line # = 1 Column # = 32 Error Text = PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the following:

is as compress compiled wrapped
The symbol "is" was substituted for "PROCEDURE" to continue.

Line # = 61 Column # = 4 Error Text = PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin end function package pragma procedure form

Not to be too dense, but I feel like I'm missing a step somewhere.

O.

 

[ulwitch]

Try this in SQL Worksheet.

Create or Replace Package HCC.MMA_TYPES AS
TYPE ref_cursor IS REF CURSOR;
PROCEDURE Plan_Events
( v_sorgid IN varchar2,
events_cursor OUT ref_cursor
);

END HCC.MMA_TYPES;

/

Create or Replace Package Body HCC.MMA_TYPES IS

PROCEDURE Plan_Events
( v_sorgid IN varchar2,
events_cursor OUT ref_cursor
)
IS
BEGIN
OPEN events_cursor For
select * from...where...=v_sorgid;
RETURN;
END HCC.MMA_TYPES;

 

[ulwitch]

oops! I forgot to end the procedure.


Create or Replace Package HCC.MMA_TYPES AS
TYPE ref_cursor IS REF CURSOR;
PROCEDURE Plan_Events
( v_sorgid IN varchar2,
events_cursor OUT ref_cursor
);

END HCC.MMA_TYPES;

/

Create or Replace Package Body HCC.MMA_TYPES IS

PROCEDURE Plan_Events
( v_sorgid IN varchar2,
events_cursor OUT ref_cursor
)
IS
BEGIN
OPEN events_cursor For
select * from...where...=v_sorgid;
RETURN;
END Plan_Events;
END HCC.MMA_TYPES;

 

[olichap]

I found my problems in the package. I did not have "AS" at the top and was missing an END; at the bottom. It compiled OK, now I have to see if my call through VB works. Cross your fingers.

O.

 

[olichap]

Excellent, it finally worked. Thank you very much ulwitch, I appreciate you assistance and patience.

O.

 

[WireHed]

Great thread...
There's one more piece of info that I need on this. I am a newbie to the group, and if you feel this should be an independent thread instead of an addendum, I'll re-post...

I'm following the same process as laid out in your thread to pass a recordset object to an imbedded Crystal Reports object, and I need to do a recordset.RecordCount check before allowing report generation to proceed. Due to a known ADO bug, command.RecordCount passed to rs.Recordcount can randomly lead to an "E_FAIL" message and cannot be relied on.

In the SQL Server world, I'd set up an output parameter and store @@ROWCOUNT to be passed out as a redundant Record Count.

In Oracle, the docs say you can capture "cursor%ROWCOUNT" when you fetch into a cursor, but says nothing abut how to capture %ROWCOUNT when using "OPEN refCcursor FOR", which, I'm assuming, is an "implied fetch". Attempts to set a variable equal to refCursor%ROWCOUNT always return 0 so far...

any ideas? What am I missing?

 

[slan]

Hi olichap and ulwitch,

I've been reading your discussion about getting a recordset from Oracle store proc through VB. Instead of VB, I'm using VB script to get back the recordset. I've done eveything you guys said but when I ran script, I get an Oracle error saying that its wrong type of argument in call of my store proc.

ulwitch, you said that a out parameter is not needed in the command statement if use ref cursor. Is this true for VB script as well. thanks.

 

[olichap]

Check and make sure the parameter names you give the Parameter object in VB is the same (and has all the same characteristics) as that expected by your procedure.
Do your procedure, package, and package body compile without errors?

 

[WireHed]

Hi oilchap,
Yes, the Parameter object is the same, with the same name. The procedure, package, and package body all compile properly. I get the recordset back correctly, but I still haven't been able to get a proper recordcount from the refcursor%ROWCOUNT property (still returns 0). Trying to capture SQL%ROWCOUNT instead returns a NULL (which also seems appropriate).

I worked around this by declaring a "DUMMY cursor ON" against the exact same SQL statement returning the recordset, fetching through the results and capturing DUMMY%ROWCOUNT. There must be a way to capture this without creating a second cursor and cycling through each record just to get a count.

Any ideas are appreciated...thanks for the time.

 

[slan]

olichap, was your reply/question directed towards me or WireHed. If it is, then yes my package/procedure all compile correctly.

The error occur when I ran the VB script. The error indicated something about wrong argument in procedure. ulwitch said that its not necessary to specify an out parameter in the command property of VB . Is this true for your case. Can you post a sample code of the command object that you use to get the recordset back. thanks

 

[olichap]

Sorry, I think I was trying to get info out of slan before; should have been more specific.

My procedure is define in the package body with the following params:

Procedure Plan_Events(v_sorgid IN varchar2, v_dtFrom in date, v_dtTo in date, events_cursor out ref_cursor)

Below is some code that calls this proc/package and populates a grid:

Private Sub Command1_Click()
Dim cnOracle As New ADODB.Connection
Dim cmOracle As New ADODB.Command
Dim rsData As New ADODB.Recordset
Dim sID As String
Dim para As New ADODB.Parameter
Dim para2 As New ADODB.Parameter
Dim para3 As New ADODB.Parameter

Dim dDtFrom As Date
Dim dDtTo As Date

dDtTo = Date
dDtFrom = #1/1/2002#
sID = "000000000000087"

cnOracle.ConnectionString = "Provider=MSDAORA;User ID=hcc;password=memo2114;Data Source=PROD;Persist Security Info=true"
cnOracle.Open
cmOracle.ActiveConnection = cnOracle
cmOracle.CommandText = "mma_types.plan_events"
cmOracle.CommandType = adCmdStoredProc
Set para = cmOracle.CreateParameter("v_sorgid", adVarChar, adParamInput, 20, sID)
Set para2 = cmOracle.CreateParameter("v_dtfrom", adDate, adParamInput, , dDtFrom)
Set para3 = cmOracle.CreateParameter("v_dtto", adDate, adParamInput, , dDtTo)

cmOracle.Parameters.Append para
cmOracle.Parameters.Append para2
cmOracle.Parameters.Append para3

Set rsData = cmOracle.Execute

Set Me.MSHFlexGrid1.DataSource = rsData

End Sub

 

[slan]

thanks olichap. I finally got it to work. I found some info at

http://www.vbip.com/books/1861003927/chapter_3927_16.asp

I just have to change the connection string to the following.

cnOracle.ConnectionString = "Provider=OraOLEDB.Oracle;User ID=hcc;password=memo2114;Data Source=PROD;PLSQLRSet=1"

Also, in the command object, I omitted out
cmOracle.CommandType = adCmdStoredProc and in the
cmOracle.CommandText I use "{call mma_types.plan_events}".
It's basically the same as what you have with a little slight change to make it work for me.

Again, thanks for your help.

slan