Oracle Cloud Infrastructure Documentation
Try Free Tier

Summary of Share Producer Subprograms

This table lists the DBMS_SHARE package procedures and functions used to produce shares for recipients.

Subprogram Description
ADD_TO_SHARE Procedure Add a table or view to a share.
ASSERT_SHAREABLE_OBJECT Procedure Return without error, if the object exists and can be shared.
ASSERT_SHARING_ID Procedure Run basic validation checks against a sharing id and return one in canonical form.
CAN_CREATE_SHARE Function This function checks to see if the current schema can create share recipients.
CAN_CREATE_SHARE_RECIPIENT Function This function checks to see if the current schema can create share recipients.
CLEAR_RECIPIENT_EVENTS Procedure Clear events from the share recipient event log.
CLEAR_SHARE_EVENTS Procedure Clear events from the share event log.
CREATE_BEARER_TOKEN_CREDENTIAL Procedure Create a credential suitable for use with delta share providers.
CREATE_CLOUD_STORAGE_LINK Procedure Create a named cloud storage URI link.
CREATE_OR_REPLACE_CLOUD_STORAGE_LINK Procedure Create or replace a named cloud storage URI.
CREATE_OR_REPLACE_SHARE_RECIPIENT Procedure Create or replace a share recipient.
CREATE_SHARE Procedure Create a named share object.
CREATE_SHARE_RECIPIENT Procedure Create a new share recipient.
DROP_CLOUD_STORAGE_LINK Procedure Drop a cloud storage link.
DROP_RECIPIENT Procedure Drop a recipient.
DROP_SHARE Procedure Drop a share and all of its contents.
DROP_SHARE_LINK_VIEW Procedure Drop a view that was created by the CREATE_SHARE_LINK_VIEW procedure.
DROP_SHARE_VERSION Procedure Drop a single share version.
DROP_SHARE_VERSIONS Procedure Drop a range of share versions.
DROP_UNUSED_SHARE_VERSIONS Procedure Drop any share version that is not currently in use.
ENABLE_SCHEMA Procedure Enable or disable a schema for sharing.
GET_ACTIVATION_LINK Function Generate the link that gets put into emails to the authorized recipient.
GET_PUBLISHED_IDENTITY Function Get data about the current user that was set by SET_PUBLISHED_IDENTITY.
GET_RECIPIENT_PROPERTY Function Return the value of a property for a recipient.
GET_SHARE_PROPERTY Function Get the property value of an existing share.
GET_SHARE_TABLE_PROPERTY Function Get the property value of an existing share table.
GRANT_TO_RECIPIENT Procedure Grant access on a share to a specific recipient.
POPULATE_SHARE_PROFILE Procedure Generate a delta profile for a recipient.
PUBLISH_SHARE Procedure Publish a share and return immediately.
PUBLISH_SHARE_WAIT Procedure Publish a share and wait until the background job is complete.
PURGE_DETACHED_FILES Procedure Delete or forget parquet files that have become detached from their shares.
REMOVE_FROM_SHARE Procedure Remove a table or view from a share.
RENAME_RECIPIENT Procedure Rename a recipient.
RENAME_SHARE Procedure Rename a share.
RENAME_SHARE_LINK Procedure Rename a registered share link.
RENAME_SHARE_SCHEMA Procedure Rename a share schema.
RENAME_SHARE_TABLE Procedure Rename a share table.
REVOKE_FROM_RECIPIENT Procedure Revoke access on a share from a specific recipient.
SET_CURRENT_SHARE_VERSION Procedure Change the current version of a share.
SET_PUBLISHED_IDENTITY Procedure Set data about the current user that will be supplied to recipients of published ORACLE shares.
SET_RECIPIENT_LOG_LEVEL Procedure Change the log level for an existing share recipient.
SET_SHARE_LOG_LEVEL Procedure Change the log level for an existing share.
SET_STORAGE_CREDENTIAL Procedure Set the access credential name for the given storage.
STOP_JOB Procedure Stop a running share job.
UNPUBLISH_SHARE Procedure Unpublish a share.
UPDATE_DEFAULT_RECIPIENT_PROPERTY Procedure Update the default recipient property values.
UPDATE_DEFAULT_SHARE_PROPERTY Procedure Update the default share property values.
UPDATE_RECIPIENT_PROPERTY Procedure Update a property of an existing recipient.
UPDATE_SHARE_JOB_PROPERTY Procedure Modify properties of a running share job.
UPDATE_SHARE_PROPERTY Procedure Update a property of an existing share.
UPDATE_SHARE_TABLE_PROPERTY Procedure Update the property value of an existing share table.
VALIDATE_CREDENTIAL Function Validate a credential name, converting it to canonical form first if required.
VALIDATE_SHARE_STORAGE Procedure Check to see if the given storage is suitable for versioned shares.
WAIT_FOR_JOB Procedure This procedure waits until the specified share job is complete.

Parent topic: DBMS_SHARE Package

ADD_TO_SHARE Procedure

Add a table or view to a share. The object becomes visible to any external user who has been granted access to the share.

Syntax

