How to retrieve and use Oracle TIMESTAMPs in Java

A few rules about using TIMESTAMPs in Java:

1. Never make assumptions about things like the timezone in the database and/or the application server.
2. It is preferable to retrieve the TIMESTAMP (or Date) field from the database as a string using the appropriate format. You can then easily convert it to java.sql.Timestamp and use it in Java.
• If you have to compare values that are stored in the database, it is better to do it in your SELECT statement and return something appropriate (i.e. time difference) to Java.
• If you must return something to use it for a comparison in Java, then prefer to return the TIMESTAMP after you have ensured that this is converted to GMT (UTC) time.

How to do it:

• Retrieve the GMT value of a TIMESTAMP as a string that can be converted to java.sql.Timestamp. In the following example change_tm is a Timestamp filed. We are using SYS_EXTRACT_UTC to convert it to GMT.

SELECT 
     TO_CHAR(CHANGE_TM, 'yyyy-mm-dd hh24:mi:ss') CHANGE_TM,
     TO_CHAR(SYS_EXTRACT_UTC(CHANGE_TM), 'yyyy-mm-dd hh24:mi:ss') GMT_CHANGE_TM
FROM TIME_TBL
WHERE  ID = :b1;



• Convert the selected value to a java.sql.Timestamp by simply calling Timestamp.valueof().
• Compare the selected value with the current time of the application server using a Calendar. The Calendar will provide useful information like the offset from GMT.

     Calendar nowCal =GregorianCalendar.getInstance();
     System.out.println("Date now: " + nowCal.get(Calendar.YEAR) + "/" +
                         nowCal.get(Calendar.MONTH) + "/" +
                         nowCal.get(Calendar.DAY_OF_MONTH) + " " +
                         nowCal.get(Calendar.HOUR_OF_DAY) + ":" +
                         nowCal.get(Calendar.MINUTE) + ":" +
                         nowCal.get(Calendar.SECOND) +
                         " Offset from GMT " +
                         nowCal.get(Calendar.ZONE_OFFSET) +
                         " Timezone: " +
                         nowCal.getTimeZone().getDisplayName());

     long now = nowCal.getTimeInMillis();

     System.out.println("Diff before now and last change time: " +
                    ((now - rec.getGmtChangeTm().getTime() - nowCal.get(Calendar.ZONE_OFFSET)) / 1000 / 60) +
                    " minutes");

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);

How to encrypt/decrypt a field in Oracle

Oracle provides package DBMS_OBFUSCATION_TOOLKIT which can be used for encrypting/decrypting database fields.

There are a couple of things to remember about this package though:

1. The package provides PROCEDURES to perform encryption/decryption. This alone may not be very useful if you need to use plain SQL. So I provide two functions to wrap these procedures (see below).
2. The length of both the value to be encrypted and the encryption key must be exact multiples of 8. Therefore you need to pad the value with somethig (i.e blanks) to reach the desired length.
   

These are the functions:

CREATE OR REPLACE function encrypt_val(
   input_string       VARCHAR2,
   key_string         VARCHAR2 ) return varchar2 as
   encrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string => input_string, key_string => key_string,
                                        encrypted_string => encrypted_string);
   --DBMS_OUTPUT.put_line ('encrypted hex value : ' || RAWTOHEX (UTL_RAW.cast_to_raw (encrypted_string)));
   return encrypted_string;
END;
/

CREATE OR REPLACE FUNCTION decrypt_val (encrypted_string VARCHAR2, key_string VARCHAR2)
   RETURN VARCHAR2
AS
   decrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string => encrypted_string, key_string => key_string,
                                        decrypted_string => decrypted_string);
--   DBMS_OUTPUT.put_line ('decrypted string output : ' || decrypted_string);
   RETURN decrypted_string;
END;
/

Example:

The following statement reads a table containing users and encrypts the username (i.e. in order to generate a default password). The actual value to be encrypted is the user name right padded with blanks so that the total length is a multiple of 8.

SELECT user_nm val,
       encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
FROM   ref_user;
        
To retrieve the decrypted value, you need to both decrypt and TRIM:

SELECT val, enc_val, TRIM (decrypt_val (enc_val, 'mykey678')) dec_val
FROM   (
        SELECT user_nm val,
               encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
        FROM   ref_user       
        );

How to calculate factorial in Oracle

I found this script to calculate the factorial of number n:

---

SELECT ROUND (EXP (SUM (LN (n))))
FROM (
      SELECT LEVEL AS n
      FROM DUAL
      CONNECT BY LEVEL <= :n);

---

Unfortunately, I cannot find the blog/forum where this was originally posted.

There are other implementations, but I found that this one is executing faster.