CLOB e ORA-06502 - Por que e como corrigir

Ontem eu estava depurando uma procedure Oracle que faz a importação de dados de um sistema para outro. Essa procedure de importação gera log de tudo: dados importados, dados rejeitados, motivo de rejeição e etc. O log é todo armazenado em uma variável CLOB, que ao final do processo é anexada ao registro que armazena a execução da importação - foi executado na data tal, e o resultado foi tal.

Ocorre que com o aumento da massa de dados a ser importada, começaram a aparecer erros ORA-06502 (PL/SQL: numeric or value error). Com a ajuda do PL/SQL Developer, consegui identificar que a exceção era gerada em uma linha que concatenava alguns valores na variável CLOB. Eu demorei algumas horas até me lembrar que uu já tinha visto esse erro devido à conversão implícita de string em número.

Nessas horas foram algumas pesquisas na internet, até que achei um blog que me deu algumas dicas e a memória clareou (vou ficar devendo o crédito pois perdi o endereço do blog original).

O fato é que na conversão implícita, o Oracle tenta converter tudo para NÚMERO. Isso é porque as operações com números (geralmente comparações, originadas de cláusulas WHERE) são muito mais rápidas que comparações de string. Só que apesar de mais rápido, é uma conversão menos segura, pois pode não funcionar em todos os casos. No nosso caso de CONCATENAÇÃO (e não de comparação), o Oracle tenta converter tudo para varchar, e nisso dá erro.

Vamos a comprovação: Um laço que a cada volta, concatena um número em um CLOB.

declare
    l_clob clob;
 
begin

    for i in 1..50000
    loop
        l_clob   := l_clob || 1;
        -- conversão implícita de número em varchar
        -- força a conversão do blob em varchar também.
    end loop;
    dbms_output.put_line (length(l_clob));

exception
    when others then
          dbms_output.put_line ('error: ' || sqlerrm);
          dbms_output.put_line (length(l_clob));
end;

Internamente o oracle converte esse CLOB em varchar, concatena com o 1 (também internamente já convertido em varchar), e armazena de volta na variável CLOB. Só que conforme a variável CLOB vai crescendo, chega um momento que fica maior que 32768 bytes (o limite de tamanho de varchar). Nessa hora, gera a exceção.

Agora veja o exemplo abaixo, quase igual ao anterior. A diferença é que agora a concatenação é de uma string em um CLOB. Nesse caso o Oracle não faz a conversão do CLOB em varchar, e portanto não viola o limite do varchar. Logo, não dá erro!

declare
    l_clob clob;
 
begin

    for i in 1..50000
    loop
        l_clob := l_clob || to_char(1);
        -- sem conversão implícita de número (ou datas),
        -- a operação é entre varchar e clob. Neste caso,
        -- não há conversão do clob em varchar.
    end loop;
    dbms_output.put_line (length(l_clob));

exception
    when others then
          dbms_output.put_line ('error: ' || sqlerrm);
          dbms_output.put_line (length(l_clob));
end;

É isso.
Espero que sirva para ajudar mais alguém por aí.

Abraços e até a próxima!

ASP.NET MVC3 com Assemblies Privados

Esta semana eu precisei colocar um site novo no ar, no espaço de hospedagem da ImaginarTI. Curiosamente foi o primeiro site em ASP.NET MVC3 que vai para a nossa hospedagem - os demais ficaram hospedados diretamente nos clientes. O nosso provedor de hospedagem afirma que suporta MVC3, então achei que seria 100% transparente. Não foi bem assim...

O fato é que após subir os arquivos, começaram a aparecer um monte de problemas. Modificamos o web.config para exibir os erros (só para debug do deloy mesmo) e vimos que eram assemblies faltando. Como assim faltando?

Entrei em contato com o suporte da hospedagem e eles disseram que suportam o MVC 3, mas que eu tenho que colocar as DLL's no BIN. Ora, isso não é exatamente "suportar" MVC3, né? Me senti enganado (menos um ponto para esses caras), mas resolvi não brigar dessas vez. Bola para frente.

Como resolver?
Bem, o projeto (no VS2010) tinha sido criado com o template de projeto do próprio MVC, então todas as referências já estavam setadas corretamente. Bastou eu alterar algumas referências, mudando o COPY LOCAL para true, e acrescentar outras referências (também com COPY LOCAL true) e tudo voltou a funcionar.

Não cheguei nem a procurar na internet, mas me lembro que o Scott Gu já tinha falado sobre isso. De qualquer forma, o que resolveu o meu caso foi:


Mudar o COPY LOCAL para True

• System.Web.Helpers
• System.Web.Mvc
• System.Web.WebPages

Novas referências

• Microsoft.Web.Infrastructure
• System.Web.WebPages.Razor
• System.Web.WebPages.Deployment
• System.Web.Razor

