simple xml parser

CREATE OR REPLACE FUNCTION dwfacts.parse_xml(p_string IN VARCHAR2
,p_node IN VARCHAR2
)
RETURN VARCHAR2
AS
v_data VARCHAR2(4000);
v_node VARCHAR2(4000);
v_upper_str VARCHAR2(4000);
v_startnode VARCHAR2(4000);
v_endnode VARCHAR2(4000);
v_startnodelength NUMBER;
BEGIN
v_node := UPPER(p_node);
v_upper_str := UPPER(p_string);
v_startnode := '<'||v_node||'>';
v_endnode := '';
v_startnodelength := LENGTH(v_startnode);

v_data := SUBSTR(p_string,INSTR(v_upper_str,v_startnode)+v_startnodelength);
v_data := SUBSTR(v_data,1,INSTR(UPPER(v_data),v_endnode)-1);
RETURN(TRIM(v_data));
END;

External Table fro XML file

Turns out that an east way to load simple xml file is to treat in just like a plain text file
e.g.

CREATE TABLE dwstage.reh_table
(
accountid VARCHAR2(80)
,bets NUMBER
,losses NUMBER
,loyaltyPoints NUMBER
,playedHands NUMBER
,playedTournaments NUMBER
,rakeLosses NUMBER
,tableRake NUMBER
,totalRake NUMBER
,tournamentFee NUMBER
,transferIn NUMBER
,transferOut NUMBER
,wins NUMBER
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TMP
ACCESS PARAMETERS
(
records delimited by ""
badfile TMP:'table.bad'
logfile TMP:'table.log'
fields
(
accountid CHAR(80) enclosed by "<accountid>" and "</accountid>"
,bets CHAR(80) enclosed by "<bets>" and "</bets>"
,losses CHAR(80) enclosed by "<losses>" and "</losses>"
,loyaltyPoints CHAR(80) enclosed by "<loyaltypoints>" and "</loyaltypoints>"
,playedHands CHAR(80) enclosed by "<playedhands>" and "</playedhands>"
,playedTournaments CHAR(80) enclosed by "<playedtournaments>" and "</playedtournaments>"
,rakeLosses CHAR(80) enclosed by "<rakelosses>" and "</rakelosses>"
,tableRake CHAR(80) enclosed by "<tablerake>" and "</tablerake>"
,totalRake CHAR(80) enclosed by "<totalrake>" and "</totalrake>"
,tournamentFee CHAR(80) enclosed by "<tournamentfee>" and "</tournamentfee>"
,transferIn CHAR(80) enclosed by "<transferin>" and "</transferin>"
,transferOut CHAR(80) enclosed by "<transferout>" and "</transferout>"
,wins CHAR(80) enclosed by "<wins>" and "</wins>"
)
)
LOCATION ('table.xml')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

Multiple time rows from dual

SELECT TRUNC(SYSDATE - 4 / 1440, 'MI') - (LEVEL - 1) / 1440 starttime
FROM SYS.DUAL
CONNECT BY LEVEL <= 36)

dynamic sampling

http://www.oracle.com/technology/oramag/oracle/09-jan/o19asktom.html

Bash date arithmetic

Not really oracle but useful nevertheless
date -d "yesterday" +"%d %B %y"


http://www.walkernews.net/2007/06/03/date-arithmetic-in-linux-shell-scripts/




Convert comma separated list into an array - dbms_utility.comma_to_table

Simple way to convert a comma separated list held in a single variable into its component parts.
pl/sql equivalent to php explode
e.g.

SET SERVEROUTPUT ON
DECLARE
l_list1   VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
l_list2   VARCHAR2(50);
l_tablen  BINARY_INTEGER;
l_tab     DBMS_UTILITY.uncl_array;
BEGIN
DBMS_OUTPUT.put_line('l_list1 : ' || l_list1);

DBMS_UTILITY.comma_to_table (
   list   => l_list1,
   tablen => l_tablen,
   tab    => l_tab);

FOR i IN 1 .. l_tablen LOOP
  DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
END LOOP;

DBMS_UTILITY.table_to_comma (
   tab    => l_tab,
   tablen => l_tablen,
   list   => l_list2);

DBMS_OUTPUT.put_line('l_list2 : ' || l_list2);



END;

http://www.oracle-base.com/articles/9i/UsefulProceduresAndFunctions9i.php

ON CURSOR FOR LOOPS

Oracle magazine article