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.).
This stored procedure only allows dropping base views created with the CREATE_REMOTE_TABLE stored procedure.
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
nullthen 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.|
The user must have the following privileges to be able to execute the DROP_REMOTE_TABLE procedure:
Connectprivilege in the VDP database where is the base view.
Writeprivilege in the base view.
Connectprivilege in the VDP database where is the data source of the base view.
Executeprivilege 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.
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:
- Deletes the remote table accessed by the base view
- Deletes the base view