luni, 22 decembrie 2014

Oracle SQL Select Case When

A lot of sql developers are wondering if:

• decode can evaluate a boolean expression
• there is any IIF function in Oracle SQL

Unfortunately, the answer is NO for both questions.
There is no SQL boolean type in Oracle. Just PL SQL.
There is no IIF function, either.

The solution is:

select Case When 1=2 Then 'YES' else 'NO' End from dual;

You can even select multiple columns without brackets.

select 1, Case When 1=1 Then 'YES' else 'NO' End from dual;

and, of course, you can evaluate table columns within.
Enjoy!

luni, 15 decembrie 2014

How to delete all the records containing a given value on a given named column from all the tables continaing that named column, in the schema.

Let's say, we have to delete all the records  containing a given value on a given named column from all the tables continaing that named column, in the schema. In my example, the column is THE_NAME_OF_THE_COL and the value is 40322.

This can be a way to do it:

first, create the tables:

/*all the tables containing the wanted column:*/
create table tabele_codmis as (select table_name from user_tab_columns a where (a.column_name='THE_NAME_OF_THE COL' ) and (select count(*) from user_tables b where a.table_name=b.table_name)>0 );
/*all the table containing the wanted column and having at least one record containing a given value on that column (will be emptied and filled within the procedure: */

create table tabele_codmis_ex  as select * from tabele_codmis;

DECLARE

cursor r1 is select * from tabele_codmis;
cursor r2 is select * from tabele_codmis_ex;
cursor r4 is select * from constr_mis_ex;
cate number;
miscod number;
sql2 varchar2(1000);
numet varchar2(1000);

BEGIN


/* empty the table continaing the names of tables containing the given column*/
execute immediate 'delete from tabele_codmis';


/* fill the above table*/
execute immediate 'insert into tabele_codmis select table_name from user_tab_columns a where (a.column_name=''THE_NAME_OF_THE_COL'' ) and (select count(*) from user_tables b where a.table_name=b.table_name)>0';


/* empty the table containing the given value*/
execute immediate 'delete from tabele_codmis_ex';


/* fill the table above */
FOR c1 IN r1

  loop

      cate:=0;

      miscod:=40322; -- this was my value

      execute immediate 'select count(*)   from '||c1.table_name||' where THE_NAME_OF_THE_COL =:1' into cate using miscod;

      if cate>0 then

        sql2:='insert into tabele_codmis_ex values (:1)';


         select c1.table_name into numet from dual;
        execute immediate sql2 using numet;
      end if;  END LOOP;

/* drop the table containing the enabled constraints contained by the wanted tables*/
dbms_utility.exec_ddl_statement('drop table constr_mis_ex');
/* create and fill table containing the enabled constraints contained by the wanted tables*/
dbms_utility.exec_ddl_statement('create table constr_mis_ex as select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED from user_constraints  where constraint_type=''R'' and status=''ENABLED'' and table_name in (select table_name from tabele_codmis_ex)'); 

/* we are interested only in referential constraints*/

/* disable all the constraints from above */

for c4 in r4

    loop

    dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' read only'); /* this is not mandatory, but you could find it useful, otherwise you can experience some locking erros */
    dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' disable novalidate constraint ' || c4.constraint_name);
end loop;

 

/* delete all the records containing the given value */

FOR c2 IN r2
  loop      execute immediate 'delete from  '||c2.table_name||' where THE_NAME_OF_THE_COL =:1'  using miscod;
END LOOP;


/* enable back the constraints back */
for c4 in r4
    loop
       dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' enable novalidate constraint ' || c4.constraint_name);     dbms_utility.exec_ddl_statement('alter table  '||c4.table_name || '  read write');
    end loop;
end;
/


 Now all the unwanted records should have been erased. Enjoy!

marți, 9 decembrie 2014

How to empty an Oracle database schema

Seems to be easy, but there are some itchy problems.
First, disable all the constraints, but mind the disabled one, don't enable them aftermath. Same for the triggers.
In order to do this, you have to keep them recorded somehow.

