Leap Year Check

This is a quick SQL statement to check if a year is a leap year:

SELECT :YEAR,
       DECODE (MOD (:YEAR, 4), 0, DECODE (MOD (:YEAR, 400), 0, 1, DECODE (MOD (:YEAR, 100), 0, 0, 1)), 0) AS leap_year
FROM   DUAL;

The above SQL returns 1 if :YEAR is a leap year. Else, returns 0.

How to use a flat file as an Oracle External Table

In this example, we define an External Table that corresponds to a fixed-length flat file.

A much better description can be found here.

The file must be present in an Oracle Directory.

The file is mapped to a table with a command similar to the following:

CREATE TABLE my_external_table
(
   field1    VARCHAR2(8)    ,
   field2    VARCHAR2(8)    ,
   field3    VARCHAR2(6) ,
   .....
   fieldn   VARCHAR2(40)
)
ORGANIZATION EXTERNAL 
   (
   TYPE oracle_loader
   DEFAULT DIRECTORY my_external_dir
   ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
        FIELDS
        (
        FIELD1     POSITION(1:8) ,
        FIELD2     POSITION(9:16),
        FIELD3     POSITION(17:22),
        .....
        FIELDN    POSITION(1457:1496)
       )
     )
     LOCATION ('my_flat_file.dat')
  )
REJECT LIMIT UNLIMITED;

Once the table has been created, then it is possible to perform queries as with any normal table:

select *
from my_external_table
where rownum < 10;

Note that the ACCESS PARAMETERS section can contain any valid sql*loader statement.

... and how to get all letters from a word

The function provided below returns the letters in a single word along with their position in the word:

CREATE OR REPLACE TYPE     TYP_WORD_LETTER AS OBJECT
(
  letter VARCHAR2(2),
  position  number(2)
)
/

CREATE OR REPLACE TYPE TYP_WORD_LETTER_COL
AS TABLE OF TYP_WORD_LETTER;
/

CREATE OR REPLACE FUNCTION get_word_letters (v_word VARCHAR2)
   RETURN typ_word_letter_col PIPELINED
AS
   ret_val   NUMBER;
   aletter   VARCHAR2 (2);
   pos       PLS_INTEGER  := 1;
BEGIN
   LOOP
      aletter := SUBSTR (v_word, pos, 1);
      PIPE ROW (NEW typ_word_letter (aletter, pos));
      pos := pos + 1;
      EXIT WHEN pos > LENGTH (v_word);
   END LOOP;

   RETURN;
END;
/

How to break a phrase into words in Oracle

This is a technique to get the individual words in a phrase.
It is based on the use of:

• PIPELINED functions, and
• Regular Expressions
  

First we need a object type and a corresponding collection (to be used by the PIPELINED function).

CREATE OR REPLACE TYPE     typ_word AS OBJECT
(
  text VARCHAR2(64)
)
/

CREATE OR REPLACE TYPE TYP_word_COL
AS TABLE OF TYP_word;
/

The function itself:

CREATE OR REPLACE FUNCTION get_words (v_sentence VARCHAR2)
   RETURN typ_word_col PIPELINED
AS
   occurence   PLS_INTEGER   := 1;
   aword       VARCHAR2 (64);
BEGIN
   LOOP
      aword := REGEXP_SUBSTR (v_sentence, '\w+', 1, occurence);

      IF aword IS NULL
      THEN
         RETURN;
      END IF;

      PIPE ROW (NEW typ_word(aword));
      occurence := occurence + 1;
   END LOOP;
END;
/

How to calculate time difference from TIMESTAMPs

The following script reads a table containing user actions and calculates the time difference (in hours) between the first and last action per user and work day.

The field action_tm is of type TIMESTAMP.

SELECT TRUNC (action_tm) work_day, user_nm,
       MIN (action_tm) start_time,
       MAX (action_tm) end_time,
       ROUND (((TO_CHAR (MAX (action_tm), 'SSSSS') + TO_CHAR (MAX (action_tm), 'FF') * .000001) -
               (TO_CHAR (MIN (action_tm), 'SSSSS') + TO_CHAR (MIN (action_tm), 'FF') * .000001)) /60/60,2) work_hours
FROM user_actions a
GROUP BY TRUNC (action_tm), user_nm
ORDER BY TRUNC (action_tm);