Páginas

segunda-feira, 16 de novembro de 2015

[Oracle] - Função para Retornar partes de um texto(string)


Bom pessoal, vou compartilhar uma função que retorna valores por parte de um texto especifico que estou utilizando, facilitando a utilização de particionamento de texto utilizando um carácter como ponto de particionamento:

CREATE OR REPLACE FUNCTION STRIPART(iTEXT VARCHAR2,
                     iCARA CHAR,
                     iINIC INTEGER,
                     iFINA INTEGER,
                     iTUDO INTEGER DEFAULT 1) RETURN VARCHAR2 AS
      vTEXT VARCHAR2(500) := iTEXT;
      vINIC INTEGER := 0;
      vFINA INTEGER := 0;
   BEGIN
      IF iINIC = 0
      THEN
         -- SE FOR ZERO É INICIO DE STRING SEMPRE
         vINIC := 1;
      ELSE
         -- PEGA A POSIÇÃO DO CARACTER iCARA
         vINIC := INSTR(iTEXT, iCARA, 1, iINIC) + 1;
      END IF;
      IF INSTR(iTEXT, iCARA, 1, iFINA) = 0
      THEN
         -- SE NAO ENCONTRAR O CARACTER FINAL PEGA TODA A STRING
         IF iTUDO = 1
         THEN
            vFINA := LENGTH(iTEXT);
         ELSE
            vFINA := 0;
         END IF;
      ELSE
         vFINA := INSTR(iTEXT, iCARA, 1, iFINA) - vINIC;
      END IF;
      vTEXT := SUBSTR(vTEXT, vINIC, vFINA);
      RETURN vTEXT;
   END;



quarta-feira, 4 de novembro de 2015

[Oracle] - Função para Remover caracteres especiais em Textos

Bom pessoal, a função abaixo remover caracteres especiais em textos no Oracle, facilitando o tratamento de dados do tipo texto, auxiliando em consultas e criação de índices.

CREATE OR REPLACE FUNCTION NORMALIZAR(str_in VARCHAR2) RETURN VARCHAR2 IS
   pos           NUMBER(10);
   chars_special VARCHAR2(255);
   chars_normal  VARCHAR2(255);
   str           VARCHAR2(255) := UPPER(str_in);
BEGIN
   chars_special := 'ÁÀÃÂÉÊÍÓÔÕÚÜÇ.-';
   chars_normal  := 'AAAAEEIOOOUUC  ';
   str           := TRIM(upper(str));
   pos           := length(chars_normal);
   WHILE pos > 0
   LOOP
      str := REPLACE(str,
                     substr(chars_special, pos, 1),
                     substr(chars_normal, pos, 1));
      pos := pos - 1;
   END LOOP;
   str := TRIM(str);
   WHILE regexp_like(str, ' {2,}')
   LOOP
      str := REPLACE(str, '  ', ' ');
   END LOOP;
   pos := length(str);
   WHILE pos > 0
   LOOP
      IF regexp_like(substr(str, pos, 1), '[^A-Z0-9Ç@._ +-]+')
      THEN
         str := concat(substr(str, 1, pos - 1), substr(str, pos + 1));
      END IF;
      pos := pos - 1;
   END LOOP;
   RETURN str;
END;
/


[Oracle] - Trabalhando com Listas Dinâmicas


Bom pessoal, vou informar abaixo a implementação de criação e utilização de listas dinâmicas no Oracle, validas para versões 10g, 11g e 12c.

CREATE OR REPLACE TYPE t_id IS TABLE OF VARCHAR2(32000);
/

CREATE OR REPLACE
FUNCTION fnc_gera_lista(lista       VARCHAR2,
                           delimitador VARCHAR2) RETURN t_id IS
      v_id t_id;
   BEGIN
      SELECT regexp_substr(REPLACE(lista, delimitador, ','),
                           '[^,]+',
                           1,
                           LEVEL) AS lista
      BULK   COLLECT
      INTO   v_id
      FROM   dual
      CONNECT BY regexp_substr(REPLACE(lista, delimitador, ','),
                               '[^,]+',
                               1,
                               LEVEL) IS NOT NULL;
      RETURN v_id;
END;
/

----------------------------------------------
--- EXEMPLO DE UTILIZAÇÃO: ---

-- LISTA DE DADOS NUMÉRICOS --
SELECT TO_NUMBER(COLUMN_VALUE) AS LISTA
        FROM   TABLE(FNC_GERA_LISTA('22;19;30;35;40;60;71;92;', ';')); 
-- LISTA DE DADOS ALPHANUMÉRICOS --
SELECT TO_CHAR(COLUMN_VALUE) AS LISTA
        FROM   TABLE(FNC_GERA_LISTA('a;B;C;d;E;F;g;H;', ';'));

-- LISTA DE DADOS ALPHANUMÉRICOS(DATAS) --
SELECT TO_CHAR(TO_DATE(COLUMN_VALUE ,'DD/MM/YYYY'), 'DD/MM/YYYY') AS LISTA
FROM   TABLE(FNC_GERA_LISTA('15/01/2011;11/12/2010;10/10/1999;16/08/1998;01/10/2003;12/12/2012;10/10/2010;11/11/2011;',
                            ';'));