sexta-feira, 27 de fevereiro de 2015

Como retornar todas as linhas de um Select em apenas uma [Oracle e SQL Server];

Aqui vai uma dica muito legal de como retornar todas as linhas de um SELECT em apenas uma só!

Com certeza em algum momento, vocês ainda irão precisar disso.

Bom.. Vamos lá!

--ORACLE - Função WM_CONCAT ou LISTAGG

select wm_concat(COLUNA) from TABELA
where rownum <= 5;

SELECT LISTAGG(COLUNA, ',') WITHIN GROUP (ORDER BY COLUNA) APELIDO
FROM TABELA where rownum <= 5;



--SQL Server - Função STUFF (Para retirar a primeira vírgula) e utilizando FOR XML PATH('')

SELECT top 1
     STUFF( 
       (  
           select ',' + COLUNA
           from TABELA
           FOR XML PATH('')
       ),1,1,''
    )AS A
from TABELA

Abs,
Bruno Duarte.

quinta-feira, 5 de fevereiro de 2015

Apagar todos os Objetos de um schema/usuário [Oracle];

Quando precisamos "Limpar" um schema/usuário no Oracle, utilizamos o comando:

DROP USER schema CASCADE;

O problema disso é ter o trabalho de recriar o usuário e conceder todas as permissões (Grants) novamente.

Porém...
Podemos utilizar este Script que apaga (DROP) todos os objetos do schema, facilitando assim muito o nosso trabalho.


-- ORACLE 11g
BEGIN
   for rec1 in (select decode(object_type, 'TABLE', 'A', '')||OBJECT_TYPE AS ORDEM_TYPE, object_type, object_name from user_objects where object_type not in ('PACKAGE BODY','INDEX','TRIGGER', 'LOB', 'JOB') order by 1) loop
     EXECUTE IMMEDIATE 'drop ' || rec1.object_type || ' ' || rec1.object_name || case rec1.object_type when 'TABLE' then ' cascade constraints' when 'TYPE' then ' FORCE' else '' end;
   end loop;
      
   for rec in (select job_creator, job_name from user_SCHEDULER_JOBS) loop
       DBMS_SCHEDULER.DROP_JOB(JOB_NAME => '"'||rec.job_creator||'"."'||rec.job_name||'"', defer=>false, force=>false);
   end loop;

   for rec in (select job from user_JOBS) loop
       DBMS_JOB.REMOVE(job => rec.job);
   end loop;

exception when others then
   raise_application_error(-20001,'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END;
/
--Limpa a Lixeira a nível de Usuário
PURGE RECYCLEBIN;
/


-- ORACLE 10g
BEGIN
   for rec1 in (select decode(object_type, 'TABLE', 'A', '')||OBJECT_TYPE AS ORDEM_TYPE, object_type, object_name from user_objects where object_type not in ('PACKAGE BODY','INDEX','TRIGGER', 'LOB', 'JOB') order by 1) loop
     EXECUTE IMMEDIATE 'drop ' || rec1.object_type || ' ' || rec1.object_name || case rec1.object_type when 'TABLE' then ' cascade constraints' when 'TYPE' then ' FORCE' else '' end;
   end loop;
      
   for rec in (select job_creator, job_name from user_SCHEDULER_JOBS) loop
       DBMS_SCHEDULER.DROP_JOB(JOB_NAME => '"'||rec.job_creator||'"."'||rec.job_name||'"', force=>false);
   end loop;

   for rec in (select job from user_JOBS) loop
       DBMS_JOB.REMOVE(job => rec.job);
   end loop;

exception when others then
   raise_application_error(-20001,'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END;
/
--Limpa a Lixeira a nível de Usuário
PURGE RECYCLEBIN;
/

Abs, Bruno Duarte.