Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ExecuteScalar error

390104Mar 14 2003 — edited Mar 20 2003
I'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........
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 17 2003
Added on Mar 14 2003
4 comments
807 views