Aprenda PL/SQL

Category Archive: PL/SQL

report_error2

Este é mais um daqueles casos que temos que chamar o Padre Quevedo pra dizer: “Este fenômeno nôm exziste“. Abaixo, vamos fazer uma análise de um programa com erro e como foi solucionado o erro.

O PROBLEMA

O relatório funcionava perfeitamente. Mas raramente, ou seja, 1% das vezes em que era executado, o relatório saía com os valores de todos os campos duplicados! Exemplo:  Deveria sair o valor R$ 20,00  mas saía R$ 40,00. Isso ocorria em todas colunas!

Leia mais…




Bug do ano 2013

25/02/2013 | Tags:, , | Categories: PL/SQL

Pra quem pensa que o Bug do Milênio foi o último envolvendo datas, aí vai a sensação do momento: O Bug de 2013.

O Bug ocorre na seguinte situação:
* O programa X grava dados de data na tabela usando o seguinte formato de datas:  YYYYMMDD.
* O programa Y lê dessa tabela usando o formato DDMMYYYY. (Sim, é um erro, um bug no programa).

Até o ano de 2012, este erro não aparecia, veja um exemplo:
* O programa X salva a data 23abr201120110423 (YYYYMMDD)
* Quando essa data era lida pelo programa Y, ele lia  20110423 (DDMMYYYY) = 20nov0423. Data errada, mas não acusava erro!

Pois o ano de 2013 chegou e esse erro apareceu. Veja:
* O programa X salva a data 23abr201320130423 (YYYYMMDD)
* Quando essa data era lida pelo programa Y, ele lia  20130423 (DDMMYYYY) = 20-???-0423. Como não existe mês 13, dá erro!

O interessante é que esse bug está aparecendo só agora, depois de muitos anos de erro oculto. Finalmente em 2013 ele está fazendo os programas pararem de rodar.

Exemplo/Situação compartilhada por Rafael Lopes Lima (Programador Oracle EBS)




Neste artigo irei apresentar um recurso muito bom que existe no Oracle Database desde a versão 9i e chama-se Pipelined Table Function.

Este recurso permite criar funções que retornam dados como se fossem uma tabela virtual, podendo transformar os dados de retorno enquanto eles são produzidos, ou seja, é possível alterar os dados pesquisados em uma tabela, linha por linha, enquanto eles são processados, sem ter que esperar pelo retorno completo do “result set” (conjunto de dados que são retornados pela função).

Este recurso é ótimo para ETL (Extract, Transform, and Load), pois é rápido e consome menos memória que outros métodos que podem ser utilizados para o mesmo objetivo, como por exemplo, preencher um cursor e percorrê-lo para transformar e retornar dados.

Seguem abaixo 3 scripts que demonstram como criar e testar uma Pipelined Table Function. Os scripts utilizam a tabela EMPLOYEES do schema de exemplo HR.

Para iniciar o passo-a-passo dos itens abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, com um usuário com privilégios administrativos (usuário contendo a role DBA ou o privilégio de sistema SYSDBA) ou com o usuário HR.

1- Criando a package HR.PKG_TYPES

A package HR.PKG_TYPES contém os tipos de dados que são criados para retornarem uma tabela virtual na função que será criada no próximo passo:

create or replace package HR.PKG_TYPES as
  TYPE TABLEEMPTYPE IS TABLE OF EMPLOYEES%ROWTYPE;
  TYPE ROWEMPTYPE IS RECORD(
          EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
          FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
          LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
          EMAIL          EMPLOYEES.EMAIL%TYPE,
          PHONE          EMPLOYEES.PHONE_NUMBER%TYPE,
          HIRE_DATE      EMPLOYEES.HIRE_DATE%TYPE,
          JOB_ID         EMPLOYEES.JOB_ID%TYPE,
          SALARY         EMPLOYEES.SALARY%TYPE,
          COMMISSION_PCT EMPLOYEES.COMMISSION_PCT%TYPE,
          MANAGER_ID     EMPLOYEES.MANAGER_ID%TYPE,
          DEPARTMENT_ID  EMPLOYEES.DEPARTMENT_ID%TYPE
         );
END;
/


Leia mais…




Eu considero Edition-Based Redefinition a nova característica mais matadora do Oracle Database 11g Release 2. Em suma, é a capacidade de executar uma atualização de aplicações online. É também um recurso gigante, tão grande que vai demorar pelo menos três colunas para descrevê-lo. Vou começar em Como usar Edition-Based Redefinition afim de aplicar “patches” em sistemas.

Ao longo dos anos, o banco Oracle permite realizrmos muitas operações online, como:

  • Modificar a maioria dos parâmetros (Apenas 90 dos 350 não são modificáveis online.)
  • Reorganização de objetos (transformar uma tabela não particionada em uma particionada, recuperando um espaço livre, e assim por diante)
  • Criar índices
  • Aplicação de patches no Banco de dados com o Oracle Real Application Clusters
  • Atualizar o banco de dados Oracle de release em release

O resultado é que quase todas mudanças em nível de banco de dados pode ser feito enquanto o banco está funcionando e realizando transações – com algumas raras excepções gritantes, como re-criar uma procedure, alterar triggers, adicionar grants, revoke de grant, e modificar views. Em suma, os objetos que constituem esse tipo de modificação não podiam ser modificadas, enquanto os usuários estavam utilizando. Se uma procedure estava sendo executada e um DBA tentou atualizar ela (CREATE OR REPLACE o código com o novo código mexido, afim de corrigir um bug), o DBA iria ficar esperando (BLOCKED) por essa pessoa até terminar sua execução.

