Hi,

I have a .NET application which gets back a CLOB from the oracle
database. I'm using OracleParameter("name", OracleType.Clob) for the
parameter spec. We recently created a new Oracle AL32UTF8 unicode
database, and I'm trying to run the app against the database, however
I'm getting the ORA-06512 exception. The application works fine
against a non-unicode database. The OUT variables in the stored
procedure are defined as CLOB, and actaully when I call the stored
procedure out of PL/SQL Developer the CLOB come back fine. Is there
anything that I need to do in the .NET app to get to work with
unicode?

On Feb 6, 2:55*am, stas1... (AT) msn (DOT) com wrote:
ORA-6512 is error stack backtrace message (that is, it's being output
for each error stack entry as the error stack of an unhandled
exception that was raised in a stored procedure unwinds.) What's the
exception itself? Can you provide full error stack and relevant PL/SQL
code where it's being raised?

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

On Feb 7, 2:50*am, "Vladimir M. Zakharychev"
<vladimir.zakharyc... (AT) gmail (DOT) com> wrote:
Vladimir,

Here is the full error:

ORA-06512: at "SA.PKG_TEST", line 963
ORA-06512: at line 1

at System.Data.OracleClient.OracleConnection.CheckErr or
(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute
(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean
needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList&
resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQ ueryInternal
(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQ uery()
at NotificationApp.AudioEmailConfirmations.ProcessOrd er(Int64
alOrderID, Char acRevenueType, Char acConfirmationType)

Thank you!

On Feb 10, 9:01*am, stas1... (AT) msn (DOT) com wrote:
The procedure which is being called in Oracle is defined as follows:
PROCEDURE prc_test(
anId IN ord.order_id%TYPE,
acype IN CHAR,
acType IN CHAR,
avcComment OUT VARCHAR2,
aclobData OUT CLOB,
aclobSub OUT CLOB,
aclobTxt OUT CLOB,
aclobHTML OUT CLOB,
anCd OUT INTEGER).
In the application (which is .NET and uses the
System.Data.OracleClient for
connections) we define the parameters as follows:

arParms[4] = new OracleParameter("aclobDatasource", OracleType.Clob);
arParms[4].Direction = ParameterDirection.Output;

Thank you!

On Feb 10, 6:01*pm, stas1... (AT) msn (DOT) com wrote:
This is still not the exception itself, but the place where it was
raised: at line 963 of the PKG_TEST package. Can you post the code
around (and including) this line and re-check the error message for
the exception? (it could be displayed in the message box title) Seeing
the call to ExecuteNonQuery() may also help. You see, the more
relevant information you provide, the easier it is to diagnose the
issue and suggest corrective action.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

On Feb 11, 3:04*am, "Vladimir M. Zakharychev"
<vladimir.zakharyc... (AT) gmail (DOT) com> wrote:
Vladimir,

Line 963 is actually is where the exception is raised. There is
actually nothing wrong with the package procedure code, the issue is
with .NET Data.Oracle.OracleClient handling the CLOBs that Oracle is
returning. When I call the that procedure out of different
applications, it comes back fine. It also comes back fine when the
database character set is single byte such as WE8ISO8859P1 or
WE8MSWIN1252. I think that the problem here is passing CLOBs between a
Unicode UTF8 database, (which stores CLOBs in a UCS-2 format doubles
the size). I was hoping that someone ran into this issue in the past,
and had a quick solution for it. I know that one possible fix is to
use the OracleDataReader and pass back the data as a stream, however
that would require us to redesign the app. But I guess nothing is
easy

Than you!

On Feb 11, 5:42*pm, stas1... (AT) msn (DOT) com wrote:
Well, yes, this is where the exception is raised - but what is that
exception? ORA-6502 or something else? So far you only gave the error
stack backtrace, but not the error itself.

This might be a buffer overrun issue (because UTF-8 is multi-byte
variable-width encoding.) The issue might be related to the way you
are (or the data provider is) passing the arguments to the procedure
or allocating variables. Can be easily correctable or can require
fundamental changes. That's why I'm asking for the .NET code invoking
the procedure, PL/SQL code around line 963 and the error code that is
being raised at that line - to figure out what's going wrong and what
to fix.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

On Feb 12, 12:57*am, "Vladimir M. Zakharychev"
<vladimir.zakharyc... (AT) gmail (DOT) com> wrote:
Vladimir,

Here is the .NET code invoking the stored procedure.

try
{
myConnection.Open();

// get clobs to process
OracleParameter[] arParms = new OracleParameter[9];

arParms[0] = new OracleParameter("anOrderId", OracleType.Number);
arParms[0].Direction = ParameterDirection.Input;
arParms[0].Value = alOrderID;

arParms[1] = new OracleParameter("acRevenueType",
OracleType.Char);
arParms[1].Direction = ParameterDirection.Input;
arParms[1].Value = acRevenueType;


arParms[2] = new OracleParameter("acConfirmationType",
OracleType.Char);
arParms[2].Direction = ParameterDirection.Input;
arParms[2].Value = acConfirmationType;


arParms[3] = new OracleParameter("avcErrorComment", OracleType.Clob);
arParms[3].Direction = ParameterDirection.Output;


arParms[4] = new OracleParameter("aclobDatasource", OracleType.Clob);
arParms[4].Direction = ParameterDirection.Output;

arParms[5] =
new OracleParameter("aclobSubject", OracleType.Clob);
arParms[5].Direction = ParameterDirection.Output;

arParms[6] = new OracleParameter("aclobText",
OracleType.Clob);
arParms[6].Direction = ParameterDirection.Output;

arParms[7] = new OracleParameter("aclobHTML",
OracleType.Clob);
arParms[7].Direction = ParameterDirection.Output;

arParms[8] = new OracleParameter("anErrorCd",
OracleType.Int32);
arParms[8].Direction = ParameterDirection.Output;

OracleCommand myCommand = new OracleCommand();
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;

myCommand.Parameters.Add(arParms[0]);
myCommand.Parameters.Add(arParms[1]);
myCommand.Parameters.Add(arParms[2]);
myCommand.Parameters.Add(arParms[3]);
myCommand.Parameters.Add(arParms[4]);
myCommand.Parameters.Add(arParms[5]);
myCommand.Parameters.Add(arParms[6]);
myCommand.Parameters.Add(arParms[7]);
myCommand.Parameters.Add(arParms[8]);


myCommand.CommandText = CMD_FIND_BY_ORDERID;
myCommand.ExecuteNonQuery();

Fails on the execute.

Thanks again!

On Feb 12, 12:57*am, "Vladimir M. Zakharychev"
<vladimir.zakharyc... (AT) gmail (DOT) com> wrote:
There is nothing else. The exception is ORA-6502,

Thank you,
Mike

On Feb 17, 11:35*pm, stas1... (AT) msn (DOT) com wrote:
Still not the full picture: what's CMD_FIND_BY_ORDERID value (that is,
the PL/SQL code being executed by ExecuteNonQuery()?) And the code in
the stored procedure that raises the exception?

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com