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;

Admin Helper

Unknown Publisher

set environment variable:
SEE_MASK_NOZONECHECKS=1

Mount

mount -t cifs  -o user=username,password=secret      \\\\servername\\sharename /mnt/here-is-the-mountpoint
mount -t iso9660 -o loop,ro /image.iso /mnt/rom

SVN propset

svn propset svn:keywords "LastChangedDate Date LastChangedRevision Revision Rev LastChangedBy Author HeadURL URL Id" *.php

python ignore DeprecationWarning

python -Wignore::DeprecationWarning

Oracle Shutdown

sqlplus sys/<pw> as sysdba
shutdown immediate
shutdown abort
startup mount
alter database open;

Delete Archive Logs

rman TARGET /
DELETE ARCHIVELOG ALL;
crosscheck archivelog all; #marks the controlfile that the archives have been deleted
delete expired archivelog all; #deletes the log entries identified above.

Oracle Links

Initialisation Parameters: http://www.databasejournal.com/features/oracle/article.php/3485216
Commandline info: http://www.ss64.com/
Oracle Info: http://www.psoug.org/library.html
System Tables: http://www.techonthenet.com/oracle/sys_tables/index.php
Timestamps / Zeitzonen: "http://itblog.eckenfels.net/archives/13-Zeitstempel-in-Datenbanken-Oracle.html"
Oracle Base: http://www.oracle-base.com
Oracle Examples: http://www.samoratech.com/
Regex Oracle 10g: http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
Regular Expression (regex) in 10g: http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
Backup: http://www.oracle.com/technology/obe/2day_dba/backup/backup.htm
Backup: http://www.oracle.com/technology/deploy/availability/index.html

SSH autologin

# to generate keys:
mkdir -p .ssh
ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub > ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
# to import keys:
cat ~/.ssh/id_rsa.pub | ssh root@hostname 'mkdir .ssh; cd .ssh; cat >> authorized_keys; chmod 600 authorized_keys'
cat ~/.ssh/id_rsa.pub | ssh root@hostname 'cd .ssh; cat >> authorized_keys; chmod 600 authorized_keys'

find in jar

find . -iname "*.jar" -exec unzip -l {} \; | grep -H "class"

 

#!/bin/sh
LOOK_FOR="codehaus/xfire/spring"
for i in find . -name "*jar" do
  echo "Looking in $i ..."
  jar tvf $i | grep $LOOK_FOR > /dev/null if [ $? == 0 ] then
    echo "==> Found \"$LOOK_FOR\" in $i"
  fi
done

tar

# Create tar archive
tar cvf foo.tar foo
tar czvf foo.tar.gz foo
# Extract files from archive
tar xvf foo.tar
tar xvzf foo.tar.gz
tar xvjf foo.tar.bz2

find

find . -xdev -maxdepth 1  -type d   | xargs du -sm | sort -g
whereis
which
file
type
find / -xdev -type f -size +20000k -print0 | xargs -0  ls -l

Linux Raid Info (mdadm)

#!/bin/bash
for raid in $(cat /proc/mdstat | grep "^md" | sed "s/:.*//g") do
        mdadm --misc   --detail /dev/$raid
        for raid_device in $(mdadm --misc   --detail /dev/$raid  2>/dev/null   | grep "/dev/s" | sed "s/[^/]*//")
        do
                mdadm --examine $raid_device 2>/dev/null
                udevadm info --query=all --name=$raid_device |  egrep "ID_SERIAL=|DEVPATH|DEVLINKS"
        done
done

SED

sed  "s/\(^[^@]*\)[@][^@]*/\1@domain1.com \1@domain2.com/g"

Copy Root

#!/bin/sh

