Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2003
    Location
    UK(berkshire)
    Posts
    11

    Unanswered: Get SP parameters ADO OLEBE oracle

    Hi

    I am using MDAC 2.7 with the Oracle OLEDB provider 9.2.0.2.0(wierd oracle version number)

    Using VB and SQL server I managed to return a list of the parameters for a stored procedure. Great the results that I was after.

    Using the attached file source below
    --------------------------------------------------------------------
    Private Sub Command1_Click()
    On Error GoTo e:
    Dim sConnString As String
    Dim oConn As ADODB.Connection
    Dim oCMD As ADODB.Command
    Dim oParams As ADODB.Parameters
    Dim oParam As ADODB.Parameter

    'SQL Server Connection
    'sConnString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=bosphorus;DATABASE=northwind;UID=us ername;PWD=password;"

    'ORACLE CONNECTION STRING
    sConnString = "Provider=OraOLEDB.Oracle;User ID=username;Password=password;Data Source=DS;PLSQLRSet=1;"




    Set oConn = New ADODB.Connection
    Set oCMD = New ADODB.Command
    oConn.open sConnString

    oCMD.ActiveConnection = oConn

    'stored proc for sql server northwind
    'oCMD.CommandText = "SalesByCategory"

    'stored proc for oracle db
    oCMD.CommandText = "pkgAssociation.spAddTerms"


    oCMD.CommandType = adCmdStoredProc
    oCMD.Parameters.Refresh
    Set oParams = oCMD.Parameters
    For Each oParam In oParams
    Text1.Text = Text1.Text & oParam.Name & vbCrLf
    Next

    e:
    If Err.Number <> 0 Then
    Text1.Text = "err number: " & Err.Number & " : " & Err.Source & " : " & Err.Description
    End If

    End Sub

    --------------------------------------------------------------------

    WhenI changed the provider to oracle OLE DB I get the following error..

    err number: -2147217839 : OraOLEDB : Provider cannot derive parameter information and SetParameterInfo has not been called.

    Is it possible to return a list of expected parameters to an oracle SP ?
    Attached Files Attached Files
    Thanks

    youeee

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •