Select * from ... in Procedure

DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Select * from ... in Procedure

  1. #1
    Richard MARCHAND Guest

    Select * from ... in Procedure


    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.

  2. #2
    Guy Smith Guest

    Re: Select * from ... in Procedure

    "Richard MARCHAND" <> wrote:
    >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
    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)

    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
    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

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

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

    Exit Sub

    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
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center