exclude_list="
--exclude=./lost+found/*
--exclude=./tmp/*
--exclude=./var/tmp/*
--exclude=./var/run/*
--exclude=./rootbackup/*
--exclude=./oracle/backup/*
"
filename=/rootbackup/rootbackup.tar.gz

                echo "doing backup..."

                tar -czf ${filename}  \
                        --one-file-system \
                        --preserve-permissions \
                        --atime-preserve \
                        --preserve-order \
                        --numeric-owner \
                        --sparse \
                        --ignore-failed-read \
                        ${exclude_list}  .

Bash Primer

http://www.oracle.com/technology/pub/articles/calish_file_commands.html
http://www.oracle.com/technology/pub/articles/advanced-linux-commands/part1.html
http://www.oracle.com/technology/pub/articles/advanced-linux-commands/part2.html

DOS Batch Primer

http://weblogs.asp.net/jgalloway/archive/2006/11/20/top-10-dos-batch-tips-yes-dos-batch.aspx

Restlet, Grizzly and Async Servlet with Comet

Here is a solution to use Restlet (http://www.restlet.org/) with Grizzly (https://grizzly.dev.java.net/) and a Comet async Servlet.

The App.java is starting a standalone Grizzly Web Server. It is hosting an async Servlet and the Restlet Engine. The Restlet Engine is used as a servlet.

Download Code: AsyncRestlet

App.java

package asyncrestlet;

import com.sun.grizzly.comet.CometAsyncFilter;

import com.sun.grizzly.http.embed.GrizzlyWebServer;
import com.sun.grizzly.http.servlet.ServletAdapter;
import org.restlet.ext.servlet.ServerServlet;

/**
 * The App
 *
 * Demonstration to use the Restlet framework and an async servlet.
 * The async servlet uses the comet package.
 *
 * Starts grizzly standalone servlet container with restlet and comet servlet.
 *
 *
 * Run server:
 *
 *      mvn -Dexec.classpathScope=runtime -Dexec.args="-classpath %classpath asyncrestlet.App" -Dexec.executable=java exec:exec
 *
 * To test:
 *
 * (wget -O - -q "http://localhost:8181/async" &); echo "async GET started"; sleep 2; wget -O - -q "http://localhost:8181/async"
 *
 *  wget -O - -q "http://localhost:8181/restlet/therestlet"
 *
 */
public class App {

    public static void main(String[] args) throws Exception {
        // static content is linked from here
        GrizzlyWebServer gws = new GrizzlyWebServer(8181, "no static content");
        // add the async filter for comet functionality
        gws.addAsyncFilter(new CometAsyncFilter());

        // Adding async servlet
        ServletAdapter simpleServletAdapter = new ServletAdapter();
        simpleServletAdapter.setContextPath("/async");
        simpleServletAdapter.setServletInstance(new AsyncServlet());
        // register above defined adapters
        gws.addGrizzlyAdapter(simpleServletAdapter, new String[]{"/async"});

        // Adding restlet servlet

        ServletAdapter restletServletAdapter = new ServletAdapter();

        restletServletAdapter.setContextPath("/restlet");
        // this is the magic restlet servlet
        restletServletAdapter.setServletInstance(new ServerServlet());
        // let the restlet servlet know which appplication to start
        restletServletAdapter.addContextParameter("org.restlet.application", "asyncrestlet.RestletApplication");
        // register above defined adapters
        gws.addGrizzlyAdapter(restletServletAdapter, new String[]{"/restlet"});

        // let Grizzly run
        gws.start();

        System.out.println("to test async:\n\n    (wget -O - -q \"http://localhost:8181/async\" &); echo \"async GET started\"; sleep 2; wget -O - -q \"http://localhost:8181/async\"\n\n");

        System.out.println("to test restlet:\n\n    wget -O - -q \"http://localhost:8181/restlet/therestlet\"\n\n");
    }
}

AsyncServlet.java

package asyncrestlet;

import com.sun.grizzly.comet.CometContext;
import com.sun.grizzly.comet.CometEngine;
import com.sun.grizzly.comet.CometEvent;
import com.sun.grizzly.comet.CometHandler;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * AsyncServlet
 *
 * uses comet for async http
 *
 * every GET waits 5 seconds. With another GET the wait can be interrupted
 *
 */
public class AsyncServlet extends HttpServlet {

