Oracle Numbering a dataset or a data sub set

to number a table or a sub set of data use ROW_NUMBER() with the OVER clause eg.

SELECT 
      NAME
    , ZIP_CODE

    , DATE_CHANGED
    , ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY DATE_CHANGED) AS ROW_NUM

FROM LOCATIONS
WHERE NAME = ‚test1‘
ORDER BY DATE_CHANGED
;

NAME  ZIP_CODE  DATE_CHANGED  ROW_NUM
test1 5610 01.01.1998 1
test1 5610 01.04.2006 2
test1 5610 01.07.2006 3
test1 5610 31.07.2008 4
test1 5610 30.04.2009 5
test1 5610 31.08.2011 6
test1 8211 30.04.2012 7
test1 6920 31.01.2014 8

Oracle Data-Tree

here is a simple example for the CONNECT BY clause.

tom is the manager of peter and simon
and simon is the manager of greg (see the sub-select)

first all possible trees

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“
    , LEVEL    
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, NULL AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
CONNECT BY PRIOR EMP_ID = MAN_ID
ORDER BY EMP_ID
;

now select only the tree from root (START WITH)

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“    
    , LEVEL
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, NULL AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
START WITH EMP_ID = 1
CONNECT BY PRIOR EMP_ID = MAN_ID
ORDER BY EMP_ID
;

here with a cycle, when tom is his own manager. now the NOCYCLE clause is needed and with CONNECT_BY_ISCYCLE you can find all cycles.

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , LEVEL AS myLEVEL
    , CONNECT_BY_ISCYCLE „Cycle“
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“    
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
CONNECT BY NOCYCLE PRIOR EMP_ID = MAN_ID
ORDER BY LEVEL
;

CREATE FUNCTION in ORACLE

simple Oracle-function to calculate the second next day of week per month

CREATE OR REPLACE FUNCTION PROJEKTE_INDI.GET_SECOND_NEXT_DAYOFWEEK(firstofmonth IN date, dayofweek IN VARCHAR2) 
RETURN date
IS 
    second_next_dayofweek date;
/*


returns the second next dayofweek in a month.
firstmonth is the first day in the month
dayofweek is the name of the day – monday, … (language depends on server installation!)


eg
SELECT GET_SECOND_NEXT_DAYOFWEEK(‚01.03.2011‘, ‚Mittwoch‘) AS DAYOFWEEK from DUAL


*/
BEGIN
    SELECT NEXT_DAY(NEXT_DAY(firstofmonth – 1,dayofweek),dayofweek) AS DAYOFWEEK 
    INTO second_next_dayofweek
    FROM DUAL;
    RETURN(second_next_dayofweek);
END
;



Oracle XML datahandling (import etc)

importing XML-files into an oracle database

create a table with a CLOB-column
CREATE TABLE CLOBTEST
(
  POID    NUMBER,
  POCLOB  CLOB
)

POID can/should be autoincrement…

using sqlloadr to load the XML-files into the table
with the unix find-command create a table with all your XML-files you want to import (see example below). the XML.ctl file looks like this:
LOAD DATA 
INFILE *
INTO TABLE CLOBtest TRUNCATE
FIELDS TERMINATED BY ‚,‘
(
    POID CHAR(10),
    clob_filename FILLER CHAR(100),
    POCLOB  LOBFILE(clob_filename) TERMINATED BY EOF
)
–sqlldr schema@db XML.ctl

–find /home/data/ -name *.XML
BEGINDATA
1,/home/data/DOC00001.XML
2,/home/data/DOC00001.XML
3,/home/data/DOC00001.XML

query to extract XML-data

the hard part is to query the XML-tags (elements, values etc) in the CLOB-column. here is an example. first convert the CLOB columns into a XMLType and then use extractvalue or other functions (extract…) to query your data

SELECT extractvalue(xmldata, ‚/700/710/711/‘) AS col1
FROM 
    SELECT XMLType(poClob) AS xmldata
    FROM CLOBtest
)
;
to query sub-tables in the XML-data
SELECT 

    t.id
    , li.ctry
