Error executing a stored procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
le
Posts: 6
Joined: Mon 26 Feb 2007 15:32

Error executing a stored procedure

Post by le » Thu 06 Sep 2007 20:24

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

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 07 Sep 2007 05:59

Do you really need parameters of type OracleDbType.Long? This is long string!
Please provide the definition of your database objects.

le
Posts: 6
Joined: Mon 26 Feb 2007 15:32

Procedure

Post by le » Mon 10 Sep 2007 13:26

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;

le
Posts: 6
Joined: Mon 26 Feb 2007 15:32

Issue fixed by downgrading

Post by le » Mon 10 Sep 2007 14:29

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

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 11 Sep 2007 09:47

Please provide the definition of application_users.

le
Posts: 6
Joined: Mon 26 Feb 2007 15:32

Post by le » Wed 12 Sep 2007 13:47

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)

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 12 Sep 2007 14:34

But what about 'user_object'? Could you post all needed definitions here?

le
Posts: 6
Joined: Mon 26 Feb 2007 15:32

Post by le » Wed 12 Sep 2007 17:41

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))

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 14 Sep 2007 10:20

Are you sure you are using the latest version of OraDirect .NET?
Try to see in debugger what modules are loaded. Make sure that there are no old assemblies.

le
Posts: 6
Joined: Mon 26 Feb 2007 15:32

Post by le » Wed 19 Sep 2007 20:33

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

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 20 Sep 2007 09:07

Actually, in my first reply I asked whether you really need parameters of type OracleDbType.Long...

Post Reply