writing sql queries to modify oracle DB

soprano
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
    Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,212 Dataiker
    edited July 17

    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

Setup Info
    Tags
      Help me…