Oracle Cloud Infrastructure Documentation
Try Free Tier

Summary of Share Consumer Subprograms

This table lists the DBMS_SHARE package procedures and functions used to consume shares.

Subprograms Description
ASSERT_SHARING_ID Procedure Run basic validation checks against a sharing id and return one in canonical form.
CREATE_CREDENTIALS Procedure and Function Create a credential containing the bearer token from a delta profile.
CREATE_OR_REPLACE_SHARE_LINK Procedure Subscribe to share from a registered share provider.
CREATE_OR_REPLACE_ORACLE_SHARE_PROVIDER Procedure Subscribe to an Oracle share provider, with a local name.
CREATE_ORACLE_SHARE_PROVIDER Procedure Subscribe to an Oracle share provider, with a local name.
CREATE_SHARE_LINK Procedure Subscribe to share from a registered share provider.
CREATE_SHARE_LINK_VIEW Procedure Create or replace a named view that gives access to a remote shared table.
CREATE_SHARE_PROVIDER Procedure Subscribe to a delta share provider.
DISCOVER_AVAILABLE_SHARES Function Return one SHARE_AVAILABLE_SHARES_ROW for each available table from the subscribed share providers.
DISCOVER_AVAILABLE_TABLES Function Return one SHARE_AVAILABLE_TABLES_ROW for each available table from the subscribed share providers or from an explicit delta endpoint.
DROP_SHARE_LINK Procedure Drop a share link created by the CREATE_SHARE_LINK procedure.
DROP_SHARE_PROVIDER Procedure Drop a subscription to a share provider.
ENABLE_DELTA_ENDPOINT Procedure Create necessary ACLs that allow the specified user to connect to a delta endpoint.
FLUSH_SHARE_LINK_CACHE Procedure Flush the cache of shares for a given share link.
FLUSH_SHARE_PROVIDER_CACHE Procedure Flush the cache of shares for a given share provider.
GENERATE_SHARE_LINK_SELECT Procedure and Function Generate a SELECT statement that returns data from a shared table.
GET_ORACLE_SHARE_LINK_INFO Function Retrieve the cloud link name and namespace for an Oracle-to-Oracle share.
GET_SHARE_LINK_INFO Procedure Get the endpoint(s), share type and share name along with any additional JSON metadata for a share link.
GET_SHARE_PROVIDER_CREDENTIAL Procedure Get the credential name to be used by the current user when it attempts to access the given delta share provider.
GET_SHARE_PROVIDER_INFO Procedure Get the endpoint string(s) and share type along with any additional JSON metadata for a share provider.
GET_SHARING_ID Function Return an identifier that can be used as the sharing_id in the CREATE_SHARE_RECIPIENT procedure.
OPEN_SHARE_LINK_CURSOR Procedure Open a cursor that returns data from a shared table.
REFRESH_BEARER_TOKEN_CREDENTIAL Procedure Update one or more credentials created by CREATE_BEARER_TOKEN_CREDENTIAL or CREATE_CREDENTIALS.
RENAME_CLOUD_STORAGE_LINK Procedure Rename a registered cloud storage link.
RENAME_SHARE_LINK Procedure Rename a registered share link.
RENAME_SHARE_PROVIDER Procedure Rename a registered share provider.
REMOVE_SHARE_SCHEMA Procedure Remove a schema and all its contents from a share.
SET_SHARE_LINK_METADATA Procedure Set the additional JSON metadata for the share link.
SET_SHARE_PROVIDER_CREDENTIAL Procedure Set the credential name to access the given share provider.
SET_SHARE_PROVIDER_METADATA Procedure Set additional JSON metadata for the share provider.
UPDATE_BEARER_TOKEN_CREDENTIAL Procedure Modify an attribute of a credential created by CREATE_CREDENTIALS or CREATE_BEARER_TOKEN_CREDENTIAL.

Parent topic: DBMS_SHARE Package