FROM 

    SELECT XMLType(poClob) AS xmldata
    FROM CLOBtest
)    
p,
XMLTable
    (
        ‚/*‘ PASSING p.xmldata
        COLUMNS 
            id VARCHAR2(28) PATH ‚/product/@id‘,
            ctry XMLType PATH ‚/product/soldctry‘
    ) t
    ,
XMLTable
    (
        ‚ctry‘ PASSING t.ctry
        COLUMNS 
            ctry        VARCHAR2(2)  PATH ‚/‘
    ) li
ORDER BY
    t.id
    , li.ctry
;

the p-table is the conversion of the CLOB-datatype into an XML-document.
the t-table extracts the ID of the product-element and the country/countries inside the product.
the li-table extracts all country-names of the product.
the result is a list with all countries of a product.
ID CTRY
15A1 AT
15A1 BE
15A1 BG
15A1 CH
15A1 CY
01A2 AT
01A2 BE

oracle – generic min max avg select statement

to check value of larger tables this procedure creates a sql-string to get all min/max/avg values of the given table

to-do: all column-data-types are used in this version.

DECLARE 


    v_sql VARCHAR2(1000);


    v_table_name VARCHAR2(100);
    v_column_count NUMBER(3);
    v_column_name VARCHAR2(50);
    v_von NUMBER(3);


BEGIN


    v_table_name := ‚MY_TABLE‘;
    
    v_sql := ‚SELECT ‚;
    v_von := 1;


    — get number of columns in the table
    SELECT COUNT(*) INTO v_column_count FROM all_tab_columns WHERE table_name = v_table_name; 


    WHILE v_von <= v_column_count LOOP


        — get current column name
        SELECT COLUMN_NAME INTO v_column_name 
        FROM all_tab_columns 
        WHERE 
            COLUMN_ID = v_von 
            AND table_name = v_table_name

            AND DATA_TYPE IN (‚DATE‘, ‚FLOAT‘, ‚LONG‘, ‚NUMBER‘)

        ; 


        v_sql := v_sql || ‚MIN(‚ || v_column_name || ‚), ‚;
        v_sql := v_sql || ‚MAX(‚ || v_column_name || ‚), ‚;
        v_sql := v_sql || ‚AVG(‚ || v_column_name || ‚), ‚;


        v_von := v_von + 1;


    END LOOP;
    
    — delete the last comma 
    v_sql := SUBSTR(v_sql,1,LENGTH(v_sql) – 2);


    v_sql := v_sql || ‚ FROM ‚ || v_table_name;
    dbms_output.put_line(‚v_sql: ‚ || v_sql);
END;

distance calculation

procedure to calculate a dístance in oracle sql

DECLARE
    earthSphereRadiusKilometers DECIMAL(10,6);


    lat1Degrees decimal(15,12);
    lon1Degrees decimal(15,12);
    lat2Degrees decimal(15,12);
    lon2Degrees decimal(15,12);


    lat1Radians decimal(15,12);
    lon1Radians decimal(15,12);
    lat2Radians decimal(15,12);
    lon2Radians decimal(15,12);


    distance decimal(10,6);    
    
BEGIN


    earthSphereRadiusKilometers := 6366.707019;


    /*wien: 48.214608,16.364136 linz: 48.305121,14.293213*/


    lat1Degrees := 48.214608;
    lon1Degrees := 16.364136;
    lat2Degrees := 48.305121;
    lon2Degrees := 14.293213;


    lat1Radians := (lat1Degrees / 180) * ACOS(-1.0);
    lon1Radians := (lon1Degrees / 180) * ACOS(-1.0);
    lat2Radians := (lat2Degrees / 180) * ACOS(-1.0);
    lon2Radians := (lon2Degrees / 180) * ACOS(-1.0);


    — formula for distance from [lat1,lon1] to [lat2,lon2]
    distance := ROUND(2 * ASIN(SQRT(POWER(SIN((lat1Radians – lat2Radians) / 2) ,2)
        + COS(lat1Radians) * COS(lat2Radians) * POWER(SIN((lon1Radians – lon2Radians) / 2), 2)))
        * earthSphereRadiusKilometers, 4);


    DBMS_OUTPUT.PUT_LINE(‚distance: ‚ || distance);


END;


result is

distance: 153,5289

how to limit character data type to number data type

to make sure you query number data types in character columns, you can do the following in the WHERE clause (eg a postalcode column must be a number, but the data includes characters as well):

AND LENGTH(TRIM(TRANSLATE(PostalCode, ‚ +-.0123456789′,‘ ‚))) IS NULL


if you want the first number to be between 1 and 9 (no zero) do the following


AND LENGTH(TRIM(TRANSLATE(SUBSTR(PostalCode,1,1), ‚123456789‘,‘ ‚))) IS NULL

the advantage of this quite circuitous workaround is: the original data type can be untouched, and the views contain only numbers.