Running with different materilizations leaves dirty objects

[vitoravancini]

• Oracle DBT version: 0.1.4
• Python version: 3.6
• Operating System: ubuntu 18

Description

Not sure the sequence of actions, but I think I ran dbt with models materialized as table, and then the same models materialized as views, it seems that the adapter is not cleaning the tables properly, and when the run try to create a temp view it fails because there is a table with the same name and view code only tries to delete views.

Maybe if the weird oracle procedure attempts to delete both table and view it would be safer

18:30:43 | 1 of 1 START view model BILABELLAMAFIA2.s_prd_class.................. [RUN]
/* {"app": "dbt", "dbt_version": "0.17.0", "profile_name": "lbm", "target_name": "prod", "node_id": "model.lbm.s_prd_class"} */
DECLARE
     dne_942    EXCEPTION;
     PRAGMA EXCEPTION_INIT(dne_942, -942);
  BEGIN
     EXECUTE IMMEDIATE 'DROP view BILABELLAMAFIA2.s_prd_class__dbt_tmp cascade constraint';
  EXCEPTION
     WHEN dne_942 THEN
        NULL; -- if it doesn't exist, do nothing .. no error, nothing .. ignore.
  END;
/* {"app": "dbt", "dbt_version": "0.17.0", "profile_name": "lbm", "target_name": "prod", "node_id": "model.lbm.s_prd_class"} */
DECLARE
     dne_942    EXCEPTION;
     PRAGMA EXCEPTION_INIT(dne_942, -942);
  BEGIN
     EXECUTE IMMEDIATE 'DROP view BILABELLAMAFIA2.s_prd_class__dbt_backup cascade constraint';
  EXCEPTION
     WHEN dne_942 THEN
        NULL; -- if it doesn't exist, do nothing .. no error, nothing .. ignore.
  END;
/* {"app": "dbt", "dbt_version": "0.17.0", "profile_name": "lbm", "target_name": "prod", "node_id": "model.lbm.s_prd_class"} */

  create view BILABELLAMAFIA2.s_prd_class__dbt_tmp as
  with renamed as (

    select 
    
    CAST(cd_tipoclas as int) as cd_tipoclas
,
    trim(cd_classificacao) as cd_classificacao,
    u_version, 
    
    CAST(cd_operador as int) as cd_operador
,
    dt_cadastro,
    ds_classificacao
    from 
    ulabellamafia.prd_classificacao
)


select * from renamed

Oracle error: ORA-00955: name is already used by an existing object
18:30:46 | 1 of 1 ERROR creating view model BILABELLAMAFIA2.s_prd_class......... [ERROR in 2.10s]

[fabrice-etanchaud]

Hi, I have the same problem with seeds in full-refresh, and it seems to be related to the quoting policy not being applied in the adapter's macros : https://getdbt.slack.com/archives/C2JRRQDTL/p1591907986483500

Because of the drop_relation's exception handling, when oracle complains because it cannot find the table/view (quoted, names becomes case sensitive), the error disappears, and the object is not dropped.

A workaround could be to replace the double quotes in the drop_relation macro, like it is in the truncate one.