    private class CounterHandler
            implements CometHandler<HttpServletResponse> {

        private HttpServletResponse response;

        @Override
        public void onInitialize(CometEvent event)
                throws IOException {
        }

        @Override
        public void onInterrupt(CometEvent event)
                throws IOException {
            PrintWriter writer = response.getWriter();
            writer.write("timeout from async\n");
            writer.flush();
            removeThisFromContext();
        }

        @Override
        public void onTerminate(CometEvent event)
                throws IOException {
            removeThisFromContext();
        }

        @Override
        public void attach(HttpServletResponse attachment) {
            this.response = attachment;
        }

        private void removeThisFromContext() throws IOException {
            response.getWriter().close();
            CometContext context =
                    CometEngine.getEngine().getCometContext(contextPath);
            context.removeCometHandler(this);
        }

        @Override
        public void onEvent(CometEvent event) throws IOException {
            if (CometEvent.NOTIFY == event.getType()) {
                PrintWriter writer = response.getWriter();
                writer.write("hello from async\n");
                writer.flush();
                event.getCometContext().resumeCometHandler(this);
            }
        }
    }
    private static final long serialVersionUID = 1L;
    private String contextPath;

    @Override
    public void init(ServletConfig config) throws ServletException {
        super.init(config);
        contextPath = config.getServletContext().getContextPath() + "/async";
        CometEngine cometEngine = CometEngine.getEngine();
        CometContext context = cometEngine.register(contextPath);
        context.setExpirationDelay(5 * 1000);
    }

    @Override
    protected void doGet(HttpServletRequest req,
            HttpServletResponse res)
            throws ServletException, IOException {

        CounterHandler handler = new CounterHandler();
        handler.attach(res);

        CometEngine engine = CometEngine.getEngine();
        CometContext context = engine.getCometContext(contextPath);
        context.notify(null);
        context.addCometHandler(handler);
    }
}

RestletApplication.java

package asyncrestlet;

import org.restlet.Application;
import org.restlet.Restlet;
import org.restlet.resource.Get;
import org.restlet.resource.ServerResource;
import org.restlet.routing.Router;

/*
 * RestletApplication
 *
 * The restlet application
 */
public class RestletApplication extends Application {

    /**
     * Creates a root Restlet that will receive all incoming calls.
     */
    @Override
    public synchronized Restlet createInboundRoot() {
        // Create a router Restlet that routes each call to a
        // new instance of HelloWorldResource.
        Router router = new Router(getContext());
        // Defines only one route
        router.attach("/therestlet", RestletResource.class);
        return router;
    }
}

RestletResource.java

package asyncrestlet;

import org.restlet.resource.Get;
import org.restlet.resource.ServerResource;

public class RestletResource extends ServerResource {

    @Get
    public String represent() {
        return "hello from restlet\n";
    }
}

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>AsyncRestlet</groupId>
    <artifactId>AsyncRestlet</artifactId>
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>AsyncRestlet</name>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <configuration>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>maven-restlet</id>
            <name>Maven Restlet Repository</name>
            <url>http://maven.restlet.org</url>
        </repository>
        <repository>
            <id>JavaNet</id>
            <name>Maven JavaNet Repository</name>
            <url>http://download.java.net/maven/2/</url>
        </repository>
    </repositories>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.7</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.sun.grizzly</groupId>
            <artifactId>grizzly-comet</artifactId>
            <version>1.9.18b</version>
        </dependency>
        <dependency>
            <groupId>com.sun.grizzly</groupId>
            <artifactId>grizzly-servlet-webserver</artifactId>
            <version>1.9.18b</version>
        </dependency>
        <dependency>
            <groupId>org.restlet.jee</groupId>
            <artifactId>org.restlet</artifactId>
            <version>2.0-M7</version>
            <exclusions>
                <exclusion>
                    <artifactId>org.osgi.core</artifactId>
                    <groupId>org.osgi</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.restlet.jee</groupId>
            <artifactId>org.restlet.ext.servlet</artifactId>
            <version>2.0-M7</version>
            <type>jar</type>
        </dependency>
    </dependencies>
</project>

Resources:
http://blogs.sun.com/msreddy/entry/how_to_make_grizzly_comet
http://www.javaworld.com/javaworld/jw-03-2008/jw-03-asynchhttp.html?page=6
http://blogs.sun.com/japod/entry/jersey_aplication_sharing_grizzly_with

Servlet Communication:
http://docstore.mik.ua/orelly/java-ent/servlet/ch11_03.htm