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 .
|
- 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. - 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. - 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. The available share names can be found by callingDISCOVER_AVAILABLE_SHARES
. - 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 oneSHARE_AVAILABLE_SHARES_ROW
for each available table from the subscribed share providers. - DISCOVER_AVAILABLE_TABLES Function
Return oneSHARE_AVAILABLE_TABLES_ROW
for each available table from the subscribed share providers. - DROP_SHARE_LINK Procedure
Drop a share link created by theCREATE_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. Admin privileges are required for this procedure. - 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. - 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. - 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. For ORACLE share providers, the Oracle provider ID is returned in the endpoint argument. - GET_SHARING_ID Function
Return an identifier that can be used as thesharing_id
in theCREATE_SHARE_RECIPIENT
procedure. This function can be used to share data between two users, the "provider" and the "recipient", in different databases. - 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 byCREATE_BEARER_TOKEN_CREDENTIAL
orCREATE_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. - RENAME_CLOUD_STORAGE_LINK Procedure
Rename a registered cloud storage 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 be used by the current user when it attempts 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 byCREATE_CREDENTIALS
orCREATE_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 .
|
Parent topic: Summary of Share Consumer Subprograms
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.
The
profile may also, optionally, include a
tokenEndpoint property along with a
clientID and
clientSecret .
See Profile File Format and Bearer Token for further information. |
out_credential_name |
The name of the newly created bearer token credential. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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 theSET_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. |
Parent topic: Summary of Share Consumer Subprograms
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.
|
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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
Parent topic: Summary of Share Consumer Subprograms
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.
Parent topic: Summary of Share Consumer Subprograms
DROP_SHARE_LINK Procedure
Drop a share link created by the CREATE_SHARE_LINK
procedure.
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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
);
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. |
Parent topic: Summary of Share Consumer Subprograms
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>
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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:
- The recipient calls
DBMS_SHARE.GET_SHARING_ID
to get a unique identifier. - The recipient sends this identifier (e.g. via email) to the provider.
- The provider calls
DBMS_SHARE.CREATE_SHARE_RECIPIENT
, passing in the identifier assharing_id
. - 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.
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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.
Syntax
PROCEDURE REFRESH_BEARER_TOKEN_CREDENTIAL
(
credential_name IN VARCHAR2 := NULL
);
Parameters
Parameter | Description |
---|---|
credential_name |
The name of the credential to refresh. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
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. |
Parent topic: Summary of Share Consumer Subprograms
UPDATE_BEARER_TOKEN_CREDENTIAL Procedure
Modify an attribute of a credential created by
CREATE_CREDENTIALS
or
CREATE_BEARER_TOKEN_CREDENTIAL
.
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
Parent topic: Summary of Share Consumer Subprograms