Home » SQL & PL/SQL » SQL & PL/SQL » LOB Locator (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0, CentOS release 6.7)
LOB Locator [message #687622] Wed, 19 April 2023 05:38 Go to next message
fixxxer
Messages: 45
Registered: August 2014
Member
Hi there,

This is a general question: I have a table with a BLOB column defined on it. There are records in this table with the BLOB column populated. I have a procedure which will update this BLOB column; it takes an IN parameter that is also of BLOB type. If I do a simple:

UPDATE blob_tbl
 SET   blob_column = IN_blob_value;
COMMIT;
Will this create a new LOB Locator for the UPDATED value in that BLOB column, or will it update the existing BLOB value with the content from IN_blob_value and retain the same LOB locator value?

I have tried testing this, but I am unaware of anyway to track the "LOB Locator" value, to see if it changes as part of an UPDATE statement on the BLOB column. Is there a way to SELECT/PRINT/VIEW a LOB Locator for a BLOB column?

Thanks in advance.

Report message to a moderator

Re: LOB Locator [message #687624 is a reply to message #687622] Thu, 20 April 2023 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68511
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First a BLOB may be IN ROW or OUTLINE.
In the first case, there is no LOB locator, the data are directly stored inside the table block.
In the later case, as the ROWID does not change when you update a row, the LOB locator does not change when you change the LOB content.
As far a I know this is the current behavior which does not mean it won't change some day.

In addition, as some operations change the ROWID of a row, some change the LOB locator of a LOB value.

So I don't know why you care about the LOB locator value but you should not rely on it in anyway but during the transaction you are locking it (by locking the row).

Report message to a moderator

Re: LOB Locator [message #687626 is a reply to message #687622] Thu, 20 April 2023 11:51 Go to previous messageGo to next message
John Watson
Messages: 8880
Registered: January 2010
Location: Global Village
Senior Member
The doc for dbms_lob.write says
Quote:
WRITE replaces (overwrites) any data that already exists in the LOB at the offset, for the length you specify.
which certainly implies that the existing LOB, with of course the same locator, is updated. I would expect that your UPDATE statement is implemented with the same underlying code.
Of course, just because it is in the doc, doesn't mean it is true Smile

Report message to a moderator

Re: LOB Locator [message #687641 is a reply to message #687626] Mon, 24 April 2023 02:53 Go to previous messageGo to next message
fixxxer
Messages: 45
Registered: August 2014
Member
Thanks for the replies, guys.

@Michel - a BLOB instance is made up of LOB locator and value, regardless of in row or out of row. If it is in row, it is stored a BLOB object (locator and value) in row. If it is out of row, the LOB locator is stored in row, and the LOB value out. Based on the documentation at least:

Quote:

1.4 LOB Locator and LOB Value
A LOB instance has a locator and a value. A LOB locator is a reference, or a pointer, to where the LOB value is physically stored. The LOB value is the data stored in the LOB.
https://docs.oracle.com/en/database/oracle/oracle-database/23/adlob/LOB-locator-and-LOB-value.html

Some context to this question:

We store JSON data in a SecureFile BLOB column.

When the application does a GET call to the database for this BLOB information, the database returns the BLOB instance, and the application's Oracle driver is doing a subsequent RPC with the LOB locator to get the LOB value.

If an UPDATE occurs from another session, on the BLOB column for that record, in-between the JDBC call and the subsequent RPC call, the RPC call gets: ORA-22922: nonexistent LOB value.

It would appear that a direct UPDATE on the BLOB column is overwriting the existing LOB locator and LOB value with the locator and value from BLOB instance provided in the update; that makes sense in hindsight. The application that is performing the update is creating a brand new BLOB instance, and passing that to a stored procedure to update the table with; it would appear that Oracle is overwriting the entire existing BLOB instance with the new one that was instantiated by the application.

Unless I'm mistaken, the best way to guarantee read consistency of the existing LOB locator is to manipulate the existing BLOB object through DBMS_LOB.COPY, rather than a simple UPDATE. We were able to simulate the issue, and when we modified the DB code to perform a DBMS_LOB.COPY, the error disappeared.

[Updated on: Mon, 24 April 2023 02:55]

Report message to a moderator

Re: LOB Locator [message #687642 is a reply to message #687641] Mon, 24 April 2023 06:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3254
Registered: January 2010
Location: Connecticut, USA
Senior Member
That usually indicates bad coding:

oracle.jdbc.driver.OracleSQLException: ORA-22922: nonexistent LOB value(Doc ID 284290.1)

SY.

Report message to a moderator

Re: LOB Locator [message #687648 is a reply to message #687642] Tue, 25 April 2023 03:11 Go to previous message
fixxxer
Messages: 45
Registered: August 2014
Member
Hi SY,

Yes, this one example will produce the same error that we are seeing; but: it is not the same scenario. The issue in the example you have provided is due to the fact "dest_lob" is an IN OUT parameter, and the first code sample has only set the IN value, and not registered the OUT value.

Thanks

Report message to a moderator

Previous Topic: Custom password verify function
Next Topic: How can I speed this query up?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Oct 04 11:07:57 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.