Mission Data Blog
Louisville-based Web Development & Software Engineering

I have seen several examples of sending email using oracle with non-ascii characters floating around the Internet, but few seem to tackle sending these ’special’ characters in both the subject and the body. Many also take a shortcut and use 8bit transfer encoding for the body. I am 99.9% sure that this will work on today’s mail servers, but basic SMTP only really needs to support 3 transfer encodings - 7bit ascii, base64, and quoted-printable.

(more…)

If you ever need to work with something other than a day when using sysdate you are in luck. As it turns out you can work on sysdate with fractional days. This is just what a person needs when they want to do something like:

SELECT mycol FROM sometable WHERE sometimestamp < "30 seconds ago"

The main trick here is to know that sysdate math is based in days and fractional days work. Here are a few examples:

Remove hours from a date:

sysdate - hours/hours in a day

Remove minutes from a date:

sysdate - minutes/(hours in a day * minutes in an hour)
or
sysdate - minutes/minutes in a day

Remove seconds from a date:

sysdate - seconds/(hours in a day * minutes in an hour * seconds in a minute)
or
sysdate - seconds/seconds in a day

The example above would be:

SELECT mycol FROM sometable WHERE sometimestamp < sysdate - 30/(24*60*60)

If you want to access a database without it being in your tnsnames.ora file, you can use a ‘URL’ at the command line:

sqlplus login/pwd@//hostname:1521/sidname

This should work with versions 9 and above. Let me know if other versions work.

A few days ago I made use of a couple Oracle built in functions and it made me happy I didn’t have to write a stored proc or some type of mini-app to do it. I needed to parse a timestamp out of a field that was put there by a java program. The timestamp was just the output of System.currentTimeInMillis() and was concatenated onto some other information.

It took a little digging to find out how to convert a epoch style timestamp but here it is:

select new_time( to_date('01011970', 'ddmmyyyy') + 1/24/60/60 * :currenttimeinmillis/1000, 'GMT', 'EDT' ) from dual

Note that I convert the output from GMT to EDT here.

Setting up ruby to work with Oracle seems to be a pain for a lot of people. Here are the steps I follow to set it up on a linux box from nothing to Active Record or DBI in 7 steps.

1. Gather the installation sources you will need. You have to be registered with oracle to get their instant client packages.
   Download the ruby oci8 drivers
   Download the oracle instant client
   You want the following packages (these examples assume the zip format):
   • Instant Client Package - Basic or Instant Client Package - Basic Lite
   • Instant Client Package - SDK
   • Instant Client Package - SQL*Plus (optional but nice to have)

(more…)

Today I needed to find a way to count the number of unique email domains in a table. I figured there was a way of getting the index of a string in another string and sure enough there is. This did the trick in Oracle:

select count(1), SUBSTR(email, INSTR(email, '@', 1, 1)+1) from SOMETABLE group by SUBSTR(email, INSTR(email, '@', 1, 1)+1) order by count(1) desc

The INSTR function gives you the location in a string where another string is located. See the following link for more on the INSTR function: http://www.techonthenet.com/oracle/functions/instr.php

I’ve always found the way Oracle handles case interesting. It looks like they are changing things a little starting with 10G: http://blogs.ittoolbox.com/database/solutions/archives/005951.asp