ASSERT_SHARING_ID Procedure

Run basic validation checks against a sharing id and return one in canonical form. An exception is raised if the id is obviously invalid.

Syntax

PROCEDURE ASSERT_SHARING_ID  
(
    sharing_id              IN OUT NOCOPY VARCHAR2,
    out_sharing_id_type     IN OUT NOCOPY VARCHAR2  
);

Parameters

Parameter Description
sharing_id The id to check.
out_sharing_id_type The type of the id, if valid. For example, TENANCY or DATABASE.

CREATE_CREDENTIALS Procedure and Function

Create a credential containing the bearer token from a delta sharing profile. The standard type, version 1, specifies an endpoint and a single long term bearer token.

Syntax

PROCEDURE CREATE_CREDENTIALS  
(
    credential_base_name IN VARCHAR2,
    delta_profile        IN CLOB,
    out_credential_name  IN OUT NOCOPY VARCHAR2  
);

Below is the functional version of the create_credentials that returns the name of the new credentials in JSON form. 

FUNCTION CREATE_CREDENTIALS 
(
   credential_base_name IN VARCHAR2,
   delta_profile        IN CLOB 
)
RETURN CLOB;

Parameters

Parameter Description
credential_base_name The base name of the credential(s) to create.
delta_profile The delta sharing profile, in JSON format, obtained from the share provider.
{
  "shareCredentialsVersion": 1,
  "endpoint": "https://<endpoint>",
  "bearerToken": "<token>",
  "expirationTime": "...",
}
The profile may also, optionally, include a tokenEndpoint property along with a clientID and clientSecret.
{
  "shareCredentialsVersion": 1,
  "endpoint": "https://<endpoint>",
  "bearerToken": "<token>",
  "expirationTime": "...",
  "tokenEndpoint": "https://<token endpoint>",
  "clientID": "<client id>",
  "clientSecret": "<client secret>"
}

See Profile File Format and Bearer Token for further information.

out_credential_name The name of the newly created bearer token credential.

CREATE_OR_REPLACE_SHARE_LINK Procedure

Subscribe to share from a registered share provider.

Syntax

