Oracle split function

This function splits a string by the given regex in Oracle 11g

The Type:

CREATE OR REPLACE TYPE str_array AS TABLE OF varchar2(1024 char);

The Code:

CREATE OR REPLACE  FUNCTION SPLIT
  (
    i_str      IN VARCHAR2,
    i_regex    IN VARCHAR2,
    i_modifier IN VARCHAR2 DEFAULT NULL
  ) RETURN str_array IS
 
    l_str_array str_array;
    l_count     NUMBER;
 
  BEGIN
 
    l_count := regexp_count(i_str,
                            i_regex,
                            1,
                            i_modifier);
 
    SELECT CAST(MULTISET (SELECT CASE
                          WHEN l_count = 0 THEN
                           i_str
                          WHEN LEVEL = l_count + 1 THEN
                           regexp_substr(i_str,
                                         '.*(' || i_regex || ')(.*)',
                                         1,
                                         1,
                                         i_modifier,
                                         2)
                       
                          ELSE
                           regexp_substr(i_str,
                                         '(.*?)(' || i_regex || ')',
                                         1,
                                         LEVEL,
                                         i_modifier,
                                         1)
                        END AS SPLIT
                   FROM dual
                 CONNECT BY LEVEL <= l_count + 1) AS str_array)
      INTO l_str_array
      FROM dual;
 
    RETURN l_str_array;
 
  END;

The Test:

SELECT COLUMN_VALUE, 1 FROM TABLE(SPLIT('1 = 1  AND  2 = 2 AND 3 = 3 AND 4 = 4',  '[[:blank:]]+AND[[:blank:]]+'  ))
UNION ALL
SELECT COLUMN_VALUE, 2 FROM TABLE(SPLIT('x = x',  '[[:blank:]]+AND[[:blank:]]+'  ))
UNION ALL
SELECT COLUMN_VALUE, 3 FROM TABLE(SPLIT('a,b,c,d,e,f,g,h',  ','  ))
UNION ALL
SELECT COLUMN_VALUE, 4 FROM TABLE(SPLIT('there is a dog and another dog!',  'dog'  ))
; 

 The Result:

1 = 1	1
2 = 2	1
3 = 3	1
4 = 4	1
x = x	2
a	3
b	3
c	3
d	3
e	3
f	3
g	3
h	3
there is a	4
and another	4
!	4

Links:

http://www.oracle.com/global/de/community/tipps/regexp/index.html

http://www.oracle-developer.net/display.php?id=508

http://glosoli.blogspot.com/2006/07/oracle-plsql-function-to-split-strings.html

http://sadique-sadiqulislam.blogspot.com/2010/02/split-function-for-oracle-10g.html

Oracle

HTTP Requests:

http://www.ordix.de/ORDIXNews/2_2006/Datenbanken/pl_sql_t2.html

DBMS_ALERT:

http://sql-plsql-de.blogspot.com/2008/04/datenbank-sessions-synchronisieren.html

Oracle Obfuscated Sequences

Obfuscating Sequences

CREATE OR REPLACE PACKAGE obfuscate_seq IS

  FUNCTION obfuscate_seq(i_seq IN NUMBER) RETURN NUMBER;
  FUNCTION deobfuscate_seq(i_nextval IN NUMBER) RETURN NUMBER;

