Old VB6 App using ADODB throwing error when calling Oracle Stored Procedure

This has to be provider related in some way开发者_如何学JAVA because it works fine on my dev box but doesn't work on another dev box.

Here is the error I'm getting on the non-working dev box:

ORA-00604: error occurred at recursive SQL level 1

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 26

ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'LISTAVAILSUBMISSIONS'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Here is the Oracle Procedure:

    Procedure ListAvailSubmissions (avail_submission in out rc_avail_submission)
is
Begin
     open avail_submission for
          select submission_id from nais_submissions
          where condition = 'ONLINE'
          and status in ('ACTIVE','LOGGED')
          order by submission_id desc;
Exception
    When no_data_found then
         v_output := utl_file.fopen (v_errdir, v_errLog, 'a');
         utl_file.put_line(v_output, to_char(sysdate,'HH24:MI:SS')||'-'||'ListAvailSubmission:Sub_id: No Data Found');
         utl_file.fclose(v_output);

    When others then
         v_error_code := sqlcode;
         v_error_message := substr (sqlerrm, 1, 300);
         v_output := utl_file.fopen (v_errdir, v_errLog, 'a');
         utl_file.put_line(v_output, to_char(sysdate,'HH24:MI:SS')||'-'||'ListAvailSubmission:Sub_id:'|| v_error_code ||':'|| v_error_message);
         utl_file.fclose(v_output);

End ListAvailSubmissions;

As you can see, the only parameter is the return parameter which is the resulting recordset

The call from VB is pretty simple.

Public Function GetTestRequests() As ADODB.Recordset
          Dim rsADO As New ADODB.Recordset
          Dim cmdCommand As New ADODB.Command

          Set cmdCommand.ActiveConnection = cnnADO //Ive already verified the connection is good
          cmdCommand.CommandText = "ListAvailSubmissions"
          Set rsADO = cmdCommand.Execute(, , adCmdStoredProc)
          Set GetTestRequests = rsADO

End Function

The frustrating part is the it works on one machine and not another. I'm using msdaora.1 as the provider and I've verified both machines have the same MDAC version using MS CompChecker tool. One thing I did discover is that if I switch the working machine to using OraOLEDB instead of msdaora.1, it will then throw the same error. Based on that I'm starting to think that the non-working machine is exhibiting the correct behavior and that I need to fix the code.

I have done quite a bit of research and I'm thinking it has to do with the in out parameter and trying to set an ADODB.Recordset equal to the parameter. I tried changing the parameter to out only but that didn't help, still got the same error.

Any help is appreciated, this error is driving me nuts.

I think one of the strings in your table exceeds the OLEDB string limit. This limit is defined differently for different drivers -- so you see one driver giving error and another works. This limit, I believe, can also be configured on a machine-by-machine basis, thus you have one machine working and one doesn't.

Check if any string in your result set is longer than, say, 256 characters or something really long. Then omit that record from your result set to see if it works.