PROCEDURE CREATE_OR_REPLACE_SHARE_LINK  
(
    share_link_name        IN VARCHAR2,
    share_provider         IN VARCHAR2,
    share_name             IN VARCHAR2,
    provider_owner         IN VARCHAR2 := NULL,
    link_owner             IN VARCHAR2 := NULL,
    use_default_credential IN BOOLEAN := TRUE,
    metadata               IN SYS.JSON_OBJECT_T := NULL,
    auto_commit            IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
share_link_name The name of the share link that will be created. The available share names can be found by calling DISCOVER_AVAILABLE_SHARES Function.
share_provider The name of the registered share provider.
share_name The name of the share from the share provider.
provider_owner The owner of the share provider. Defaults to the current schema.
link_owner The owner of the share link. Defaults to the current schema.
use_default_credential If TRUE, use the same credentials as the share provider.
metadata Optional metadata to associate with the share link.
auto_commit If TRUE (the default), this procedure call commits changes that are not visible externally until the commit takes place. If FALSE, the user must COMMIT after running this call in order to make the change visible.

CREATE_OR_REPLACE_ORACLE_SHARE_PROVIDER Procedure

Subscribe to an Oracle share provider, with a local name.

It will then appear in ALL_SHARE_PROVIDERS View with RECIPIENT_TYPE = 'ORACLE'.

Note

Use the SET_STORAGE_CREDENTIAL procedure to add a credential to the storage link. See SET_STORAGE_CREDENTIAL Procedure.

Syntax

PROCEDURE CREATE_OR_REPLACE_CLOUD_STORAGE_LINK  
(
    storage_link_name    IN VARCHAR2,
    uri                  IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    metadata             IN SYS.JSON_OBJECT_T := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
storage_link_name The name of the cloud storage link. The name of the link should follow standard Oracle naming conventions. See Database Object Names and Qualifiers
uri The URI of the storage bucket. The URI should be of the form https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucket/o
owner The owner of the storage link. Defaults to the current schema.
metadata An optional JSON metadata document containing additional information.
auto_commit If TRUE, the changes automatically commit after creating the link. The default is TRUE.

CREATE_ORACLE_SHARE_PROVIDER Procedure

Subscribe to an Oracle share provider, with a local name.

It will then appear in ALL_SHARE_PROVIDERS View with RECIPIENT_TYPE = 'ORACLE'.

Syntax

PROCEDURE CREATE_ORACLE_SHARE_PROVIDER  
(
    oracle_provider_id   IN VARCHAR2,
    provider_name        IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    metadata             IN SYS.JSON_OBJECT_T := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
oracle_provider_id The provider ID obtained from the ALL_AVAILABLE_ORACLE_SHARE_PROVIDERS view.

See ALL_AVAILABLE_ORACLE_SHARE_PROVIDERS View.

provider_name A local name for the provider
owner The owner of the new share provider. Leave as NULL for the current user.
metadata Optional JSON metadata to associate with the provider.
auto_commit If TRUE, the changes automatically commit after creating the link. The default is TRUE.

CREATE_SHARE_LINK Procedure

Subscribe to share from a registered share provider. The available share names can be found by calling DISCOVER_AVAILABLE_SHARES.

Syntax

PROCEDURE CREATE_SHARE_LINK  
(
    share_link_name      IN VARCHAR2,
    share_provider       IN VARCHAR2,
    share_name           IN VARCHAR2,
    provider_owner       IN VARCHAR2 := NULL,
    link_owner           IN VARCHAR2 := NULL,
    use_default_credential IN BOOLEAN := TRUE,
    metadata             IN SYS.JSON_OBJECT_T := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
share_link_name The name of the share link to create. This should follow standard Oracle naming conventions and does not need to be the same as the share_name parameter.
share_provider The name of the share provider, listed in ALL_SHARE_PROVIDERS, that shared the data.
share_name The name of the share, as defined by the share provider.
provider_owner The schema that subscribed the share provider, as listed in ALL_SHARE_PROVIDER. Leave as null to default to the current user, which is typical.
link_owner The owner of the new share link. Leave as null to default to the current user, which is typical.
use_default_credential Set to TRUE to use the same credential for both the share link and the share provider.
metadata Optional JSON metadata to associated with the share link.
auto_commit If TRUE, the changes automatically commit after creating the link. The default is TRUE.

CREATE_SHARE_LINK_VIEW Procedure

Create or replace a named view that gives access to a remote shared table.

Syntax

PROCEDURE CREATE_SHARE_LINK_VIEW  
(
    view_name            IN VARCHAR2,
    share_link_name      IN VARCHAR2,
    share_schema_name    IN VARCHAR2,
    share_table_name     IN VARCHAR2,
    view_owner           IN VARCHAR2 := NULL,
    share_link_owner     IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
view_name The name of the new view.
share_link_name The name of the share link.
share_schema_name The name of the shared schema.
share_table_name The name of the shared table.
view_owner The view owner. Defaults to the current schema.
share_link_owner The link owner. Defaults to the current schema.

CREATE_SHARE_PROVIDER Procedure

Subscribe to a delta share provider.

Syntax

PROCEDURE CREATE_SHARE_PROVIDER
(
  provider_name        IN VARCHAR2,
  endpoint             IN VARCHAR2,
  token_endpoint       IN VARCHAR2 := NULL,
  share_type           IN VARCHAR2 := 'DELTA',
  owner                IN VARCHAR2 := NULL,
  metadata             IN SYS.JSON_OBJECT_T := NULL,
  auto_commit          IN BOOLEAN := TRUE
);

Parameters

Parameter Description
provider_name The local name of this share provider.
endpoint The delta endpoint, from the delta sharing profile.
token_endpoint This parameter is ignored.
share_type The type of share provider. Leave this as DELTA.
owner The owner of the share provider. Defaults to the current schema.
metadata Optional JSON metadata to associate with the share provider.
auto_commit If TRUE (the default), this procedure call commits changes that are not visible externally until the commit takes place. If FALSE, the user must COMMIT after running this call in order to make the change visible.

DISCOVER_AVAILABLE_SHARES Function

Return one SHARE_AVAILABLE_SHARES_ROW for each available table from the subscribed share providers.

Syntax

FUNCTION DISCOVER_AVAILABLE_SHARES  
(
    share_provider       IN VARCHAR2,
    owner                IN VARCHAR2 := NULL  
) RETURN share_available_shares_tbl PIPELINED;

Parameters

Parameter Description
share_provider The name of the share provider.
owner The owner of the share provider. Defaults to the current schema.

Example: Discover a list of shares available from a given provider

SQL> select available_share_name
  2    from dbms_share.discover_available_shares('share_prov')
  3   order by available_share_name;

AVAILABLE_SHARE_NAME
--------------------------------------------------------------------------------
BURLINGTON_EXPEDITION_2022 

EGYPT_EXPEDITION_2022

DISCOVER_AVAILABLE_TABLES Function

Return one SHARE_AVAILABLE_TABLES_ROW for each available table from the subscribed share providers.

Syntax

FUNCTION DISCOVER_AVAILABLE_TABLES
(
  share_provider       IN VARCHAR2 := NULL,
  share_name           IN VARCHAR2 := NULL,
  owner                IN VARCHAR2 := NULL,
  endpoint             IN VARCHAR2 := NULL,
  credential_name      IN VARCHAR2 := NULL
) RETURN share_available_tables_tbl PIPELINED;

Parameters

Parameter Description
share_provider An optional share provider name. If NULL, search all subscribed share providers.
share_name An optional share name. If NULL, search all discovered shares.
owner The owner of the share provider. Defaults to the current schema.
endpoint An optional delta endpoint.
credential_name An optional bearer token credential to access the endpoint.

Example: List shares available from all subscribed share providers

SQL> select * from dbms_share.discover_available_tables()
  2  order by share_name, schema_name, table_name;

 PROVIDER_NAME             PROVIDER_OWNER  SHARE_NAME
------------------------- --------------- ------------------------------
SCHEMA_NAME               TABLE_NAME
------------------------- -------------------------
My Test Oracle Provider   ADP_SHARE_TEST  BURLINGTON_EXPEDITION_2022
SH                        COUNTRIES 

My Test Oracle Provider   ADP_SHARE_TEST  BURLINGTON_EXPEDITION_2022
SH                        SH_COUNTRIES 

My Test Oracle Provider   ADP_SHARE_TEST  EGYPT_EXPEDITION_2022
SHARED_SCHEMA             SHARED_VIEW_1 

My Test Oracle Provider   ADP_SHARE_TEST  EGYPT_EXPEDITION_2022
SHARED_SCHEMA             SHARED_VIEW_2

Example: List tables available from an unsubscribed endpoint

SQL> exec dbms_cloud.create_credential('MY_CRED', 'BEARER_TOKEN', '123456') 
PL/SQL procedure successfully completed. 
SQL> column share_name format a13
SQL> column table_name format a20
SQL> column schema_name format a10 
SQL> select share_name, schema_name, table_name 
2   from dbms_share.discover_available_tables( 
3     endpoint=>'https://my_endpoint', 
4     credential_name=>'MY_CRED') 
5   order by 1, 2, 3; 

SHARE_NAME SCHEMA_NAM TABLE_NAME
------------- ---------- --------------------
DELTA_SHARING DEFAULT BOSTON-HOUSING
DELTA_SHARING DEFAULT COVID_19_NYT
DELTA_SHARING DEFAULT FLIGHT-ASA_2008
DELTA_SHARING DEFAULT LENDING_CLUB
DELTA_SHARING DEFAULT NYCTAXI_2019
DELTA_SHARING DEFAULT NYCTAXI_2019_PART
DELTA_SHARING DEFAULT OWID-COVID-DATA 

7 rows selected.

DROP_SHARE_LINK Procedure

Drop a share link created by the CREATE_SHARE_LINK procedure.

See CREATE_SHARE_LINK Procedure for further information.

Syntax

PROCEDURE DROP_SHARE_LINK  
(
    link_name            IN VARCHAR2,
    link_owner           IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
link_name The name of the share link to drop.
link_owner The owner of the share link to drop. Leave as null for the current schema.

DROP_SHARE_PROVIDER Procedure

Drop a subscription to a share provider.

Syntax

PROCEDURE DROP_SHARE_PROVIDER  
(
    provider_name        IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    drop_credentials     IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
provider_name The name of the share provider to drop.
owner The owner of the share provider to drop. Defaults to the current schema.
drop_credentials If TRUE, any credentials associated with the provider are dropped. If FALSE, credentials are not dropped.

ENABLE_DELTA_ENDPOINT Procedure

Create necessary ACLs that allow the specified user to connect to a delta endpoint. Admin privileges are required for this procedure.

Syntax

PROCEDURE ENABLE_DELTA_ENDPOINT  
(
    schema_name          IN VARCHAR2,
    delta_profile        IN CLOB,
    enabled              IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
schema_name The schema to enable or disable.
delta_profile The delta profile. Only the endpoint and tokenEndpoint are required.
enabled TRUE to enable and FALSE to disable.

FLUSH_SHARE_LINK_CACHE Procedure

Flush the cache of shares for a given share link. The list of shares for the remote endpoints is fetched instead of relying on cached values.

Syntax

PROCEDURE FLUSH_SHARE_LINK_CACHE  
(
    link_name            IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
link_name The name of the share link.
owner The owner of the share link. Defaults to the current schema.
auto_commit If TRUE, the changes are automatically committed. The default is TRUE.

FLUSH_SHARE_PROVIDER_CACHE Procedure

Flush the cache of shares for a given share provider. The list of shares for the remote endpoints is fetched instead of relying on cached values.

Syntax

PROCEDURE FLUSH_SHARE_PROVIDER_CACHE
(
  provider_name        IN VARCHAR2,
  owner                IN VARCHAR2 := NULL,
  auto_commit          IN BOOLEAN := TRUE
);

Parameters

Parameter Description
provider_name The name of the share provider.
owner The owner of the share provider. Defaults to the current schema.
auto_commit If TRUE, the changes are automatically committed. The default is TRUE.

GENERATE_SHARE_LINK_SELECT Procedure and Function

Generate a SELECT statement that returns data from a shared table.

Syntax

Procedure version of GENERATE_SHARE_LINK_SELECT. 

PROCEDURE GENERATE_SHARE_LINK_SELECT  
(
    share_link_name      IN VARCHAR2,
    share_schema_name    IN VARCHAR2,
    share_table_name     IN VARCHAR2,
    stmt                 IN OUT NOCOPY CLOB,
    share_link_owner     IN VARCHAR2 := NULL  
);
Function version of GENERATE_SHARE_LINK_SELECT.
 FUNCTION GENERATE_SHARE_LINK_SELECT  
(
    share_link_name      IN VARCHAR2,
    share_schema_name    IN VARCHAR2,
    share_table_name     IN VARCHAR2,
    share_link_owner     IN VARCHAR2 := NULL  
)  
RETURN CLOB;

Parameters

Parameter Description
share_link_name The name of the share link.
share_schema_name The name of the shared schema.
share_table_name The name of the shared table.
stmt The generated select statement.
share_link_owner The link owner. Defaults to the current schema.

GET_ORACLE_SHARE_LINK_INFO Function

Retrieve the cloud link name and namespace for an Oracle-to-Oracle share.

Syntax

FUNCTION GET_ORACLE_SHARE_LINK_INFO
(
  oracle_provider_id   IN VARCHAR2,
  share_name           IN VARCHAR2,
  share_schema_name    IN VARCHAR2,
  share_table_name     IN VARCHAR2
) 
RETURN CLOB;

Parameters

Parameter Description
oracle_provider_id The Oracle Provider ID from ALL_AVAILABLE_ORACLE_SHARE_PROVIDERS.
share_name The name of a share from ALL_AVAILABLE_ORACLE_SHARE_PROVIDERS.SHARES.
share_schema_name The name of a share schema from ALL_AVAILABLE_ORACLE_SHARE_PROVIDERS.SHARES.
share_table_name The name of a table from ALL_AVAILABLE_ORACLE_SHARE_PROVIDERS.SHARES.

Return

The return value is a JSON object containing three properties: schema, table, and dblink. The caller can use these three properties to fetch data using a query of the following form:

SELECT *
FROM <schema>.<table>@<dblink>

GET_SHARE_LINK_INFO Procedure

Get the endpoint(s), share type and share name along with any additional JSON metadata for a share link.

Syntax

PROCEDURE GET_SHARE_LINK_INFO  
(
    link_name            IN VARCHAR2,
    endpoint             IN OUT NOCOPY VARCHAR2,
    share_type           IN OUT NOCOPY VARCHAR2,
    share_name           IN OUT NOCOPY VARCHAR2,
    token_endpoint       IN OUT NOCOPY VARCHAR2,
    metadata             IN OUT NOCOPY BLOB,
    link_owner           IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
link_name The name of a share link (previously created by CREATE_SHARE_LINK).
endpoint OUT: The endpoint, if any, associated with the share link.
share_type OUT: The type of share link (for example, DELTA).
share_name OUT: The name of the linked share.
token_endpoint This parameter is no longer used.
metadata OUT: Optional metadata associated with the share link.
link_owner The owner of the share link. Defaults to the current schema.

GET_SHARE_PROVIDER_CREDENTIAL Procedure

Get the credential name to be used by the current user when it attempts to access the given delta share provider.

Syntax

PROCEDURE GET_SHARE_PROVIDER_CREDENTIAL  
(
    provider_name        IN VARCHAR2,
    share_credential     IN OUT NOCOPY VARCHAR2,
    get_token_credential IN OUT NOCOPY VARCHAR2,
    owner                IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
provider_name The name of the share provider.
share_credential OUT: The name of the credential associated with the provider. The credential name is returned without double quotes, as it would appear in the CREDENTIAL_NAME column of the USER_CREDENTIALS view.

See ALL_CREDENTIALS View.

get_token_credential This parameter is not used.
owner The owner is the name of the schema where the share provider was registered, not the owner of the credential. Defaults to the current schema.

GET_SHARE_PROVIDER_INFO Procedure

Get the endpoint string(s) and share type along with any additional JSON metadata for a share provider. For ORACLE share providers, the Oracle provider ID is returned in the endpoint argument.

Syntax

PROCEDURE GET_SHARE_PROVIDER_INFO  
(
    provider_name        IN VARCHAR2,
    endpoint             IN OUT NOCOPY VARCHAR2,
    share_type           IN OUT NOCOPY VARCHAR2,
    token_endpoint       IN OUT NOCOPY VARCHAR2,
    metadata             IN OUT NOCOPY BLOB,
    owner                IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
provider_name The name of the share provider.
endpoint The delta endpoint.
share_type The share type: DELTA or ORACLE.
token_endpoint This parameter is not used.
metadata The optional metadata that was associated with the share provider.
owner The owner of the share provider. Defaults to the current schema.

GET_SHARING_ID Function

Return an identifier that can be used as the sharing_id in the CREATE_SHARE_RECIPIENT procedure. This function can be used to share data between two users, the "provider" and the "recipient", in different databases.

See CREATE_SHARE_RECIPIENT Procedure for further information.

Syntax

FUNCTION GET_SHARING_ID  
(
    sharing_id_type      IN VARCHAR2 := SHARING_ID_TYPE_DATABASE  
)
  RETURN VARCHAR2;

Parameters

Parameter Description
sharing_id_type The type of sharing ID.

Usage

The flow is as follows:

  1. The recipient calls DBMS_SHARE.GET_SHARING_ID to get a unique identifier.
  2. The recipient sends this identifier (e.g. via email) to the provider.
  3. The provider calls DBMS_SHARE.CREATE_SHARE_RECIPIENT, passing in the identifier as sharing_id.
  4. The provider calls DBMS_SHARE.GRANT_TO_RECIPIENT to give the recipient access to shared data.

The sharing_id_type parameter is used to specify which database users can access the share following the above sequence.

  • DATABASE The share will be visible to any admin user in the database where GET_SHARING_ID was called.
  • COMPARTMENT The share will be visible to any admin user in any database in the same compartment where GET_SHARING_ID was called.
  • TENANCY The share will be visible to any admin user in any database in the same tenancy where GET_SHARING_ID was called.
  • REGION The share will be visible to any admin user in any database in the same region where GET_SHARING_ID was called.

OPEN_SHARE_LINK_CURSOR Procedure

Open a cursor that returns data from a shared table.

Syntax

PROCEDURE OPEN_SHARE_LINK_CURSOR  
(
    share_link_name      IN VARCHAR2,
    share_schema_name    IN VARCHAR2,
    share_table_name     IN VARCHAR2,
    table_cursor         IN OUT NOCOPY SYS_REFCURSOR,
    share_link_owner     IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
share_link_name The name of the share link.
share_schema_name The name of the shared schema.
share_table_name The name of the shared table.
table_cursor The cursor.
share_link_owner The link owner. Defaults to current schema.

REFRESH_BEARER_TOKEN_CREDENTIAL Procedure

Update one or more credentials created by CREATE_BEARER_TOKEN_CREDENTIAL or CREATE_CREDENTIALS by calling the registered token endpoints and fetching new bearer tokens. Note this procedure is called automatically by a scheduler job, ADP$BEARER_REFRESH_JOB, that runs every 50 minutes.

See CREATE_BEARER_TOKEN_CREDENTIAL Procedure or CREATE_CREDENTIALS Procedure and Function for further information.

Syntax

PROCEDURE REFRESH_BEARER_TOKEN_CREDENTIAL  
(
    credential_name           IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
credential_name The name of the credential to refresh.

RENAME_CLOUD_STORAGE_LINK Procedure

Rename a registered cloud storage link.

Syntax

PROCEDURE RENAME_CLOUD_STORAGE_LINK  
( 
    old_name             IN VARCHAR2,
    new_name             IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
old_name The current name of the cloud storage link.
new_name The new name of the cloud storage link.
owner The owner of the cloud storage link. Defaults to the current schema.
auto_commit If TRUE, the changes are automatically committed. The default is TRUE.

RENAME_SHARE_PROVIDER Procedure

Rename a registered share provider.

Syntax

PROCEDURE RENAME_SHARE_PROVIDER
(
  old_name             IN VARCHAR2,
  new_name             IN VARCHAR2,
  owner                IN VARCHAR2 := NULL
);

Parameters

Parameter Description
old_name The current name of the share provider.
new_name The new name of the share provider.
owner The owner of the share provider. Defaults to the current schema.

REMOVE_SHARE_SCHEMA Procedure

Remove a schema and all its contents from a share.

Syntax

PROCEDURE REMOVE_SHARE_SCHEMA  
(
    share_name           IN VARCHAR2,
    schema_name          IN VARCHAR2,
    share_owner          IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of the share.
schema_name The name of the schema to remove.
share_owner The owner of the share.
auto_commit If TRUE, the changes are automatically committed. The default is FALSE.

SET_SHARE_LINK_METADATA Procedure

Set the additional JSON metadata for the share link.

Syntax

PROCEDURE SET_SHARE_LINK_METADATA  
(
    link_name            IN VARCHAR2,
    metadata             IN SYS.JSON_OBJECT_T,
    replace_existing     IN BOOLEAN := FALSE,
    link_owner           IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
link_name The name of the share link.
metadata The new metadata.
replace_existing If TRUE, then all existing metadata is replaced by the new version. If FALSE, then the new value is merged with any existing metadata.
link_owner The owner of the share link. Defaults to the current schema.

SET_SHARE_PROVIDER_CREDENTIAL Procedure

Set the credential name to be used by the current user when it attempts to access the given share provider.

Syntax

PROCEDURE SET_SHARE_PROVIDER_CREDENTIAL  
(
    provider_name        IN VARCHAR2,
    share_credential     IN VARCHAR2,
    get_token_credential IN VARCHAR2 := NULL,
    owner                IN VARCHAR2 := NULL,
    check_if_exists      IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
provider_name The name of the share provider.
share_credential The bearer token credential.
get_token_credential This argument is ignored.
owner The owner of the share provider. Defaults to the current schema.
check_if_exists If TRUE (default), then validate that the credential exists.

SET_SHARE_PROVIDER_METADATA Procedure

Set additional JSON metadata for the share provider.

Syntax

PROCEDURE SET_SHARE_PROVIDER_METADATA  
(
    provider_name        IN VARCHAR2,
    metadata             IN SYS.JSON_OBJECT_T,
    replace_existing     IN BOOLEAN := FALSE,
    owner                IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
provider_name The name of the share provider.
metadata The new metadata.
replace_existing If TRUE, then all existing metadata is replaced by the new version. If FALSE, the new value is merged with any existing metadata.
owner The owner of the share provider. Defaults to the current schema.

UPDATE_BEARER_TOKEN_CREDENTIAL Procedure

Modify an attribute of a credential created by CREATE_CREDENTIALS or CREATE_BEARER_TOKEN_CREDENTIAL.

See CREATE_BEARER_TOKEN_CREDENTIAL Procedure or CREATE_CREDENTIALS Procedure and Function for further information.

Syntax

PROCEDURE UPDATE_BEARER_TOKEN_CREDENTIAL  
(
     credential_name           IN VARCHAR2,
     attribute                 IN VARCHAR2,
     new_value                 IN VARCHAR2  
);

Parameters

Parameter Description
credential_name The name of the credential to update.
attribute The attribute to update. One of 'BEARER_TOKEN', 'CLIENT_ID', 'CLIENT_SECRET', 'TOKEN_REFRESH_RATE'. The token endpoint can not be changed.
new_value The new value.

Example: Update the CLIENT_ID of a credential 

SQL> BEGIN
  2    dbms_share.create_bearer_token_credential(
  3      credential_name=>'MY_RENEWABLE_CREDENTIAL',
  4      token_endpoint=>'https://myserver/ords/share_provider/oauth/token',
  5      client_id=>'VXGQ_44s6qJ-K4WHUNM2yQ..',
  6      client_secret=>'y9ddppgwEmZl7adDHFQndw..');
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL> select credential_name, username from user_credentials where credential_name LIKE 'MY_RENEWABLE_CREDENTIAL%';
CREDENTIAL_NAME
------------------------------------------                           
USERNAME
-------------------------------------
MY_RENEWABLE_CREDENTIAL
BEARER_TOKEN
MY_RENEWABLE_CREDENTIAL$TOKEN_REFRESH_CRED
ABCDEF

Was this article helpful?