USER MANUALS

DROP_REMOTE_TABLE

Description

The DROP_REMOTE_TABLE stored procedure deletes a base view or a summary view and the remote table accessed by the view of a JDBC data source (Oracle, PostgreSQL, DB2, Hive, Impala, Redshift, etc.).

Note

This stored procedure only allows dropping base views created with the CREATE_REMOTE_TABLE stored procedure or summary views.

Syntax

DROP_REMOTE_TABLE(
      base_view_database_name : text
    , base_view_name : text
    , drop_base_view_on_cascade : boolean
)

  • base_view_database_name (optional): VDP database where is the base view that will be deleted. If the value of this parameter is null then the procedure will use the VDP database to which the user is connected.

  • base_view_name: Name of the base view that will be deleted.

  • drop_base_view_on_cascade (optional): If the value is true, then the base view dependent elements will be deleted. The default value is false.

Stored procedure result

Two or three rows with the result of each step of the procedure. Example of execution with drop_base_view_on_cascade = false:

Step 1 of 2: Remote table ‘<remote table name>’ dropped successfully.

Step 2 of 2: Deleted view ‘<view name>’ successfully in the ‘<vdp database name>’ database.

Example of execution with drop_base_view_on_cascade = true:

Step 1 of 3: Remote table ‘<remote table name>’ dropped successfully.

Step 2 of 3: Deleted view ‘<view name>’ successfully in the ‘<vdp database name>’ database.

Step 3 of 3: Deleted view dependent elements.

Privileges Required

The user must have the following privileges to be able to execute the DROP_REMOTE_TABLE procedure:

  • Connect privilege in the VDP database where is the view.

  • Write privilege in the view.

  • Write privilege in the view dependent elements.

  • Connect privilege in the VDP database where is the data source of the view.

  • Execute privilege in the data source of the view.

Furthermore, the user used in the VDP data source to connect to the database must have DROP TABLE privileges.

Example

This example uses a VDP database that contains a base view called vdp_base_view that was created with the CREATE_REMOTE_TABLE stored procedure.

SELECT *
FROM DROP_REMOTE_TABLE()
WHERE base_view_database_name = 'customer360_db'
    AND base_view_name = 'customer'
    AND drop_base_view_on_cascade = true;

In this example, the procedure will perform these steps:

  1. Deletes the remote table accessed by the base view customer.

  2. Deletes the base view customer.

  3. Deletes the base view dependent elements.

Add feedback