Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Hello everyone,
I am trying to write an sql query that drops a table from an oracle DB if it exists , otherwise it doesn't generate any errors.
I tried with this code :
DECLARE
nb number;
BEGIN
SELECT count *
into nb
FROM tables
WHERE table_name = 'my_table';
IF nb = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE my_table';
END IF;
END;
--DKU_END_STATEMENT
But it does not work. Can anyone help me please with the right syntax that conveys the goal !
Thank you so very much in advance.
Hi,
To execute this you should use SQL Script recipe ( not an SQL Query recipe) https://doc.dataiku.com/dss/latest/code_recipes/sql.html#sql-script-recipe
Some formatting may have been lost when posting but the syntax should look like :
DECLARE
nb number;
BEGIN
SELECT COUNT(*) INTO nb FROM user_tables WHERE table_name = 'my_table';
IF nb = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE my_table';
END IF;
END;
--DKU_END_STATEMENT
Thanks