Validando Digito Verificador Inscrição Estadual Bahia (BA)

Fala PessoAll,

Bom, como toda e boa pessoa normal, de vez em quando bate a saudade das épocas de desenvolvimento, de quebrar cabeça para fazer alguns códigos “ninja” pra resolver alguns problemas do nosso Dia a Dia (Oracle, rsrs).

Otem me deparei com a demanda de um amigo, precisando fazer uma validação do dígito da Inscrição estadual da Bahia, em PL/SQL. Vamos lá!

Primeiro passo: Pesquisar como se faz a validação do dígito verificador da inscrição estadual da BAHIA!! BAHIA!! Ninguém melhor que a Sefaz/BA pra me dizer isso. Então, fui no site da SEFAZ/BA e achei como fazer a validação, depois foi só cair no PL/SQL de cabeça e colocar pra funcionar. A página que diz como deve ser feita a validação é: http://www.sefaz.ba.gov.br/contribuinte/informacoes_fiscais/doc_fiscal/calculodv.htm.

Segue abaixo a function utilizada para fazer esta validação. Essa function retorna ‘S’ caso esteja OK e ‘N’ caso não seja validado o dígito verificador.


create or replace function valida_dig_inscest_BA(pInscEstadual varchar2) return varchar2 is
v_IE varchar2(10);

idig1 number;
idig2 number;

iRes1 number;
iRes2 number;

cBase1 number;
cBase2 number;
begin
v_IE := rtrim(replace(replace(replace(pInscEstadual, '.', ''), '-', ''), '/', ''));

if length(v_IE) <> 8 then
return('N');
end if;

iRes1 := 0;
iRes2 := 0;

cBase1 := substr(v_IE, 1, 6);
iDig1 := substr(v_IE, 7, 1);
iDig2 := substr(v_IE, 8, 1);

--Se o primeiro dígito for um dos testados, modulo 10, senao, modulo 11
if(substr(cBase1, 1, 1) in (0, 1, 2, 3, 4, 5, 8)) then
--Faz um loop de 7 até 2...
for i in reverse 2 .. 7 loop
--Verifica se o numero encontrado é válido.
if instr('0123456789', SubStr(cBase1, (7-i)+1, 1)) > 0 Then
--Sendo válido, já soma com a multiplicação pelo indice.
ires2 := ires2 + (SubStr(cBase1, (7-i)+1, 1) * i);
else
return('N');
end if;
end loop;

--Recupera o resto do calculo realizado por 10
ires2 := mod(ires2, 10);

--Se for 0, o digito é zero.
if(ires2 = 0) then
ires2 := 0;
--Se não for zero, subtrai o valor de 10
else
ires2 := 10 - ires2;
end if;

--O numero para o calculo do primeiro digito, é os 6 primeiros + o digito 2
cBase2 := cBase1||ires2;

--Faz um loop de 8 até 2...
for i in reverse 2 .. 8 loop
--Verifica se o numero encontrado é válido.
if instr('0123456789', SubStr(cBase2, (8-i)+1, 1)) > 0 Then
--Sendo válido, já soma com a multiplicação pelo indice.
ires1 := ires1 + (SubStr(cBase2, (8-i)+1, 1) * i);
else
return('N');
end if;
end loop;

--Recupera o resto do calculo realizado por 10
ires1 := mod(ires1, 10);

--Se for 0, o digito é zero.
if(ires1 = 0) then
ires1 := 0;
--Se não for zero, subtrai o valor de 10
else
ires1 := 10 - ires1;
end if;

--Se resultado 1 e 2 iguais a digitos 1 e 2, é válido
if(ires1 = idig1 and ires2 = idig2) then
return('S');
else
return('N');
end if;
--Se começa com 6, 7 ou 9
else
--Faz um loop de 7 até 2...
for i in reverse 2 .. 7 loop
--Verifica se o numero encontrado é válido.
if instr('0123456789', SubStr(cBase1, (7-i)+1, 1)) > 0 Then
--Sendo válido, já soma com a multiplicação pelo indice.
ires2 := ires2 + (SubStr(cBase1, (7-i)+1, 1) * i);
else
return('N');
end if;
end loop;

--Recupera o resto do calculo realizado por 11
ires2 := mod(ires2, 11);

--Se for 0, o digito é zero.
if(ires2 = 0) then
ires2 := 0;
--Se não for zero, subtrai o valor de 11
else
ires2 := 11 - ires2;
end if;

