domingo, 30 de setembro de 2018

Criando colunas de auditoria em tabelas Oracle

Olá pessoal,

é comum a inclusão de colunas de auditoria quando se cria um sistema.

As colunas de auditoria são as que armazenam informações sobre a criação e alteração do registro que se encontra na tabela. Os nomes de coluna mais comuns em Português são: CRIADO_POR, CRIADO_EM, ALTERADO_POR e ALTERADO_EM.

Primeiramente temos que criar essas 4 colunas em todas as tabelas (Caso já não existam).

Calma! Você não precisará entrar em todas as suas dezenas (ou centenas) de tabelas e alterar.

Segue um script para fazer esta atividade de forma automática.

Copie e cole o script abaixo no Comandos SQL e pressione "Executar" (ou "Run")

--------------------INICIO SCRIPT---------------------
----------------Inclui colunas de auditoria-------------
BEGIN

FOR cur IN (select * from user_tables) LOOP

   EXECUTE IMMEDIATE 'alter table ' || cur.table_name || ' add (CRIADO_POR VARCHAR2(255))';
   EXECUTE IMMEDIATE 'alter table ' || cur.table_name || ' add (CRIADO_EM DATE)';
   EXECUTE IMMEDIATE 'alter table ' || cur.table_name || ' add (ALTERADO_POR VARCHAR2(255))';
   EXECUTE IMMEDIATE 'alter table ' || cur.table_name || ' add (ALTERADO_EM DATE)';

END LOOP;

END;
------------------------FIM SCRIPT-------------------------

Ao final da execução, aparecerá uma mensagem de sucesso, conforme imagem abaixo.



Para ter certeza de que deu certo, vá ao Browser de Objetos e confira se as colunas foram criadas.



A melhor maneira de garantir que suas colunas de auditoria serão preenchidas corretamente, é por meio de trigger no banco de dados.

Para facilitar o trabalho, segue um script que cria as triggers para as tabelas com tamanho menor que 26 (As tabelas com tamanho maior que 26 deverão ser criadas manualmente).

O script deve ser executado no Comandos SQL também.

Observação: O trecho que utiliza APEX$SESSION funciona apenas a partir do Apex 5, portanto deve ser alterado para versões anteriores do Apex.

--------------------INICIO SCRIPT---------------------
DECLARE
   v_comando VARCHAR2(4000);

BEGIN

FOR cur IN (select * from user_tables where length(table_name) <=26) LOOP 
   v_comando := 'create or replace trigger BIU_'||cur.table_name||' before insert or update on "'||cur.table_name||'" 
for each row 
begin
    if inserting then
        :new.criado_em := sysdate;
        :new.criado_por := nvl(sys_context(''APEX$SESSION'',''APP_USER''),user);
    end if;
    :new.alterado_em := sysdate;
    :new.alterado_por := nvl(sys_context(''APEX$SESSION'',''APP_USER''),user);
end;';
htp.p(v_comando);
EXECUTE IMMEDIATE v_comando;

END LOOP;

END;
------------------------FIM SCRIPT-------------------------

Verifique se o comando foi executado corretamente. Você pode ir também ao Browser de Objetos e conferir se as triggers foram criadas sem problemas.


Para verificar quais tabelas devem ter as triggers criadas  manualmente, execute o SELECT abaixo no Comandos SQL.

select * from user_tables where length(table_name) >26


Utilize o CREATE TRIGGER abaixo no Comandos SQL substituindo as tags <<nome_tabela_abreviada>> e <<nome_tabela>> pelo nome desejado.

create or replace trigger BIU_<<nome_tabela_abreviado>> before insert or update on <<nome_tabela>> 
for each row 
begin
    if inserting then
        :new.criado_em := sysdate;
        :new.criado_por := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.alterado_em := sysdate;
    :new.alterado_por := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end; 

Pronto! Agora basta inserir algum registro por algum formulário ou fazer algum comando DML diretamente no banco que as colunas serão preenchidas.