drop table my_constraints; --if exists
create table my_constraints as select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED,    GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED from user_constraints;

--don't use the "select * from" syntax, and you will avoid a "long 
--datatype" error
--maybe you don't need all  those fields, but it doesn't cost so 
--much to store them

--if you want to empty the recycle bin (three rows bellow you can see why): 

purge recyclebin;


DECLARE
cursor r1 is select * from my_constraints where status='ENABLED' and upper(substr(constraint_name,1,3))<>'BIN'; --recycle bin objects
cursor r2 is select * from user_tables;
BEGIN
  FOR c1 IN r1
  loop
    for c2 in r2
    loop
       if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
       dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable novalidate constraint ' || c1.constraint_name);
         end if;
    end loop;
  END LOOP;
end;
/

--now, the triggers

drop table my_triggers;

create table my_triggers as select TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, COLUMN_NAME, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION, ACTION_TYPE,
CROSSEDITION, BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, INSTEAD_OF_ROW, FIRE_ONCE, APPLY_SERVER_ONLY from user_triggers;

DECLARE
cursor r1 is select * from my_triggers where status='ENABLED' and upper(substr(trigger_name,1,3))<>'BIN'; 
--remember? recycle bin objects
BEGIN
  FOR c1 IN r1
  loop
       dbms_utility.exec_ddl_statement('alter trigger '  || c1.trigger_name || ' disable ' );
  END LOOP;
end;
/

--create a table to hold the name of the tables you want to empty them.

drop table empty_tables; --if exists

-- be carefull, do not empty the my_constraints , my_triggers, empty_tables tables

create table empty_tables as select table_name from user_tables  where lower(table_name) not in (.. some list of names..., 'my_constraints' , 'my_triggers' , 'empty_tables');


-- if you want to use other way than "truncate", feel free to do it, but mind the size of the archivelogs and the load of the system resources

DECLARE
cursor r1 is select * from empty_tables;
BEGIN
  FOR c1 IN r1
  loop
       dbms_utility.exec_ddl_statement('truncate table '  || c1.table_name || ' drop storage ' );
  END LOOP;end;
/


-- now, reinforce the constraints and the triggers:
-- first, the primary constraints, because, otherwise, you will receive an error when you try to
-- enable a FK constraint, referring a non primary key column.

DECLARE
cursor r1 is select * from user_tables where table_name in (select table_name from my_constraints where constraint_type='P');
BEGIN
  FOR c1 IN r1
  loop
       dbms_utility.exec_ddl_statement('alter  table '  || c1.table_name || ' enable primary key ' );
  END LOOP;
end;
/

-- now, the unique constraints,  same reason from above
declare
cursor r1 is select * from my_constraints where constraint_type='U' and constraint_name not like 'SYS%' order by constraint_name ; 
-- i put there the "sys" exception because i received some errors, you can imagine which type of
BEGIN
  FOR c1 IN r1
    loop       dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable novalidate constraint ' || c1.constraint_name);
  END LOOP;
end;
/

-- now, the rest:

declare
cursor r1 is select * from my_constraints where constraint_name not like 'SYS%' order by constraint_name ; 
-- i put there the "sys" exception because i received some errors, you can imagine which type of
BEGIN
  FOR c1 IN r1
    loop       dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable novalidate constraint ' || c1.constraint_name);
  END LOOP;
end;
/


-- and, eventually, the triggers:

DECLARE

cursor r1 is select * from my_triggers where status='ENABLED' and upper(substr(trigger_name,1,3))<>'BIN';
BEGIN
  FOR c1 IN r1
  loop
       dbms_utility.exec_ddl_statement('alter trigger '  || c1.trigger_name || ' enable ' );
  END LOOP;
end;
/


And that's all! Your schema is empty, except the tables you mentioned when you populate the empty_tables table.

And now drop the unnecessary tables:

drop table my_constraints;
drop table my_triggers;
drop table empty_tables;