END obfuscate_seq;
/
CREATE OR REPLACE PACKAGE BODY obfuscate_seq IS

  g_crypto_mod NUMBER := dbms_crypto.des3_cbc_pkcs5;

  g_seq_key RAW(128) := hextoraw('082748387234987899239487972395030981208309823098');

  FUNCTION obfuscate_seq(i_seq IN NUMBER) RETURN NUMBER IS

    l_seed_raw    RAW(7); -- max 7 byte
    l_nextval     NUMBER(19); -- can hold 64bit signed integer
    l_nextval_raw RAW(8); -- max 8 byte
    l_salt        NUMBER(19);

  BEGIN
    -- max      = 2^56 = 2^(7*8) = 72057594037927936 = 0xFFFFFFFFFFFFFF
    -- max seq  = 2^32 = 2^(4*8) = 4294967296        =       0xFFFFFFFF
    -- max salt = 2^24 = 2^(3*8) = 16777216          = 0xFFFFFF

    l_salt := trunc(dbms_random.VALUE(1,
                                      16777216 + 1));

    l_seed_raw := to_char(l_salt,
                          'FM00000X') || to_char(i_seq,
                                                 'FM0000000X');

    dbms_output.put_line('   seed:   0x' || l_seed_raw || ' len: ' || utl_raw.length(l_seed_raw) || ' decimal: ' ||
                         to_number(l_seed_raw,
                                   'XXXXXXXXXXXXXX'));

    l_nextval_raw := dbms_crypto.encrypt(l_seed_raw,
                                         g_crypto_mod,
                                         g_seq_key);

    IF (to_number(substr(l_nextval_raw,
                         1,
                         1),
                  'X') > 7)
    THEN
      l_nextval := to_number(l_nextval_raw,
                             'XXXXXXXXXXXXXXXX') - 18446744073709551616; --  number - 2^64 (2 complement)
    ELSE
      l_nextval := to_number(l_nextval_raw,
                             'XXXXXXXXXXXXXXXX');
    END IF;

    dbms_output.put_line('nextval: 0x' || l_nextval_raw || ' len: ' || utl_raw.length(l_nextval_raw) || ' decimal: ' ||
                         l_nextval);

    RETURN l_nextval;

  END;

  FUNCTION deobfuscate_seq(i_nextval IN NUMBER) RETURN NUMBER IS

    l_nextval_raw RAW(8); -- max 8 byte
    l_seed_raw    RAW(7); -- max 7 byte
    l_seq         NUMBER(10); -- max 4 byte

  BEGIN

    IF (i_nextval < 0)
    THEN

      l_nextval_raw := to_char(i_nextval + 18446744073709551616,
                               'FM000000000000000X');

    ELSE
      l_nextval_raw := to_char(i_nextval,
                               'FM000000000000000X');
    END IF;

    dbms_output.put_line('nextval: 0x' || l_nextval_raw || ' len: ' || utl_raw.length(l_nextval_raw) || ' decimal: ' ||
                         i_nextval);

    l_seed_raw := dbms_crypto.decrypt(l_nextval_raw,
                                      g_crypto_mod,
                                      g_seq_key);

    dbms_output.put_line('   seed:   0x' || l_seed_raw || ' len: ' || utl_raw.length(l_seed_raw) || ' decimal: ' ||
                         to_number(l_seed_raw,
                                   'XXXXXXXXXXXXXX'));

    l_seq := to_number(substr(to_char(l_seed_raw),
                              7),
                       'XXXXXXXXXXXXXX');

    RETURN l_seq;
  END;

END obfuscate_seq;
/

Test Script

DECLARE
  seq_original   NUMBER(10);
  seq_obfuscated NUMBER;
  seq_restored   NUMBER;
BEGIN
  seq_original   := 1;
  seq_obfuscated := obfuscate_seq.obfuscate_seq(seq_original);
  seq_restored   := obfuscate_seq.deobfuscate_seq(seq_obfuscated);
  dbms_output.put_line('seq_original : ' || seq_original);
  dbms_output.put_line('seq_restored : ' || seq_restored);

  seq_original   := 4294967295;
  seq_obfuscated := obfuscate_seq.obfuscate_seq(seq_original);
  seq_restored   := obfuscate_seq.deobfuscate_seq(seq_obfuscated);
  dbms_output.put_line('seq_original : ' || seq_original);
  dbms_output.put_line('seq_restored : ' || seq_restored);

  seq_original   := 0;
  seq_obfuscated := obfuscate_seq.obfuscate_seq(seq_original);
  seq_restored   := obfuscate_seq.deobfuscate_seq(seq_obfuscated);
  dbms_output.put_line('seq_original : ' || seq_original);
  dbms_output.put_line('seq_restored : ' || seq_restored);
END;