Espero que tenha gostado!

Até a próxima.

quinta-feira, 21 de junho de 2018

O que é essa tabela HTMLDB_PLAN_TABLE? Posso apagar?

Olá pessoal,

hoje vamos falar sobre a tabela HTMLDB_PLAN_TABLE que aparecem em esquemas Oracle associados a algum workspace do Apex.

Afinal, para que serve esta tabela? Posso apagá-la?

Ao realizar uma busca por HTMLDB_PLAN_TABLE na documentação da Oracle e do Apex: No results found.

Caso a tabela HTMLDB_PLAN_TABLE já exista, execute o comando de dropar e após isso faça uma consulta para confirmar a não existência.
Observe que o Comandos SQL informa que "a tabela ou view não existe", quando um comando de SELECT é executado.

Seguem os comandos executados:
DROP TABLE htmldb_plan_table;
SELECT * FROM htmldb_plan_table;

Após isso, digite um comando de SELECT qualquer. Utilizei o comando abaixo:

SELECT * 
FROM emp e
JOIN dept d ON e.deptno = d.deptno

Clique em "Explicação" e verifique que o Comandos SQL apresenta o resultado equivalente ao Explain Plan da Oracle.
Veja referência para o Explain Plan na documentação da Oracle: https://docs.oracle.com/database/121/SQLRF/statements_9010.htm#SQLRF01601
Caso prefira um artigo em português, segue o do colega Tércio Costa (Oracle ACE Associate):  https://oraclepress.wordpress.com/2017/01/16/explain-plan/


Após isso, veja que o Apex criou a tabela htmldb_plan_table com o resultado do explain plan.

Conclusão

A tabela HTMLDB_PLAN_TABLE é utilizada pelo Apex para armazenar os resultados da "Explicação" (Explain plan) de consultas e pode ser apagado sem medo.

quarta-feira, 9 de maio de 2018

Cursos de Oracle SQL grátis

Olá pessoal,
quando fiquei sabendo da notícia não pude deixar de compartilhar aqui no blog.
No Oracle Dev Gym há dois cursos de SQL gratuitos de excelente qualidade.
Só por curiosidade, repare na URL do Dev Gym e perceba que o site foi todo desenvolvido utilizando o Oracle Application Express... YAAA


Para dar uma ideia da qualidade, o curso é oferecido pela Oracle e ministrado pelo instrutor Chris Saxon (do site "Ask Tom" e do "Magic of SQL").
Para quem é desenvolvedor Apex, o conhecimento de SQL é fundamental! Sugiro investir um tempinho para realizá-los.
Para quem tem dificuldade com o inglês, infelizmente os cursos não estão disponíveis em português, mas pode ser adicionada uma legenda, pois os vídeos estão no YouTube.
O primeiro treinamento chamado de "Databases for Developers: Foundations" está disponível na forma "On Demand". Este curso é mais básico e pode ser encontrado no seguinte link: https://devgym.oracle.com/devgym/database-for-developers.html
Está sendo lançado agora o "Databases for Developers: Next Level" que iniciará no dia 14 de maio de 2018 e permite ajudar a melhorar as habilidades com Oracle SQL. Este curso possui certificado desde que você complete todos os exercícios de maneira satisfatória e está disponível no seguinte link: https://devgym.oracle.com/pls/apex/dg/class/databases-for-developers-next-level.html
Deixe seu comentário sobre o que achou dos cursos.

Bons estudos!

Migrando os dados utilizando o Comandos SQL

Olá pessoal,

vamos para nossa última postagem da "trilogia" de posts sobre colocar uma aplicação Apex em operação.

O post de hoje é sobre a migração dos dados de um ambiente para outro.

Normalmente quando estamos colocando uma aplicação em operação, queremos migrar alguns dados. Os principais dados que normalmente tem que ser migrados são os de tipo, por exemplo: TIPO_ALUNO, TIPO_PROCESSO, etc.

