Saturday, July 23, 2005
DBA de Peso
Hoje eu percebi como estou um DBA de peso. Estava na rua Santa Ifigênia (é assim mesmo que se escreve com "i") procurando um novo micro para mim, quando ao ver uma farmácia, tive a "brilhante" idéia de entrar e ir direto a balança. Quase desmaio. 90 Kg. Noventa! Meu peso deve ser 75-78 e eu estou com No-Ven-Ta!
Eu sou um sistema OLTP, mas atualmente minha configuração está para uma Data Warehouse. Tá na hora de começar a fazer um shrink nos meus datafiles. Vou procurar exercitar meus índices. Ou seja, meu organismo precisa *urgente* de um DBA. Eu não segui as recomendações, estive mantendo histórico demais, não fiz leitura dos alerts, não medi o sistema, não considerei minhas instrumentações, ainda bem que não houve nenhum crash até agora.
Performance então, ufff. Não consigo correr 100 metros sem travar. Assim como uma aplicação mal dimensionada - simplesmente trava - o coração bloqueia o pulmão que bloqueia o cérebro (v$lock mostra a fila). Meu tkprof está mostrando muito parse para pouca execução. Também pudera, ficar fazendo full table scan em tabelas gordas gigantes. Eu diria que estou executando a 100%.
Metáforas a parte, eu cruzei meu threshold. Passei da linha! E deixei tudo de lado, passo tempo demais atrás desses teclados estudando, escrevendo, testando, etc. Perdi contato com os amigos, com o futebol, atividades físicas e lúdicas. Isso reflete no humor. Agora eu entendo porque muitas vezes o Oracle é rude comigo, vive me respondendo mal.
Hoje na Sta Ifigênia, em meio aquela multidão e eu, solitário, caminhando, procurando mais uma máquina para testar, instalar o sistema operacional, colocar na rede, instalar outro servidor de BD, Apache, mod_sql, htmldb, VMWare, VNC, me atolar...
Como mudar isso tudo? Como voltar no passado? Veja, se fosse no Oracle eu já saberia a resposta: eu usaria flashback, voltaria backup, mas com minha vida. Sei lá! Claro, com tudo isso na cabeça, voltando com os orçamentos, dobrei na Ipiranga chegando até a São João, lembrei da música do Caetano... e também do Bar Brahma, lá fui eu comer uma feijoada e tomar uns chopps para começar um tuning segunda.
Eu sou um sistema OLTP, mas atualmente minha configuração está para uma Data Warehouse. Tá na hora de começar a fazer um shrink nos meus datafiles. Vou procurar exercitar meus índices. Ou seja, meu organismo precisa *urgente* de um DBA. Eu não segui as recomendações, estive mantendo histórico demais, não fiz leitura dos alerts, não medi o sistema, não considerei minhas instrumentações, ainda bem que não houve nenhum crash até agora.
Performance então, ufff. Não consigo correr 100 metros sem travar. Assim como uma aplicação mal dimensionada - simplesmente trava - o coração bloqueia o pulmão que bloqueia o cérebro (v$lock mostra a fila). Meu tkprof está mostrando muito parse para pouca execução. Também pudera, ficar fazendo full table scan em tabelas gordas gigantes. Eu diria que estou executando a 100%.
Metáforas a parte, eu cruzei meu threshold. Passei da linha! E deixei tudo de lado, passo tempo demais atrás desses teclados estudando, escrevendo, testando, etc. Perdi contato com os amigos, com o futebol, atividades físicas e lúdicas. Isso reflete no humor. Agora eu entendo porque muitas vezes o Oracle é rude comigo, vive me respondendo mal.
Hoje na Sta Ifigênia, em meio aquela multidão e eu, solitário, caminhando, procurando mais uma máquina para testar, instalar o sistema operacional, colocar na rede, instalar outro servidor de BD, Apache, mod_sql, htmldb, VMWare, VNC, me atolar...
Como mudar isso tudo? Como voltar no passado? Veja, se fosse no Oracle eu já saberia a resposta: eu usaria flashback, voltaria backup, mas com minha vida. Sei lá! Claro, com tudo isso na cabeça, voltando com os orçamentos, dobrei na Ipiranga chegando até a São João, lembrei da música do Caetano... e também do Bar Brahma, lá fui eu comer uma feijoada e tomar uns chopps para começar um tuning segunda.
Thursday, July 21, 2005
Enviando E-Mail Usando PLSQL (Exemplo)
O exemplo fala por si.
E no meu e-mail eu recebi:
ops$marcio@ORA10G> create or replace package mail
2 as
3 type array is table of varchar2(255);
4
5 procedure send( p_sender_email in varchar2,
6 p_from in varchar2 default NULL,
7 p_to in array default array(),
8 p_cc in array default array(),
9 p_bcc in array default array(),
10 p_subject in varchar2 default NULL,
11 p_body in long default NULL );
12 end;
13 /
Package created.
ops$marcio@ORA10G> create or replace package body mail
2 as
3
4 g_crlf char(2) default chr(13)||chr(10);
5 g_mail_conn utl_smtp.connection;
6 g_mailhost varchar2(255) := 'smtp.uol.com.br';
7
8 function address_email( p_string in varchar2,
9 p_recipients in array ) return varchar2
10 is
11 l_recipients long;
12 begin
13 for i in 1 .. p_recipients.count
14 loop
15 utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
16 if ( l_recipients is null )
17 then
18 l_recipients := p_string || p_recipients(i) ;
19 else
20 l_recipients := l_recipients || ', ' || p_recipients(i) ;
21 end if;
22 end loop;
23 return l_recipients;
24 end;
25
26
27 procedure send( p_sender_email in varchar2,
28 p_from in varchar2 default NULL,
29 p_to in array default array(),
30 p_cc in array default array(),
31 p_bcc in array default array(),
32 p_subject in varchar2 default NULL,
33 p_body in long default NULL )
34 is
35 l_to_list long;
36 l_cc_list long;
37 l_bcc_list long;
38 l_date varchar2(255) default
39 to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );
40
41 procedure writeData( p_text in varchar2 )
42 as
43 begin
44 if ( p_text is not null )
45 then
46 utl_smtp.write_data( g_mail_conn, p_text || g_crlf );
47 end if;
48 end;
49 begin
50 g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
51
52 utl_smtp.helo(g_mail_conn, g_mailhost);
53 utl_smtp.mail(g_mail_conn, p_sender_email);
54
55 l_to_list := address_email( 'To: ', p_to );
56 l_cc_list := address_email( 'Cc: ', p_cc );
57 l_bcc_list := address_email( 'Bcc: ', p_bcc );
58
59 utl_smtp.open_data(g_mail_conn );
60
61 writeData( 'Date: ' || l_date );
62 writeData( 'From: ' || nvl( p_from, p_sender_email ) );
63 writeData( 'Subject: ' || nvl( p_subject, '(no subject)' ) );
64
65 writeData( l_to_list );
66 writeData( l_cc_list );
67
68 utl_smtp.write_data( g_mail_conn, '' || g_crlf );
69 utl_smtp.write_data(g_mail_conn, p_body );
70 utl_smtp.close_data(g_mail_conn );
71 utl_smtp.quit(g_mail_conn);
72 end;
73
74
75 end;
76 /
Package body created.
ops$marcio@ORA10G>
ops$marcio@ORA10G> show error
No errors.
ops$marcio@ORA10G>
ops$marcio@ORA10G> drop table t purge;
Table dropped.
ops$marcio@ORA10G>
ops$marcio@ORA10G> create table t
2 (
3 id number,
4 dt date,
5 nome varchar2(50)
6 )
7 /
Table created.
ops$marcio@ORA10G>
ops$marcio@ORA10G> insert into t
2 select user_id, created, username
3 from all_users;
14 rows created.
ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace procedure p( p_dt in date )
2 is
3 l_body long := chr(10) || lpad('id', 10, ' ') || ' '
4 || rpad('Nome', 50, ' ') || chr(10) ||
5 rpad('-', 10, '-') || ' ' ||
6 rpad('-', 50, '-') || chr(10);
7 begin
8 for x in ( select id, nome
9 from t
10 where dt >= p_dt )
11 loop
12 l_body := l_body || lpad(x.id, 10, ' ') || ' ' ||
13 rpad(x.nome, 50, ' ') || chr(10);
14 end loop;
15 mail.send
16 ( p_sender_email => '<marcio.portes@uol.com.br>',
17 p_from => 'Marcio Portes <marcio.portes@uol.com.br>',
18 p_to => mail.array( '<mportes-blog@uol.com.br>'),
19 p_subject => 'Exemplo para o Blog! ',
20 p_body => l_body );
21 end;
22 /
Procedure created.
ops$marcio@ORA10G>
ops$marcio@ORA10G> show error
No errors.
ops$marcio@ORA10G>
ops$marcio@ORA10G> exec p( trunc(sysdate, 'y') )
PL/SQL procedure successfully completed.
E no meu e-mail eu recebi:
From - Thu Jul 21 19:55:29 2005
X-Account-Key: account1
X-UIDL: 6629
X-Mozilla-Status: 0001
X-Mozilla-Status2: 00000000
Return-Path:
Received: from saturno15.mail.sys.intranet (172.26.14.205) by saturno28.mail.sys.intranet (7.1.006)
id 42B9568E00973357 for marcio.portes@uol.com.br; Thu, 21 Jul 2005 19:51:00 -0300
Delivered-To: mportes-blog@uol.com.br
Received: from shadow5-1.uol.com.br (172.26.5.186) by saturno15.mail.sys.intranet (7.1.006)
id 41DACD340279D525 for mportes-blog@uol.com.br; Thu, 21 Jul 2005 19:51:00 -0300
Received: from smtp.uol.com.br (neovia.neoviatelecom.com.br [200.205.213.225])
by scorpio.uol.com.br (Postfix) with SMTP id 840AC8AF7
for; Thu, 21 Jul 2005 19:51:00 -0300 (BRT)
Date: 21 Jul 05 19:54:37
From: Marcio Portes
Subject: Exemplo para o Blog!
To:
Message-Id: <20050721225100.840ac8af7@scorpio.uol.com.br>
id Nome
---------- --------------------------------------------------
130 UTIL
63 OPS$MARCIO
129 PAY
66 PERFSTAT
127 PAY_DBA
128 PAY_BIN
Tuesday, July 19, 2005
Redolog Files
Autor: Fábio Girardi
A Dúvida.
Em private, pedi ao Fábio o alert log, tnsnames e sqlnet. Depois da análise, tenho uma tese.
No seu alert encontrei várias incidências de
"cannot allocate new log, sequence ...
Checkpoint not complete"
É certo que seus redolog files estão pequenos para determinado momento da sua aplicação. Eles não conseguem completar o ciclo dos 3 jogos de redolog sem que haja contenção (espera). Agora, o que eles têm a ver com o seu problema? Grande dúvida, não estou certo, mas todos os sintomas indicam que a contenção paralisa as atividades do banco (inclusive connect) e o efeito colateral disso seria o erro ORA-12519, por este motivo é que depois de 30, 45 segundos, as atividades voltam ao normal. Estou ainda pesquisando e caso encontre algo, publicarei aqui como adendo.
Primeiro, vamos tratar da sua contenção, depois por favor, verifique se o problema persiste. No seu banco, é necessário aumentar os jogos de redolog file. Copiado de seu último e-mail:
Eu acredito que 10mb para seu redo log file é pequeno para bases de produção (veja quanto voce tinha no 9i, que não apresentava o problema), eu aumentaria para 5 grupos de 100mb cada membro. Voce não pode fazer resize de redo log file, sempre adicione membro ao grupo e drop os membros que estão inativos. Faça com que todos os 5 membros tenham 100mb cada.
Depois disso faça 5 imports de uma vez e verifique se o erro volta. Em outra oportunidade podemos discutir o que está gerando tanto redo na sua aplicação, mas primeiro, vamos tentar solucionar o erro ORA-12519.
A Dúvida.
Caro DBA,
Estamos com um problema intermitente em nosso Servidor Oracle. Por vezes o erro ORA-12519 é retornado em distintas aplicações (Java sob TomCat e/ou Centura-Cliente/Servidor), e após 30-45 segundos o problema 'desaparece'.
O erro surge sem aparente problema, e sem intervenção do administrador da base o erro desaparece. Alguma dica ??
Erro:
ORA-12519: TNS:no appropriate service handler found
Dados da Instalação:
Oracle 10g: versão 10.1.0.3.0
Sistema Operacional: Red Hat Enterprise Linux ES release 3 (Taroon) 2.4.21 4.ELsmp (32-bit)
Hardware: Dell Power Edge Pentium 4 3.20GHz / 2 Gb DDR
Tamanho Total da SGA (MB) 768
Componente da SGA Alocação Atual (MB)
Shared Pool 276
Cache de Buffer 472
Large Pool 4
Pool Java 8
Outros 8
Muito obrigado,
Fábio Girardi
Assis / SP
Em private, pedi ao Fábio o alert log, tnsnames e sqlnet. Depois da análise, tenho uma tese.
No seu alert encontrei várias incidências de
"cannot allocate new log, sequence ...
Checkpoint not complete"
É certo que seus redolog files estão pequenos para determinado momento da sua aplicação. Eles não conseguem completar o ciclo dos 3 jogos de redolog sem que haja contenção (espera). Agora, o que eles têm a ver com o seu problema? Grande dúvida, não estou certo, mas todos os sintomas indicam que a contenção paralisa as atividades do banco (inclusive connect) e o efeito colateral disso seria o erro ORA-12519, por este motivo é que depois de 30, 45 segundos, as atividades voltam ao normal. Estou ainda pesquisando e caso encontre algo, publicarei aqui como adendo.
Primeiro, vamos tratar da sua contenção, depois por favor, verifique se o problema persiste. No seu banco, é necessário aumentar os jogos de redolog file. Copiado de seu último e-mail:
GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 27629 10485760 1 NO ACTIVE
2 27630 10485760 1 NO CURRENT
3 27628 10485760 1 NO INACTIVE
Eu acredito que 10mb para seu redo log file é pequeno para bases de produção (veja quanto voce tinha no 9i, que não apresentava o problema), eu aumentaria para 5 grupos de 100mb cada membro. Voce não pode fazer resize de redo log file, sempre adicione membro ao grupo e drop os membros que estão inativos. Faça com que todos os 5 membros tenham 100mb cada.
Depois disso faça 5 imports de uma vez e verifique se o erro volta. Em outra oportunidade podemos discutir o que está gerando tanto redo na sua aplicação, mas primeiro, vamos tentar solucionar o erro ORA-12519.
Friday, July 15, 2005
DBMS_SQL Introdução
Autor: Ana Claudia
A Dúvida.
Ana, tem dois dias que *QUERO* "falar um pouquinho" sobre dbms_sql, mas é difícil, principalmente sem um problema efetivo ou um pequeno estudo de caso para que se possa desenvolver algo.
Basicamente a Oracle dá duas opções para quem necessita trabalhar com setenças SQL dinâmicas seja DML ou DDL: o DBMS_SQL e o Native Dynamic SQL (mais conhecido como execute immediate). O entedimento da diferença entre os dois métodos é essencial no momento da escolha entre um ou outro.
Eu, particularmente, uso pouco o dbms_sql. Na maioria das vezes utilizo o execute immediate. Tenho usado dbms_sql nos casos onde não conheço o nome das colunas nem os datatypes, por exemplo quando tive que gerar arquivos diretamente de queries, então usei o dbms_sql para conhecer as colunas e fazer dump dos dados no arquivo.
A figura abaixo representa o fluxo de execução do DBMS_SQL.
Sobre as "variaveis" DEFINE_ARRAY e BIND_VARIABLE, na verdade elas não são variáveis e sim procedures do pacote DBMS_SQL.
BIND_VARIABLE procedure é usada para definir valores a uma(s) variavel(is), ou seja, da mesma forma e importância que utilizamos bind variable nas SQLs estáticas devemos utilizar em SQL dinâmicas. Para conceito e importância de bind variable, clique aqui para ler a respeito.
DEFINE_ARRAY procedure assim como DEFINE_COLUMN e DEFINE_COLUMN_LONG são usadas como o INTO do SQL estático, ou seja, elas preparam as variáveis para receber os valores vindos do cursor após a execução.
O exemplo abaixo mostra como funciona o DESCRIBE, porém se achar interessante outros exemplos, fique a vontadade para enviá-los. Na medida do possível tentarei desenvolvê-los.
Repare que na verdade o DESCRIBE serve para o cursor e não somente para uma tabela. Fiz uma mistura entre a tabela EMP e ALL_OBJECTS e a procedure me devolveu todas as colunas envolvidas. Agora depende de suas necessidades para sacar proveito do DBMS_SQL.
A Dúvida.
Oi Marcio
Gosto muito do seu Blog,acho bastante interessante. Estive procurando pelo pacote DBMS_SQL mas nao encontrei nada, entao eu queria te fazer este pedido: fala um pouquinho do pacote DBMS_SQL e das variaveis DEFINE_ARRAY e BIND_VARIABLE. Estou trabalhando em um projeto que tem este pacote e nao estou muito segura pra mexer com ele.
Obrigada
Ana Claudia
Ana, tem dois dias que *QUERO* "falar um pouquinho" sobre dbms_sql, mas é difícil, principalmente sem um problema efetivo ou um pequeno estudo de caso para que se possa desenvolver algo.
Basicamente a Oracle dá duas opções para quem necessita trabalhar com setenças SQL dinâmicas seja DML ou DDL: o DBMS_SQL e o Native Dynamic SQL (mais conhecido como execute immediate). O entedimento da diferença entre os dois métodos é essencial no momento da escolha entre um ou outro.
Eu, particularmente, uso pouco o dbms_sql. Na maioria das vezes utilizo o execute immediate. Tenho usado dbms_sql nos casos onde não conheço o nome das colunas nem os datatypes, por exemplo quando tive que gerar arquivos diretamente de queries, então usei o dbms_sql para conhecer as colunas e fazer dump dos dados no arquivo.
A figura abaixo representa o fluxo de execução do DBMS_SQL.
Sobre as "variaveis" DEFINE_ARRAY e BIND_VARIABLE, na verdade elas não são variáveis e sim procedures do pacote DBMS_SQL.
BIND_VARIABLE procedure é usada para definir valores a uma(s) variavel(is), ou seja, da mesma forma e importância que utilizamos bind variable nas SQLs estáticas devemos utilizar em SQL dinâmicas. Para conceito e importância de bind variable, clique aqui para ler a respeito.
DEFINE_ARRAY procedure assim como DEFINE_COLUMN e DEFINE_COLUMN_LONG são usadas como o INTO do SQL estático, ou seja, elas preparam as variáveis para receber os valores vindos do cursor após a execução.
O exemplo abaixo mostra como funciona o DESCRIBE, porém se achar interessante outros exemplos, fique a vontadade para enviá-los. Na medida do possível tentarei desenvolvê-los.
ops$marcio@ORA10G> create or replace procedure p ( p_stmt in varchar2 )
2 is
3 c number;
4 d number;
5 col_cnt integer;
6 rec_tab dbms_sql.desc_tab;
7 col_num number;
8 procedure print_rec(rec in dbms_sql.desc_rec) is
9 begin
10 dbms_output.put_line( rec.col_name);
11 end;
12 begin
13 c := dbms_sql.open_cursor;
14 dbms_sql.parse(c, p_stmt, dbms_sql.native);
15 d := dbms_sql.execute(c);
16 dbms_sql.describe_columns(c, col_cnt, rec_tab);
17
18 col_num := rec_tab.first;
19 if (col_num is not null) then
20 loop
21 print_rec(rec_tab(col_num));
22 col_num := rec_tab.next(col_num);
23 exit when (col_num is null);
24 end loop;
25 end if;
26
27 dbms_sql.close_cursor(c);
28 end;
29 /
Procedure created.
ops$marcio@ORA10G> show error
No errors.
ops$marcio@ORA10G>
ops$marcio@ORA10G> exec p( 'select * from emp, all_objects' )
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY
PL/SQL procedure successfully completed.
Repare que na verdade o DESCRIBE serve para o cursor e não somente para uma tabela. Fiz uma mistura entre a tabela EMP e ALL_OBJECTS e a procedure me devolveu todas as colunas envolvidas. Agora depende de suas necessidades para sacar proveito do DBMS_SQL.
Tuesday, July 05, 2005
Boas Práticas de Programação - WHEN OTHERS
Um colega teve uma dúvida em uma lista de discussão e me motivou a escrever sobre o assunto aqui, já que acredito que seja de interesse comum. Trata-se do uso indevido e indiscriminado da cláusula WHEN OTHERS nos códigos PL/SQL.
Assim como em qualquer linguagem de programação, no PL/SQL também podemos tratar as exceções. O problema é que, muitas vezes, por "falta de tempo" ou preguiça mesmo, muitos codificadores (DBAs, analistas, etc), simplesmente para que seus códigos não apresentem erro, jogam a cláusula EXCEPTION WHEN OTHERS then NULL para evitar que o programa seja abortado. Isso em 99% dos casos é um BUG. A cláusula mascara qualquer exceção no programa e torna muito difícil a depuração do código.
Vamos a um exemplo hipotético (porque isso nunca acontece na vida real) faltando 5 dias para o fechamento do mês, o gerente resolve atender a uma premiação que a diretoria autorizou e pede aos desenvolvedores que codifiquem uma função para aumentar em 10% o salário dos funcionários. Como o desenvolvedor é um analista sagaz, ele não pode deixar que seu código aborte, então ele vai tratar TODAS as exceções através do WHEN OTHERS. Veja o prejuízo dos funcionários.
Sem erros! Porém, quando o desenvolvedor que implementou o roda_folha começa a testar seu programa, ele não quer saber de problemas, assim já lança mão da cláusula when others e NULL para realmente não ter surpresas.
E nossos heróis esperançosos com o aumento!
Nenhum problema quando rodamos a folha. Os operadores felizes, não acionaram ninguém aquela noite!
Mas no outro dia! ;)
O que deveria ser:
Este pequeno exemplo tentou ilustrar o que ocorre com a má prática de programação quando se utiliza o artifício da cláusula WHEN OTHERS.
A dúvida do colega é se há alguma maneira de bloquear o uso da cláusula WHEN OTHERS em código fonte de triggers. O recomendável é prevenir em todos os códigos!
Se é para ser drástico, vou matar o mal pela raiz. O método usado foi: criei uma trigger de evento de DDL (create e alter), verifiquei se o CREATE ou ALTER era para trigger e busquei no código fonte o WHEN OTHERS; encontrado, devolvo um erro de usuário com uma mensagem "Uso indesejável do WHEN OTHERS"!
Demonstrando:
Obviamente, o exemplo acima deve ser aperfeiçoado, já que é possível separar WHEN OTHERS em duas linhas ou colocar mais espaços entre eles. A demonstração serve apenas para uma idéia de como implementar o bloqueio.
No texto todo, nota-se claramente o uso excessivo da cláusula WHEN OTHERS. Isso foi proposital, para frisar. Assim, quando a virem em algum código lembrem-se do que foi discutido aqui.
Assim como em qualquer linguagem de programação, no PL/SQL também podemos tratar as exceções. O problema é que, muitas vezes, por "falta de tempo" ou preguiça mesmo, muitos codificadores (DBAs, analistas, etc), simplesmente para que seus códigos não apresentem erro, jogam a cláusula EXCEPTION WHEN OTHERS then NULL para evitar que o programa seja abortado. Isso em 99% dos casos é um BUG. A cláusula mascara qualquer exceção no programa e torna muito difícil a depuração do código.
Vamos a um exemplo hipotético (porque isso nunca acontece na vida real) faltando 5 dias para o fechamento do mês, o gerente resolve atender a uma premiação que a diretoria autorizou e pede aos desenvolvedores que codifiquem uma função para aumentar em 10% o salário dos funcionários. Como o desenvolvedor é um analista sagaz, ele não pode deixar que seu código aborte, então ele vai tratar TODAS as exceções através do WHEN OTHERS. Veja o prejuízo dos funcionários.
ops$marcio@ORA10G> create table func as select empno, ename, sal from scott.emp;
Table created.
ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace function
2 aumenta_sal( sal in number ) return number
3 is
4 l_novo_sal number default 0;
5 begin
6 l_novo_sal := sal / 0; -- ops! certamente um erro!
7 end;
8 /
Function created.
ops$marcio@ORA10G> show error
No errors.
Sem erros! Porém, quando o desenvolvedor que implementou o roda_folha começa a testar seu programa, ele não quer saber de problemas, assim já lança mão da cláusula when others e NULL para realmente não ter surpresas.
ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace procedure
2 roda_folha
3 is
4 begin
5 update func
6 set sal = aumenta_sal(sal);
7 exception
8 when others then
9 null;
10 end;
11 /
Procedure created.
ops$marcio@ORA10G> show error
No errors.
E nossos heróis esperançosos com o aumento!
ops$marcio@ORA10G> select * from func;
EMPNO ENAME SAL
------------- ---------- -------------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
ops$marcio@ORA10G> exec roda_folha
PL/SQL procedure successfully completed.
Nenhum problema quando rodamos a folha. Os operadores felizes, não acionaram ninguém aquela noite!
Mas no outro dia! ;)
ops$marcio@ORA10G>
ops$marcio@ORA10G> select * from func;
EMPNO ENAME SAL
------------- ---------- -------------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
O que deveria ser:
ops$marcio@ORA10G>
ops$marcio@ORA10G> select func.*, sal * 1.10 novo_sal
2 from func;
EMPNO ENAME SAL NOVO_SAL
------------- ---------- ------------- -------------
7369 SMITH 800 880
7499 ALLEN 1600 1760
7521 WARD 1250 1375
7566 JONES 2975 3272,5
7654 MARTIN 1250 1375
7698 BLAKE 2850 3135
7782 CLARK 2450 2695
7788 SCOTT 3000 3300
7839 KING 5000 5500
7844 TURNER 1500 1650
7876 ADAMS 1100 1210
7900 JAMES 950 1045
7902 FORD 3000 3300
7934 MILLER 1300 1430
14 rows selected.
Este pequeno exemplo tentou ilustrar o que ocorre com a má prática de programação quando se utiliza o artifício da cláusula WHEN OTHERS.
A dúvida do colega é se há alguma maneira de bloquear o uso da cláusula WHEN OTHERS em código fonte de triggers. O recomendável é prevenir em todos os códigos!
Se é para ser drástico, vou matar o mal pela raiz. O método usado foi: criei uma trigger de evento de DDL (create e alter), verifiquei se o CREATE ou ALTER era para trigger e busquei no código fonte o WHEN OTHERS; encontrado, devolvo um erro de usuário com uma mensagem "Uso indesejável do WHEN OTHERS"!
Demonstrando:
ops$marcio@ORA10G> create or replace trigger prevent_when
2 after create or alter on schema
3 declare
4 sql_text ora_name_list_t;
5 stmt varchar2(2000);
6 n number;
7 begin
8 if ( ora_dict_obj_type = 'TRIGGER' ) then
9 n := ora_sql_txt(sql_text);
10 for i in 1 .. n
11 loop
12 stmt := stmt || sql_text(i);
13 end loop;
14 if ( instr(upper(stmt), 'WHEN OTHERS') > 0 ) then
15 raise_application_error( -20001, 'Uso indesejavel do WHEN OTHERS');
16 end if;
17 end if;
18 end;
19 /
Trigger created.
ops$marcio@ORA10G>
ops$marcio@ORA10G> create table t ( x int );
Table created.
ops$marcio@ORA10G> create or replace trigger t_bi_fer
2 before insert on t for each row
3 declare
4 n number;
5 begin
6 n := 10/0;
7 exception
8 when others then
9 null;
10 end;
11 /
create or replace trigger t_bi_fer
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Uso indesejavel do WHEN OTHERS
ORA-06512: at line 13
Obviamente, o exemplo acima deve ser aperfeiçoado, já que é possível separar WHEN OTHERS em duas linhas ou colocar mais espaços entre eles. A demonstração serve apenas para uma idéia de como implementar o bloqueio.
No texto todo, nota-se claramente o uso excessivo da cláusula WHEN OTHERS. Isso foi proposital, para frisar. Assim, quando a virem em algum código lembrem-se do que foi discutido aqui.
