Re: ORA-06512
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 19 Feb 2009 07:26:20 -0800 (PST)
Message-ID: <0cb11389-fd02-468e-a1e1-cc491a820f94_at_b16g2000yqb.googlegroups.com>
On Feb 17, 11:35 pm, stas1..._at_msn.com wrote:
> On Feb 12, 12:57 am, "Vladimir M. Zakharychev"
>
>
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
> > On Feb 11, 5:42 pm, stas1..._at_msn.com wrote:
>
> > > On Feb 11, 3:04 am, "Vladimir M. Zakharychev"
>
> > > <vladimir.zakharyc..._at_gmail.com> wrote:
> > > > On Feb 10, 6:01 pm, stas1..._at_msn.com wrote:
>
> > > > > On Feb 7, 2:50 am, "Vladimir M. Zakharychev"
>
> > > > > <vladimir.zakharyc..._at_gmail.com> wrote:
> > > > > > On Feb 6, 2:55 am, stas1..._at_msn.com wrote:
>
> > > > > > > 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?
>
> > > > > > 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
>
> > > > > Vladimir,
>
> > > > > Here is the full error:
>
> > > > > ORA-06512: at "SA.PKG_TEST", line 963
> > > > > ORA-06512: at line 1
>
> > > > > at System.Data.OracleClient.OracleConnection.CheckError
> > > > > (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.ExecuteNonQueryInternal
> > > > > (Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
> > > > > at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
> > > > > at NotificationApp.AudioEmailConfirmations.ProcessOrder(Int64
> > > > > alOrderID, Char acRevenueType, Char acConfirmationType)
>
> > > > > Thank you!
>
> > > > 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-Hidequotedtext -
>
> > > > - Show quoted text -
>
> > > 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!
>
> > 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-Hide quoted text -
>
> > - Show quoted text -
>
> 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!
Date: Thu, 19 Feb 2009 07:26:20 -0800 (PST)
Message-ID: <0cb11389-fd02-468e-a1e1-cc491a820f94_at_b16g2000yqb.googlegroups.com>
On Feb 17, 11:35 pm, stas1..._at_msn.com wrote:
> On Feb 12, 12:57 am, "Vladimir M. Zakharychev"
>
>
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
> > On Feb 11, 5:42 pm, stas1..._at_msn.com wrote:
>
> > > On Feb 11, 3:04 am, "Vladimir M. Zakharychev"
>
> > > <vladimir.zakharyc..._at_gmail.com> wrote:
> > > > On Feb 10, 6:01 pm, stas1..._at_msn.com wrote:
>
> > > > > On Feb 7, 2:50 am, "Vladimir M. Zakharychev"
>
> > > > > <vladimir.zakharyc..._at_gmail.com> wrote:
> > > > > > On Feb 6, 2:55 am, stas1..._at_msn.com wrote:
>
> > > > > > > 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?
>
> > > > > > 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
>
> > > > > Vladimir,
>
> > > > > Here is the full error:
>
> > > > > ORA-06512: at "SA.PKG_TEST", line 963
> > > > > ORA-06512: at line 1
>
> > > > > at System.Data.OracleClient.OracleConnection.CheckError
> > > > > (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.ExecuteNonQueryInternal
> > > > > (Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
> > > > > at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
> > > > > at NotificationApp.AudioEmailConfirmations.ProcessOrder(Int64
> > > > > alOrderID, Char acRevenueType, Char acConfirmationType)
>
> > > > > Thank you!
>
> > > > 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-Hidequotedtext -
>
> > > > - Show quoted text -
>
> > > 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!
>
> > 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-Hide quoted text -
>
> > - Show quoted text -
>
> 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!
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
Received on Thu Feb 19 2009 - 09:26:20 CST