--O numero para o calculo do primeiro digito, é os 6 primeiros + o digito 2
cBase2 := cBase1||ires2;

--Faz um loop de 8 até 2...
for i in reverse 2 .. 8 loop
--Verifica se o numero encontrado é válido.
if instr('0123456789', SubStr(cBase2, (8-i)+1, 1)) > 0 Then
--Sendo válido, já soma com a multiplicação pelo indice.
ires1 := ires1 + (SubStr(cBase2, (8-i)+1, 1) * i);
else
return('N');
end if;
end loop;

--Recupera o resto do calculo realizado por 10
ires1 := mod(ires1, 11);

--Se for 0, o digito é zero.
if(ires1 = 0) then
ires1 := 0;
--Se não for zero, subtrai o valor de 10
else
ires1 := 11 - ires1;
end if;

if(ires1 = idig1 and ires2 = idig2) then
return('S');
else
return('N');
end if;
end if;
end;

É isso aí gente, espero que seja útil.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

JOB no ORACLE – Definindo a periodicidade. (INTERVAL)

Fala PessoALL,

Bom, atendendo à dica do amigo Alessandro Varela (http://blogdovarela.com.br) hoje falarei sobre: Como definir corretamente a periodicidade da execução dos seus Job’s! Complica não? É nada!! Vamos lá!

Espero que todos tenham lido o post que fiz falando sobre Job’s, caso não tenha lido, ainda dá tempo, basta ler o post Jobs no Oracle.

Bom, quando fazemos a criação de um Job, é porque temos a necessidade que uma determinada tarefa seja executada de tempos em tempos, e para isso geralmente precisamos definir um intervalo para que o Job seja executado novamente. Por exemplo: Quero que meu Job rode a cada hora, quero que meu Job rode todo dia de 23:00, quero que meu job rode a cada 5 minutos, e assim por diante. Só que no momento de fazer esta parametrização é quando começa a confusão, pois, as pessoas esquecem de que o Job vai rodar sozinho e ele é quem tem que definir a próxma execução! Para simplificar isso, vamos lá!

Todo mundo lembra no post anterior que eu falei que para criar um Job usamos a package DBMS_JOB e a procedure DBMS_JOB.SUBMIT(...), certo? Pois é, nesta procedure, um dos parâmetros que é passado é um tal de INTERVAL, ou seja, o intervalo entre cada execução do seu Job. É aqui que mora o X (xis) da questão! É neste parametro que vamos informar de quanto em quando tempo nosso Job vai rodar.

O que tem que ser considerado aqui, é que o Job será executado automaticamente (essa é a razão do seu existir) e é justamente por isso que rola a confusão, quando este parâmetro for usado já não estaremos mais na data “agora”, estaremos na data de execução do Job, sei lá, 23:00! E aí o banco vai usar este parâmetro INTERVAL para setar o campo NEXT_DATE, que é a próxima data em que o Job será executado, ou seja você tem que levar em consideração que o banco sempre vai usar sysdate para calcular este valor.

Como é de rotina, vamos para os exemplos que tudo vai ficar mais claro.
Exemplos mais comuns:

Job para rodar de hora em hora:
Interval -> ‘sysdate + 1/24’
Ou seja, se este Job rodar dia 25/10/2009 às 14:00, quando for somada 1 hora, teremos 15:00 como NEXT_DATE.

Job para rodar a cada 5 minutos.
Interval -> ‘sysdate + 5/1440’
Ou seja, se este Job rodar dia 25/10/2009 às 14:00, quando for somado 5 minutos (1/1440), teremos 14:05 como NEXT_DATE.

Job para rodar uma vez por mês:
Interval -> ‘add_months(sysdate, 1)’
Ou seja, se executar dia 25/10/2009, aplicando este valor acima, teriamos 25/11/2009 como NEXT_DATE.

Esse foi um dos casos mais estranhos, pedido através do blog pra mim…
Job para rodar em um dia específico a cada ano e numa determinada hora:
‘add_months(to_date(to_char(sysdate, ”DD/MM/YYYY”)||” 05:00”, ”DD/MM/YYYY HH24:MI”), 12)’
Ou seja, se o job rodar dia 25/10/2009 as 15:00, e aplicarmos esta fórmula aí, teremos 25/10/2010 05:00 como NEXT_DATE.

Uma dica legal para você fazer esta fórmula que será o NEXT_DATE do seu Job é dar select em sysdate usando a dual. Que eu creio ser o que o banco faz na hora de rodar o Job.

Por exemplo:
Se executarmos o comando:
select sysdate, sysdate + 1/24 from dual
Vamos obter como retorno:

sysdate sysdate + 1/24
20/10/2009 15:55:35 20/10/2009 16:55:35

Isso aí nos dá a hora atual e a hora atual somado 1 hora. Assim você monta seu NEXT_DATE facilmente!

É isso pessoal, espero que tenha ficado claro e que daqui pra frente seja mais fácil montar seus Jobs para as mais diversas necesidades.

Qualquer coisa, estamos por aqui!

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

Privilegios (Grant) no Oracle

Fala PessoAll,

Demorei, mas estamos de volta.

Dessa vez, vou dar uma dica bem simples sobre privilégios (grants) no banco Oracle.

Quem muitas vezes não foi realizar um select em uma tabela qualquer no banco de dados e obteve um ora-00942: table or view does not exist? Pois é, este erro nem sempre é o que diz que é! Que coisa não? Muitas vezes a tabela existe só que pertence a um outro schema do banco de dados, e aí o usuário que voce está conectado não consegue enxergar este objeto. Isso pode acontecer com tabelas, views, functions, procedures, etc o que acontece é que o schema proprietário do objeto tem que conceder a devida permissão para o usuário que você está conectado.

Vamos a um cenário, para que possamos entender esse rolo todo.

Suponhamos que em nossa base a gente tenha o usuário DONOSISTEMA que criou uma tabela chamada TABELA_BASE e você se conecta no banco com o usuário USERSISTEMA, então como faríamos para que você conseguisse acessar esta tabela?

Assim:

Primeiro vamos conectar com DONOSISTEMA e criar a tabela:

SQL> conn donosistema/oracle;
Conectado.
SQL> create table tabela_base(campo1 number, campo2 varchar2(100));

Tabela criada.

Beleza, agora vamos conectar com o usuário USERSISTEMA e tentar dar um select count(*) nesta tabela:

SQL> conn usersistema/oracle;
Conectado.
SQL> select count(*) from donosistema.tabela_base;
select * from donosistema.tabela_base
*
ERRO na linha 1:
ORA-00942: a tabela ou view nÒo existe

Parece que não deu muito certo não? Pois é, não tem privilégio.
Agora vamos conectar novamente como DONOSISTEMA e conceder o privilégio:

SQL> conn donosistema/oracle;
Conectado.
SQL> grant select on tabela_base to usersistema;

ConcessÒo bem-sucedida.

Perfeito… agora vamos conectar novamente com o usuário USERSISTEMA e tentar novamente fazer o select count(*) nessa tabela:

SQL> conn usersistema/oracle;
Conectado.

SQL> select count(*) from donosistema.tabela_base;

COUNT(*)
----------
0

Agora parece que ficou beleza não? Nosso usuário conseguiu realizar acessar a tabela sem problemas.

Só que… eu sou um cara chato! Não quero ter que colocar nome do dono do objeto na frente (DONOSISTEMA.)… na verdade não quero nem que meu usuário que vai acessar a aplicação saiba quem é o dono dos objetos, pra ele não cair em tentação! Rsrs. Para isso, precisamos criar um objeto chamado SYNONYM que nada mais é que um sinônimo (óbvio não) para o objeto original. Por exemplo, no nosso caso, temos que criar um synonym com o nome TABELA_BASE que aponte para o objeto DONOSISTEMA.TABELA_BASE certo?

Então vamos lá.

Pra não dizerem que estou mentindo (rsrs) inicialmente vou conectar como USERSISTEMA para que possamos ver que se tirar o nome do dono do objeto da frente da tabela o select não vai funcionar, vamos ver:


SQL> conn usersistema/oracle;
Conectado.

SQL> select count(*) from tabela_base;
select count(*) from tabela_base
*
ERRO na linha 1:
ORA-00942: a tabela ou view nÒo existe

Hum… realmente não funciona.

Agora vamos conectar como DONOSISTEMA e criar o synonym para esta tabela ok? vamos lá:


SQL> conn donosistema/oracle;
Conectado.
SQL> create public synonym tabela_base for donosistema.tabela_base;

Sin¶nimo criado.

Beleza. Agora vamos conectar novamente como USERSISTEMA e ver se ele consegue acessar sem o nome do dono na frente? Vamos:


SQL> conn usersistema/oracle;
Conectado.
SQL> select count(*) from tabela_base;

COUNT(*)
----------
0

Perfeito não? Que beleza!! É assim que fazemos para que tenhamos um usuário dono dos objetos do nosso banco de dados e vários outros usuários que apenas utilizam estes objetos. Desta forma organizamos nossos objetos e garantimos que fica transparente aos usuários seu acesso.

E como eu sei se tenho permissão ou não para um determinado objeto?
Simples, consultando o dicionário dados. Tentem rodar o select abaixo:


select grantor, grantee, table_name, privilege
from all_tab_privs
where grantee = 'USUARIO_RECEBEDOR_PRIVILEGIO'
and grantor = 'USUARIO_DONO_OBJETO'
and table_name = 'NOME_DO_OBJETO'

Aí vocês podem usar ou não todos os filtros que coloquei.
Aí podemos filtrar por:
GRANTOR: que é o usuário que concedeu a permissão. No nosso caso DONOSISTEMA.

GRANTEE: que é o usuário que recebeu a permissão. No nosso caso USERSISTEMA.

TABLE_NAME: que é o nome da tabela envolvida no nosso processo. No nosso caso TABLE_BASE.

É isso gente.
Espero que tenham gostado e que daqui pra frente fique tudo mais claro sobre permissões, sinônimos e etc.

Atc.
Gerson Júnior
(gerson.vasconcelos@gmail.com)

Usando CASE no Oracle SQL.

Fala PessoAll,

Bom, conforme prometido no Post anterior, estou aqui para dar uma apimentada na utilização da função DECODE usando SQL Oracle.

A funcionalidade que iremos falar hoje é a CASE. Esta funcionalidade é muito boa e dá uma dinâmica muito interessante a comandos SQL, em muitas vezes nos poupando de ter que fazer uma Stored Prodecure ou uma Function para fazer alguma coisa que um simples SQL pode resolver.

Para não fugir dos costumes… vamos ao exemplo.

Suponha que você tenha 3 classificações diferentes para seus vendedores, que funcionam da seguinte forma: Se o cara vendeu entre R$0.00 e R$1,000.00 ele é classificado como “Ruim”, se foi entre R$1,001.00 e R$4,000.00 ele é classificado como “Bom”, se foi entre R$4,001.00 e R$8,000.00 ele é classificado como “Ótimo” e se for acima de R$8,001.00 ele é considerado como “Fenomenal”, e você precisa exibir isso no relatório para sua gerência.

Como fazer isso?

Aí você começa a pensar…
DECODE? Não dá… tenho faixa de valores, e não valores específicos!

Cria uma View? Não… uma view é um mero Select, não vai resolver!

Ah…. claro! Cria uma função!!! Na função eu passo o valor que o cara vendeu e ela me retorna qual a classificação do cara! Perfeito!
É…. funcionar vai funcionar, mas você tem que criar um objeto no banco, tem que criar um Script, tem que se preocupar com Grant’s e todos os demais aspectos para que criemos um novo objeto!

Não seria mais simples que no próprio Select a gente resolvesse esse problema? SIM!!! Claro!! Porque não. Para isso, vamos usar a funcionalidade CASE. Como faríamos isso, para este caso?

Agora sim, cenário montado, vamos ao exemplo (de verdade):


select v.nome_vendedor,
ve.mes,
ve.vlr_meta,
ve.vlr_venda,
case
when ve.vlr_venda between 0 and 1000 then
'Ruim'
when ve.vlr_venda between 1001 and 4000 then
'Bom'
when ve.vlr_venda between 4001 and 8000 then
'Ótimo'
when ve.vlr_venda >= 8001 then
'Fenomenal'
end classificacao_vendedor
from vendedores v,
vendas ve
where ve.cod_vendedor = v.cod_vendedor;
and ve.mes = '07/2009';

Que beleza não? Resolvido nosso provlema! Temos agora em nosso SQL a coluna classificacao_vendedor que nos dá a informação que precisamos, sem problemas com criação de função nem nada do tipo!

Bom não? Simples de usar, rápido e tudo quanto é de vantagem!

É isso aí, creio que a partir desse exemplo dá pra “voar” bem alto! Agora é só adaptar para sua necessidade e tá tudo certo!!

Espero que gostem e comentem!

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

Função DECODE no Oracle SQL

Fala PessoAll,

Hoje vamos falar de Construção de SQL.

As vezes precisamos fazer umas coisas mais avançadas num comando SQL e aí precisamos utilizar alguns recursos mais interessantes que o Oracle nos proporciona.

Quem nunca pensou: “Ai como seria bom se tivesse um IF no select”. Tem sim!

O primeiro recurso que podemos utilizar é o DECODE, essa função é bem interessante e quebra um bocado de galhos, vamos ver um exemplo pra “clarear” as idéias.

Suponha que você tem uma tabela que contém o sexo das pessoas, mas na tabela só armazena F ou M e você quer que seja mostrado “Masculino” e “Feminino” no retorno do seu select, como fazemos isso no select? Com DECODE!

Assim:


select nome,
dt_nascimento,
decode(sexo,
'M', 'Masculino',
'F', 'Feminino',
'Indefinido') sexo
from pessoas;

Como funciona isso… o DECODE testa o valor que você passa no primeiro parâmetro e vai comparando com o que você especifica e retorna o que você deseja. Complexo não? NÃO!! Vamos explicar o nosso exemplo:

No exemplo acima é passado o campo sexo, em seguida passamos o primeiro valor de teste e o que vai retornar caso encontre este valor, que neste caso é: Se encontrar 'M' retorne 'Masculino', sempre aos pares. Ou seja, valor encontrado e logo depois o valor que vai retornar. Como podemos ver, depois vem mais um par… 'F' e 'Feminino' o que nos diz que se encontrar um 'F', traga 'Feminino' na coluna.

Tá, mas e esse último valor que tem 'Indefinido'? Ele não tem par!! Vai dar erro? Não, esse é uma espécie de ELSE do DECODE… se ele não encontrar nenhuma das alternativas passadas para retornar um valor especificado, ele retorna esse último valor. No nosso exemplo, podemos ver que caso não encontre 'M' nem 'F' no campo, ele irá retornar o valor 'Indefinido' para a coluna… agora ficou claro!

O DECODE não tem um limite mínimo nem máximo de “pares de teste e retorno”, você pode ir especificando os valores e os retornos de acordo com sua necessidade.

Bom, esse é um recurso do Oracle que pode ser bem útil para pessoas que fazem SQL, que elaboram relatórios, que vivem fazendo query entre outros casos.

Como podemos verificar, para usar o DECODE nós temos que saber os valores que estão na coluna para que possamos informar qual o retorno caso determinado valor seja encontrado, mas… e se eu quiser utilizar uma faixa de valores por exemplo? Tipo… se coluna nota estiver entre 0 e 3 retorno 'Péssimo', entre 4 e 5 retorne 'Ruim', entre 6 e 8 retorne 'Bom' e acima de 8 retorne 'Ótimo'? Não dá pra fazer isso com DECODE né? Mas, tenha calma… no próximo post falaremos sobre uma outra função do Oracle que é uma espécie de DECODE avançado, que permite esse tipo de teste e muito mais.

Espero que gostem e que seja útil. Até a próxima.

Não exitem em comentar e/ou mandar e-mails.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

Job no Oracle

Fala PessoALL,

Atendendo a pedidos, hoje falaremos sobre Job’s no Oracle.
Do que se trata? Job’s são tarefas que são “scheduladas” (agendadas) no Oracle para rodar numa determinada hora que você estipula.

Como assim? Bom, vamos aos exemplos:

Suponha que você tem um sistema na sua empresa de controle de acesso, ele é quem gera os crachás para os funcionários entrarem na empresa. No sistema de RH, é onde são cadastrados os meus funcionários… e agora? Tenho que fazer os dois sistemas conversarem. Geralmente essa “conversa” é feita através de arquivos, chamados EDI (eletronic Data Interchange) e seu sistema de RH teria que gerar esses arquivos diariamente durante a noite com todas as pessoas Admitidas e Demitidas para que o sistema de crachá leia este arquivo e habilite ou desabilite o acesso do funcionário. Como fazer?

Criamos um JOB para gerar este arquivo “na calada da noite”.

Num JOB você diz as rotinas que vão ser executadas, que podem ser códigos direto com blocos de PL/SQL, ou podem ser chamadas Procedures ou Packages que executam as rotinas que você precisa.

Suponhamos mais uma vez, que neste nosso exemplo, precisamos de um JOB que chame a procedure: Pr_Gera_Arq_Mov_Funcionarios, e este JOB precisa gerar este arquivo às 22:00, porque às 23:00 o sistema de controle de acesso vai ler o arquivo gerado. Pronto… cenário montado, vamos criar o JOB.

Como criamos um JOB? Temos no banco de dados Oracle uma Package que faz todo controle e manipulação dos JOB’s no banco, o nome dela é DBMS_JOB, e é ela que vamos utilizar para criar nosso JOB. Como fazemos isso? Com blocos de PL/SQL, simples e rápido, assim:


declare
--Declare variável que recebe número do JOB.
job_num binary_integer;
begin
--Cria o JOB no banco e retorna o número dele job_num
dbms_job.submit(job_num,
'begin Pr_Gera_Arq_Mov_Funcionarios; end;',
to_date('10/06/2009, 22:00', 'dd/mm/yyyy hh24:mi'),
'sysdate + 1');
end;

Vamos explicar….

A rotina dbms_job.submit(...) exige 2 parâmetros como obrigatórios, são eles:
job, que é um parâmetro OUT, ou seja, a rotina vai retornar um valor para a variável que estiver neste local, no nosso caso é o número do JOB.
what que é uma string que diz para o banco que rotinas o JOB vai executar, note que este parâmetro é uma string e tem que conter um bloco PL/SQL, ou seja, tem que ter begin...end; e tudo mais.

Os outros dois parâmetros passados neste caso, para a rotina dbms_job.submit(...) não são obrigatórios, eles são responsáveis por dizer que data o JOB vai rodar a próxima vez (next_date) e com que intervalo vai rodar novamente (interval), não são obrigatórios porque na package dbms_job existe diversas outras procedures que manipulam os JOB’s e entre elas está dbms_job.next_date(...) e dbms_job.interval(...) (entre outras), nestas rotinas é necessário informar o número do JOB e o valor que você deseja atribuir para estes atributos.

Observe que o tempo todo falamos de “número do JOB”, é isso mesmo. No Oracle, os JOB’s são identificados por números, não tem como atribuirmos nomes aos JOB’s (nem tudo é perfeito), por isso, na rotina que cria o JOB ele exige uma variável para receber o número que foi atribuído ao JOB.

Depois do seu JOB criado, você pode fazer alterações no bloco que o JOB executa, na próxima vez que ele vai rodar, no intervalo e etc, tudo usando as procedures da package dbms_job.

Para listar os JOB’s criados no seu banco de dados, bem como ver os seus Status, ultimas execuções, Status de execução, Próxima vez que vai rodar e etc, podemos dar um select na view user_jobs. E se ainda, quisermos saber que JOB’s estão sendo executados neste exato momento, podemos dar um select na tabela dba_jobs_running, mas para esta segunda você precisa ter privilégios de DBA.

Espero ter sido claro e tomara que este post seja útil.
Qualquer dúvida não deixe de enviar um comentário ou até mesmo e-mail que descobrimos juntos as respostas.

Abraços à todos.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

Como configurar um servidor Oracle de forma segura

Fala PessoAll,

Gostaria de compartilhar com vocês uma apresentação que desenvolvi para um trabalho na minha graduação. A matéria é Projeto de Segurança de Sistemas e o objetivo era escolher um servidor e mostrar como configurá-lo de forma segura.

Escolhi o servidor Oracle Database 10g (não podia ser diferente) para tentar mostrar como configurá-lo de forma mais segura possível.

A apresentação tem um “ar” de bom humor para que não fosse mais uma daquelas apresentações que no final estivesse acordado apenas o apresentador e o professor, louco pra dar um zero como nota.

Na apresentação são abordados assuntos como Senhas de usuários do banco, Privilégios de SYSDBA, Acesso ao dicionário de dados, entre outros.

Segue os links:

Apresentação: ConfigurarOracleSeguro.pdf

Scripts usados como exemplo: Scripts.rar

HowTo para configuração: HowToConfigurarOracleSeguro.pdf

Espero que gostem.

Opinem e mandem comentários.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

SQL Dinâmico gerando arquivo

Fala PessoAll,

Bom, no último tópico postado Oracle Execute Immediate SQL Dinâmico eu falei um pouco sobre como montar e executar query’s dinâmicamente. Ficou bem interessante, mas… o exemplo que postei era para um único registro, e não é sempre que temos este tipo de caso. Algumas vezes temos que executar consultas que retornam mais de um registro, precisamos inserir isso numa tabela, gravar em um log, gerar um arquivo, e outra série de possibilidades.

Se vocês observarem os comentários do tópico anterior, vão ver que tem um comentário de um Anônino perguntando se não tenho um exemplo de SQL Dinâmico e que gere um arquivo. Resposta: Não tenho. Mas isso não quer dizer que não podemos fazer um.

Vamos ao nosso exemplo.


declare
type tpNome is REF CURSOR;
cNome tpNome;

–Variáveis para armazenar query e retorno.
v_sql varchar2(1000);
v_nome varchar2(100);

–Variáveis para geração do arquivo
v_arq utl_file.file_type;
begin
–Define a query
v_sql := ‘Select nome from pessoas where rownum < 50’;

–Abre o arquivo
v_arq := utl_file.fopen(‘DIRECTORY’, ‘FILE_NAME’, ‘W’);

open cNome for v_sql;
loop
fetch cNome into v_nome;
exit when cNome%notfound;

utl_file.put_line(v_arq, v_nome);
end loop;

–Fecha o arquivo
utl_file.fclose;
end;

No exemplo acima, estamos lendo os 50 primeiros registros de uma tabela chamada pessoas e gerando um arquivo com o nome destas pessoas. Bastante simples.

O exemplo acima abre uma série de portas, este é o famoso “fio da meada” e a partir deste exemplo uma série de coisas podem ser feitas.

Querido amigo Anônimo, espero que isso te ajude, na próxima vez deixa teu email que fica mais fácil de responder pra você.

Grande abraço a todos.

Atc.
Gerson Júnior
(gerson.vasconcelos@gmail.com)

Oracle Execute Immediate SQL Dinâmico

Fala pessoAll,

Vamos nós novamente.
A pedido do amigo Marcos Castro dessa vez estaremos falando sobre SQL Dinâmicos, um recurso muito interessante no PL/SQL que em diversas vezes não é utilizado por nossos excelentíssimos desenvolvedores, fazendo com que nossos códigos sejam bem maiores, bem mais complexos e bem mais chatos de dar manutenção. Mas… o lado B dessa história é: Se você complicar muito no uso de SQL Dinâmico, não tenha dúvida de que seu código vai ficar muito complexo e todo mundo que for dar manutenção vai te xingar um bocado! Rsrs.

Vamos lá, ao exemplo prático e simples, que é o que interessa.

Suponha que você tem uma função para ser feita, que deve retornar o nome de uma determinada pessoa… essa pesquisa pode ser pelo seu RG, CPF ou Habilitação. Como podemos fazer isso?

Exemplo 1 (sem SQL Dinâmico):

create or replace function pesquisa_pessoa(pcodigo number, pfiltro varchar2) return varchar2 is
v_nome_pessoa pessoas.nome%type;
begin
if(pfiltro = ‘RG’) then
select nome
into v_nome_pessoa
from pessoas
where rg = pcodigo;
elsif(pfiltro = ‘CPF’) then
select nome
into v_nome_pessoa
from pessoas
where cpf = pcodigo;
elsif(pfiltro = ‘CNH’) then
select nome
into v_nome_pessoa
from pessoas
where cnh = pcodigo;
end if;

return v_nome_pessoa;
end;

Notemos que o SQL é o mesmo sempre (select nome into v_nome_pessoa from pessoas where … = pcodigo ), o que muda é apenas o nome do campo que será utilizado para filtrar a pessoa, portanto, podemos utilizar o recurso de SQL Dinâmico para otimizar este código, e como ele ficaria? Assim:

create or replace function pesquisa_pessoa(pcodigo number, pfiltro varchar2) return varchar2 is
v_nome_pessoa pessoas.nome%type;
v_where varchar2(50);
begin
if(pfiltro = ‘RG’) then
v_where := ‘rg’;
elsif(pfiltro = ‘CPF’) then
v_where := ‘cpf’;
elsif(pfiltro = ‘CNH’) then
v_where := ‘cnh’;
end if;

dbms_output.put_line(‘select nome into v_nome_pessoa from pessoas where ‘||v_where||’ = ‘||pcodigo||’;’);

execute immediate ‘select nome from pessoas where ‘||v_where||’ = ‘||pcodigo
into v_nome_pessoa;

return v_nome_pessoa;
end;

Bem mais simples, e não precisamos ficar repetindo o mesmo comando várias vezes.

Bom, isso é só um exemplo bem simples, mas… o Execute Immediate pode ser usado de diversas outras maneiras.

Achei um site bem interessante, que mostra as diversas usabilidades deste recurso com exemplos que podem ser utilizados para um melhor entendimento. O site é o DBA Support.

É isso aí, espero que este recurso seja útil e bem utilizado nas suas aplicações.

Atc.

Gerson Júnior

(gerson.vasconcelos@gmail.com)

Union x Union ALL — Select’s no Oracle

Fala PessoAll,

Vamos com mais uma dica interessante sobre Oracle.
Vamos analisar desta vez, os comandos UNION e UNION ALL. Estes comandos servem para que possamos unir mais de uma query (select) em um mesmo comando, trazendo os resultados de ambas as query’s. Que? Entendeu nada!? Vamos lá:

Existe no Oracle a possibilidade de unirmos mais de um comando select de forma que possamos obter o resultados de todos eles unidos (claro!) em um único resultado, como se fosse um comando só, como assim?

Suponha que você possui uma tabela de clientes e uma tabela de funcionários, e você quer trazer todos os códigos e nomes de clientes e funcionários para exibir em um relatório? Como fazer isso? Assim:


Select codcli,
nom_cli,
'C' tipo
from clientes
union
Select cod_func,
nom_func,
'F'
from funcionarios;

Se vocês executarem este comando, teremos todos os clientes e todos os funcionários listados em um mesmo resultado, mas… temos algumas coisas a considerar.

1 – Vocês devem ter notado que os nomes das colunas das tabelas são diferentes, quando eu rodar que nome minha coluna vai ter? O nome das colunas da primeira query da lista de comandos, a primeira query vai mandar em tudo e as colunas do resultado vão receber o nome das colunas da primeira query.

2 – Quando usamos o comando UNION, temos obrigatóriamente que ter a mesma quantidade de colunas e o mesmo tipo de dados das colunas em todas as query’s. Como assim? Suponha que na tabela clientes a coluna cod_cli tenha tipo de dados number e na tabela funcionarios a coluna cod_func tenha tipo de dados varchar2, não pode! Ao executar este comando o banco vai retornar um erro dizendo que as colunas das query’s não estão corretamente relacionadas. Como em ambos os comandos elas são a primeira coluna da query e estarão relacionadas, ambas tem que ter o mesmo tipo.

3 – Não podemos utilizar ORDER BY nas query’s envolvidas, apenas na última query, que ordernará os registros de todas as query’s. Neste caso acima, por exemplo, se na ultima query colocássemos um ORDER BY 1 por exemplo, ele ordenaria o resultado das duas query’s juntas, como se fosse um comando só. Se tiver códigos iguais nas duas query’s, serão trazidos juntos, pois os resultados serão considerados juntos na hora de ordenar.

Outra coisa que comentei no início foi o UNION ALL, qual a diferença dele pro UNION? O UNION utilizado sozinho faz um distinct no resultado da query, ou seja, se tiverem resultados repitidos eles não serão exibidos, será exibida apenas uma ocorrência. Suponha que por coincidência tenhamos um cliente com código 1 e nome CICERO e tenhamos um funcionario com código 1 e nome CICERO também, caso não tivéssemos essa coluna tipo para diferenciar os registros de clientes e funcionários teríamos a ocorrência 1 CICERO apenas uma vez em nosso resultado. Já usando o UNION ALL, independente da quantidade de registros repetidos que sejam retornados, todos serão exibidos.

Como usa? Assim:


Select codcli,
nom_cli,
'C' tipo
from clientes
union all
Select cod_func,
nom_func,
'F'
from funcionarios;

Qual a diferença? Só o all depois do union, depois disso o resto o Oracle resolve. Espero esse tal de Oracle!!

Momento DBA: Para o banco, por não precisar se preocupar em verificar se existem resultados iguais para que exiba apenas um deles, o UNION ALL tem bem menos custo para ser executado, portanto, se você tiver query’s em que você tem certeza que não serão retornados valores repetidos (como essa do exemplo, em que temos uma coluna tipo para diferenciar) use sempre UNION ALL, seu DBA e seu Banco de Dados agradecem!

É isso pessoal, espero que tenham entendido e que gostem.
Qualquer dúvida, sugestão, reclamação etc não deixem de entrar em contato.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com