Oracle Error ORA-22990

[Domingo Garc]

Hi,

I'm inserting records into a Table in
Oracle 8 and Delphi 4 using TOracleDataSet with the following SQL:

SELECT

.ROWID,

.* FROM

The table contains a CLOB field, and when I write any text into the field (using a DBMemo) and call Post, I get the following message:

ORA-22990: LOB locators cannot span transactions.

Any ideas?

TIA

Domingo Garc

 

[Marco Kalter]

Could it be that you have set the Session.AutoCommit property to True? This property is incompatible with LOB Locators, because any modification to a LOB column requires that the corresponding record is locked. With AutoCommit you cannot lock records, as these locks will immediately be committed. Set the AutoCommit property to False to fix the problem, and make sure that you explicitly manage the transactions in your application.

This incompatibility is one of the reasons that AutoCommit is obsolete in Direct Oracle Access 3.3.

------------------
Marco Kalter
Allround Automations

 

[Domingo Garc]

It works now, thank you

Domingo Garc

 

[Domingo Garc]

I thought it was fixed, but I was wrong!!

I have discovered that you can't have two OracleDataSets pointing to the same table selecting CLOBs fields. This sample code shows it:

DataSet1 -> (SELECT * FROM

)
DataSet2 -> (SELECT * FROM

)

DataSet1.First;
while not DataSet1.EOF do
begin
{Inside this, It's possible that I have to
iterate trough DataSet2}

DataSet2.First;
while not DataSet2.EOF do
begin
{More things}
DataSet2.Next;
end;

DataSet1.Next;
end;

If I come to this situation, when calling DataSet2.Next, I get ORA-22990 error.

My solution is to remove the CLOB field from the SELECT. I guess the LOB locator doesn't know where to point at where you iterate two times through the same table.

Now it works, but...
Is there any other solution? Is there anything I should know?

Thanks,

Domingo Garc

 

[Marco Kalter]

What happens if you set the ReadBuffer property of both datasets to 1 (and include the CLOB field again)? If this also fixes the problem could you let me know what exactly you are doing within the loop?

------------------
Marco Kalter
Allround Automations

 

[Domingo Garc]

Everything works fine now, Thanks!!

I know it's a weird piece of code, but it's necessary. Does the following:

The table works like an internal dictionary of columns (just like the COLS view in the Oracle Dictionary). When the administrator of the application revokes a table privilege from a user, I have to loop trough the columns and make some changes in a few tables. In some kind of columns, I must rebuild some views for the user, that are also based on the columns table. That's why the double loop is necessary. It's a bit difficult to explain, I don't know if you will be able to understand.

You may think why I don't use the Oracle dictionary instead. The answer is there's a lot more information that I need to store.

Thanks again for solving the problem...

Domingo Garc