writing sql queries to modify oracle DB
soprano
Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1 ✭
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.
Tagged:
Answers
-
Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,225 Dataiker
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