Steps to Reproduce Behavior

1. On your Oracle server, run the following data definition language (DDL) script:

   DROP TABLE TEST;
    
   CREATE TABLE TEST ( 
     INCOMEEVENTPAYABLEDATEFREQSPA   VARCHAR2 (50), 
     INCOMEEVENTPAYABLEDATEFREQSPAN  VARCHAR2 (50));
   
   insert into test values('Twenty-Nine Chars','Thirty Chars');
   					

2. On your Oracle server, create the following package:

   CREATE OR REPLACE package test as
    
         TYPE TEST_REC_TYP IS RECORD(
          IncomeEventPayableDateFreqSpa TEST.IncomeEventPayableDateFreqSpa%TYPE,
          IncomeEventPayableDateFreqSpan TEST.IncomeEventPayableDateFreqSpan%TYPE);
    
         TEST_REC    TEST_REC_TYP;
   
       TYPE TEST_CUR
                 IS REF CURSOR RETURN TEST_REC%TYPE;
    
         TYPE Spa_TABLE                     IS TABLE OF
   TEST.IncomeEventPayableDateFreqSpa%TYPE
           INDEX BY BINARY_INTEGER;
    
         TYPE span_TABLE               IS TABLE OF
   TEST.IncomeEventPayableDateFreqSpan%TYPE
           INDEX BY BINARY_INTEGER;
    
   PROCEDURE SP_GET_INCOME_EVENTS_FOR_UPD(
          IncomeEventPayableDateFreqSpa            OUT SPA_TABLE,
          IncomeEventPayableDateFreqSpan     OUT SPAN_TABLE);
    
   end test;
   / 
   					

3. On your Oracle server, create the following package body:

   CREATE OR REPLACE PACKAGE BODY TEST AS
    
   PROCEDURE SP_GET_INCOME_EVENTS_FOR_UPD(IncomeEventPayableDateFreqSpa   
      OUT SPA_TABLE,IncomeEventPayableDateFreqSpan OUT SPAN_TABLE ) IS
         V_INDX      NUMBER DEFAULT 1 ;
         C1          TEST_CUR;
   BEGIN
          OPEN C1 FOR
          SELECT * FROM TEST;
          LOOP
               FETCH C1 INTO IncomeEventPayableDateFreqSpa(V_INDX),
   IncomeEventPayableDateFreqSpan(V_INDX);
               EXIT WHEN (C1%NOTFOUND) OR (C1%NOTFOUND IS NULL);
               V_INDX := V_INDX + 1;
          END LOOP;
   END SP_GET_INCOME_EVENTS_FOR_UPD;
   END TEST;
   / 
   					

4. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
5. On the Project menu, click References, and then select the Microsoft ActiveX Data Objects 2.x Library check box.
6. Add a Command button to Form1, and then paste the following code into the Code window of Form1:

   Private Sub Command1_Click()
       Dim cn As New ADODB.Connection
       Dim cmd As New ADODB.Command
       Dim sqlcmd As String
   
       sqlcmd = "{call test.SP_GET_INCOME_EVENTS_FOR_UPD" _
          & ({resultset 5000, IncomeEventPayableDateFreqSpa, IncomeEventPayableDateFreqSpan})}"
               
       cn.CursorLocation = adUseClient
   
       cn.Open "Provider=MSDAORA.1;Password=mypwd;User ID=myuid;Data Source=myserver"
       'cn.Open "dsn=oracle;Pwd=mypwd;uid=myuid;"
   
       cmd.CommandText = sqlcmd
       cmd.ActiveConnection = cn
       cmd.Execute
   
       MsgBox ("Test Complete")
   End Sub
   					

7. Modify the ADO connection string as appropriate for your environment.
8. Run the project, and then click the command button. Notice that data appears in the Immediate window. When the command is run, you receive the above-mentioned error message.
9. To work around this problem, stop the project, and then change the connection so that it uses ODBC. When you run the project again, you do not receive the error message.

Back to the top