Older VB6 Application Using ADODB Error When Calling Oracle Stored Procedure

Question

This should be related to the provider in some way, because it works fine in my dev block, but does not work in another dev block.

Here is the error I get from a non-working dev:

ORA-00604: error at recursive SQL level 1

ORA-06502: PL / SQL: numeric or significant error: character string buffer too low

ORA-06512: on line 26

ORA-06550: row 1, column 7:

PLS-00306: Incorrect number or types of arguments when calling 'LISTAVAILSUBMISSIONS'

ORA-06550: row 1, column 7:

PL / SQL: expression 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 result set of records

Calling 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 disgusting part is working on one machine, and not another. I use msdaora.1 as a provider, and I verified that both machines have the same version of MDAC using MS CompChecker. One thing I discovered is that if I switch my working computer to use OraOLEDB instead of msdaora.1, it will then throw the same error. Based on this, I begin to think that a non-working computer shows the correct behavior and that I need to fix the code.

I have done quite a bit of research, and I think this is due to the out out parameter and is trying to set ADODB.Recordset equal to the parameter. I tried to change the parameter only, but it did not help, I still got the same error.

Any help is appreciated, this mistake infuriates me.

Answer 1

I think one of the rows in your table exceeds the OLEDB row limit. This limit is defined differently for different drivers - so you see one driver giving an error, and the other is working. This limit, I believe, can also be configured in stages, so you have one machine and another not.

Make sure that any line in your result set is larger than, say, 256 characters or something really long. Then omit this entry from the result set to see if it works.