Problem With Varchar2 Parameter in ASP.NET

[Robert E. Flaherty]

I am using System.Data.OracleClient (Oracle Provider for OLE DB 9.2.0.4.0)
in an ASP.NET 1.1 app. I am evoking a stored procedure with a number of
input parameters and two output parameters, both declared
ParametrerDirection.InputOutput. The first of these two output parameters
is an integer value. It is the status of the running of the stored
procedure. It is working fine.

The second parameter is designed to return a message from the stored
procedure like "Row has been updated" or "Row could not be found". This has
been declared as a varchar2 variable in the stored procedure and a
OracleType.VarChar in the ASP.NET.

If I do not initialize the ADO parameter for this second output parameter to
a large number of spaces, the ASP.NET app blows up when executing the
cmd.ExecuteOracleScalar(). When I do initialize with a large number of
spaces, ASP.NET does not blow up, the stored procedure run successfully but
the value property of this parameter is still set to the large number of
spaces instead of the message being generated by the stored procedure.

[Paul Glavich [MVP ASP.NET]]

Have you tried explicitly setting the OracleParameter object to the correct
size/length? eg. oracleParameter.Size = 40;

BTW, what exactly does "blow up" mean?
--

- Paul Glavich
ASP.NET MVP
ASPInsider (www.aspinsiders.com)
"Robert E. Flaherty" <Bo**@TimeVision.Com> wrote in message
news:u4**************@TK2MSFTNGP14.phx.gbl...

I am using System.Data.OracleClient (Oracle Provider for OLE DB 9.2.0.4.0)
in an ASP.NET 1.1 app. I am evoking a stored procedure with a number of
input parameters and two output parameters, both declared
ParametrerDirection.InputOutput. The first of these two output parameters
is an integer value. It is the status of the running of the stored
procedure. It is working fine.

The second parameter is designed to return a message from the stored
procedure like "Row has been updated" or "Row could not be found". This has been declared as a varchar2 variable in the stored procedure and a
OracleType.VarChar in the ASP.NET.

If I do not initialize the ADO parameter for this second output parameter to a large number of spaces, the ASP.NET app blows up when executing the
cmd.ExecuteOracleScalar(). When I do initialize with a large number of
spaces, ASP.NET does not blow up, the stored procedure run successfully but the value property of this parameter is still set to the large number of
spaces instead of the message being generated by the stored procedure.

[Robert E. Flaherty]

I have tried setting the parameter.size. When I set it to 255 and set the
value to " ", I get the following error message in the ASP.NET app:

Server Error in '/NGIT_Parameters' Application.
ORA-06502: PL/SQL: numeric or value error ORA-06512: at
"NG.SSYNC_NG_UPDATE_TMGRS", line 92 ORA-06512: at "NG.SSYNC_NG_MGT_TMGRS",
line 50 ORA-06512: at line 1

When I set the parameter to a large number of spaces, it does not blow up
but on inspection of the parameter.value after the execution, it is still
equal to those spaces.

"Paul Glavich [MVP ASP.NET]" <gl**@aspalliane.com-NOSPAM> wrote in message
news:er**************@TK2MSFTNGP10.phx.gbl...

Have you tried explicitly setting the OracleParameter object to the
correct
size/length? eg. oracleParameter.Size = 40;

BTW, what exactly does "blow up" mean?
--

- Paul Glavich
ASP.NET MVP
ASPInsider (www.aspinsiders.com)
"Robert E. Flaherty" <Bo**@TimeVision.Com> wrote in message
news:u4**************@TK2MSFTNGP14.phx.gbl...

I am using System.Data.OracleClient (Oracle Provider for OLE DB
9.2.0.4.0)
in an ASP.NET 1.1 app. I am evoking a stored procedure with a number of
input parameters and two output parameters, both declared
ParametrerDirection.InputOutput. The first of these two output
parameters
is an integer value. It is the status of the running of the stored
procedure. It is working fine.

The second parameter is designed to return a message from the stored
procedure like "Row has been updated" or "Row could not be found". This

has

been declared as a varchar2 variable in the stored procedure and a
OracleType.VarChar in the ASP.NET.

If I do not initialize the ADO parameter for this second output parameter

to

a large number of spaces, the ASP.NET app blows up when executing the
cmd.ExecuteOracleScalar(). When I do initialize with a large number of
spaces, ASP.NET does not blow up, the stored procedure run successfully

but

the value property of this parameter is still set to the large number of
spaces instead of the message being generated by the stored procedure.