Board index Database Programming

How to retrieve recordsets from Oracle Stored Procs Using ADO

How to retrieve recordsets from Oracle Stored Procs Using ADO

Post by Nancy N » Wed, 27 Jan 1999 04:00:00



        I have to pass a parameter to the Oracle Stored Procedure and
retrieve a recordset back. I am now using VB6 ADO, through MS OLE DB for
Oracle, to Oracle 7. Do anybody know how to retrieve recordsets from
Oracle Stored Procedure using ADO? Or is this possible?

I tried the following in VB and error
**************************************************
Private Sub Form_Load()
    gFConnectOracle
    Dim Qy As New ADODB.Command
    Dim Parm As New ADODB.Parameter

    Set Qy.ActiveConnection = gCnOracle

    Set Parm = Qy.CreateParameter(, adInteger, adParamInput)
    Qy.Parameters.Append Parm
    Parm.Value = 555662222

    Qy.CommandType = adCmdStoredProc
    Qy.CommandText = "packperson.oneperson"

    Set rs = Qy.Execute(showrows)
End Sub

**************************************************
    'Run-time error '-2147217900 (80040e14)':
    'ORA-06550: line 1, column 7:
    'PLS-00306: wrong number or types of arguments in call to
'ONEPERSON'
    'ORA-06550: line 1, column 7:
    'PL/SQL: Statement ignored
**************************************************
The Oracle Procedure named oneperson, in Package named packperson, with
parameter
        (onessn  IN    NUMBER,
         ssn     OUT   tssn,
         fname   OUT   tfname,
         lname   OUT   tlname)
**************************************************

Please Help!

 
 
 
Top

How to retrieve recordsets from Oracle Stored Procs Using ADO

Post by Dale Side » Wed, 27 Jan 1999 04:00:00


Get the book Oracle Programming with Visual Basic from Sybex.  It walks
through it step by step.

>        I have to pass a parameter to the Oracle Stored Procedure and
>retrieve a recordset back. I am now using VB6 ADO, through MS OLE DB for
>Oracle, to Oracle 7. Do anybody know how to retrieve recordsets from
>Oracle Stored Procedure using ADO? Or is this possible?

>I tried the following in VB and error
>**************************************************
>Private Sub Form_Load()
>    gFConnectOracle
>    Dim Qy As New ADODB.Command
>    Dim Parm As New ADODB.Parameter

>    Set Qy.ActiveConnection = gCnOracle

>    Set Parm = Qy.CreateParameter(, adInteger, adParamInput)
>    Qy.Parameters.Append Parm
>    Parm.Value = 555662222

>    Qy.CommandType = adCmdStoredProc
>    Qy.CommandText = "packperson.oneperson"

>    Set rs = Qy.Execute(showrows)
>End Sub

>**************************************************
>    'Run-time error '-2147217900 (80040e14)':
>    'ORA-06550: line 1, column 7:
>    'PLS-00306: wrong number or types of arguments in call to
>'ONEPERSON'
>    'ORA-06550: line 1, column 7:
>    'PL/SQL: Statement ignored
>**************************************************
>The Oracle Procedure named oneperson, in Package named packperson, with
>parameter
>        (onessn  IN    NUMBER,
>         ssn     OUT   tssn,
>         fname   OUT   tfname,
>         lname   OUT   tlname)
>**************************************************

>Please Help!


 
 
 
Top

How to retrieve recordsets from Oracle Stored Procs Using ADO

Post by Yazid Arezk » Wed, 27 Jan 1999 04:00:00


Hello

1    Do you know which line it fails in your VB Program ?
2   Can you try this, may be the order matters and don't you give a name to
your Command ?

With Qy
     Set .ActiveConnection = gCnOracle
    .CommandType = adCmdStoredProc
    .CommandText = "packperson.oneperson"
    Set Parm = .CreateParameter("onessn", adInteger, adParamInput,555662222)
    .Parameters.Append Parm
    Set rs = Qy.Execute
end

I have noticed that your input value onessn and declared as adInteger and
your value is much bigger that an integer.

3   Does your stored procedure runs Oracle ?

Regards

Dr Y Arezki
MCP


>        I have to pass a parameter to the Oracle Stored Procedure and
>retrieve a recordset back. I am now using VB6 ADO, through MS OLE DB for
>Oracle, to Oracle 7. Do anybody know how to retrieve recordsets from
>Oracle Stored Procedure using ADO? Or is this possible?

>I tried the following in VB and error
>**************************************************
>Private Sub Form_Load()
>    gFConnectOracle
>    Dim Qy As New ADODB.Command
>    Dim Parm As New ADODB.Parameter

>    Set Qy.ActiveConnection = gCnOracle

>    Set Parm = Qy.CreateParameter(, adInteger, adParamInput)
>    Qy.Parameters.Append Parm
>    Parm.Value = 555662222

>    Qy.CommandType = adCmdStoredProc
>    Qy.CommandText = "packperson.oneperson"

>    Set rs = Qy.Execute(showrows)
>End Sub

>**************************************************
>    'Run-time error '-2147217900 (80040e14)':
>    'ORA-06550: line 1, column 7:
>    'PLS-00306: wrong number or types of arguments in call to
>'ONEPERSON'
>    'ORA-06550: line 1, column 7:
>    'PL/SQL: Statement ignored
>**************************************************
>The Oracle Procedure named oneperson, in Package named packperson, with
>parameter
>        (onessn  IN    NUMBER,
>         ssn     OUT   tssn,
>         fname   OUT   tfname,
>         lname   OUT   tlname)
>**************************************************

>Please Help!

 
 
 
Top

1. Retrieve Recordsets from Oracle 8i Stored Procs

Has anyone had success retrieving resultsets from oracle stored
procedures with multiple input parameters.

TIA

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

2. How to refer treeviews items/nodes

3. Returning a shaped ADO.Recordset using Stored Procs

4. Groups

5. US-VA-VIRGINIA BEACH, VA- DEVELOPER 2000 SKILLS NEEDED

6. retrieve recordsets from Oracle using ADO

7. Still with my instantiation problem...

8. passing UDT into Oracle Stored Procs using ADO

9. Problem using VB to retrieve ASP/XML using ADO recordset


All times are UTC
Board index