ExecuteScalar error
390104Mar 14 2003 — edited Mar 20 2003I'm getting the following error when trying to retrieve a single value from a query using the ExecuteScalar method of the ODP.Net provider:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
--------------------------------------------------------
The code I'm executing is as follows (set up command object):
Me.cmdOracleCVOrdStatdlyRptActBAT1.CommandText = "FIBERNET_LOOKUP_TABLES.BAT_CKT_REPORTID"
Me.cmdOracleCVOrdStatdlyRptActBAT1.Connection = Me.cnOracleCVRpts
Me.cmdOracleCVOrdStatdlyRptActBAT1.CommandType = CommandType.StoredProcedure
.
. 'set up command parameters
.
cmdOracleCVOrdStatdlyRptActBAT1.Parameters.Add("BAT_TYPE", OracleDbType.Varchar2, 2, strBatType, ParameterDirection.Input)
cmdOracleCVOrdStatdlyRptActBAT1.Parameters.Add("ID_REPORT", OracleDbType.Varchar2, 10, ParameterDirection.Output)
.
. 'execute the stored proc
.
If cnOracleCVRpts.State = ConnectionState.Closed Then
cnOracleCVRpts.Open()
End If
cmdOracleCVOrdStatdlyRptActBAT1.ExecuteScalar()
strReportID = cmdOracleCVOrdStatdlyRptActBAT1.Parameters("ID_REPORT").Value
It blows up on the bold-faced line above. I have a valid connection at the time because it is opened up before I execute the command.
--------------------------------------------------------
My stored procedure is as follows:
procedure BAT_CKT_REPORTID(BAT_TYPE IN VARCHAR2, ID_REPORT OUT VARCHAR2) is
PVT_BAT_TYPE VARCHAR2(2);
PVT_ID_REPORT VARCHAR2(10);
begin
PVT_BAT_TYPE := BAT_TYPE;
PVT_ID_REPORT := ID_REPORT;
SELECT MAX(DISTINCT ID_REPORT)
INTO PVT_ID_REPORT
FROM RPT_CIRCUIT
WHERE ID_REPORT LIKE PVT_BAT_TYPE || '%' || 'B'
ORDER BY ID_REPORT;
ID_REPORT := PVT_ID_REPORT;
end BAT_CKT_REPORTID;
---------------------------------------------------------
When I test the stored procedure manually via PL/SQL developer, I get the following value back from the OUTPUT variable (ID_REPORT): CO031203B
which is correct.
When I try and execute this same exact stuff with the above code from VisualStudio.Net, I get the "ORA-06502" error...
I also use ExecuteScalar from another non-ORACLE OLEDB database which works fine via code...
Does anyone know how I can resolve this error???
Thanks,
Bill........