Tomara que ajude mais alguém por aí.

Até a próxima!

ASP x Asp.Net -- como explicar para leigos

Hoje um amigo aqui do trabalho, o Rodrigo Diniz (responsável pelo ExtJS Extender Controls) tentava me explicar como tentar fazer um advogado entender a diferença entre as tecnologias ASP (Asp 3 ou Asp "clássico") e Asp.Net. O advogado em questão era um outro funcionário que validava uma feature do sistema jurídico.

Conta o Diniz que argumentou o seguinte:

Escrever em Asp é como escrever em Latim, enquanto Asp.Net é como escrever em Portugês. Dá para dizer a mesma coisa, mas um é mais antigo, menos preciso e já não há tanta gente que consegue entender.

Exageros a parte, até concordei com a idéia.
E você, o que achou?

LiveMeeting no Ubuntu

Aconteceu ontem o MVC Summit 2011, desta vez totalmente web, transmitido através do LiveMeeting.

Mesmo tendo a plataforma da Microsoft como a minha principal (C# Asp.Net), meu desktop roda Linux Ubuntu e, claro, não há um cliente nativo do LiveMeeting para o Ubuntu. Restou então a versão webhosted do client, que é baseado em Java.

Claro que não ia ser simples assim, afinal a Canonical resolveu trocar o Java da Sun (agora Oracle), pelo Open Java, com o argumento que é a mesma coisa, só que OpenSource. Vá lá, ele até é bem bonzinho sim, mas o webclient do LiveMeeting não quis rodar com ele de jeito nenhum.

Desconfiei que fosse esse o problema, e resolvi trocar a OpenJRE pela da Sun mesmo. Uma rápida "Googlada", e encontrei como substituir um pelo outro. Se alguém tiver dificuldade de achar, comenta aí, que eu passo o caminho das pedras.

OpenJRE removido, Sun JRE instalado, vamos aos testes. A Microsoft tem disponível uma página de testes de reuniões LiveMeeting. Infelizmente essa reunião de testes não tem áudio configurado, mas pelo menos serve para testar o vídeo. Só que mesmo com o JRE da Sun, o troço ainda não funcionava.

Mais uma procurada, dessa vez bem demorada, e acabei encontrando um blog que divulgava uma dica de um brasileiro, que ensinava como acertar. O link que eu achei foi este aqui, o crédito original é do @CiroRodrigues e o resumo da história é esse:

Vá no menu System > Preferences > Sun Java 6 Plugin Control Panel.
Vá na aba "Advanced", abra o item "Security", abra "Mixed Code (sandboxed vs. trusted) security verification", e selecione a opção "Enable - hide warning and run with protections".

É isso aí, espero que ajude.

Abraços e até a próxima.
@leozacche

Teletransporte

Quem nunca desejou ter a sua própria máquina de tele-transporte, que atire a primeira pedra. Eu sempre quis ter uma, mesmo antes de assistir ao filme "A Mosca" (aquele com o Jeff Goldblum).

É, meu caro, parece que não estamos sozinhos. A Sony, através do PlayStation Home, realizou o nosso sonho. Virtualmente, pelo menos.

Agora é só torcer para não haver colisão de pacotes, congestionamento de tráfego, nem faltar luz no meio do caminho. Veja o trailer e me diga o que achou.

abs

LZ

Dicas para SQL Dinâmico - Apóstrofos

Olá pessoal.

A dica de hoje vai para quem precisa construir comandos SQL dinâmicos, seja dentro de stored procedures ou diretamente no código da sua linguagem de preferência. Vou usar Oracle para o exemplos, mas o conceito também se aplica ao SQL Server. Para deixar os posts menores, vou fazer cada dica um post diferente.

Apóstrofo (ou aspas simples)
Todo mundo sabe que, em uma instrução SQL, as strings são delimitadas por apóstrofo (que alguns chamam de aspas simples). Assim:

create table tab_a (col1 varchar2(100) not null, col2 varchar2(100) not null);
declare
    minha_string varchar2(1000);
begin
    minha_string := 'isso é uma string';
end;
/

Muito bem. Mas e se a string em questão for uma instrução SQL? Ela, por natureza é uma linha de texto e que pode ter uma string dentro. Imagine o comando:

insert into tab_A (col1, col2) values ('valor 1', 'valor 2');

Se esse comando for uma instrução SQL dinâmica, você teria algo como isso:

declare
    meu_sql varchar2(1000);
begin
    meu_sql := 'insert into tab_A (col1, col2) values (''valor 1'', ''valor 2'')';
end;
/

Repare que para reproduzir um apóstro dentro de uma string, eu tenho que duplicá-lo, porque ele é um caracter especial (delimitador de string). Se colocasse sem duplicar, o compilador ia acusar erro, achando que haveria strings em sequencia, sem concatenar, nem nada.

Agora vamos adicionar um pouco de complexidade ao exemplo. Vou transformar esse pequeno bloco anônimo numa stored procedure com dois parâmetros do tipo string, que serão usados para preencher col1 e col2.

create or replace procedure proc_exemplo (pCol1 in varchar2, pCol2 in varchar2) as
    vSQL varchar2(1000);
begin
    vSQL := 'insert into tab_A (col1, col2) values (''' || pCol1 || ''', ''' || pCol2 || ''')';
    execute immediate vSQL;
end;
/

Sabemos que usar variáveis de ligação é muito mais eficiente, mas resolvi adotar esta estratéria
"ruim" apenas como exemplo de manipulação de strings. Outro dia eu falo sobre binding vars.

Repare como os diversos apóstrofos colados começam a prejudicar a leitura do código da proc. E é apenas uma linha de código!!! Imagine um insert imenso com muitas colunas string, o comando quebrado em várias linhas... Começa a ficar realmente ruim.

Para deixar o código mais limpo (facilitar a leitura), eu vou substituir cada apóstrofo duplicado (aqueles de dentro da string, e somente esses!) por uma aspa comum (ou dupla, se preferir). Percebe que um apóstrofo duplicado ('') não é a mesma coisa que uma aspa ("). São caracteres diferentes!!!.

create or replace procedure proc_exemplo (pCol1 in varchar2, pCol2 in varchar2) as
    vSQL varchar2(1000);
begin
    vSQL := 'insert into tab_A (col1, col2) values ("' || pCol1 || '", "' || pCol2 || '")';
    execute immediate vSQL;
end;
/

Mais fácil de ler, não?
Mais fácil para identificar onde começa e onde termina cada string (cada "pedaço" do comando SQL).

Só que se você executar esta proc, vai dar erro. Isto porque as aspas não são aceitas (em PL/SQL nem T-SQL) como delimitadores de string.


A correção é bem simples: Pode usar aspas à vontade dentro da sua string e, no último momento antes de executar o comando, você converte todas as aspas para apóstrofos duplos.

create or replace procedure proc_exemplo (pCol1 in varchar2, pCol2 in varchar2) as
    vSQL varchar2(1000);
begin

    -- preparando o comando SQL
    vSQL := 'insert into tab_A (col1, col2) values ("' || pCol1 || '", "' || pCol2 || '")';

    -- fazendo os ultimos ajustes antes de executar
    vSQL := replace(vSQL, '"', '''');
    execute immediate vSQL;

end;
/

A linha do replace é a única que você terá que manter bagunçada (vários caracteres apóstrofo), mas como é uma linha só, que fica só no final do bloco de código, dá para conviver.

Há uma restrição que você não pode usar aspas para nenhum outro fim, dentro do seu comando SQL. Se você puder atender este requisito, está feito. E eu particularmente não considero uma coisa muito esperta usar aspas (") nos nomes dos objetos Oracle. Serve só para dar dor-de-cabeça, mas isso também é papo para outro dia.

É isso pessoal.
Espero que sirva de ajuda para alguém por aí.


Abraços,

LZ

Parâmetro opcional em stored procedure (mão na roda)

Hoje vou dar uma dica para facilitar o trabalho de debug de uma stored procedure. Vai em Oracle, porque é o que eu tenho na mão para mostrar (sem falar da minha preferência, claro).

Frequentemente, quando escrevo uma nova stored procedure (ou altero uma existente), eu acrecento um parâmetro adicional, opcional, que indica se a proc deve rodar em "modo debug". Coloco no final da lista de parâmetros (geralmente é o último) e como opcional, porque assim eu não preciso alterar nenhum programa que use esta proc, e ainda tenho a flexibilidade de usar o parâmetro.

"Mas o que faz este parâmetro?" - perguntarão alguns. Eu já disse! Indica se a proc deve rodar em modo debug.

"Modo debug?"

É. É um apelido, uma forma sofisticada de dizer que a proc vai fazer algum trabalho extra. No caso, o trabalho de coletar informações sobre a sua própria execução.
Essa coleta pode ser, exibir o valor de uma variável, indicar se entrou em um IF (ou se entrou no ELSE), exibir a quantide de registros lidos, etc...

Vamos ao exemplo prático:

(lembrando, é ORACLE e vc pode colar os comandos direto no SQL*Plus)

Imagine uma proc spProc1, que serve apenas para inserir descrições em uma tabela TabA, se não existirem, e retornar o Id gerado. Assim:

create table TabA (
Id number not null,
Descricao varchar2(50) not null,
DataCriacao date not null
);

alter table TabA add constraint PK_TabA primary key (Id);
alter table TabA add constraint UK_TabA_Descricao unique (Descricao);

create or replace procedure spProc1(pDescricao in varchar2, pId out number) as
vQtd number;
begin

select count(1) into vQtd from TabA where Descricao = pDescricao;

if (vQtd = 0) then

select nvl(max(Id),0)+1 into pId from TabA;

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

end if;

end;
/
show errors

Vamos inserir uns dados para ver que está tudo funcionando direito...

variable novo_id number

exec spProc1('Leonardo', :novo_id);
print novo_id;

exec spProc1('ImaginarTI', :novo_id);
print novo_id;

exec spProc1('Zacche', :novo_id);
print novo_id;

select * from TabA;

Agora vamos supor que você descobriu que, se ao chamar a proc, fornecer uma descrição que já exista na tabela, o ID retornado é nulo.

exec spProc1('Mais um', :novo_id);
print novo_id;

exec spProc1('Mais um', :novo_id);
print novo_id;

Depurar esta procedure é fácil porque é curta. Uma procedure longa, seria mais complicado. Para facilitar, vou colocar algumas linhas de DEBUG na procedure.

create or replace procedure spProc1(pDescricao in varchar2, pId out number) as
vQtd number;
begin

select count(1) into vQtd from TabA where Descricao = pDescricao;
dbms_output.put_line(vQtd || ' registro(s) encontrado(s).');


if (vQtd = 0) then

select nvl(max(Id),0)+1 into pId from TabA;
dbms_output.put_line('O Id gerado foi ' || pID);

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

end if;

end;
/
show errors

Vamos testar novamente, com um novo valor, e depois repetí-lo.

set serveroutput on 100000
exec spProc1('Teste dois', :novo_id);
print novo_id;

exec spProc1('Teste dois', :novo_id);
print novo_id;

Descobrimos que não está entrando no IF. Vamos escrever um ELSE.

create or replace procedure spProc1(pDescricao in varchar2, pId out number) as
vQtd number;
begin

select count(1) into vQtd from TabA where Descricao = pDescricao;
dbms_output.put_line(vQtd || ' registro(s) encontrado(s).');


if (vQtd = 0) then

select nvl(max(Id),0)+1 into pId from TabA;
dbms_output.put_line('O Id gerado foi ' || pID);

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

else

select Id into pId from TabA where Descricao = pDescricao;
dbms_output.put_line('O Id encontrado foi ' || pID);

end if;

end;
/
show errors


exec spProc1('Teste tres', :novo_id);
print novo_id;

exec spProc1('Teste tres', :novo_id);
print novo_id;

Agora a proc já está funcionando da forma que desejamos! Um último refactoring para otimizar o código e ...

create or replace procedure spProc1(pDescricao in varchar2, pId out number) as
begin

begin
select Id into pId from TabA where Descricao = pDescricao;
dbms_output.put_line('O Id encontrado foi ' || nvl(to_char(pID),'[nulo]'));
exception
when NO_DATA_FOUND then pId := null;
when OTHERS then raise;
end;


if (pId is null) then

select nvl(max(Id),0)+1 into pId from TabA;
dbms_output.put_line('O Id gerado foi ' || pID);

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

end if;

end;
/
show errors

exec spProc1('Teste quatro', :novo_id);
print novo_id;

exec spProc1('Teste quatro', :novo_id);
print novo_id;

Perfeito! A proc está exatamente como desejamos!

Agora podemos remover aquelas linhas de debug...

Mas seria interessante deixar essas linhas, para o caso de precisar depurar novamente...

Só que não é legal deixar essas linhas serem executadas SEMPRE, somente quando precisarmos
depurar.

Aí entra em cena o parâmetro opcional que comentei no início deste post. Como o parâmetro é OPCIONAL (todo parâmetro que tem um valor default pode ser otimido), você não precisa indicar o valor.

Assim, os programas que usam a proc não precisam ser alterados.

Eu defini o valor default como FALSE porque normalmente a proc não deve exibir informações de debug.

Nos casos (especiais) que eu quiser mostrar, informo explicitamente o valor true.

create or replace procedure spProc1(pDescricao in varchar2, pId out number, p_showDebug in boolean default false) as
begin

begin
select Id into pId from TabA where Descricao = pDescricao;
if (p_showDebug) then
dbms_output.put_line('O Id encontrado foi ' || nvl(to_char(pID),'[nulo]'));
end if;
exception
when NO_DATA_FOUND then pId := null;
when OTHERS then raise;
end;


if (pId is null) then

select nvl(max(Id),0)+1 into pId from TabA;
if (p_showDebug) then
dbms_output.put_line('O Id gerado foi ' || pID);
end if;

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

end if;

end;
/
show errors


exec spProc1('Teste cinco', :novo_id);
print novo_id;

exec spProc1('Teste cinco', :novo_id);
print novo_id;

exec spProc1('Teste cinco', :novo_id, true);
print novo_id;

exec spProc1('Teste seis', :novo_id, true);
print novo_id;

Abraços e até a próxima!

LZ

www.imaginarti.com