Hello,
We're using:
ODP.NET 9.2.0.4.01
Oracle 9i r2
.NET 1.1
WinXP sp2
conn string: Password=GENERAL_USER;User ID=GENERAL_USER;Data Source=327;Persist Security Info=True; Pooling=false;
Although all SPs are owned by an OPER user we want connect using a 'security' account GENERAL_USER who is only assigned to a GENERAL_ROLE
GENERAL_ROLE create script grants:
GRANT CONNECT TO GENERAL_ROLE
/
OPER create script grants:
GRANT CONNECT, RESOURCE, CREATE ANY TABLE TO "OPER"
/
GRANT "GENERAL_ROLE" TO "OPER"
/
GENERAL_USER create script grants:
GRANT "GENERAL_ROLE" TO "GENERAL_USER"
/
the SP that's causing problems is in OPER schema and does the following:
-- a simple insert
-- returns a "FOR UPDATE" cursor like this:
OPEN p_Query_Columns_Cursor FOR
SELECT
ORIGINAL_QUERY
, INTERNAL_QUERY
FROM
SCH_QRY_DET_GRP
WHERE
ID = p_ID
FOR UPDATE;
the recordset should always contain only one row and the two columns are CLOB
the SP has also a few input params and an output param
on the client side we do the following (code stripped for readability):
connection = GetConnection(connectionString);
using(OracleCommand command = new OracleCommand())
{
connection.Open();
try
{
command.Connection = (OracleConnection)connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spName;
command.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
.... // create and add params
OracleDataReader dr = command.ExecuteReader();
returnId = Convert.ToInt32(prm0.Value);
while(dr.Read())
{
int STREAM_CHUNK_SIZE = 29000;
using(OracleClob clob = dr.GetOracleClob(0))
{
clob.Erase(); // ***1***
clob.Flush();
... // fill and flush clob
clob.Dispose();
}
... // the same with the second clob
}
command.Transaction.Commit();
}
catch
... // rollback and rethrow
We come to this solution because we must insert in the CLOBs data in amounts larger than 32000 kB or so and a string did not suffice.
Everything is fine until the ***1*** statement. When we try to clear the already existing data the ORA-00942 is issued.
We searched the OTN forums and stumbled upon other threads that might be related to the same thing:
567602
1305707
If it is an ODP bug then this is it. Weird is the thing that we're in a situation when we cannot change the ODP version (contract and other issues).
Thing is, it is not the first time this old version of ODP is causing us troubles.
If this can worked around or if we don't do something right, please advise.
Thank you in advance