Além disso, qualquer pessoa que tentou posteriormente executar uma procedure que o DBA está tentando substituir também ficaria bloqueado pelo DBA. E na maioria dos casos, o DBA não modifica apenas uma única procedure, mas muitas,  e o CREATE OR REPLACE do novo código da procedure tenderia a invalidar outros objetos dependentes também. O banco de dados parece “congelar”, o DBA não pode realizar as tarefas de patch (substituir alguns procedimentos, pacotes, views, triggers, e assim por diante), e os usuários finais não podem realizar as suas tarefas. Eles acabaram bloqueando e travando-se mutuamente.

Isto tudo termina tudo com o Oracle Database 11g Release 2 e o Edition-Based Redefinition, que permite aos DBAs e usuários finais acessar mais de uma ocorrência de uma stored procedure, trigger, view, e outros objetos e, portanto, isolar as mudanças em um esquema. Começando com o Oracle Database 11g Release 2, um único esquema pode agora ter duas ou mais ocorrências (vamos pensar em “versões”) de um stored procedure (função, trigger, e assim por diante) ou uma view ou de sinônimos e todos seus metadados relacionados, tais como GRANTS a esses objetos. (Estas duas ocorrências são independentes, eles coexistem, mas não interferem umas com as outras.) A “mágica” que permite isso é o novo objeto de edição (EDITION),  que introduz um novo namespace transparente que permite mais de uma ocorrência ao mesmo tempo de uma stored procedure, trigger e assim por diante.
Leia mais…




MS Excel usa um formato de data chamado “Serial Date format” ao armazenar e lidar com datas e horas. A “Data serial” é calculada pegando o número de dias entre uma data qualquer e o dia 01/01/1900.

Por que isso é importante para um desenvoledor Oracle ? Se você está criando um documento em PL/SQL que será aberto no Excel, a data ficará incorreta a não ser que você converta antes para o padrão do Excel.

Felizmente, a formula de conversão de datas é muito simples. A seguinte função fará a conversão de uma data no Oracle para o padrão do Excel:

CREATE OR REPLACE FUNCTION convertOracleToSerialDate(p_date DATE) RETURN NUMBER
IS
BEGIN
RETURN (p_date – TO_DATE(’01-JAN-1900?))+2;
END;
/


Leia mais…




Colunas Virtuais no 11g – Parte 2 – Índices e Constraints

Continuando o artigo sobre colunas virtuais no Oracle 11g, veremos outras possibilidades do uso dessa feature.

Se tentarmos inserir dados que resultem em uma coluna virtual duplicada, podemos esperar uma violação de Unique Constraint:
Leia mais…




Oracle suportou expressões armazenadas por muitos anos, em views e índices baseados em função. Mais principalmente, são as views que nos permitem armazenar e modularisar expressões calculadas baseados em outras colunas da tabela.

Em versões mais recentes (a partir do 8i), temos como indexar as colunas baseados em função. (Function based indexes). Agora, a partir da versão 11g, o banco Oracle nos permite também armazenar expressões diretamente nas tabelas como colunas virtuais.

Como veremos neste artigo, colunas virtuais são mais flexíveis que as outras alternativas citadas. Vamos examinar seu uso básico e também considerar alguns outros aspectos dessa nova feature.
Leia mais…




Trabalhando com Strings no Oracle

03/06/2009 | Tags:, | Categories: PL/SQL

Vou agora tentar mostrar como trabalha com string no Oracle… Venho acompanhando alguns Foruns de Desenvolvimento e sempre estou me deparando com questões sobre o correto uso de funções para trabalhar com String…

  1. Concatenando 2 Strings
  2. Colocando a primeira letra do texto como Maiúscula
  3. Transformando o texto em texto Minúsculo e Maiúsculo
  4. Convertendo letras em códigos ASCII
  5. Localizando a posição uma string no meio do texto
  6. Atribuindo dígitos no final e/ou no inicio do texto
  7. Removendo espaços no inicio e/ou fim do texto
  8. Como inverter o texto
  9. Substituindo Strings por outras Strings
  10. Cortando o conteúdo do texto
  11. Descobrindo o tamanho da string
  12. Retornando colunas com valor não nulo
  13. Exemplo da utilização de algumas funções
    • Pesquisando em uma string por um digito e exibindo o resto da string pegando como base esse digito localizado.
    • Repartindo uma string e inserindo um valor no meio.


Leia mais…




Execução ordenada nas triggers

14/05/2009 | Tags:, | Categories: PL/SQL

A partir do Oracle 8i é possível criar mais de uma trigger do mesmo tipo na mesma tabela. Exemplo: duas triggers AFTER EACH ROW on INSERT. O tipo de trigger determina a ordem de execução: As BEFORE são executadas antes, e as AFTER depois. Contudo, como saber qual trigger executará primeiro quando se tem duas AFTER EACH ROW on INSERT ?
Leia mais…




Hoje vamos ver o quanto é bom explorar certos recursos da ferramenta de Desenvolvimento Oracle. Sabe aquela rotina bem dinâmica que você não imagina como poderá resolver? Agora, imagine tentar guardar tudo isso em memória… Melhorou um pouco, correto? Vamos ver como é bom trabalhar com registros de memória no Oracle.

Leia mais…