Select * from ... in Procedure

[Richard MARCHAND]

Hi,

I would create a procedure or a function that return the result
of a "select * from employe" in Oracle.

How can I do, because after I would open this procedure
with the ado component (Microsoft)

Please help me,
In advance thank you.

[Guy Smith]

"Richard MARCHAND" <r.marchand@specinov.fr> wrote:
>Hi,
>
>I would create a procedure or a function that return the result
>of a "select * from employe" in Oracle.
>
>How can I do, because after I would open this procedure
>with the ado component (Microsoft)
>
>Please help me,
>In advance thank you.

This example uses the "SCOTT" schema and the "emp" table in it, as provided
by the oracle samples. This example is done using Oracle 8.1.7.

Within oracle you need to define a ref cursor type within a package.
-----------------
CREATE OR REPLACE PACKAGE scott.mycursors
AS
TYPE t_refcursor IS REF CURSOR;
END mycursors;
-----------------

Create a procedure that defines an OUT parameter as the REF CURSOR type defined
in the package.
-----------------
CREATE OR REPLACE PROCEDURE scott.return_recordset( cur_employees OUT mycursors.t_refcursor)
AS
BEGIN

OPEN cur_employees FOR SELECT * FROM EMP;

END return_recordset;
-----------------

Always place the OUT ref cursors types as the last parameter(s) of the procedure
( if the procedure also has IN parameters)... You can return more than on
OUT ref cursor.

In VB you need to use the OraOLEDB provider. I am using ADO 2.6.

-------------------
Private Sub Command1_Click()

Dim objError As ADODB.Error
Dim conn As ADODB.Connection
Dim sconn As String
Dim sSQL As String
Dim rec As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter

On Error GoTo err_hndl

'Open connection to Oracle
Set conn = New ADODB.Connection
With conn
.Provider = "OraOLEDB.Oracle"
.ConnectionString = "Data Source=ORADB;User ID=scott;Password=tiger;"
.CursorLocation = adUseClient
.Open
End With

'Create the command to execute
Set cmd = New ADODB.Command

With cmd

.ActiveConnection = conn
' .CommandType = adCmdStoredProc '* will not work if set this way!

' You need to use the ODBC escape sequence to call stored procedures
' that return ref cursors. Note that the out ref cursor type is not
' included as a parameter. As an example, if procedure has two
' input parameters and an OUT ref cursor, the escape sequence would be
' {CALL myprocedure(?,?)}

.CommandText = "{CALL return_recordset}" 'the procedure name goes here.
'this procedure has no inputs hence only the name is used

' Enable PLSQLRSet property -- this is needed to return pl/sql ref cursors
.Properties("PLSQLRSet") = True

Set rec = .Execute

'Disable PLSQLRSet property -- always disable it immediately after executing
.Properties("PLSQLRSet") = False

End With

'lets display the results

Dim strRow As String
Dim i As Integer

While Not rec.EOF
strRow = ""
For i = 0 To rec.Fields.Count - 1
strRow = strRow & rec.Fields(i) & ", "
Next i
Debug.Print strRow
rec.MoveNext
Wend

'* if more than one ref cursor is returned, to get the next ones using

'Set Recordset2 = Recorset1.NextRecordset
'Set Recordset3 = recordset1.NextRecorset
'etc...


Exit Sub

err_hndl:
MsgBox Err.Number & " " & Err.Description, , "err object"

If conn.Errors.Count > 0 Then
For Each objError In conn.Errors
MsgBox objError.Number & " " & objError.Description, , "ado
error"
Next
End If


End Sub
-----------------

That should work for you. If you have difficulties post them here. I'll
try to help. You should also read the OraOLEDB provider documentation as
provided by oracle. It has a wealth of information and samples.

Guy