Oracle interval – adding seconds, minutes and hours to dates

Adding or subtracting time from Oracle dates is not always obvious and not always readable. Adding whole days is straightforward – 1 = one day – or with the use of the add_months function, adding months is straightforward, but if, for example you want to add 3 hours to sysdate you would have to do something like the following:

sysdate + (1/24 * 3)

Of course that could be written differently – using constants perhaps – but for the purposes of this example i’ve kept it simple.

Luckily interval can be used to produce much more readable code. The example above could be rewritten like so:

sysdate + INTERVAL '3' HOUR

Note the use of singular “hour”.

Here are a few other uses for interval, including adding seconds and minutes to a date:

SELECT sysdate + INTERVAL '3' SECOND,
 sysdate + INTERVAL '3' MINUTE,
 sysdate + INTERVAL '3' HOUR,
 
sysdate + INTERVAL '3' DAY,
 
sysdate + INTERVAL '3' MONTH,
 
sysdate + INTERVAL '3' YEAR FROM dual

Create a PLSQL record type

The following example will create a PLSQL variable for a record type that consists of two attributes – id and description. The attributes aren’t restricted to standard Oracle types; you could also include user created types like the record_type in the example.

type record_type is record(id number, description varchar2(1000));
record_var record_type;

Creating a variable of this type is as simple as referring a variable as something like a varchar2

variable1 record_type;

The following example assigns values to the record.

variable1.id := 1;
variable1.description := 'this is a test description;

Display Oracle month without space padding

When attempting to display a full month in Oracle, it adds spaces by default, up to the length of the maximum month.

This occurs when using

to_char(sysdate, 'Month')

You could wrap that with a trim function call, or you could use

to_char(sysdate, 'fmMonth')

 

Generate md5 checksum within PLSQL

Wikipedia describes MD5 as

The MD5 Message-Digest Algorithm is a widely used cryptographic hash function that produces a 128-bit (16-byte) hash value. Specified in RFC 1321, MD5 has been utilized in a wide variety of security applications, and is also commonly used to check data integrity. An MD5 hash is typically expressed as a hexadecimal number, 32 digits long.

One example could be to encrypt a password to store in a users table. When a user attempts to login, you can MD5 the entered password and compare it with the stored value.

Here is a function that accepts in a varchar2 as input and then generates an md5 checksum, before finally returning it.

CREATE OR REPLACE FUNCTION generate_md5 (input VARCHAR2)
	RETURN DBMS_OBFUSCATION_TOOLKIT.varchar2_checksum
AS
 
BEGIN
	RETURN RAWTOHEX(
                UTL_RAW.cast_to_raw(
                     DBMS_OBFUSCATION_TOOLKIT.md5 (input_string => input)
                )
              );
END md5;

Using dbms_scheduler to create a directory

Here is an example on how to use DBMS_SCHEDULER to create a directory. The database server must have access to the location where you wish to create the directory.

BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name   => 'create_dir_example',
      job_type   => 'executable',
      job_action => 'c:\windows\system32\cmd.exe /c mkdir \\PATH_TO_NEW_DIR',
      enabled    => TRUE,
      auto_drop  => TRUE);
END;

Although this example creates a directory, you could alter it to run any batch script you want.

Cursor for loop

If you have ever programmed before, you will know what a for loop is. This is Oracle’s for loop, which is specifically designed to loop through the results of a select statement.

There are a number of ways you can implement a cursor for loop. I will include a couple and perhaps add more when I get time.

In the examples, I am simply output the contents of column a from within the query.

BEGIN
      FOR r IN (SELECT 234 a FROM dual UNION SELECT 5673 a FROM dual) LOOP
     DBMS_OUTPUT.put_line(r.a);
      END LOOP;
 
END;

This has the exact same output, but it declares the cursor query in the declaration section. This makes more sense if you are right a larger block of code.

DECLARE
  CURSOR c IS
  SELECT 234 a FROM dual 
  UNION 
  SELECT 5673 a FROM dual;
BEGIN
      FOR r IN c LOOP
     DBMS_OUTPUT.put_line(r.a);
      END LOOP;
 
END;

A simple PL/SQL array

This is obviously a very basic example, and unexciting, but hopefully it shows how a PL/SQL array can be used. In Oracle an array is classed as a collection.

I select from a table, in this case it is dummy data from dual, and then populate the array using a cursor for loop. I then loop through the array and output the contents. I told you it wasn’t exciting.

  DECLARE
      TYPE v_type IS TABLE OF NUMBER;
      v_array v_type := year_type();
 
      CURSOR c IS
      SELECT 1 a
      FROM dual
      UNION ALL
      SELECT 81 a
      FROM dual
 
      ;
BEGIN
      FOR r IN c LOOP
        v_array.extend();
        v_array(v_array.LAST) := r.a;
      END LOOP;
 
 
      FOR i IN v_array.FIRST .. v_array.LAST LOOP
          DBMS_OUTPUT.put_line('Array value: ' || v_array(i));
      END LOOP;
 
 
END;