ADO,Oracle Stored Procedure and Recordset

i want make a stored procedure in oracle8 which can return a recordset,
i also want to use the recordset by ADO,could someone tell me how to
realize it?

thank you.

[FWALGMan]

The trickiest part is getting the right Microsoft Driver for the job. Certain drivers dont work well with Oracle. The best driver I have found is Microsoft ODBC Driver for Oracle (MSORCL32). The only way I have found in Oracle to return a recordset to ADO is by using Oracle tables. Here is an example. It creates a package and stored proc and returns a recordset with two columns. There is good information on this in of all places Microsofts MSDN website:

CREATE OR REPLACE PACKAGE import.HOWLMARSCRIPTS AS

TYPE t_id is TABLE of NUMBER
INDEX BY BINARY_INTEGER;

TYPE t_JobTitle is TABLE of VARCHAR2(10)
INDEX BY BINARY_INTEGER;

TYPE t_Description is TABLE of VARCHAR2(100)
INDEX BY BINARY_INTEGER;

PROCEDURE ReturnRecordSet(ReturnCode IN OUT NUMBER,
JobTitle OUT t_jobTitle,
Description OUT t_description
);


END HOWLMARSCRIPTS;
/


CREATE OR REPLACE PACKAGE BODY import.HOWLMARSCRIPTS AS

PROCEDURE ReturnRecordSet(ReturnCode IN OUT NUMBER,
JobTitle OUT t_jobTitle,
Description OUT t_description
)
IS

i NUMBER DEFAULT 1;

CURSOR fdJobTitleInfo IS
SELECT Jobtitle, description
FROM import.fdjobtitle;


BEGIN

IF NOT fdJobTitleInfo%ISOPEN THEN -- open the cursor if
OPEN fdJobTitleInfo; -- not already open
END IF;

LOOP

FETCH fdJobTitleInfo INTO jobTitle(i),Description(i);
EXIT WHEN (fdJobTitleInfo%NOTFOUND);
i := i + 1;

END LOOP;

CLOSE fdJobTitleInfo;

-- Success!

returncode := 0;

EXCEPTION

WHEN OTHERS THEN

CLOSE fdJobTitleInfo;
RAISE;
returncode := -1;

END ReturnRecordSet;

END HOWLMARSCRIPTS;
/

thank you for your suggestion!
i also donot know how to set the parameter of SP when i call the SP
in VB source code,could you tell me more a little? thank you!

[FWALGMan]

The basics are as follows in this ADO Subroutine. This has one parameter and returns a resultset of up to 100,000 rows. This is a limitation in the driver that you have to specify the max number of rows to return. If you have any further questions I will be glad to answer them, or you can search msdn.microsoft.com on Oracle Recordsets for more details:

Private Sub cmbShipNumEnd_DropDown()

Dim sSQL As String
Dim CPw1 As ADODB.Command
Dim Rs As ADODB.Recordset

On Error GoTo Err_cmbShipNumEnd_DropDown

sSQL = "{call " & gsProteanSchema & "MPMSCRIPTS.MPMShipDocNumEnd (?,{resultset 100000, outShipNum})}"

Set CPw1 = New ADODB.Command
CPw1.ActiveConnection = goRDOConnection
CPw1.CommandText = sSQL
CPw1.CommandType = adCmdText

' Set Values for In Parameters

CPw1.Parameters(0).Value = cmbShipNumStart.Text

' Open the resultset based on the stored procedures and populate the
' combobox.

Set Rs = New ADODB.Recordset
Rs.CursorType = adOpenStatic

Set Rs = CPw1.Execute

cmbShipNumEnd.Clear
cmbShipNumEnd.AddItem ""

While Not Rs.EOF

If Not IsNull(Rs(0).Value) Then

cmbShipNumEnd.AddItem Rs(0).Value

End If
Rs.MoveNext

Wend

' Close the query and resultset objects to free up memory

Rs.Close
Set Rs = Nothing

Set CPw1 = Nothing

Exit Sub

Err_cmbShipNumEnd_DropDown:

' Clean up any possible open objects on errors

If Rs.State = adStateOpen Then

Rs.Close
Set Rs = Nothing

End If

Set CPw1 = Nothing

MsgBox "ERROR: " & Err.Number & " (cmbShipNumEnd.DropDown) " & Err.Description, vbInformation, "Print Shipping Documents"

End Sub

hello
your suggestion has helped me very much,thank you.
could help me with another question?

this is a stored procedure:

CREATE OR REPLACE PACKAGE BODY sp1
AS
PROCEDURE sp
(INCode IN OUT NUMBER,
pCD IN VARCHAR2(4) )
IS
CURSOR customer IS
SELECT ...
FROM...
where... ;
BEGIN
FOR singlecustomer IN customer
....

now i want to define the cursor customer which the sql statement
is depend on the process of the parameter 'incode' and 'pcd',
but the cursor has to be declared in the head of procedure...

thank you again!

[FWALGMan]

Sure not a problem. The Oracle Documentation has alot of information about Dynamic Stored Procedures. Here is a sample one though. Hope this helps. The Oracle 8 Web Page Documentation is very good for this kind of stuf:

-- Dynamic SQL Cursor for Retrieving Print TareWeight Field

source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor, 'SELECT ' || FIELDTAREWEIGHT || ' FROM ' || PRINTFIELDTABLE ||
'PF, fdAddon fdA WHERE PF.ObjectID = fdA.ObjectID
AND fdA.PARENTOBJECTID = ' || SOLDTOOBJECTID, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, PRINTTAREWEIGHT);

ignore := DBMS_SQL.EXECUTE(source_cursor);

IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN -- get column values of the row

DBMS_SQL.COLUMN_VALUE(source_cursor, 1, PRINTTAREWEIGHT);

ELSE

raise_application_error(-20101, 'DATA INTEGRITY - Cannot find Measurement Options for the trading partner ' || SOLDTO);

END IF;

DBMS_SQL.CLOSE_CURSOR(source_cursor);

EXCEPTION


WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(source_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF;

IF inShippingLine%ISOPEN THEN
CLOSE inShippingLine;
END IF;

RAISE;


END;

Create package pkname as
typr t_table is of varchar2(10) indexed by binary_integer
function p1(r t_table) return t_table;
end ;
create Package body pkname as
Function p1(t in out t_table) return t_table as
cursor c1 is select empname from emp;
i Number
begin
for i in c1
Loop
t(i) = c1.ename
i = i + 1
end loop
return t;
End p1
end;

If I am calling this procedure in Vb what is the parameter i must pass.
Because in Vb we don't have anything like Table.
So how do U proceed.