Entretanto, em ambientes onde o desenvolvedor consegue fazer alterações diretamente na operação (produção), é comum que ele comece a fazer as alterações diretamente na operação e abandone o ambiente de desenvolvimento. Cuidado com essa má prática!

Vamos abordar duas situações para essa migração de dados:
1) Ambiente de origem e destino no mesmo banco
2) Ambiente de origem e destino em bancos diferentes

MIGRAÇÃO DE DADOS COM AMBIENTES NO MESMO BANCO

Segue um passo a passo para a migração.

Passo 1) Entrar no Comandos SQL do ambiente que contém os dados. (Tenha atenção neste passo, para não errar o ambiente). "SQL Workshop" > "Comandos SQL"




Passo 2)  Copie e cole o script abaixo no Comandos SQL para fornecer os grants de SELECT para seu ambiente de destino. Não esqueça de trocar a variável "v_ambiente_destino" para o nome do seu ambiente de destino!

-----INICIO SCRIPT-----
DECLARE
v_comando VARCHAR2(200);
v_ambiente_destino VARCHAR2(30) := 'APEX_ANDERSON'; --INSIRA SEU AMBIENTE DE DESTINO
BEGIN
--Fornece permissão de leitura em todas as tabelas deste owner
FOR cur IN (SELECT * FROM USER_TABLES) LOOP
     v_comando := 'GRANT SELECT ON "'||cur.table_name||'" TO '||v_ambiente_destino;
     htp.p(v_comando);
     EXECUTE IMMEDIATE v_comando;
END LOOP;
END;
-----TERMINO SCRIPT-----

Passo 3) Clique em "Executar" ("Run" para versão em inglês) (1) e confirme que tudo ocorreu bem no painel inferior (2).



Passo 4) Entre no Comandos SQL do ambiente de destino onde os dados serão carregados. (ATENÇÃO para entrar no ambiente correto!)

Passo 5) Copie e cole o script abaixo no Comandos SQL e clique em "Executar" (ou "Run") para carregar os dados que se encontram no seu ambiente de origem. Não esqueça de trocar a variável "v_ambiente_origem" para o nome do seu ambiente de origem!

-----INICIO SCRIPT-----
DECLARE

v_comando VARCHAR2(200);

v_ambiente_origem VARCHAR2(30) := 'APEX_ANDERSON_DEV';--INSIRA SEU AMBIENTE DE ORIGEM DOS DADOS

BEGIN


--Primeiro desabilite todas as constraints 

FOR i IN (SELECT constraint_name, table_name FROM user_constraints WHERE constraint_type = 'R') LOOP

v_comando := 'alter table "'||i.table_name||'" disable constraint "'||i.constraint_name||'"';

htp.p(v_comando);

EXECUTE IMMEDIATE v_comando;


END LOOP;

--Para todas as tabelas do ambiente de origem serão carregadas as informações. Caso não queira que alguma tabela seja carregada, acrescente ao NOT IN

FOR cur IN (SELECT * FROM ALL_TABLES where owner = v_ambiente_origem AND upper(table_name) NOT IN ('HTMLDB_PLAN_TABLE', 'OUTRA_TABELA')) LOOP

     v_comando := 'INSERT INTO "'||cur.table_name||'" SELECT * FROM "'||v_ambiente_origem||'"."'||cur.table_name||'"';

     htp.p(v_comando);

     EXECUTE IMMEDIATE v_comando;

END LOOP;



--Ao final habilite novamente todas as constraints 


FOR i IN (SELECT constraint_name, table_name FROM user_constraints WHERE constraint_type = 'R') LOOP

v_comando := 'alter table "'||i.table_name||'" enable constraint '||i.constraint_name||'';

htp.p(v_comando);

EXECUTE IMMEDIATE v_comando;


END LOOP;

END;
-----TERMINO SCRIPT-----



