Oracle Cloud Infrastructure Documentation
Try Free Tier

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

  • 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 theDBMS_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;

Was this article helpful?