DBMS_LIVE_FEED Package Reference

This chapter provides information about the packages you use with the Data Load Tool in Data Studio. The DBMS_LIVE_FEED topic also covers the procedures included in the DBMS_LIVE_FEED package.

Topics:

Summary of DBMS_LIVE_FEED Subprograms

DBMS_LIVE_FEED Procedures

Summary of DBMS_LIVE_FEED Subprograms
The DBMS_LIVE_FEED package simplifies common operations like running a live table feed on demand, on a schedule, or as the result of a notification driven updates to a table based on new objects in object store buckets.

Parent topic: The Data Load Page

Summary of DBMS_LIVE_FEED Subprograms

The DBMS_LIVE_FEED package simplifies common operations like running a live table feed on demand, on a schedule, or as the result of a notification driven updates to a table based on new objects in object store buckets.

DBMS_LIVE_FEED Package Subprograms

The following table lists the DBMS_LIVE_FEED subprograms and briefly describes them.

Subprogram	Description
create_for_storage_link	This procedure creates a live feed for storage link.
create_for_directory	This procedure creates a live feed for directory.
create_for_url	This procedure creates a live feed from URL.
modify_live_feed	This procedure modifies a live feed.
drop_live_feed	This procedure drops a live feed.
load_new_content	This procedure loads new content in an existing Live Feed.
get_notification_key	This procedure retrieves Notification Key from a Live Feed.
get_confirmation_url	This procedure receives confirmation URL from a Live feed.
get_notification_state	This procedure receives notification state from a Live Feed.

Create For Storage Link Procedure

This procedure creates a live table feed from a cloud storage link. A cloud storage link is a named association between an OCI bucket URI, and a local credential name.

Syntax

procedure create_for_storage_link(live_feed_name     IN VARCHAR2,
                                           table_name         IN VARCHAR2,
                                           storage_link_name  IN VARCHAR2,
                                           schema             IN VARCHAR2 DEFAULT NULL,
                                           table_schema       IN VARCHAR2 DEFAULT NULL,
                                           column_list        IN CLOB     DEFAULT NULL,
                                           object_type        IN VARCHAR2 DEFAULT NULL,
                                           object_format      IN VARCHAR2 DEFAULT NULL,
                                           object_filter      IN VARCHAR2 DEFAULT NULL,
                                           object_filter_type IN VARCHAR2 DEFAULT NULL,
                                           scheduled          IN BOOLEAN  DEFAULT NULL,
                                           scheduler_interval IN VARCHAR2 DEFAULT NULL,
                                           notifications      IN BOOLEAN  DEFAULT NULL,
                                           job_class          IN VARCHAR2 DEFAULT NULL,
                                           start_date         IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
                                           end_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);

DBMS_ LIVE_FEED Parameters

If you are accessing DBMS_LIVE_FEED procedures, use the following parameters.

Parameter	Description
`live_feed_name`	The name of the Live feed which you want to create.
`storage_link_name`	The name of the cloud storage link. The name of the link should follow standard Oracle naming conventions.
`table_name`	The name of the Autonomous Database target table into which the live feed will load data from Cloud Object Storage.
`Object_filter`	The name of the target table that data from the live feed will be loaded into in your Autonomous Database instance to access the Cloud Object Storage.
`object_filter_type`	The regular expression to limit the live table feed to only those files in the bucket that match the expression. . It could be a regular expression or a glob, depending on the `object_filter_type` parameter.
`scheduled`	The default is `TRUE`. If `TRUE`, you can set up a schedule for running the live table feed object.
`notifications`	The default is `FALSE`. When `TRUE`, the Live Feed tool allows your livefeed to be notified by object storage that there are new files to be loaded.
`schema`	The name of the schema. The name of the schema of the livefeed. Only the current schema is supported at the moment.
`table_schema`	The schema of the target table.
`object_type`	The type of the object. The type of files you can load are CSV, JSON and Parquet.
`object_format`	This is one of the SQL Loader options. It accepts format received by `DBMS_CLOUD.COPY_DATA` procedure.
`scheduler_interval`	This parameter displays the interval at which the livefeed runs using the`DBMS_SCHEDULER` syntax.
`job_class`	The Scheduler job class. The values are `TPURGENT,TP,HIGH,MEDIUM` and `LOW`.
`start_date`	The starting date for the live feed job.
`end_date`	The ending date of the Live feed job.

Note

The parameters of all the procedures described below are listed in the above table.

Example

In this example, a live feed named MYLIVEFEED is created on the given URL.

SQL> BEGIN
   dbms_live_feed.create_for_storage_link(
     live_feed_name => 'MYLIVEFEED',
     storage_link_name => 'DATATOOLS_LF_BUCKET',
     table_name => 'MALTESE',
     object_filter => 'fndcalday?.csv',
     object_filter_type => 'GLOB',
     scheduled => false,
     notifications => false);
end;
/;
PL/SQL procedure successfully completed.

