DROP_REMOTE_TABLE

Description

The DROP_REMOTE_TABLE stored procedure deletes a base view and the remote table accessed by the base 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.

Syntax

DROP_REMOTE_TABLE(
      input_base_view_database : text
    , input_base_view_name : text
)
  • input_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.
  • input_base_view_name: Name of the base view that will be deleted.

Stored procedure result

Two rows with the result of each step of the procedure. Example:

Step 1 of 2: Remote table ‘<remote table name>’ dropped successfully.
Step 2 of 2: Deleted base view ‘<base view name>’ successfully in the ‘<vdp database name>’ database.

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 base view.
  • Write privilege in the base view.
  • Connect privilege in the VDP database where is the data source of the base view.
  • Execute privilege in the data source of the base 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.

CALL DROP_REMOTE_TABLE('vdp_database', 'vdp_base_view')

In this example, the procedure will perform these steps:

  1. Deletes the remote table accessed by the base view vdp_base_view.
  2. Deletes the base view vdp_base_view.