I recently had a requirement to store a time within Oracle, not a timestamp or a date – just the time. As it turns out, storing just the time information in Oracle isn’t something you can do without a little bit of work.

Oracle provides you a few different data types for handling date and time:

• DATE
• TIMESTAMP
• INTERVAL

Unfortunately, none of the supplied data types are an exact match for a time such as 10:30 AM. The good news is that both a DATE and a TIMESTAMP data types contain time information – so it is possible to do what you want with a little slight of hand.

The slight of hand I mentioned has to do with the default behaviour of Oracle when inserting only a time component of a DATE or a TIMESTAMP data type. For the rest of this discussion, lets assume you have a simple table set up as follows:

1. CREATE TABLE Times (
2. id INT NOT NULL,
3. thedate DATE NOT NULL,
4. thetimestamp TIMESTAMP NOT NULL
5. );

With the following three rows of data:

1. INSERT INTO Times (id, thedate, thetimestamp) VALUES (1, SYSDATE, SYSDATE);
2. INSERT INTO Times (id, thedate, thetimestamp) VALUES (2, TO_DATE('10:30 AM', 'HH:MI AM'), TO_DATE('10:30 AM', 'HH:MI AM'));
3. INSERT INTO Times (id, thedate, thetimestamp) VALUES (3, TO_DATE('0001-01-01 10:30 AM', 'YYYY-MM-DD HH:MI AM'), TO_DATE('0001-01-01 10:30 AM', 'YYYY-MM-DD HH:MI AM'));

When selecting that grid of information out, you’ll receive:

There are more than one solution to this problem, as if you’re using any of the standard date/time data types – you can always store the time component. What may or may not be of interest to everyone is what Oracle does with the date component of a DATE or TIMESTAMP data type when you don’t provide the standard date components of a date/time data type.

If you take notice of row #1 returned, you’ll see that because the inserted values for thedate and thetimetamp was SYSDATE, it has stored the date component as you’d expect.

Compare that against row #2 and you’ll notice that the insert statement simply provided the time component and made no mention of the date. When Oracle returned that time value out, it has automatically substituted the first day of the current month into the date component.

When inserting row #3, an arbitrary date of 1 Jan 0001 was supplied. When reading that information back out of Oracle, it handles the date component in a similar fashion to how it was handled for row #1 using the SYSDATE; ie you get the expected result back.

Since you can return any number of rows from Oracle doing standard date/time arithmetic, it really comes down to preference. What option above has the most semantic meaning to a user or the data in the database when you’re required to store a time and not a timestamp?

For consistency reasons, my personal preference leans towards applying technique #3 and here are a couple of simple reasons why:

• When dealing with a DATE or TIMESTAMP value stored like that in PL/SQL, you’ll always know what value is stored in the date component of the value.
• If you’re handling this information in an application language such as Microsoft .NET, Java or Python – you will always know what value to expect in the date component. Using technique #1, the date component will change for every row. Using #2, the date component of the value will change per row depending on what month the row was inserted

Does anyone else have a hot tip for storing a straight time value in Oracle?

Today I received a strange error from Oracle, for what appeared to be no good reason:

1. ORA-06552: PL/SQL: Compilation unit analysis terminated
2. ORA-06553: PLS-320: The declaration of the type of this expression is incomplete or malformed

I received the error when attempting to create a trigger on a newly created table. I could create triggers on other tables without any errors, however on the new table – it would error every time.

The table definition included a column named timestamp with the data type timestamp. Oracle was happy enough for me to create the table, however it refused to let me create a trigger on the table with a column name over lapping a reserved word.

The solution was simple, renamed the column in question to use a non-reserved word and everything continued as normal. I must say though, I think it is strange that it would let me create the table using the reserved word in the first place if it was going to complain about me using other standard Oracle features which would error as a by product of the reserved word.

Today I came up against a very frustrating problem when writing some Oracle PL/SQL stored procedures and functions:

1. ORA-06502: PL/SQL: numeric or value error