Pronto! Seu novo ambiente está com os dados desejados.


Caso seu banco de origem e destino dos dados sejam diferentes, segue outro passo a passo.


MIGRAÇÃO DE DADOS COM AMBIENTES EM BANCOS DIFERENTES


Este procedimento apenas no Apex (sem utilizar ferramenta) com bancos diferentes utiliza um processo bem mais manual. Só vale a pena executá-lo caso você queira migrar um número pequeno de tabelas.

Outro detalhe é que caso sua tabela tenha milhões de registros, como a exportação é web, as vezes não funciona bem.

Ressalto que o procedimento é para bancos diferentes. Caso sua aplicação esteja em workspaces e esquemas diferentes, mas está no mesmo banco, o melhor procedimento é o descrito acima.

Passo 1) Logue no ambiente Apex que contém os dados que deseja exportar (ambiente de origem).

Passo 2) Acesse o Data Workshop. "SQL Workshop > Utilities > Data Workshop"

Passo 3) Na seção "Data Unload", você pode escolher entre gerar um arquivo TXT ou XML com os dados de uma tabela. Para este tutorial, vamos selecionar "to XML"
Nota: Este é um dos pontos ruins da descarga de dados, pois você tem que fazer tabela a tabela.



Passo 4) Selecione as informações que devem ser incluídas no arquivo: owner, nome da tabela e as colunas que serão incluídas.
Essa seleção das colunas é um dos pontos de dificuldade dos desenvolvedores. Caso queira selecionar todas as colunas, clique na primeira coluna, mantenha pressionado o "Shift" no teclado e clique na última coluna ou clique em qualquer coluna e pressione "Ctrl"+A. Caso queira colunas específicas, mantenha pressionado "Ctrl" e vá clicando sobre as colunas desejadas.
Ao final, caso queira que os dados sejam gerados de maneira condicional, adicione algum comando no campo "Where Clause". Por exemplo, podemos descarregar somente os dados que foram criados pelo login "ANDERSONRF" com o comando CREATED_BY = 'ANDERSONRF' conforme mostra a figura abaixo.




Passo 5) Pressione "Unload Data" e o arquivo (XML ou TXT) será gerado.
Nota: Essa ação pode demorar um pouco dependendo da quantidade de dados.


Passo 6) Logue no ambiente de destino dos dados. Fique atento para não errar o ambiente!


Passo 7) Acesse "SQL Scripts" > "Data Workshop"


Passo 8) Na seção "Data Load" escolha o mesmo tipo de arquivo selecionado no Passo 3). (Seguindo nosso tutorial, devemos selecionar "XML Data").


Passo 9) Selecione o esquema, tabela e o arquivo gerado no passo Passo 5) e pressione o botão "Load Data".




Pronto! Agora seus dados foram carregados.


Espero que tenha gostado.

Qualquer problema, poste nos comentários.

Até a próxima!

quarta-feira, 14 de março de 2018

Como apagar todos os objetos de um esquema utilizando o Comandos SQL

Olá pessoal,

este post é para apresentar um script muito interessante que apaga todos os objetos de um esquema, ou seja, deixa o esquema do seu espaço de trabalho vazio.

Lembro que ele não apaga as aplicações, mas apaga todos os objetos e dados do banco.

Passo 1: Logue no espaço de trabalho, entre no "SQL Workshop" > "Comandos SQL"


Passo 2: Copie o comando abaixo e cole na área de texto do Comandos SQL

------INÍCIO SCRIPT-----
--MUITO CUIDADO--
--Este script DROPA todos os objetos do banco de dados

DECLARE 
v_comando VARCHAR2(200);

BEGIN
--Primeiro dropamos as views materializadas, pois internamente o Oracle cria um objeto do tipo TABLE e só permite dropar esse objeto com o comando DROP MATERIALIZED VIEW.
FOR cur IN (SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'MATERIALIZED VIEW') LOOP

     v_comando := 'DROP MATERIALIZED VIEW "'||cur.object_name||'"';
     htp.p(v_comando);
     EXECUTE IMMEDIATE v_comando;

