Functions on Rows

Table rows record values conforming to the table schema, but with some additional properties that are not part of the table schema. To extract the values of such properties, use the functions listed below.

These functions extract metadata or system-level information about the physical characteristics and lifecycle of a row of data. These functions require a row variable (a table alias prefixed with $) as their input argument.

Table 12-3 Functions on Rows

Function Name Description Example
modification_time (AnyRecord)

Returns the UTC time the row was last modified or inserted, as a timestamp.

 
SELECT modification_time($u) FROM Users $u where id=1;
Output:
{"Column_1":"2025-12-11T07:30:08.485Z"}
remaining_hours (AnyRecord)

Returns the number of full hours remaining until a row expires, based on its Time-To-Live (TTL) setting.

Returns -1 if no TTL has been set.

 
SELECT remaining_hours($u) from Users $u where id=3;
Output:
{"Column_1":111}
remaining_days (AnyRecord)

Returns the number of full days left until a row expires, based on its TTL setting.

Returns -1 if no TTL has been set.

 
SELECT remaining_days($u) from Users $u where id=3;
Output:
{"Column_1":4}
expiration_time (AnyRecord)

Returns the UTC time a row is scheduled to expire (based on its TTL setting) as a timestamp.

Returns January 1, 1970 UTC if no TTL has been set

 
SELECT expiration_time($u) from Users $u where id=3;
Output:
{"Column_1":"2025-12-16T00:00:00.000Z"}
expiration_time_millis (AnyRecord)

Returns the UTC time a row is scheduled to expire (based on its TTL setting) as number of milliseconds since January 1, 1970 UTC.

Returns 0 if no TTL has been set.

 
SELECT expiration_time_millis($u) from Users $u where id=3;
Output:
{"Column_1":1765843200000}
row_version (AnyRecord) / version (AnyRecord)

Returns a unique, system-generated identifier representing the current physical version of a row.

This can be used to implement Optimistic Concurrency Control (OCC). For more details, see putIfVersion. 

SELECT id, row_version($u) AS CurrentVersion FROM Users $u WHERE id = 1;
Output:
{"id":1,"CurrentVersion":"rO0ABXcsACZGlde4Y4FHbaAFvITmIKy1AAAAAAAAAeoBAwAAAAEAAAABAAAAAAADHFY="}
row_metadata (AnyRecord)

Note:

This feature has been made available to you on a "preview" basis so that you can get early access and provide feedback. It is intended for demonstration and preliminary use only. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this feature and will not be responsible for any loss, costs, or damages incurred due to the use of this feature.
Retrieves the metadata associated with the most recent write operation on the record. If the most recent write did not specify any metadata, then this function returns NULL. 
SELECT id, row_metadata($u) AS Metadata FROM Users $u WHERE id = 1;
Output:
{"id":1,"Metadata":null}
shard (AnyRecord) Returns the integer ID of the physical shard where the row is stored. 
SELECT shard($u) AS SHARD FROM Users $u where id = 2;
Output:
{"SHARD":1}
partition (AnyRecord) Returns the integer ID of the partition where the row is stored. 
SELECT partition($u) AS PARTITION FROM Users $u where shard($u) = 1;
Output:
{"PARTITION":1}
{"PARTITION":3}
{"PARTITION":7}
{"PARTITION":7}
{"PARTITION":8}
SELECT partition($u) AS PARTITION FROM Users $u where id = 4;
Output:
{"PARTITION":7}
row_storage_size (AnyRecord)

Returns the size of the row data in persistent storage, in bytes.

This also includes the size of system-defined and user-defined metadata (if any).

 
SELECT row_storage_size($u) AS ROW_STORAGE FROM Users $u where id = 4;
Output:
{"ROW_STORAGE":90}
index_storage_size (AnyRecord, index_name) Returns the size of the index entry for a row, in persistent storage, in bytes. 
SELECT index_storage_size($u,"idx_lastname") AS INDEX_STORAGE FROM Users $u where id=3;
Output:
{"INDEX_STORAGE":28}