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

    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.



  2. #2
    Guy Smith Guest

    Re: Select * from ... in Procedure


    "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



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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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