Error executing a stored procedure
Error executing a stored procedure
Hi,
I am using OraDirect 4.0 and am getting the error below whenever the ASP.Net code tries to execute a stored procedure. This only happens on one web server, which is a Win2003 with the Oracle 10.2.0.1 database installed on it. The code works well on the servers where only the Oracle Client installation. The type of the stored procedure doesn't seem to matter.
The error message is:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.]
OciDynamicType.nativea(HandleRef , HandleRef , HandleRef , Int32 , Int32 , Int32 , Int32 , Int32 ) +0
OciDynamicType.a(HandleRef , HandleRef , HandleRef , Int32 , Int32 , Int32 , Int32 , Int32 ) +45
CoreLab.Oracle.ah.a(Int32 A_0, bh A_1) +76
[OracleException (0x80004005): Internal exception in Oracle client occurred.]
CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3) +1285
CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) +193
System.Data.Common.DbCommand.ExecuteReader() +9
CoreLab.Oracle.OracleCommand.ExecuteNonQuery() +26
Here is C# code for issuing the database call:
OracleParameter[] oraParms;
oraParms = new OracleParameter[3];
oraParms[0] = new OracleParameter("p_user_id", OracleDbType.Long, 10);
oraParms[0].Value = 101065;
oraParms[1] = new OracleParameter("p_object_id", OracleDbType.Long, 10);
oraParms[1].Value = 202;
oraParms[2] = new OracleParameter("p_right_cd", OracleDbType.Char);
oraParms[2].Direction = ParameterDirection.Output;
OracleCommand oraCommand = new OracleCommand();
string oraConnectString = "xxx";
OracleConnection oraConn = new OracleConnection();
oraConn.ConnectionString = oraConnectString;
oraConn.Open();
oraCommand.Connection = oraConn;
oraCommand.CommandType = CommandType.StoredProcedure;
oraCommand.Parameters.Add(oraParms[0]);
oraCommand.Parameters.Add(oraParms[1]);
oraCommand.Parameters.Add(oraParms[2]);
oraCommand.CommandText = procName;
oraCommand.ExecuteNonQuery();
string value = oraCommand.Parameters[2].Value.ToString();
Label1.Text = value;
oraConn.Close();
Appreciate your help.
Lana
I am using OraDirect 4.0 and am getting the error below whenever the ASP.Net code tries to execute a stored procedure. This only happens on one web server, which is a Win2003 with the Oracle 10.2.0.1 database installed on it. The code works well on the servers where only the Oracle Client installation. The type of the stored procedure doesn't seem to matter.
The error message is:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.]
OciDynamicType.nativea(HandleRef , HandleRef , HandleRef , Int32 , Int32 , Int32 , Int32 , Int32 ) +0
OciDynamicType.a(HandleRef , HandleRef , HandleRef , Int32 , Int32 , Int32 , Int32 , Int32 ) +45
CoreLab.Oracle.ah.a(Int32 A_0, bh A_1) +76
[OracleException (0x80004005): Internal exception in Oracle client occurred.]
CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3) +1285
CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) +193
System.Data.Common.DbCommand.ExecuteReader() +9
CoreLab.Oracle.OracleCommand.ExecuteNonQuery() +26
Here is C# code for issuing the database call:
OracleParameter[] oraParms;
oraParms = new OracleParameter[3];
oraParms[0] = new OracleParameter("p_user_id", OracleDbType.Long, 10);
oraParms[0].Value = 101065;
oraParms[1] = new OracleParameter("p_object_id", OracleDbType.Long, 10);
oraParms[1].Value = 202;
oraParms[2] = new OracleParameter("p_right_cd", OracleDbType.Char);
oraParms[2].Direction = ParameterDirection.Output;
OracleCommand oraCommand = new OracleCommand();
string oraConnectString = "xxx";
OracleConnection oraConn = new OracleConnection();
oraConn.ConnectionString = oraConnectString;
oraConn.Open();
oraCommand.Connection = oraConn;
oraCommand.CommandType = CommandType.StoredProcedure;
oraCommand.Parameters.Add(oraParms[0]);
oraCommand.Parameters.Add(oraParms[1]);
oraCommand.Parameters.Add(oraParms[2]);
oraCommand.CommandText = procName;
oraCommand.ExecuteNonQuery();
string value = oraCommand.Parameters[2].Value.ToString();
Label1.Text = value;
oraConn.Close();
Appreciate your help.
Lana
Procedure
Hi,
Thanks for your reply. Here is the definition of the stored procedure. I have removed part of the code as it's pretty lengthy. Please note that the error is being raised when trying to execute any stored procedure, not a particular one. The main difference between the web servers where the error doesn't occure and the failing one is the presense of the Oracle database (not just the Oracle client) on the last.
PROCEDURE get_users_object_rights
(p_user_id IN application_users.user_id%TYPE, //LE defined as NUMBER(10,0) in the table)
p_object_id IN user_object.object_id%TYPE,//LE defined as NUMBER(10,0) in the table)
p_right_cd OUT user_object.right_cd%TYPE)//LE defined as CHAR(1) in the table)
IS
v_right_cd user_object.right_cd%TYPE;
BEGIN
SELECT UO.right_cd
INTO v_right_cd
FROM user_object UO
WHERE UO.user_id = p_user_id
AND UO.object_id = p_object_id
AND UO.right_cd = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_right_cd := 'N';
END;
Thanks for your reply. Here is the definition of the stored procedure. I have removed part of the code as it's pretty lengthy. Please note that the error is being raised when trying to execute any stored procedure, not a particular one. The main difference between the web servers where the error doesn't occure and the failing one is the presense of the Oracle database (not just the Oracle client) on the last.
PROCEDURE get_users_object_rights
(p_user_id IN application_users.user_id%TYPE, //LE defined as NUMBER(10,0) in the table)
p_object_id IN user_object.object_id%TYPE,//LE defined as NUMBER(10,0) in the table)
p_right_cd OUT user_object.right_cd%TYPE)//LE defined as CHAR(1) in the table)
IS
v_right_cd user_object.right_cd%TYPE;
BEGIN
SELECT UO.right_cd
INTO v_right_cd
FROM user_object UO
WHERE UO.user_id = p_user_id
AND UO.object_id = p_object_id
AND UO.right_cd = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_right_cd := 'N';
END;
Issue fixed by downgrading
I have downgraded to the version 3.55 of the OraDirect and the error is gone.
Could you please investigate as we would like to use the latest driver version?
Thanks.
Lana
Could you please investigate as we would like to use the latest driver version?
Thanks.
Lana
Here you are:
TABLE application_users
(user_id NUMBER(10,0) NOT NULL,
user_name VARCHAR2(140) NOT NULL,
user_first_name VARCHAR2(35),
user_last_name VARCHAR2(35),
user_domain VARCHAR2(255),
organization_cd VARCHAR2(6),
section_cd VARCHAR2(5),
office_cd VARCHAR2(10),
email_txt VARCHAR2(140),
phone_nbr VARCHAR2(27),
deleted_flg CHAR(1) NOT NULL,
last_update_user_id NUMBER(10,0) NOT NULL,
last_update_dt DATE NOT NULL,
create_user_id NUMBER(10,0) NOT NULL,
create_dt DATE NOT NULL,
password VARCHAR2(16),
organization_company_cd VARCHAR2(6) NOT NULL,
last_login_dt DATE,
pcc_disclaimer_accept_dt DATE)
TABLE application_users
(user_id NUMBER(10,0) NOT NULL,
user_name VARCHAR2(140) NOT NULL,
user_first_name VARCHAR2(35),
user_last_name VARCHAR2(35),
user_domain VARCHAR2(255),
organization_cd VARCHAR2(6),
section_cd VARCHAR2(5),
office_cd VARCHAR2(10),
email_txt VARCHAR2(140),
phone_nbr VARCHAR2(27),
deleted_flg CHAR(1) NOT NULL,
last_update_user_id NUMBER(10,0) NOT NULL,
last_update_dt DATE NOT NULL,
create_user_id NUMBER(10,0) NOT NULL,
create_dt DATE NOT NULL,
password VARCHAR2(16),
organization_company_cd VARCHAR2(6) NOT NULL,
last_login_dt DATE,
pcc_disclaimer_accept_dt DATE)
Well, you've only requested the application_users.
Here is the user_object:
TABLE user_object
(object_id NUMBER(10,0) NOT NULL,
user_id NUMBER(10,0) NOT NULL,
last_update_user_id NUMBER(10,0) NOT NULL,
last_update_dt DATE NOT NULL,
create_user_id NUMBER(10,0) NOT NULL,
create_dt DATE,
right_cd CHAR(1),
level_id NUMBER(10,0))
Here is the user_object:
TABLE user_object
(object_id NUMBER(10,0) NOT NULL,
user_id NUMBER(10,0) NOT NULL,
last_update_user_id NUMBER(10,0) NOT NULL,
last_update_dt DATE NOT NULL,
create_user_id NUMBER(10,0) NOT NULL,
create_dt DATE,
right_cd CHAR(1),
level_id NUMBER(10,0))
Per my original email I wasn't able to run any stored procedure due to the error above. This error was only happenning on the server with Oracle 10.2.0.3 client installed and Win2003 (9.1 + Win2003 or 10.2.0.3 with WinXP worked fine).
The following steps helped resolve the issue:
1. After downgrading to the version 3.55 of the OraDirect driver I was able to run some stored procedures.
2. The ones, which were still failing, were with the parameters of the NUMBER (10,0) in the database. I have modified the definition of such in the C# code from OracleDbType.Long to OracleDbType.Integer - and the error is gone.
It would be helpful if the OraDirect help file provides some information on the numeric data types and their Oracle database equivalent.
Thanks.
Lana
The following steps helped resolve the issue:
1. After downgrading to the version 3.55 of the OraDirect driver I was able to run some stored procedures.
2. The ones, which were still failing, were with the parameters of the NUMBER (10,0) in the database. I have modified the definition of such in the C# code from OracleDbType.Long to OracleDbType.Integer - and the error is gone.
It would be helpful if the OraDirect help file provides some information on the numeric data types and their Oracle database equivalent.
Thanks.
Lana