Hi
  Iam encountering a strange problem when i call an oracle stored procedure from visual basic 6.0 using ADO. Iam getting the following two errors for the same action. The error number and description are as follows:

-2147467259 Data type is not supported.
and sometimes this one:
-2147467259 ORA-01036: illegal variable name/number

The stored procedure definition is as follows:

Create or replace PACKAGE TestPack AS
   TYPE T1 IS REF CURSOR;
END TestPack;

PROCEDURE Testsp(lDocid Number,Ref_Cur_1 IN OUT TestPack.T1)
AS
BEGIN
        BEGIN
                OPEN Ref_Cur_1 FOR
                SELECT Doc_Id,Note_id,NoteDet from mytable where DOC_ID = lDocid
;
        END;
END Testsp;

The visual basic code calling the above oracle stored procedure is :

    On Error GoTo errtrap
    Dim adCon As ADODB.Connection
    Dim adRS As New ADODB.Recordset
    Dim adCmd As New ADODB.Command

    Dim iParameterCount As Integer
    Dim iCtr As Integer
    Dim tSql As String
    Dim MyPram As ADODB.Parameter
    Set adCon = New ADODB.Connection
    adCon.Open "File Name=c:\Oraudl.udl"

    adCmd.CommandText = "testsp"

    adCmd.ActiveConnection = adCon
    adCmd.CommandType = adCmdStoredProc

    Set MyPram = adCmd.CreateParameter("LDOCID", adInteger, adParamInput)

    MyPram.Value = 2
    adCmd.Parameters.Append MyPram
    Set adRS = adCmd.Execute ========>ERROR
    'adCmd.Execute
    'MsgBox adCmd.Parameters(2), vbInformation
    Do While adRS.EOF
        For iCtr = 1 To adRS.Fields.Count
            tSql = adRS(iCtr) & " "
        Next
        MsgBox tSql, vbInformation
    Loop

    adRS.Close
    adCon.Close

    Exit Sub
errtrap:
    MsgBox Err.Number & " " & Err.Description

The line indicated with "========>ERROR" is giving the error.

What is that iam doing wrong there. Can anybody please help. please mail the solution to "[email protected]"

thanx in advance
Sridhar

- Is the procedure defined in the package or outside?
- Is there a reason that the REF CURSOR parameter is defined as IN OUT rather than OUT?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

Hi
  Thanx for ur reply.
  The procedure is defined outside the package. only the refcursor variable is defined inside the package.
No specific reason for defining it as an IN OUT parameter.
My main aim is to return a recordset kind of data from the stored procedure. This stored procedure must be able to acept in and out parameters.
If there is any other way of achieving the this result, it would definitely serve the prurpose.

thanx
Sridhar

Hi
  this is in continuation to my above reply.
   tried with only OUT parameter for the ref cursor variable.
  but still the same error.

I believe you need to have the procedure as part of the package, not as a standalone.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

Hi
  I tried with the procedure declared in side the package, but still the same error is coming.
Now the error that is coming most often is "Data type is not supported"

thanx
Sridhar

Hi
a bite late ;)
It's not necessary to add the 2nd parameter for the recordset!

byoliver