When I first wrote the stored function in question, I was using VARCHAR2 types for storage since it was the data type returned by an Oracle provided package. The function signature looked something like the following example:

1. FUNCTION MyFunction
2. (pData IN VARCHAR2)
3. RETURN VARCHAR2;

The stored procedures and functions in question were being used with a lot of character information. Whilst running small sets of test data through the functions, everything was acting as expected. Unfortunately, as the test data sets increased in size I began to receive the ORA-06502 error.

As you may or may not be aware, within PL/SQL a VARCHAR2 type can store a maximum of 32767 bytes of information. When the ORA-06502 exceptions where taking place, this limit was being exceeded.

The thing that was so frustrating about the error, was that it wasn’t helping me identify the problem. In this particular instance, I had refactored a significant amount of PL/SQL and during that process changed some variables from VARCHAR2 into CLOB data types.

Oracle will allow you to pass a CLOB variable into a function that accepts a VARCHAR2, so long as the length of the CLOB is less than the maximum byte limit of the VARCHAR2. Since that wasn’t throwing a type conversion error in normal circumstances, it wasn’t something that I went looking into immediately as a possible problem when the Oracle ORA-06502 errors were thrown.

The solution to this particular problem is what you would expect, change the data types of all associated functions and procedures to use the CLOB data type:

1. FUNCTION MyFunction
2. (pData IN CLOB)
3. RETURN CLOB;

After looking into the error in more depth, it can be thrown for virtually any generic constraint violation. The following simple examples would produce this error code:

• assign a NULL value to a variable defined as NOT NULL
• assign a value greater than 99 to a variable defined as NUMBER(2)
• assign a CLOB with a length greater than 32767 bytes to a VARCHAR2 variable

I think it would have been a little more useful to a developer for Oracle to throw some sort of a type conversion error in this instance.

PostgreSQL provides two useful functions to aid in the safe development of dynamic SQL:

quote_ident

The quote_ident function accepts and returns a text type. Whatever text you pass into quote_ident will be suitably escaped such that you could safely use it as an identifier within a dynamic SQL statement.

According to the documentation, you should always pass table and column identifiers into the quote_ident function for safety. Calling quote_ident('mytable') will return mytable, however calling quote_ident('MyTable') would return "MyTable".

quote_literal

The quote_literal function accepts and returns a text type. Whatever text you pass into the quote_literal function will be escaped so that you can safely use them in dynamic SQL.

You should always pass values or literals into the quote_literal function. By doing so, all special characters such as quotes will be safely dealt with. Calling quote_literal('mycolumn') would return 'mycolumn' whilst quote_literal('my\'column') would return 'my''column'.

Both of these functions work a treat, however there is a caveat with the quote_ident function which isn’t well documented. When creating objects in PostgreSQL, they object names are automatically lowercased unless you create the object using double quotes. As a simple example:

1. CREATE TABLE MyTable (id integer, name varchar); would result in an object mytable being created.
2. CREATE TABLE "MyTable" (id integer, name varchar); would result in an object MyTable being created; note the casing.

Now lets assume you wanted to create some dynamic SQL to fetch information out of the first example table above. If you issued quote_ident('mytable'), your dynamic SQL statement will execute because the value returned from quote_ident is lowercase which matches the table name. If you called quote_ident('MyTable'), your dynamic SQL statement will report an error stating that it cannot find the table or relation.

Creating dynamic SQL in PostgreSQL to fetch data out of the second example above, you would run into the reverse scenario. Issuing quote_ident('mytable') would produce an error, while quote_ident('MyTable') would execute without error.

If you create your database objects without using double quotes, then it’s important to remember to not pass capitalised parameters into quote_ident. The opposite is of course true as well, if you create your objects using double quotes then you must remember to pass in the same casing to quote_ident. If quote_ident applies double quotes (be it from capitised letters, spaces or special characters), the SQL engine within PostgreSQL will assume that an object exists with the explicit name matching the returned value of the quote_ident function.