Arrumando CNPJ no banco de dados

CREATE OR replace FUNCTION Arruma_cnpj (cnpj IN VARCHAR2)
RETURN VARCHAR2

IS

  tamahocnpj  NUMBER;
  diferenca   NUMBER;
  novocnpj    VARCHAR2(100);
  mascarazero VARCHAR2(20);

BEGIN

    novocnpj := Replace(cnpj, '.', '');
    novocnpj := Replace(novocnpj, '/', '');
    novocnpj := Replace(novocnpj, '-', '');
    novocnpj := Replace(novocnpj, ' ', '');
    novocnpj := Translate(novocnpj, 'âàãáäÂÀÃÁÄêèẽéëÊÈẼÉËîìĩíïÎÌĨÍÏôòõóöÔÒÕÓÖûùũúüÛÙŨÚÜñỹṽçÑỸṼÇ', 'AAAAAAAAAAEEEEEEEEEEIIIIIIIIIIOOOOOOOOOOUUUUUUUUUUNYVCNYVC');
    novocnpj := Regexp_replace(novocnpj, '[a-zA-Z\ ]', '');
    tamahocnpj := Length(novocnpj);


    IF tamahocnpj IS NULL THEN
      tamahocnpj := 0;
    END IF;
    diferenca := 14 - tamahocnpj;


    IF diferenca > 0 THEN
      mascarazero := '00000000000000000000';
      novocnpj := Substr(mascarazero, 0, diferenca)|| novocnpj;
    END IF;

    novocnpj := Substr(novocnpj, 0, 2) || '.'
             || Substr(novocnpj, 3, 3) || '.'
             || Substr(novocnpj, 6, 3) || '/'
             || Substr(novocnpj, 9, 4) || '-'
             || Substr(novocnpj, 13, 2);

    RETURN novocnpj;
END arruma_cnpj;

Split no Oracle

SELECT SUBSTR(t.column_one, 1, INSTR(t.column_one, ' ')-1) AS col_one,
       SUBSTR(t.column_one, INSTR(t.column_one, ' ')+<<num char do separador>>) AS col_two
  FROM YOUR_TABLE t

Fonte: http://stackoverflow.com/questions/5199849/split-varchar-into-separate-columns-in-oracle

HQL com clausula ON nos JOIN

SELECT {msg.*}, {cmd.*} 
FROM Schema.Messages AS msg 
  LEFT OUTER JOIN schema.send_commands AS cmd 
    ON cmd.message_key = msg.unique_key 
    AND ( lower(cmd.status) IN (lower('failed') ) ) 
WHERE msg.sequence_received < 10";

SELECT m, c 
FROM Message m LEFT JOIN m.commands c WITH (lower(c.status) = 'failed')
WHERE m.sequenceReceived < 10

Fonte: http://stackoverflow.com/questions/6345052/hibernate-hql-joins-on-many-clauses

Querys recursivas no banco de dados oracle

Querys Recursivas no Oracle

Segue um exemplo prático de como fazer querys recursivas no Oracle, usando genealogia.

artefato/Programa..: querys_recursivas_no_oracle

-- Empresa.................:

-- Data Inicio ............: 07/04/2011

-- Data Atual..............: 22/07/2011

-- Versao..................: 0.01

-- Compilador/Interpretador: Oracle

-- Sistemas Operacionais...: Linux/Windows/Outros SOs

-- SGBD....................: Oracle 9i/10g/11g

-- Kernel..................: Nao informado!

-- Finalidade..............: uso de querys recursivas no oracle com com start with ... connect by ...

-- ........................:

-- OBS.....................:

--

/* testando no oracle com start with ... connect by */

--DROP TABLE genealogia;

CREATE TABLE genealogia

(

    id_genealogia integer     PRIMARY KEY

  , nome varchar2(25)         NOT NULL

  , id_genealogia_pai integer NULL --FOREIGN KEY fk_genealogia REFERENCES genealogia(id_genealogia)

);

--TRUNCATE TABLE genealogia;

