Categories:

System Functions (System Information)

SYSTEM$STREAM_HAS_DATA

Indicates whether a specified stream contains change data capture (CDC) records.

Syntax

SYSTEM$STREAM_HAS_DATA('<stream_name>')

Arguments

stream_name

The name of the stream to query.

  • Note that the entire name must be enclosed in single quotes, including the database and schema (if the name is fully-qualified), i.e. '<db>.<schema>.<stream_name>'.

  • If the stream name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/characters. The double quotes must be enclosed within the single quotes, i.e. '"<stream_name>"'.

Usage Notes

  • This function performs a diff of the table version metadata (between the stream offset and the current transactional time) to determine whether the stream contains CDC records. If the DML activity for the table during that period consisted of the same set of rows being inserted, optionally updated, and deleted, returning to the original table state, then it is possible this function could return a TRUE value even though the stream contains no CDC records.

Examples

create table MYTABLE1 (id int);

create table MYTABLE2(id int);

create stream MYSTREAM on table MYTABLE1;

insert into MYTABLE1 values (1);

-- returns true because the stream contains change tracking information
select system$stream_has_data('MYSTREAM');

+----------------------------------------+
| SYSTEM$STREAM_HAS_DATA('MYSTREAM')     |
|----------------------------------------|
| True                                   |
+----------------------------------------+

 -- consume the stream
begin;
insert into MYTABLE2 select id from MYSTREAM;
commit;

-- returns false because the stream was consumed
select system$stream_has_data('MYSTREAM');

+----------------------------------------+
| SYSTEM$STREAM_HAS_DATA('MYSTREAM')     |
|----------------------------------------|
| False                                  |
+----------------------------------------+