PROCEDURE ADD_TO_SHARE  
(
    share_name           IN VARCHAR2,
    table_name           IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    share_table_name     IN VARCHAR2 := NULL,
    share_schema_name    IN VARCHAR2 := NULL,
    object_metadata      IN SYS.JSON_OBJECT_T := NULL,
    replace_existing     IN BOOLEAN := FALSE,
    share_owner          IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of an existing share to which the object is granted.
table_name The name of the entity to share (for example, a table or view name).
owner The owner of the entity to share. The default is to current schema.
share_table_name The externally visible name of the table. By default, this is the uppercased table_name.
share_schema_name The externally visible schema where the table will be placed. By default this is the uppercased table owner. The schema is created automatically if it does not already exist.
object_metadata Optional metadata to associate with the shared entity.
replace_existing If TRUE, and this share_table_name already exists, the existing table_name is dropped from the share and replaced with this table_name.

If FALSE, and this share_table_name already exists, an exception is raised indicating the share table is already used.

share_owner The owner of the share.
auto_commit If TRUE, this procedure call commits changes that are not visible externally until the commit takes place. The default value is FALSE, which means that the user must COMMIT after running this call in order to make the change visible.

ASSERT_SHAREABLE_OBJECT Procedure

Return without error, if the object exists and can be shared.

Syntax

PROCEDURE ASSERT_SHAREABLE_OBJECT
(
  object_name          IN VARCHAR2,
  object_owner         IN VARCHAR2 := NULL
);

Parameters

Parameter Description
object_name The name of the object.
object_owner The owner of the object. Defaults to current schema.

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.

CAN_CREATE_SHARE Function

This function checks to see if the current schema can create share recipients. If shares can be created, a 1 is returned and 0 otherwise.

Syntax

FUNCTION CAN_CREATE_SHARE
RETURN NUMBER;

Example: Before and after enabling the admin schema

SQL> select dbms_share.can_create_share from dual;
 CAN_CREATE_SHARE
----------------
               0 
SQL> exec dbms_share.enable_schema('admin') 
PL/SQL procedure successfully completed. 
SQL> select dbms_share.can_create_share from dual; 
CAN_CREATE_SHARE
----------------
               1

CAN_CREATE_SHARE_RECIPIENT Function

This function checks to see if the current schema can create share recipients. If shares can be created a 1 is returned, and 0 otherwise.

Syntax

FUNCTION CAN_CREATE_SHARE_RECIPIENT
RETURN NUMBER;

CLEAR_RECIPIENT_EVENTS Procedure

Clear events from the share recipient event log.

Syntax

PROCEDURE CLEAR_RECIPIENT_EVENTS 
(
   recipient_name       IN VARCHAR2,
   from_time            IN TIMESTAMP WITH TIME ZONE := NULL,
   to_time              IN TIMESTAMP WITH TIME ZONE := NULL,
   recipient_owner      IN VARCHAR2 := NULL 
);

Parameters

Parameter Description
recipient_name The local name of the share recipient.
from_time Earliest time for events that should be cleared or NULL.
to_time Latest time for events that should be cleared or NULL.
recipient_owner The schema that owns the recipient.

CLEAR_SHARE_EVENTS Procedure

Clear events from the share event log.

Syntax

PROCEDURE CLEAR_SHARE_EVENTS 
(
    share_name           IN VARCHAR2,
    from_time            IN TIMESTAMP WITH TIME ZONE := NULL,
    to_time              IN TIMESTAMP WITH TIME ZONE := NULL,
    share_owner          IN VARCHAR2 := NULL 
);

Parameters

Parameter Description
share_name The name of the share.
from_time Earliest time for events that should be cleared or NULL.
to_time Latest time for events that should be cleared or NULL.
share_owner The schema that owns the share.

CREATE_BEARER_TOKEN_CREDENTIAL Procedure

Create a credential suitable for use with delta share providers.

This is similar to the CREATE_CREDENTIALS call, but it takes explicit values instead of a delta sharing profile. See CREATE_CREDENTIALS Procedure and Function for further information.

Syntax

PROCEDURE CREATE_BEARER_TOKEN_CREDENTIAL 
(
   credential_name      IN VARCHAR2,
   bearer_token         IN VARCHAR2 := NULL,
   token_endpoint       IN VARCHAR2 := NULL,
   client_id            IN VARCHAR2 := NULL,
   client_secret        IN VARCHAR2 := NULL,
   token_refresh_rate   IN PLS_INTEGER := 3600 
);

Parameters

Parameter Description
credential_name The name of the new credential.
bearer_token The bearer token, if known.
token_endpoint The endpoint to call to get a new token.
client_id The username to send to the token_endpoint.
client_secret The password to send to the token_endpoint.
token_refresh_rate An optional refresh time, in seconds.

Example: Credential with a fixed bearer token

In its simplest form, this procedure is equivalent to calling DBMS_CREDENTIAL with a user name of'BEARER_TOKEN' and the bearer token itself as password. 

SQL> exec dbms_share.create_bearer_token_credential('MY_FIXED_CREDENTIAL', 'FF42322D27D4C2DEE05392644664351E')
PL/SQL procedure successfully completed. 
SQL> select username from user_credentials where credential_name = 'MY_FIXED_CREDENTIAL';
USERNAME
------------------------------------------------------------------------------------------------------------------------
BEARER_TOKEN

Example: Credential with a renewable bearer token

Create a credential that contains a short lived bearer token obtained from a token endpoint. The bearer token will be refreshed once every hour using a second credential, which is populated from the client id and secret.

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    VXGQ_44s6qJ-K4WHUNM2yQ..

CREATE_CLOUD_STORAGE_LINK Procedure

Create a named cloud storage URI link. A cloud storage link is a named association between an OCI bucket URI, and a local credential name.

Note

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

Syntax

PROCEDURE CREATE_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.
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. Leave as NULL for the current user.
metadata (Optional) A JSON metadata document containing additional information.
auto_commit The default is TRUE. If TRUE, this transaction is committed. If FALSE, the user must commit the transaction. Changes are not visible until the commit takes place.

Example

In this example, a cloud storage link named MY_SHARE_STORAGE is created on the given URL. 

SQL> BEGIN
  2    dbms_share.create_cloud_storage_link(
  3      'MY_SHARE_STORAGE',
  4      'https://objectstorage.../n/abcdef/b/my_bucket/o' );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> select storage_link_name from user_lineage_cloud_storage_links;

STORAGE_LINK_NAME
----------------------------------------------------------------------------------------
MY_SHARE_STORAGE

CREATE_OR_REPLACE_CLOUD_STORAGE_LINK Procedure

Create or replace a named cloud storage URI. A cloud storage link is a named association between an OCI bucket URI, and a local credential name.

Note

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

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_OR_REPLACE_SHARE_RECIPIENT Procedure

Create or replace a share recipient. You must provide at least an email address or sharing id.

Syntax

PROCEDURE CREATE_OR_REPLACE_SHARE_RECIPIENT  
(
    recipient_name       IN VARCHAR2,
    description          IN VARCHAR2 := NULL,
    recipient_owner      IN VARCHAR2 := NULL,
    email                IN VARCHAR2 := NULL,
    sharing_id           IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
recipient_name The local name of the share recipient. Some names are not allowed (e.g. MY_TENANCY).
description A description of the recipient.
recipient_owner The schema that owns the recipient.
email An email that will be registered for the OAUTH user.
sharing_id The sharing id of the recipient, from GET_SHARING_ID Function.

CREATE_SHARE Procedure

Create a named share object.

Syntax

PROCEDURE CREATE_SHARE
(
  share_name           IN VARCHAR2,
  share_type           IN VARCHAR2 := SHARE_TYPE_VERSIONED,
  storage_link_name    IN VARCHAR2 := NULL,
  storage_link_owner   IN VARCHAR2 := NULL,
  description          IN VARCHAR2 := NULL,
  public_description   IN VARCHAR2 := NULL,
  configuration        IN SYS.JSON_OBJECT_T := NULL,
  force_create         IN BOOLEAN  := FALSE,
  share_owner          IN VARCHAR2 := NULL,
  auto_commit          IN BOOLEAN := FALSE,
  log_level            IN PLS_INTEGER := LOG_LEVEL_BASIC,
  run_storage_tests    IN BOOLEAN := TRUE
);

Parameters

Parameter Description
share_name The name of the share. This name is uppercased since delta shares are case insensitive. The name follows standard Oracle conventions, so it must be 128 characters or fewer and must be double quoted if it is not a simple identifier. The only difference is that it will be uppercased even if it is double quoted.
share_type The type of share. For information on constants used for this parameter, see descriptions for Share Types in DBMS_SHARE Constants.
storage_link_name The name of the cloud storage link where the objects are created. The user must have read/write access to this storage and have the ability to create pre-authenticated requests on the storage. The parameter is required for versioned shares, and optional for current shares.
storage_link_owner The owner of the cloud storage link where objects are created.
description A local description for the share.
public_description A public description for the share.
configuration A configuration document that defines how objects are created.
force_create Set force_create to TRUE to redefine the share if it exists.
share_owner The owner of the share.
auto_commit If TRUE, this procedure call commits changes that are not visible externally until the commit takes place. The default value is FALSE, which means that the user must COMMIT after running this call in order to make the change visible.
log_level Event logging level. This controls the amount of detail recorded in the ALL_SHARE_EVENTS View. For information on constants used for this parameter, see descriptions for Log Level in DBMS_SHARE Constants.
run_storage_tests If this parameter is TRUE then DBMS_SHARE runs tests to verify that the specified share storage link has the correct privileges.

If this parameter is FALSE, then the procedure does not run any checks at creation time. This may lead to errors later, during publication or consumption of the share.

Oracle recommends that you specify TRUE for this parameter.

CREATE_SHARE_RECIPIENT Procedure

Create a new share recipient.

Syntax

PROCEDURE CREATE_SHARE_RECIPIENT  
(
    recipient_name       IN VARCHAR2,
    description          IN VARCHAR2 := NULL,
    recipient_owner      IN VARCHAR2 := NULL,
    email                IN VARCHAR2 := NULL,
    sharing_id           IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
recipient_name The local name of the share recipient. Some names are not allowed, for example: MY_TENANCY.
description A description of the recipient.
recipient_owner The schema that owns the recipient.
email An email that will be registered for the OAUTH user. You must provide at least one of email or sharing id.
sharing_id The sharing id of the recipient from GET_SHARING_ID Function. You must provide at least one of email or sharing id.

DROP_CLOUD_STORAGE_LINK Procedure

Drop a cloud storage link.

Syntax

PROCEDURE DROP_CLOUD_STORAGE_LINK  
(
    storage_link_name    IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
storage_link_name The name of the cloud storage link to drop.
owner The owner of the cloud storage link. Leave as NULL for the current user.
auto_commit If TRUE, the code automatically commits after dropping the link. The default is TRUE.

DROP_RECIPIENT Procedure

Drop a recipient. All access to the recipient will be revoked.

Syntax

PROCEDURE DROP_RECIPIENT  
(
    recipient_name       IN VARCHAR2,
    owner                IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
recipient_name The name of the share recipient.
owner The schema that defines the recipient.

DROP_SHARE Procedure

Drop a share and all of its contents. Future access to the share by consumers will end.

Syntax

PROCEDURE DROP_SHARE
(
  share_name           IN VARCHAR2,
  share_owner          IN VARCHAR2 := NULL,
  destroy_objects      IN BOOLEAN := TRUE
);

Parameters

Parameter Description
share_name The name of the share to drop.
share_owner The owner of the share to drop.
destroy_objects If TRUE, delete all objects created on behalf of the share. The default is TRUE.

DROP_SHARE_LINK_VIEW Procedure

Drop a view that was created by the CREATE_SHARE_LINK_VIEW procedure.

See CREATE_SHARE_LINK_VIEW Procedure for further information.

Syntax

PROCEDURE DROP_SHARE_LINK_VIEW  
(
    view_name            IN VARCHAR2,
    view_owner           IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
view_name The name of the new view.
view_owner The view owner. The default is the current schema.

DROP_SHARE_VERSION Procedure

Drop a single share version. Note that you cannot drop the current version.

Syntax

PROCEDURE DROP_SHARE_VERSION  
(
    share_name           IN VARCHAR2,
    share_version        IN NUMBER,
    destroy_objects      IN BOOLEAN := TRUE,
    force_drop           IN BOOLEAN := FALSE,
    share_owner          IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
share_name The name of the share.
share_version The version to drop. You can not drop the current version.
destroy_objects Destroy any associated object in cloud storage, if applicable.
force_drop Drop the share version even if there is an outstanding PAR file on the version.
share_owner The owner of the share.

DROP_SHARE_VERSIONS Procedure

Drop a range of share versions. Note that you cannot drop the current version using this procedure.

Syntax

PROCEDURE DROP_SHARE_VERSIONS  
(
    share_name           IN VARCHAR2,
    from_share_version   IN NUMBER,
    to_share_version     IN NUMBER,
    destroy_objects      IN BOOLEAN := TRUE,
    force_drop           IN BOOLEAN := FALSE,
    share_owner          IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
share_name The name of the share.
from_share_version The lowest version to drop.
to_share_version The highest version to drop.
destroy_objects Destroy any associated object in cloud storage, if applicable.
force_drop Drop the share version even if there is an outstanding PAR file on the version.
share_owner The owner of the share.

DROP_UNUSED_SHARE_VERSIONS Procedure

Drop any share version that is not currently in use.

Syntax

PROCEDURE DROP_UNUSED_SHARE_VERSIONS  
(
    share_name           IN VARCHAR2,
    destroy_objects      IN BOOLEAN := TRUE,
    share_owner          IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
share_name The name of the share.
destroy_objects Destroy any associated object in cloud storage, if applicable.
share_owner The owner of the share.

ENABLE_SCHEMA Procedure

Enable or disable a schema for sharing. This procedure must be run by the ADMIN user.

Syntax

Users can consume delta shares without being enabled with this procedure, but they cannot create or publish shares. Sharing is disabled by default for all schemas, including ADMIN.

PROCEDURE ENABLE_SCHEMA  
(
    schema_name          IN VARCHAR2,
    enabled              IN BOOLEAN := TRUE,
    privileges           IN PLS_INTEGER := NULL
);

Parameters

Parameter Description
schema_name The schema to enable or disable.
enable TRUE to enable, FALSE to disable.
privileges The privileges argument has bitmap values. If you leave the argument as NULL, it defaults to PRIV_CREATE_SHARE + PRIV_CREATE_RECIPIENT + PRIV_CONSUME_ORACLE_SHARE.

Bitmap values are as follows:
  • PRIV_CREATE_SHARE
    Allow users to create an publish shares in their own schema.
    PRIV_CREATE_SHARE         CONSTANT PLS_INTEGER := 1;
  • PRIV_CREATE_RECIPIENT

    Allow users to create share recipients in their own schema.

    PRIV_CREATE_RECIPIENT     CONSTANT PLS_INTEGER := 2;
  • PRIV_CONSUME_ORACLE_SHARE

    Allow users to consume Oracle-to-Oracle live shares.

    PRIV_CONSUME_ORACLE_SHARE CONSTANT PLS_INTEGER := 4;
  • PRIV_ORDS_ACL

    Grant an ACL to the user on the local ORDS endpoint. This is required for the user to generate bearer tokens on locally created shares.

    PRIV_ORDS_ACL             CONSTANT PLS_INTEGER := 8;

GET_ACTIVATION_LINK Function

Generate the link that gets put into emails to the authorized recipient. This activation link leads to the download page, where the recipient clicks a button to get the delta profile.

Syntax

FUNCTION GET_ACTIVATION_LINK
(
  recipient_name       IN VARCHAR2,
  recipient_owner      IN VARCHAR2 := NULL,
  expiration           IN PLS_INTEGER := 259200,
  invalidate_previous  IN BOOLEAN := TRUE
)
RETURN VARCHAR2;

Parameters

Parameter Description
recipient_name The local name of the recipient.
recipient_owner The schema that owns the recipient.
expiration Number of seconds before the activation link expires.
invalidate_previous If TRUE, which is the default, a previously generated activation link is made invalid. If FALSE, a previously generated activation link remains valid.

Example: Print activation link to the screen

SQL> exec dbms_share.create_share_recipient('new_recipient', email=>'anyone@example.com')

PL/SQL procedure successfully completed.

SQL> column PROFILE format A200
SQL> variable sprof varchar2(32767)
SQL> declare
  2    profile sys.json_object_t;
  3  begin
  4    dbms_share.populate_share_profile('NEW_RECIPIENT', profile);
  5    :sprof := profile.to_string;
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(dbms_share.get_activation_link('NEW_RECIPIENT'))
http://.../ords/_adpshr/delta-sharing/download?key=43BA....YXJlX3Rlc3Q= 

PL/SQL procedure successfully completed.

GET_PUBLISHED_IDENTITY Function

Get data about the current user that was set by SET_PUBLISHED_IDENTITY.

Syntax

FUNCTION GET_PUBLISHED_IDENTITY
  RETURN CLOB;

Example

SQL> declare
  2    id_json json_object_t := json_object_t();
  3  begin
  4    id_json.put('name', 'Demo Publisher');
  5    id_json.put('description', 'Documentation Share Provider');
  6    id_json.put('contact', 'null@example.com');
  7    dbms_share.set_published_identity(id_json);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select json_query(dbms_share.get_published_identity, '$' pretty) "Published Identity"
  2  from dual;

Published Identity-
-------------------------------------------------------------------------------
{
  "name" : "Demo Publisher",
  "description" : "Documentation Share Provider",
  "contact" : "null@example.com"
}

GET_RECIPIENT_PROPERTY Function

Return the value of a property for a recipient.

Syntax

FUNCTION GET_RECIPIENT_PROPERTY
(
  recipient_name       IN VARCHAR2,
  recipient_property   IN VARCHAR2,
  recipient_owner      IN VARCHAR2 := NULL
)RETURN VARCHAR2;

Parameters

Parameter Description
recipient_name The name of the recipient.
recipient_property The property to get. These properties include:
  • PROP_SHARE_DESC
  • PROP_SHARE_PUBLIC_DESC
  • PROP_SHARE_VERSION_ACCESS
  • PROP_SHARE_JOB_NAME
  • PROP_SHARE_SPLIT_SIZE
  • PROP_SHARE_LOG_LEVEL
  • PROP_SHARE_JOB_DOP
  • PROP_SHARE_JOB_CLASS
  • PROP_SHARE_JOB_PRIORITY
  • PROP_SHARE_VERSION_ACCESS

For information on constants used for this parameter, see descriptions for Share Properties in DBMS_SHARE Constants.

recipient_owner The owner of the recipient. Defaults to current user.

GET_SHARE_PROPERTY Function

Get the property value of an existing share.

Syntax

FUNCTION GET_SHARE_PROPERTY
(
  share_name       IN VARCHAR2,
  share_property   IN VARCHAR2,
  share_owner      IN VARCHAR2 := NULL
)
RETURN VARCHAR2

Parameters

Parameter Description
share_name The name of the share.
share_property The property value to get. For information on constants used for this parameter, see descriptions for Share Properties in DBMS_SHARE Constants.
share_owner The owner of the share. Defaults to current user.

GET_SHARE_TABLE_PROPERTY Function

Get the property value of an existing share table.

Syntax

FUNCTION GET_SHARE_TABLE_PROPERTY
(
  share_name           IN VARCHAR2,
  share_table_name     IN VARCHAR2,
  share_table_property IN VARCHAR2,
  share_schema_name    IN VARCHAR2 := NULL,
  share_owner          IN VARCHAR2 := NULL
)RETURN VARCHAR2;

Parameters

Parameter Description
share_name The name of the share.
share_table_name The name of the share table.
share_table_property The share table property to update. For information on constants used for this parameter, see descriptions for Share Table Properties in DBMS_SHARE Constants.
share_schema_name The name of the share schema. Defaults to uppercase of current user.
share_owner The owner of the share.

GRANT_TO_RECIPIENT Procedure

Grant access on a share to a specific recipient. The share and recipient must both belong to the same schema.

Syntax

PROCEDURE GRANT_TO_RECIPIENT  
(
    share_name           IN VARCHAR2,
    recipient_name       IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of the share to grant.
recipient_name The name of the recipient.
owner The owner of both the share and recipient.
auto_commit The auto_commit parameter is ignored. This procedure will always commit.

POPULATE_SHARE_PROFILE Procedure

Generate a delta profile for a recipient. You could print this to the screen or upload it somewhere. For example, to an object bucket using DBMS_CLOUD.EXPORT_DATA.

Syntax

PROCEDURE POPULATE_SHARE_PROFILE  
(
    recipient_name       IN VARCHAR2,
    share_profile        IN OUT NOCOPY SYS.JSON_OBJECT_T,
    recipient_owner      IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
recipient_name The local name of the recipient.
share_profile The share profile, without bearer token.
recipient_owner The schema that owns the recipient.

Example: Print the profile to the screen.

SQL> EXEC DBMS_SHARE.CREATE_SHARE_RECIPIENT('new_recipient', email=>'anyone@example.com')
 
PL/SQL procedure successfully completed. 
SQL> column PROFILE format A200
SQL> variable sprof varchar2(32767)
SQL> declare
  2    profile sys.json_object_t;
  3  begin
  4    dbms_share.populate_share_profile('NEW_RECIPIENT', profile);
  5    :sprof := profile.to_string;
  6  end;
  7  /

PL/SQL procedure successfully completed. 

SQL> select json_query(:sprof, '$' pretty) "PROFILE" from dual;
PROFILE------------------------------------------------------------------------------------------------------------------------
{
  "shareCredentialsVersion" : 1,
  "endpoint" : "https://.../ords/share_test/_delta_sharing/",
  "bearerToken" : "mc7puvhqCpU6xjTOjRdl_w",
  "tokenEndpoint" : "https://.../ords/share_test/oauth/token",
  "clientID" : "VXGQ_44s6qJ-K4WHUNM2yQ..",
  "clientSecret" : "y9ddppgwEmZl7adDHFQndw.."
}

PUBLISH_SHARE Procedure

Publish a share and return immediately.

The publication will continue in the background. You can check on the status of the job by querying the USER_SHARE_JOBS view. See USER_SHARE_JOBS View for further information.

Syntax

PROCEDURE PUBLISH_SHARE  
(
    share_name           IN VARCHAR2,
    share_owner          IN VARCHAR2 := NULL,
    drop_prior_versions  IN BOOLEAN := FALSE,
    share_job_dop        IN NUMBER := NULL,
    share_job_priority   IN NUMBER := NULL,
    job_class            IN VARCHAR2 := 'DEFAULT_JOB_CLASS',
    force_job_class      IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of the share to publish.
share_owner The share owner, which must be the current user or NULL.
drop_prior_versions Set to TRUE if you want to drop all prior versions of the share. Note that versions are only dropped if there are no outstanding Pre-Authenticated Requests (PARs).
share_job_dop Specify the maximum number of dbms_scheduler jobs that will be used to publish the share. Leave as NULL to use the system default number.
share_job_priority Specify a relative priority of this share publication compared to others. If two shares are being published at the same time, then the one with the highest priority is processed first even if it was started later.
job_class The scheduler job class, from all_scheduler_job_classes, that are used to publish the share.
force_job_class Use the specifiedjob_class even if the admin has defined a different default job class.

PUBLISH_SHARE_WAIT Procedure

Publish a share and wait until the background job is complete. The publication continues even if the call is interrupted.

Syntax

PROCEDURE PUBLISH_SHARE_WAIT  
(
       share_name           IN VARCHAR2,
       share_owner          IN VARCHAR2 := NULL,
       drop_prior_versions  IN BOOLEAN := FALSE,
       share_job_dop        IN NUMBER := NULL,
       share_job_priority   IN NUMBER := NULL,
       job_class            IN VARCHAR2 := 'DEFAULT_JOB_CLASS',
       force_job_class      IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of the share to publish.
share_owner The share owner, which must be the current user or NULL.
drop_prior_versions Set to TRUE if you want to drop all prior versions of the share. Note that versions are only dropped if there are no outstanding Pre-Authenticated Requests (PARs).
share_job_dop Specify the maximum number of dbms_scheduler jobs that will be used to publish the share. Leave as NULL to use the system default number.
share_job_priority Specify a relative priority of this share publication compared to others. If two shares are being published at the same time, then the one with the highest priority is processed first even if it was started later.
job_class The scheduler job class, from all_scheduler_job_classes, that are used to publish the share.
force_job_class Use the specifiedjob_class even if the admin has defined a different default job class.

PURGE_DETACHED_FILES Procedure

Delete or forget parquet files that have become detached from their shares.

Syntax

PROCEDURE PURGE_DETACHED_FILES  
(
    file_pattern         IN VARCHAR2 := '%',
    credential_name      IN VARCHAR2 := NULL,
    purge_mode           IN PLS_INTEGER := PURGE_DROP,
    owner_id             IN NUMBER := SYS_CONTEXT('USERENV', 'CURRENT_USERID')  
);

A version of PURGE_DETACHED_FILES that returns the ID of the purge job, if any. 

PROCEDURE PURGE_DETACHED_FILES  
(
    purge_job_id         IN OUT NOCOPY NUMBER,
    file_pattern         IN VARCHAR2 := '%',
    credential_name      IN VARCHAR2 := NULL,
    purge_mode           IN PLS_INTEGER := PURGE_DROP,
    owner_id             IN NUMBER := SYS_CONTEXT('USERENV', 'CURRENT_USERID')  
);

Parameters

Parameter Description
purge_job_id The purge job ID.
file_pattern An optional LIKE pattern for the files that are to be purged.
credential_name An optional credential to be used to delete the files.
purge_mode Specifies how the files are purged. Purge mode values include:
  • PURGE_DROP
    Attempt to drop the files, using the specified credential. If the file cannot be dropped, then the file will continue to be listed in the *_SHARE_DETACHED_FILES views.
    PURGE_DROP             CONSTANT PLS_INTEGER := 1;
  • PURGE_DROP_FORCE
    Attempt to drop the files, using the specified credential. The file will be removed from the *_SHARE_DETACHED_FILES views even if the attempt to drop the file fails again.
    PURGE_DROP_FORCE       CONSTANT PLS_INTEGER := 2;
  • PURGE_FORGET
    Remove the files *_SHARE_DETACHED_FILES views without attempting to drop them.
    PURGE_FORGET           CONSTANT PLS_INTEGER := 3;
owner_id The owner ID whose files are to be purged.

REMOVE_FROM_SHARE Procedure

Remove a table or view from a share.

Syntax

PROCEDURE REMOVE_FROM_SHARE  
(
    share_name           IN VARCHAR2,
    share_table_name     IN VARCHAR2,
    share_schema_name    IN VARCHAR2 := NULL,
    share_owner          IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of an existing share to which the object is revoked.
share_table_name The name of the share table to revoke. This must match the externally visible name, so the input will be uppercased.
share_schema_name The name of the share schema. Defaults to uppercase of current user.
share_owner The owner of the share.
auto_commit If TRUE, this procedure call commits changes that are not visible externally until the commit takes place. The default value is FALSE, which means that the user must COMMIT after running this call in order to make the change visible.

RENAME_RECIPIENT Procedure

Rename a recipient. This procedure only changes the local name of the recipient. The external definition of the recipient, for example the name of the OAUTH user or sharing id, is not changed.

Syntax

PROCEDURE RENAME_RECIPIENT  
(
    old_recipient_name   IN VARCHAR2,
    new_recipient_name   IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
old_recipient_name The current name of the share recipient.
new_recipient_name The new name of the share recipient.
owner The schema that defines the recipient.
auto_commit If TRUE, the changes are automatically committed. Changes are not visible externally until the commit takes place. The default is TRUE.

RENAME_SHARE Procedure

Rename a share. Care should be take with this procedure since the change effects any existing consumers whose access is based on the previous name. Consumers are not notified directly or updated.

Syntax

PROCEDURE RENAME_SHARE  
(
    old_share_name       IN VARCHAR2,
    new_share_name       IN VARCHAR2,
    share_owner          IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
old_share_name The current name of the share.
new_share_name The new name of the share.
share_owner The owner of the share.
auto_commit If TRUE, the changes are automatically committed. Changes are not visible externally until the commit takes place. The default is TRUE.

RENAME_SHARE_LINK Procedure

Rename a registered share link.

Syntax

PROCEDURE RENAME_SHARE_LINK  
(
    old_name             IN VARCHAR2,
    new_name             IN VARCHAR2,
    link_owner           IN VARCHAR2 := NULL  
);

Parameters

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

RENAME_SHARE_SCHEMA Procedure

Rename a share schema.

Syntax

PROCEDURE RENAME_SHARE_SCHEMA  
(
    share_name           IN VARCHAR2,
    old_schema_name      IN VARCHAR2,
    new_schema_name      IN VARCHAR2,
    share_owner          IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of the share.
old_schema_name The old name of the schema.
new_schema_name The new name of the schema.
share_owner The owner of the share. Defaults to the current schema.
auto_commit If TRUE, the changes are automatically committed. Changes are not visible externally until the commit takes place. The default is FALSE.

RENAME_SHARE_TABLE Procedure

Rename a share table.

Syntax

PROCEDURE RENAME_SHARE_TABLE  
(
    share_name           IN VARCHAR2,
    share_schema_name    IN VARCHAR2,
    old_share_table_name IN VARCHAR2,
    new_share_table_name IN VARCHAR2,
    share_owner          IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of the share.
share_schema_name The name of the share schema.
old_share_table_name The old name of the share table.
new_share_table_name The new name of the share table.
share_owner The owner of the share.
auto_commit If TRUE, the changes are automatically committed. Changes are not visible externally until the commit takes place. The default is FALSE.

REVOKE_FROM_RECIPIENT Procedure

Revoke access on a share from a specific recipient.

Syntax

PROCEDURE REVOKE_FROM_RECIPIENT  
(
    share_name           IN VARCHAR2,
    recipient_name       IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of the share to revoke.
recipient_name The name of the recipient.
owner The owner of the share and recipient.
auto_commit If TRUE, the changes are automatically committed. Changes are not visible externally until the commit takes place. The default is FALSE.

SET_CURRENT_SHARE_VERSION Procedure

Change the current version of a share.

Syntax

PROCEDURE SET_CURRENT_SHARE_VERSION  
(
    share_name         IN VARCHAR2,
    share_version      IN NUMBER,
    share_owner        IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
share_name The name of the share.
share_version The new version or NULL. The version must exist and must be valid. If the share_version is NULL, then no version will be marked as CURRENT and the share will be "unpublished".
share_owner The owner of the share. Defaults to the current schema.

SET_PUBLISHED_IDENTITY Procedure

Set data about the current user that will be supplied to recipients of published ORACLE shares.

Syntax

PROCEDURE SET_PUBLISHED_IDENTITY  
(
    metadata             IN SYS.JSON_OBJECT_T := NULL  
);

Parameters

Parameter Description
metadata If the provider identity has already been set, then only the items that the caller wants to update need to be included in the JSON. Supplying a NULL value for an item causes that item to be removed from the stored identity. However, "name", "description" and "contact" cannot be removed in this way.

If the metadata argument is NULL, the existing provider identity is deleted. This can only happen if the current user has no published shares.

If the provider identity has not yet been set, the JSON must contain at least:
  • name (<=128 bytes)
  • description (<=4000 bytes)
  • contact (<=4000 bytes) values

Additional items may be included at the caller's discretion.

Example: Include additional items in the JSON metadata

In addition to the required "name", "description", and "contact", this example includes additional item "schedule".
{
  "name" : "A sample share provider",
  "description" : "Test of share provider metadata",
  "contact" : "provider1@example.com".
  "schedule" : "New data provided on alternate rainy Fridays"
}

Example: Update "description" and remove "schedule"

To update "description" and remove "schedule", the following JSON can be used:
{
  "description" : "The Share Provider You Can Trust!(tm)",
  "schedule" : null
}

SET_RECIPIENT_LOG_LEVEL Procedure

Change the log level for an existing share recipient.

Syntax

PROCEDURE SET_RECIPIENT_LOG_LEVEL
(
  recipient_name       IN VARCHAR2,
  log_level            IN PLS_INTEGER,
  recipient_owner      IN VARCHAR2 := NULL
);

Parameters

Parameter Description
recipient_name The local name of the share recipient.
log_level Event logging level. For information on constants used for this parameter, see descriptions for Log Level in DBMS_SHARE Constants.
recipient_owner The schema that owns the recipient.

SET_SHARE_LOG_LEVEL Procedure

Change the log level for an existing share.

Syntax

PROCEDURE SET_SHARE_LOG_LEVEL
(
  share_name           IN VARCHAR2,
  log_level            IN PLS_INTEGER,
  share_owner          IN VARCHAR2 := NULL
);

Parameters

Parameter Description
share_name The name of the share.
log_level Event logging level. For information on constants used for this parameter, see descriptions for Log Level in DBMS_SHARE Constants.
share_owner The owner of the share.

SET_STORAGE_CREDENTIAL Procedure

Set the credential name used by the current user when it attempts to access the given storage.

Syntax

PROCEDURE SET_STORAGE_CREDENTIAL  
(
    storage_link_name    IN VARCHAR2,
    credential_name      IN VARCHAR2,
    owner                IN VARCHAR2 := NULL,
    check_if_exists      IN BOOLEAN := TRUE,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
storage_link_name The name of a cloud storage link previously created using CREATE_CLOUD_STORAGE_LINK Procedure.
credential_name The name of a local credential that gives access to the storage.

The credentials used for share storage must be able to read, write, delete, and manage pre-authenticated requests. See Using Pre-Authenticated Requests
owner The owner of the cloud storage link. Leave as NULL for the current user.
check_if_exists If check_if_exists is TRUE, then the function will also confirm that the credential exists for the current user.
auto_commit The default is TRUE. If TRUE, this transaction is committed. If FALSE, the user must commit the transaction. Changes are not visible until the commit takes place.

STOP_JOB Procedure

Attempt to stop a running share job. The procedure should return quickly, but it may take some time for the associated job to stop.

Syntax

PROCEDURE STOP_JOB
(
  share_job_id         IN NUMBER,
  share_job_owner      IN VARCHAR2 := NULL
);

Parameters

Parameter Description
share_job_id The ID of the share job.
share_job_owner The owner of the job. Defaults to the current schema.

UNPUBLISH_SHARE Procedure

Unpublish a share.

Syntax

PROCEDURE UNPUBLISH_SHARE  
(
    share_name           IN VARCHAR2,
    out_share_job_id     IN OUT NOCOPY NUMBER,
    share_owner          IN VARCHAR2 := NULL,
    drop_all_versions    IN BOOLEAN := FALSE,
    restart_versions     IN BOOLEAN := FALSE  
);

Parameters

Parameter Description
share_name The name of the share.
out_share_job_id The ID of any share job that needs to be run to process this command.
share_owner The owner of the share. Defaults to the current schema.
drop_all_versions If set to TRUE, all existing versions along with all associated storage is dropped.

If drop_all_versions is FALSE, then all existing versions continue to exist, but the share is not visible to recipients. A subsequent call to PUBLISH_SHARE or SET_CURRENT_SHARE_VERSION makes them visible again.

restart_versions Restart version numbering. If FALSE, then the next published version number will be the same as it would have been if no versions had been dropped.

If TRUE, then the next published version will be set to one more than the highest existing version. When used in conjunction with drop_all_versions, this has the effect of resetting the share to its original state. Note that this may confuse any existing delta clients, so it should be used with care.

UPDATE_DEFAULT_RECIPIENT_PROPERTY Procedure

Update the default recipient property values. This procedure requires the user to have admin privileges.

Syntax

PROCEDURE UPDATE_DEFAULT_RECIPIENT_PROPERTY
(
  recipient_property   IN VARCHAR2,
  new_value_vc         IN VARCHAR2
);

Parameters

Parameter Description
recipient_property The property to update. For information on constants used for this parameter, see descriptions for Share Recipient Properties in DBMS_SHARE Constants.
new_value_vc The new property value.

UPDATE_DEFAULT_SHARE_PROPERTY Procedure

Update the default share property values.

Syntax

PROCEDURE UPDATE_DEFAULT_SHARE_PROPERTY  
(
    share_property       IN VARCHAR2,
    new_value            IN VARCHAR2  
);

Parameters

Parameter Description
share_property The property to update. For information on constants used for this parameter, see descriptions for Share Properties in DBMS_SHARE Constants.

These properties can be read using the ALL_SHARE_DEFAULT_SETTINGS View.

new_value The new property value.

UPDATE_RECIPIENT_PROPERTY Procedure

Update a property of an existing recipient.

Syntax

PROCEDURE UPDATE_RECIPIENT_PROPERTY  
(
    recipient_name       IN VARCHAR2,
    recipient_property   IN VARCHAR2,
    new_value            IN VARCHAR2,
    recipient_owner      IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
recipient_name The name of the recipient.
recipient_property The property to update. These properties include:
  • PROP_RECIPIENT_EMAIL
  • PROP_RECIPIENT_DESCRIPTION
  • PROP_RECIPIENT_LOG_LEVEL
  • PROP_RECIPIENT_SHARING_ID
  • PROP_RECIPIENT_PROFILE_URL
  • PROP_RECIPIENT_PAR_TYPE
  • PROP_RECIPIENT_MIN_PAR_LIFETIME
  • PROP_RECIPIENT_PAR_LIFETIME
  • PROP_RECIPIENT_TOKEN_LIFETIME
For information on constants used for this parameter, see descriptions for Share Recipient Recipient Properties in DBMS_SHARE Constants.
new_value The new property value.
recipient_owner The owner of the recipient. Defaults to the current schema.

UPDATE_SHARE_JOB_PROPERTY Procedure

Modify properties of a running share job. The procedure should return quickly, but it may take some time for the changes to take effect.

Syntax

PROCEDURE UPDATE_SHARE_JOB_PROPERTY  
(
    share_job_id         IN NUMBER,
    share_property       IN VARCHAR2,
    new_value            IN VARCHAR2,
    share_job_owner      IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
share_job_id The ID of the share job.
share_property The property to update. For information on constants used for this parameter, see descriptions for Share Job Properties in DBMS_SHARE Constants.
new_value The new property value.
share_job_owner The owner of the job. Defaults to the current schema.

UPDATE_SHARE_PROPERTY Procedure

Update a property of an existing share.

Syntax

PROCEDURE UPDATE_SHARE_PROPERTY  
(
    share_name           IN VARCHAR2,
    share_property       IN VARCHAR2,
    new_value            IN VARCHAR2,
    share_owner          IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
share_name The name of the share.
share_property The property to update. For information on constants used for this parameter, see descriptions for Share Properties in DBMS_SHARE Constants.
new_value The new property value.
share_owner The owner of the share. Defaults to the current schema.
Auto_commit If TRUE (the default), the changes are automatically committed. If FALSE, the user must commit the changes. Changes are visible externally after the commit takes place.

UPDATE_SHARE_TABLE_PROPERTY Procedure

Update the property value of an existing share table.

Syntax

PROCEDURE UPDATE_SHARE_TABLE_PROPERTY  
(
    share_name           IN VARCHAR2,
    share_table_name     IN VARCHAR2,
    share_table_property IN VARCHAR2,
    new_value            IN VARCHAR2,
    share_schema_name    IN VARCHAR2 := NULL,
    share_owner          IN VARCHAR2 := NULL,
    auto_commit          IN BOOLEAN := TRUE  
);

Parameters

Parameter Description
share_name The name of the share.
share_table_name The name of the share table.
share_table_property The property to update. These properties include:
  • PROP_SHARE_TABLE_SPLIT_METHOD
  • PROP_SHARE_TABLE_SPLIT_COLUMNS
  • PROP_SHARE_TABLE_ORDER_COLUMNS
  • PROP_SHARE_TABLE_SHARE_COLUMNS
  • PROP_SHARE_TABLE_SPLIT_SIZE
  • PROP_SHARE_TABLE_GATHER_STATS
  • PROP_SHARE_TABLE_SPLIT_ROWS
  • PROP_SHARE_TABLE_FLASHBACK
new_value The new property value.
share_schema_name The name of the share schema (defaults to uppercase of current user).
share_owner The owner of the share. Defaults to the current schema.
Auto_commit If TRUE (the default), the changes are automatically committed. If FALSE, the user must commit the changes. Changes are visible externally after the commit takes place.

VALIDATE_CREDENTIAL Function

Validate a credential name, converting it to canonical form first if required.

The procedure raises an exception if the name is not a valid Oracle identifier. The credential_name is returned without double quotes, as it would appear in the CREDENTIAL_NAME column of the USER_CREDENTIALS view.

Syntax

FUNCTION VALIDATE_CREDENTIAL  
(
        credential_name      IN VARCHAR2,
        check_if_exists      IN BOOLEAN := TRUE  
)  
RETURN VARCHAR2;

Parameters

Parameter Description
credential_name The name of the credential to validate in standard database form, with double quotes if the name is not a simple identifier.
check_if_exists If TRUE, then the function also confirms that the credential exists for the current user.

VALIDATE_SHARE_STORAGE Procedure

Check to see if the given storage is suitable for versioned shares.

Syntax

The procedure syntax including the validation_results output parameter. 

 PROCEDURE VALIDATE_SHARE_STORAGE  
(
    storage_link_name    IN VARCHAR2,
    validation_results   IN OUT NOCOPY VARCHAR2,
    run_storage_tests    IN BOOLEAN := TRUE,
    storage_link_owner   IN VARCHAR2 := NULL  
);

The procedure syntax not including the validation_results output parameter. 

PROCEDURE VALIDATE_SHARE_STORAGE  
(
    storage_link_name    IN VARCHAR2,
    run_storage_tests    IN BOOLEAN := TRUE,
    storage_link_owner   IN VARCHAR2 := NULL  
);

Parameters

Parameter Description
storage_link_name The cloud storage link name.
validation_results (Optional input and output) Validation result details returned in JSON form.

The validation_results include the results for each separate test.

run_storage_tests Run tests to validate the storage. If TRUE (the default), the procedure tests READ, WRITE, DELETE, and PREAUTHENTICATED REQUESTS.
storage_link_owner The cloud storage link owner.

Example: Validation results

{
  "READ":"PASSSED",
  "WRITE":"PASSSED",
  "CREATE_PAR":"PASSSED",
  "DELETE_PAR":"PASSSED",
  "DELETE":"PASSSED"
}

WAIT_FOR_JOB Procedure

This procedure waits until the specified share job is complete.

Syntax

PROCEDURE WAIT_FOR_JOB  
(
    share_job_id         IN NUMBER,
    completed            IN OUT NOCOPY BOOLEAN,
    maximum_wait         IN NUMBER := NULL  
);

Parameters

Parameter Description
share_job_id The ID of the share job.
completed Job completion indicator.
maximum_wait The maximum wait period, in seconds. A NULL value implies no limit.

Was this article helpful?