Manage listings with SQL as a consumer - examples

The following are examples of the common tasks that consumers can complete programmatically with SQL commands:

Show available listings

Shows the listings available to the consumer running the command. For more information about the SHOW AVAILABLE LISTINGS command, see SHOW AVAILABLE LISTINGS.

Description

Notes

Show the available listings.

Use IS_SHARED_WITH_ME = TRUE to show only the listings shared privately with the consumer running the command. Use IS_IMPORTED = TRUE to show only imported listings.

 
SHOW AVAILABLE LISTINGS
Copy

Describe available listings

After running SHOW AVAILABLE LISTINGS to identify the available listings and the global listing names, a consumer can run DESCRIBE AVAILABLE LISTING to return descriptions of the columns in the listings that are available to them. For more information about the DESCRIBE AVAILABLE LISTING command, see DESCRIBE AVAILABLE LISTING.

Description

Notes

Describe the listing columns.

Use listing_global_name to identify the specific global listing to describe. When the is_ready_for_import column is TRUE, the data is already present in the region and can be imported by the consumer immediately.

 
DESCRIBE AVAILABLE LISTING < listing_global_name >
Copy

Request a listing and automatically poll for availability

After running SHOW AVAILABLE LISTINGS to identify the available listings, a consumer can use the SYSTEM$REQUEST_LISTING_AND_WAIT stored procedure to request a listing and automatically poll for availability. A consumer can also use this stored procedure when the is_ready_for_import column is FALSE. For more information about the SYSTEM$REQUEST_LISTING_AND_WAIT stored procedure, see SYSTEM$REQUEST_LISTING_AND_WAIT.

Description

Notes

Request a specific listing and poll for availability.

<timeout_mins> specifies the listing fulfillment waiting period in minutes. The default is 240 minutes or 4 hours.

When a requested listing becomes available or is already available, the message Success: Listing <listing_global_name> is ready to be imported is returned.

If the timeout period is exceeded, the message Error: Timed out waiting for the listing to be available after <timeout_mins> min(s) is returned.

To request a listing without waiting for listing fulfillment, enter 0 (zero) for the <timeout_mins> value. When the value is 0, the message Success: Listing <listing_global_name> requested successfully, but not waiting to confirm fulfillment is returned.

 
CALL SYSTEM$REQUEST_LISTING_AND_WAIT( ' <listing_global_name> ' [ , <timeout_mins>. ] );
Copy

Create a database from a listing

After requesting a listing, a consumer can use the CREATE DATABASE … FROM LISTING … command to create a database from a listing. For more information about the CREATE DATABASE … FROM LISTING … command, see CREATE DATABASE … FROM LISTING ….

Description

Notes

Create a database from a listing.

<name> specifies the database identifier. It must be unique for your account. The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes. For example "My object". Identifiers enclosed in double quotes are also case-sensitive.

 
CREATE DATABASE <name> FROM LISTING '<listing_global_name>';
Copy
Language: English