Question

AA SQL dropping tables 

  • 12 January 2024
  • 6 replies
  • 100 views
A
Badge +1

How does some one drop a list of tables and verify first ifd the y exit or not 
if they dont the BOT runs as it should but if they exist drop them 

 

6 replies

Padmakumar
Userlevel 7
Badge +13

 

Hi @Arthas03 

Have you tried with Loop → While by passing the condition to check the availability of the ID within the Table?

 
Padmakumar
A
Badge +1

tried but ended up with the entire bot not doing anything after he verified all of the tables are created 

Padmakumar
Userlevel 7
Badge +13

tried but ended up with the entire bot not doing anything after he verified all of the tables are created 

 

Could you please share the screenshot of your code here? Just wants to know how you actually put the logic.

 
Padmakumar
A
Badge +1

ill do you one better :

Sql code so far , my logic for the bot is run connect delete commit end


SELECT 'DROP TABLE ' + TABLE_NAME + ' CASCADE CONSTRAINTS PURGE; '
FROM ALL_TABLES WHERE TABLE_NAME LIKE 'JPP_%'


DROP TABLE JPP_1 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_2 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_3 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_4 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_5 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_6 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_7 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_8 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_9 CASCADE CONSTRAINTS PURGE;

Padmakumar
Userlevel 7
Badge +13

ill do you one better :

Sql code so far , my logic for the bot is run connect delete commit end


SELECT 'DROP TABLE ' + TABLE_NAME + ' CASCADE CONSTRAINTS PURGE; '
FROM ALL_TABLES WHERE TABLE_NAME LIKE 'JPP_%'


DROP TABLE JPP_1 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_2 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_3 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_4 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_5 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_6 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_7 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_8 CASCADE CONSTRAINTS PURGE;
DROP TABLE JPP_9 CASCADE CONSTRAINTS PURGE;

 

Here what is your criteria for dropping the table?

 
Padmakumar
A
Badge +1

The criteria for selecting these tables are defined in the WHERE clause of the query:

WHERE TABLE_NAME LIKE 'JPP_%'

This condition specifies that only tables whose names begin with 'JPP_' will be selected. 

Reply


Terms & ConditionsCookie settings