END LOOP;

--Depois dropamos as tabelas, pois devemos utilizar o comando cascade constraints para excluir em cascata as constraints da tabela
FOR cur IN (SELECT * FROM USER_TABLES) LOOP

     v_comando := 'DROP TABLE "'||cur.table_name||'" cascade constraints';
     htp.p(v_comando);
     EXECUTE IMMEDIATE v_comando;

END LOOP;

--A seguir dropamos os outros objetos (lembrando que package body e jobs são dropados de outra maneira)
FOR cur IN (SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE NOT IN ('PACKAGE BODY', 'JOB', 'LOB')  ) LOOP

     v_comando := 'DROP ' || cur.object_type  || ' "'||cur.object_name||'"';
     htp.p(v_comando);
     EXECUTE IMMEDIATE v_comando;

END LOOP;

--Comando para dropar as jobs (scheduler jobs)
FOR cur IN (SELECT * FROM USER_scheduler_jobs ) LOOP

     v_comando := 'DBMS_SCHEDULER.drop_job(job_name =>''' ||cur.job_name||''')';
     htp.p(v_comando);
     DBMS_SCHEDULER.drop_job(job_name => cur.job_name);

END LOOP;

--Observação: Caso tenha jobs do tipo JOBS que estão deprecated, consulte a documentação de como dropá-las.

END;

------FIM  SCRIPT-----

Observação: Caso esteja utilizando colunas em tabela do tipo LOB, os objetos do tipo LOB continuarão ocupando espaço em disco. Caso queira liberar o espaço, a tabela que contém o objeto do tipo LOB deve ser "purgada" da lixeira (RECYCLE BIN) com o comando:

PURGE TABLE <<nome tabela>>

Passo 3) Clique no botão "Executar"

Passo 4) Confira o resultado da execução no painel de Resultados localizado na parte inferior. Caso tenha ocorrido algum problema, tente identificar o motivo.


Observação: Normalmente o feedback final do Apex é de "Tabela eliminada.". Esse feedback é incorreto, pois o Comandos SQL do Apex informa apenas o resultado do primeiro comando executado.

Espero que tenha gostado!
Qualquer dúvida ou, sugestão, deixe no comentário!

Até a próxima!

Como gerar o DDL dos objetos de banco e importar em outro ambiente

Olá pessoal,

seguindo a série de posts "Como colocar uma aplicação Apex em operação utilizando o App Builder e o SQL Workshop", vamos hoje falar sobre a geração do DDL (Data Definition Language) dos objetos de banco e a importação desses objetos em outro ambiente.

Antes de entrarmos propriamente no objetivo do post, gostaria de esclarecer uma dúvida comum de desenvolvedores iniciantes sobre a separação no Apex entre a aplicação e os objetos de banco (tabelas, views, functions, procedures etc).

A aplicação é manipulada no "App Builder", enquanto os objetos de banco e os dados são manipulados pelo desenvolvedor no "SQL Workshop". Apesar do nome SQL Workshop, nesta área é possível também manipular PL/SQL... :)

Veja a imagem abaixo com a identificação de onde manipulamos aplicações e objetos de banco.


Adivinha onde podemos gerar o DDL dos objetos de banco?

Isso mesmo, no SQL Workshop!

Vamos seguir um passo a passo para fazermos essa migração do DDL de um ambiente para outro.

Passo 1) Logar no ambiente que contém os objetos de banco (provavelmente seu ambiente de desenvolvimento) e acessar o SQL Workshop

Passo 2) Clicar em "Utilities" e em "Generate DDL"



Passo 3) Clique no botão "Create Script>"


Passo 4) Selecione o esquema onde os objetos se encontram e clique "Next >".
  
Passo 5) Selecione o tipo de "Output", sugiro "Save As Script File", pois gerará um arquivo que você poderá importar no outro ambiente. Caso deseje que todos os objetos sejam exportados, clique em "Check all" e no botão "Generate DDL". Caso contrário, selecione individualmente cada tipo de objeto que deseja exportar e clique "Next >" para refinar a seleção dos objetos a serem exportados. 



Passo 6) Digite o nome do Script e uma descrição para o script que será criado no repositório do "SQL Scripts" (componente que fica dentro do "SQL Workshop") e clique "Create Script"


Observação: Ao clicar em "Create Script", a geração do script pode ser uma operação bem lenta. No ambiente do apex.oracle.com é tão lento que as vezes dá timeout!!! Aparentemente a lentidão é maior quanto maior a quantidade de objetos no banco todo.

Passo 7) Ao finalizar a criação do script, clique no lápis logo abaixo da coluna "Edit"


Passo 8)  Clique em Download para baixar o arquivo.


Pronto! Você já gerou o arquivo que será necessário para a criação do DDL no outro ambiente.

Obs: Fique atento que a partir de agora os passos serão executados em outro ambiente Apex (normalmente o de operação)!!!

Passo 9) Agora, faça o login no ambiente Apex onde este arquivo será importado e acesse "SQL Workshop" > "SQL Scripts". 



Passo 10) Em SQL Scripts, clique no botão "Upload >".



Passo 11) Selecione o arquivo gerado no passo 8), digite o nome do script e clique "Upload"


Passo 12) Após o upload, clique no "Run" para executar o script.


Passo 13) Antes de executar, o Apex faz um "parse" do seu script e informa a quantidade de comandos. Confira se está ok, e clique em "Run Now"



Passo 14) Confira os erros e caso necessário, utilize o procedimento do post indicado a seguir para "limpar" todo o esquema.




Observação: Alguns erros acontecem, pois às vezes o script gerado pelo Apex cria o mesmo objeto duas vezes e ocorre o erro de nome já utilizado em outro objeto ("name is already used by an existing object"). Acontece muito com índices de chave primária e índices de LOB.

Pronto! Agora seus objetos estão todos no esquema desejado!

Para conferir os objetos criados, você pode acessar o "SQL Workshop > Browser de objetos".

Lembrando que os dados não foram migrados. Em breve, postaremos como migrar os dados de outro esquema. Não perca!

Espero que tenha gostado.

Deixe seu comentário!

quarta-feira, 28 de fevereiro de 2018

Colocando uma aplicação Apex em operação apenas com o App Builder e SQL Commands

Olá pessoal,

muitos iniciantes tem dúvidas de como colocar uma aplicação em operação (produção) no Apex.

Observação: Em diversos lugares utiliza-se o termo em produção para as aplicações que o usuário final já está operando. Prefiro utilizar o termo em operação, pois está previsto no ITIL e não confunde. Muitas pessoas acham que um sistema em produção é um sistema que ainda está sendo produzido, mas no jargão da TI este termo na verdade significa um sistema já em operação/uso.

Primeiro, gostaria de ressaltar que na verdade toda aplicação Apex já é acessível pelo usuário final como se estivesse em operação (Talvez seja necessário criar um usuário para que ele possa autenticar/logar). Basta divulgar o link!

Segundo, este post e os subsequentes da série abordarão como realizar as operações utilizando apenas o App Builder e o SQL Commands do Apex. Sei que há maneiras melhores de se fazer essas operações utilizando ferramentas!

A boa prática preza que a aplicação deve ser desenvolvida em um ambiente diferente do ambiente de operação.

Portanto, quando você terminar de desenvolver e quiser colocar em operação basicamente deve executar 3 ações de cópia do ambiente de desenvolvimento para o de operação:

1) Copiar o DDL (Data definition language) dos objetos de banco

2) Copiar a aplicação

3) Copiar os dados

Até mais!