CAT | Oracle
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:
Toda empresa desenvolve produtos para obter lucro, mas algumas parecem se render a simples trocados e com isso depreciam ou a imagem da própria empresa ou a imagem do produto, um exemplo disso é a Oracle.
Desde que a Oracle comprou a Sun e com isso absorveu o Java e outros produtos que no instalador da JVM ela sorrateiramente adicionou a opção de instalar a barra de ferramentas da Ask no browser da vítima do usuário.
É claro que a Ask pagou um considerável valor para que a Oracle distribuísse sua barra de ferramentas, mas para a Oracle além deste valor não ser a salvação da lavoura só acaba por depreciar o Java.
Depreciar porque tanto no ambiente corporativo quanto doméstico a barra de ferramentas da Ask é nociva, ao ser instalada ele deprecia a performance ao uso do browser, abre conexões obscuras do browser com seus servidores e mesmo que seja removida futuramente ainda deixa vestígios espalhados pelo sistema, o único “benefício” é a busca na web via servidores da Ask que digamos, ainda está muito longe de ser semelhante a inteligência de busca do buscador do Google.
A forma como a opção de instalar simultaneamente a barra da Ask atrapalha o processo de instalação da JVM e usuários leigos podem deixar a opção marcada com medo que ao desmarcar a JVM não seja instalada, estratégia digna de softwares adware que encontramos por aí, que só desacredita a confiabilidade do Java ou da JVM.
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.
