TAG | PL/SQL
Trabalhando o charset para gerar arquivos de um CLOB no Oracle
Sem comentários · Post por Petter Rafael em Dicas, Oracle
Vamos falar um pouco sobre charset no banco de dados Oracle, uma boa dica é primeiro descobrir quais charsets estão instalados e validados para se trabalhar no Oracle, para isso executamos a seguinte query:
SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'CHARACTERSET' |
Feito isso é também uma boa identificar qual é o ID de um determinado charset (mais a frente verá como aplicar isso), para descobrir então o número ID de um charset no Oracle executamos a seguinte query:
SELECT NLS_CHARSET_ID('WE8MSWIN1252') FROM DUAL; |
Note que neste exemplo localizamos o ID de um charset para Windows, conhecido como WIN-1252.
Uma forma de como utilizar e de quando vai trabalhar com isso é quando surgir a necessidade de gerar um arquivo com base em um CLOB, neste caso um dos parâmetros é o ID do charset que o arquivo será gerado:
DBMS_XSLPROCESSOR.CLOB2FILE(um_clob, path_do_arquivo, nome_do_arquivo, NLS_CHARSET_ID('AL16UTF16')); |
Reparem que no último parâmetro eu utilizo a função NLS_CHARSET_ID para descobrir e passar o ID do charset informado ou ainda poderá passar um NUMBER que represente o ID do charset.
Uma dica é deixar que o arquivo seja gerado com o mesmo charset do banco de dados (para evitar que caracteres especiais não sejam impressos corretamente no arquivo caso o charset do banco de dados seja conflitante com o charset escolhido para o arquivo) apenas suprimindo o último parâmetro:
DBMS_XSLPROCESSOR.CLOB2FILE(um_clob, path_do_arquivo, nome_do_arquivo); |
Leia mais:
Precisando agendar a execução de um objeto no Oracle e não sabe como?
No Oracle temos um recurso chamado de JOB, com ele é possÃvel agendar execuções de tempos em tempos de qualquer objeto “executável†em PL/SQL. Veja um exemplo:
DECLARE jobno NUMBER; |
BEGIN
DBMS_JOB.submit(jobno,’MINHa_PROC;’, sysdate, sysdate + 5/1440);
end;
Vamos então aos parâmetros:
- O primeiro parâmetro é apenas uma variável NUMBER de retorno, ela irá conter o número no novo JOB;
- O segundo parâmetro é o nome do objeto, neste exemplo uma procedure PL/SQL;
- O terceiro parâmetro é a data inicial do processo;
- O quarto parâmetro é a ordem de repetição.
O maior segredo está na repetição, pois é nela que iremos definir o intervalo de repetição entre as execuções, neste exemplo a expressão quer dizer que ela irá ser executa a cada 5 minutos.
Leia mais:
Recuperando o próximo registro no Oracle PL/SQL
Sem comentários · Post por Petter Rafael em Dicas, Oracle
Já falamos sobre o comando LAG do PL/SQL, agora e se precisássemos utilizar os valores do próximo registro de uma instrução SELECT?
Semelhante ao LAG temos a função analÃtica LEAD no Oracle PL/SQL que tem a função de retornar o registro anterior sem precisarmos reprocessar tudo novamente. Veja a sintaxe:
lead ( expression [, offset [, DEFAULT] ] ) OVER ( [ query_partition_clause ] order_by_clause ) |
Veja um exemplo:
SELECT product_id, order_date, lead (order_date,1) OVER (ORDER BY order_date) AS next_order_date FROM orders; PRODUCT_ID Â Â ORDER_DATE Â Â NEXT_ORDER_DATE 1000 Â Â Â Â 25/09/2007 Â Â Â Â Â Â Â 26/09/2007 2000 Â Â Â Â 26/09/2007 Â Â Â Â Â Â Â 27/09/2007 1000 Â Â Â Â 27/09/2007 Â Â Â Â Â Â Â 28/09/2007 2000 Â Â Â Â 28/09/2007 Â Â Â Â Â Â Â 29/09/2007 2000 Â Â Â Â 29/09/2007 Â Â Â Â Â Â Â 30/09/2007 1000 Â Â Â Â 30/09/2007 Â Â Â Â Â Â Â <NULL> |
O uso do LEAD é bem simples, assim como o LAG, agora é fácil trabalhar tanto o registro anterior como o próximo registro de um SELECT, bem útil quando vamos gerar arquivos texto utilizando quebras pela iteração dos registros.
Leia mais:
Quando tentamos acessar algum objeto do Oracle por alguma aplicação externa (PHP, Java, C#, etc) e recebemos o seguinte erro:
ora-06550: line string, COLUMN string: string |
ou
ora-00942: TABLE OR VIEW does NOT exist |
Esse erro pode ocorrer por diversos problemas, os mais comuns é a falta de um GRANT ou uma ROLE, porém os erros que menos ocorrem são os que são mais difÃceis de serem resolvidos.
Este erro também pode ser disparado caso você esteja acessando uma instancia errada.
Explicando melhor, em um ambiente de grande porte diversas instancias do Oracle pode estar espalhadas em diversos servidores, assim caso o seu editor de PL/SQL esteja apontando para uma instancia e a sua aplicação para outra pode ser que ao realizar testes no editor PL/SQL tudo transcorra bem mas a aplicação emita o erro acima.
Para verificar em qual instancia você está executando o PL/SQL pode utilizar a query abaixo:
SELECT instance_name, host_name FROM v$instance; |
A partir daà é só corrigir e pronto.
Leia mais:
O LAG é uma função analÃtica do PL/SQL Oracle e tem a finalidade de retornar o registro anterior da tabela. Veja a sintaxe:
lag ( expression [, offset [, DEFAULT] ] ) OVER ( [ query_partition_clause ] order_by_clause ) |
Veja um exemplo:
SELECT ename,hiredate,sal, LAG(sal, 1, 0) OVER (ORDER BY hiredate) AS PrevSal FROM emp WHERE job = 'CLERK'; Ename Hired            SAL     PREVSAL -------- --------------- -------- ------------ SMITH 17-DEC-80  800      0 JAMES 03-DEC-81  950    800 MILLER 23-JAN-82 1300    950 ADAMS 12-JAN-83 1100   1300 |
O exemplo é bem simples e fácil de ser entendido, nele exibimos o salário (coluna SAL) e também exibimos o salário anterior (coluna PREVSAL).
Leia mais:
Para quem já trabalhou ou trabalha com rotinas em PL/SQL que movimentam grande volume de dados e que tendem a serem lentas sabe que é importante uma gerar uma métrica para medir os tempos de trechos do seu código a fim de se identificar pontos de lentidão.
Para quem pensou que um trace resolveria isso vale lembrar que o trace via de regra coleta dados de iteração no banco de dados Oracle, como por exemplo, query´s, iterações de memória e outros detalhes não são rastreados pelo trace, daà a necessidade da própria rotina PL/SQL possuir ferramentas próprias para medir o tempo.
Abaixo temos uma função que retorna a quantidade de segundos que decorreram de um tempo inicial até um tempo final.
FUNCTION difere_segundos(par_inicio IN TIMESTAMP, -- Timestamp definido antes de executar o trecho que se quer marcar o tempo par_fim IN TIMESTAMP)    -- Timestamp definido após executar o trecho que se quer marcar o tempo RETURN NUMBER IS var_segundos  NUMBER(15,6) := 0; BEGIN var_segundos := EXTRACT(DAY FROM (par_fim - par_inicio)) * 24 * 60 * 60 + EXTRACT(HOUR FROM (par_fim - par_inicio)) * 60 * 60 + EXTRACT(MINUTE FROM (par_fim - par_inicio)) * 60 + EXTRACT(SECOND FROM (par_fim - par_inicio)); RETURN(var_segundos); END; |
A função acima recebe o tempo inicial e o tempo final e retorna a diferença em segundo de ambas, para utilizar basta coletar o tempo (em timestamp) do ponto inicial e no ponto final executar a função e você terá a diferença em ambos e poderá então demarcar os pontos de maior demora e que devem receber maior atenção na sua rotina PL/SQL.
Leia mais:
Seguindo a pedidos segue mais um pouco de Regex aplicado ao Oracle PL/SQL.
Uma das grandes dúvidas é uma forma simples e eficiente para impor uma máscara para variáveis contendo um CNPJ ou CPF, constantemente veja o uso de DECODE´s empilhados ou complicadas funções que contam caracter a caracter para então ir realizando a formatação escolhido, estes casos ou causam perda de performance ou ainda se tornam pequenos “monstros†difÃceis de manter.
Veja o caso do CPF:
SELECT regexp_replace(LPAD(‘12543684261’, 11, '0'), '([0-9]{3})([0-9]{3})([0-9]{3})([0-9]{2})','\1.\2.\3-\4') FROM dual |
E no caso de um CNPJ:
SELECT regexp_replace(LPAD('1238524478912', 15, '0'),'([0-9]{3})([0-9]{3})([0-9]{3})([0-9]{4})([0-9]{2})','\1.\2.\3/\4-\5') FROM dual |
Nem são precisos demais comentários (se quiser pode verificar o que falamos anteriormente sobre Regex com PL/SQL), fica fácil identificar a divisão “em blocos†que fizemos com o Regex e em seguida sua métrica para formatar os dados.
Leia mais:
Continuando a falar de Regex vamos agora conhecer a função de alguns metacaracteres (ou alias):
| Metacaracter | Função | Exemplo |
| \ | Escape | \’ representa ‘ |
| ^ | Posição inicial da linha | ^C todas as strings que começam com C |
| $ | Posição final da linha | $o todas as strings que terminam com o |
| [] | Lista | [rot] qualquer string que contenha qualquer um dos caracteres da lista |
| {n} | Número de caracteres | {r} número de ocorrências |
| | | Alternativa | (‘eu’ | ‘você’) busca as duas alternativas |
| . | Qualquer caracter | (‘voc.’) irá retornar você ou voce |
Estas são metacaracteres básicos comuns a implementações de Regex na maioria das linguagens e também funcionais em PL/SQL, caso você queira se aprofundar poderá procurar literatura especializada sobre Regex.
Para quem já é acostumado a realizar buscas com o comando LIKE verá algumas semelhanças, porém o LIKE tem a tendência a não trazer todas as informações, por isso utilizamos constantemente o %, já Regex tem a tendência de trazer tudo, logo nós é que precisamos nos preocupar em restringir a nossa busca.
No próximo post vamos mostrar alguns exemplos práticos de como utilizar Regex no PL/SQL.
Leia mais:
Regex costuma ser um grande desafio para desenvolvedores iniciantes, primeiro por não compreenderem claramente como funciona e o seu objetivo e segundo por não saberem corretamente quando aplicar, vamos aproveitar este espaço para esclarecer o que é Regex e em seguida como aplica-la utilizando Oracle PL/SQL.
Regex nada mais é do que uma expressão (conjunto de caracteres especÃficos) utilizada para realizar buscas especÃficas alfanuméricas, seu poder é tão vasto que a maioria dos mecanismos de buscas atuais implementa Regex em sua engine visto a sua versatilidade, eficiência e escalabilidade.
Regex (abreviação de Regular Expressions ou traduzindo o termo para o português fica ER de Expressão Regulares) tem origem nos estudos de neurolinguÃstica da década de 40 e nos estudos de matemática na década de 50 e tem como representante maior Stephen Cole Kleene e tem ampla aplicação na ciência da computação e matemática.
Está incorporada em diversas linguagens e plataformas de programação, como Java, C e C# (só para citar alguns) e em diversos SGBDs sendo incorporada a normatização ANSI SQL99. Algumas de suas aplicações na informática são:
- Localizar um pedaço de texto onde temos somente uma “ideia†de como será (sem precisão na busca);
- Localizar texto pelo inÃcio ou termino já conhecido;
- Efetuar buscas complexas (com base em padrões) em texto;
- Validar strings;
- Filtrar texto.
Especificamente falando em Oracle, Regex está implementando na versão 10G e superiores, os principais comandos embutidos no PL/SQL para utilização de Regex são:
- REGEXP_LIKE(): similar ao comando LIKE, porém implementado sob a ótica das Regex, presta-se a consulta dos termos estabelecidos;
- REGEXP_REPLACE(): altera a string localizada pelo termo informado, substituindo por outro valor (string);
- REGEXP_SUBSTR(): efetua a busca por parte da string encontrada com base no termo proposto;
- REGEXP_COUNT(): está disponÃvel somente a partir da versão 11G. Efetua a contagem de quantas ocorrências para o termo buscado foram encontradas.
Leia mais:
Já comentei sobre o modo como o PL/SQL lida com os parâmetros enviados, ele simplesmente movimenta todo o conteúdo ao invés de apenas acessar a instancia daquele objeto (parâmetro) em memória, em consequência temos uma gradativa perda de performance quando passamos parâmetros maiores como um CLOB ou até mesmo uma PL Table.
Uma das soluções que apresentei foi definir a variável de forma global, assim tanto o objeto chamador como o objeto chamado teriam acesso a variável sem a necessidade que o conteúdo da mesmo transitasse como parâmetro, mas e se por questões técnicas não pudéssemos trabalhar desta forma?
TerÃamos então que utilizar a cláusula NOCOPY na definição dos parâmetros, isso iria instruir ao PL/SQL para não movimentar todo o conteúdo do parâmetro, o mesmo seria somente “pontuadoâ€, veja um exemplo:
Movimentando todo o conteúdo:
PROCEDURE pc_teste(varNome IN VARCHAR2) IS ... |
Apenas apontoando para o parâmetro:
PROCEDURE pc_teste(varNome IN OUT NOCOPY VARCHAR2) IS ... |
Porém é preciso tomar cuidado com alguns detalhes:
- É obrigatório o uso de parâmetros no formato IN OUT, somente IN ou somente OUT irá reportar erros de compilação;
- Parâmetros com NOCOPY não podem ser debugados o que pode dificultar no momento de detectar possÃveis bugs na sua aplicação.