Create For Directory Procedure

This procedure creates a live table feed from a directory.

Syntax

procedure create_for_directory(live_feed_name     IN VARCHAR2,
                                 table_name         IN VARCHAR2,
                                 directory_name     IN VARCHAR2,
                                 schema             IN VARCHAR2 DEFAULT NULL,
                                 table_schema       IN VARCHAR2 DEFAULT NULL,
                                 column_list        IN CLOB     DEFAULT NULL,
                                 object_type        IN VARCHAR2 DEFAULT NULL,
                                 object_format      IN VARCHAR2 DEFAULT NULL,
                                 object_filter      IN VARCHAR2 DEFAULT NULL,
                                 object_filter_type IN VARCHAR2 DEFAULT NULL,
                                 scheduled          IN BOOLEAN  DEFAULT NULL,
                                 scheduler_interval IN VARCHAR2 DEFAULT NULL,
                                 notifications      IN BOOLEAN  DEFAULT NULL,
                                 job_class          IN VARCHAR2 DEFAULT NULL,
                                 start_date         IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
                                 end_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);

Create For URL Procedure

This procedure creates a live table feed from a URL.

Syntax

 procedure create_for_url(live_feed_name     IN VARCHAR2,
                           table_name         IN VARCHAR2,
                           url                IN VARCHAR2,
                           credential_name    IN VARCHAR2,
                           schema             IN VARCHAR2 DEFAULT NULL,
                           table_schema       IN VARCHAR2 DEFAULT NULL,
                           column_list        IN CLOB     DEFAULT NULL,
                           object_type        IN VARCHAR2 DEFAULT NULL,
                           object_format      IN VARCHAR2 DEFAULT NULL,
                           object_filter      IN VARCHAR2 DEFAULT NULL,
                           object_filter_type IN VARCHAR2 DEFAULT NULL,
                           scheduled          IN BOOLEAN  DEFAULT NULL,
                           scheduler_interval IN VARCHAR2 DEFAULT NULL,
                           notifications      IN BOOLEAN  DEFAULT NULL,
                           job_class          IN VARCHAR2 DEFAULT NULL,
                           start_date         IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
                           end_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);

Modify Live Feed Procedure

This procedure modifies a live table feed.

Syntax

procedure modify_live_feed(live_feed_name     IN VARCHAR2,
                             schema             IN VARCHAR2 DEFAULT NULL,
                             scheduled          IN BOOLEAN  DEFAULT NULL,
                             scheduler_interval IN VARCHAR2 DEFAULT NULL,
                             notifications      IN BOOLEAN  DEFAULT NULL,
                             job_class          IN VARCHAR2 DEFAULT NULL,
                             start_date         IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
                             end_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);

Example

In this example, a live feed named MYLIVEFEED is modified.

begin
   dbms_live_feed.modify_live_feed(
     live_feed_name => 'MYLIVEFEED',
     scheduled => true,
     scheduler_interval => 'FREQ=daily;INTERVAL=1',
     notifications => true);
end;
/

Drop Live Feed Procedure

This procedure drops a live table feed.

Syntax

procedure drop_live_feed(live_feed_name     IN VARCHAR2,
                                  schema             IN VARCHAR2 DEFAULT NULL);

Example

In this example, a live feed named MYLIVEFEED is dropped.

begin
   dbms_live_feed.drop_live_feed(live_feed_name => 'MYLIVEFEED');
end;
/

Load New Content Procedure

This procedure loads new content in the feed.

Syntax

 procedure load_new_content(live_feed_name IN VARCHAR2,
                                     schema         IN VARCHAR2 DEFAULT NULL);

Get Notification Key Procedure

This procedure receives Live Table Feed Notification Key. It is used for a live feed with notifications enabled to construct a notification URL to provide a cloud service. Follow the instructions in the Creating a Notification-Based Live Table Feed chapter to create a notification based live feed.

Syntax

function get_notification_key(live_feed_name IN VARCHAR2,
                                       schema         IN VARCHAR2 DEFAULT NULL)
    return VARCHAR2;

Get Confirmation URL

This procedure receives confirmation URL which verifies that the endpoint can receive notifications.

You must visit this URL to enable notifications you receive from a cloud service. It is not possible to confirm the enabling of notifications automatically due to database firewall restrictions. Click this URL to enable notifications else you will not receive any notifications.

Syntax

function get_confirmation_url(live_feed_name IN VARCHAR2,
                                       schema         IN VARCHAR2 DEFAULT NULL)
    return VARCHAR2;

Get Notification State

This procedure receives the state of notification of the Live Feed job execution.

Syntax

 function get_notification_state(live_feed_name IN VARCHAR2,
                                          schema         IN VARCHAR2 DEFAULT NULL)
    return VARCHAR2;

Parent topic: DBMS_LIVE_FEED Package Reference

Oracle Cloud Infrastructure Documentation

Summary of DBMS_LIVE_FEED Subprograms