SELECT * FROM genealogia;

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (1,'ABRAÃO',NULL);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (2,'ISAC',1);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (3,'ESAÚ',2);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (4,'JACÓ',2);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (5,'RÚBEN',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (6,'SIMEÃO',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (7,'LEVI',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (8,'JUDÁ',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (9,'ISSACAR',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (10,'ZEBULON',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (11,'JOSÉ',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (12,'BENJAMIM',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (13,'DÃ',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (14,'NAFTALI',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (15,'GADE',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (16,'ASER',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (17,'DINÁ',4);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (18,'PEREZ',8);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (19,'ZERA',8);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (20,'ESRON',18);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (21,'ARÃO ',20);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (22,'AMINADABE',21);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (23,'NASSON',22);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (24,'SALMON',23);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (25,'BOAZ',24);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (26,'OBEDE',25);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (27,'JESSÉ',26);

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (28,'DAVI',27);

SELECT * FROM genealogia;

--query 1, AUTO RELACIONAMENTO

            SELECT g1.nome

                 , g1.id_genealogia

                 , g2.id_genealogia_pai

              FROM genealogia g1

         LEFT JOIN genealogia g2

                ON g1.id_genealogia = g2.id_genealogia_pai

                 ;  

--query 2, CONNECT BY PRIOR

            SELECT nome

                 , id_genealogia

                 , id_genealogia_pai

              FROM genealogia

  CONNECT BY PRIOR id_genealogia = id_genealogia_pai

                 ;  

--query 3, LEVEL             

            SELECT nome

                 , id_genealogia

                 , id_genealogia_pai

                 , LEVEL

              FROM genealogia

  CONNECT BY PRIOR id_genealogia = id_genealogia_pai

                 ;  

--query 4,  START WITH

            SELECT nome

                 , id_genealogia

                 , id_genealogia_pai

                 , LEVEL

              FROM genealogia

        START WITH id_genealogia = 4  --JACÓ

  CONNECT BY PRIOR id_genealogia = id_genealogia_pai

          ORDER BY LEVEL ASC

                 ;

                  

--query 5, COM ARVORE

            SELECT RPAD(LPAD(' ', 5*(LEVEL-1))||nome,30) AS arvore 

                 , nome

                 , id_genealogia

                 , id_genealogia_pai

                 , LEVEL

              FROM genealogia

        START WITH id_genealogia = 1

  CONNECT BY PRIOR id_genealogia = id_genealogia_pai

          ORDER BY LEVEL ASC

                 ;

--query 6, SYS_CONNECT_BY_PATH

            SELECT LPAD(' ', 5*(LEVEL-1)) || nome AS representacao_arvore1

                 , SYS_CONNECT_BY_PATH(nome, '/') AS represencao_arvore2 

                 , nome

                 , id_genealogia

                 , id_genealogia_pai

                 , LEVEL

              FROM genealogia

        START WITH id_genealogia = 1

  CONNECT BY PRIOR id_genealogia = id_genealogia_pai

          ORDER BY LEVEL ASC

                 ;

--query 7, ORDER SIBLINGS BY

            SELECT LPAD(' ', 5*(LEVEL-1)) || nome AS representacao_arvore1

                 , SYS_CONNECT_BY_PATH(nome, '/') AS represencao_arvore2 

                 , nome

                 , id_genealogia

                 , id_genealogia_pai

                 , LEVEL

              FROM genealogia

        START WITH id_genealogia = 1

  CONNECT BY PRIOR id_genealogia = id_genealogia_pai

 ORDER SIBLINGS BY nome ASC

                 ;

                  

--query 8, CONNECT_BY_ROOT

CREATE OR REPLACE  VIEW  vw_genealogia  AS

            SELECT LPAD('>', 5*(LEVEL-1)) || nome AS representacao_arvore1

                 , SYS_CONNECT_BY_PATH(nome, '\') AS represencao_arvore2 

                 , CONNECT_BY_ROOT nome AS raiz

                 , nome

                 , id_genealogia

                 , id_genealogia_pai

                 , LEVEL AS nivel

              FROM genealogia

        START WITH id_genealogia = 1

--CONNECT BY NOCYCLE PRIOR  id_genealogia = id_genealogia_pai

  CONNECT BY PRIOR id_genealogia = id_genealogia_pai

 --ORDER SIBLINGS BY level  ASC

ORDER  BY level  ASC;

SELECT * FROM vw_genealogia;