Changing the Current Value of Oracle Sequences

Thanks to a recent product upgrade a sequence in one of our databases was reset to about 100,000 below its previous value. To reset it Oracle, and most other sites, tell me I need to drop the sequence and recreate it to change its current value.

To avoid dropping the sequence and invalidating all the triggers and anything else that is dependent on it I decided a different approach was in order.

I first determined the difference between the trigger and the maximum value in the table. I then changed the amount it increments with to the difference (plus a few to be save), selected nextval, then change it back to increment by 1.

The commands were something like this:

alter sequence id_sequence increment by 142900;

select id_sequence.nextval from dual;

alter sequence id_sequence increment by 1;

This method of course only works to increase the sequence. One additional risk is that something will increment the trigger while the increment is set high. In this case you’re stuck dropping the sequence and recreating it. Just remember, if it comes to this you’ll want to recompile all invalid objects so they won’t slow down the next time they run.

oracle, sql, dba, database administration, database development

UNIX timestamp to Oracle Date Conversion

Earlier today a situation came up where a UNIX timestamp (a count of the number of seconds from January 1, 1970, midnight GMT) needed to be converted into an Oracle DATE format. The Oracle TO_DATE (covered in more detail in my article Oracle, SQL, Dates and Timestamps) does not support this type of conversion.

A Google search confirmed for me that their was no easy way to make the TO_DATE function do this, but I did find this article from the Oracle + PHP Cookbook on Oracle’s site which contained this simple function to convert UNIX timestamps to Oracle dates.

[SQL]CREATE OR REPLACE
FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
/**
* Converts a UNIX timestamp into an Oracle DATE
*/
unix_epoch DATE := TO_DATE(‘19700101000000′,’YYYYMMDDHH24MISS’);
max_ts PLS_INTEGER := 2145916799; — 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; — 1903-01-01 00:00:00
oracle_date DATE;

BEGIN

IF unixts > max_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
‘UNIX timestamp too large for 32 bit limit’
);
ELSIF unixts < min_ts THEN RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too small for 32 bit limit' ); ELSE oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND'); END IF; RETURN (oracle_date); END; / [/SQL] Once compiled, you can use the function to convert numerical UNIX timestamps into Oracle dates in the same manner you would use the TO_DATE function to convert text to dates. Here is an example: SQL> select unixts_to_date(1094165422) from dual;

UNIXTS_TO
---------
02-SEP-04

oracle, sql, dba, database administration, database development, unix, unix timestamp, timestamp

PL/SQL Tutorials and Reference

After some web searching I found these two resources on PL/SQL. Of course there are many others, but these two seem fairly good without being overly complicated.

Using Oracle PL/SQL from Stanford University

PL/SQL Reference & Tutorial from Elliot Spencer’s web site

While neither of these sites display the polish of a site like w3schools.com they both have great, well organized information.

For more of my favorite Oracle resources, check out my previous article on Oracle Web Resources

oracle, sql, plsql, dba, database administration, database, database design

Converting Time Zones in Oracle

In my previous article Oracle, SQL, Dates and Timestamps I talked about inserting, selecting and comparing dates in Oracle. Now I want to cover some functionality for converting between time zones.

We’ll use the table in the form specified in the previous article, but insert one more row:

insert into dates values(6, to_date('09/20/05 23:15', 'MM/DD/YY HH24:MI'));

The contents of the table now look like this:

1 09/14/05, 21:08
2 09/27/05, 00:00
3 10/02/05, 22:05
4 09/01/05, 17:01
5 09/12/05, 14:30
6 09/20/05, 23:15

Changing Time Zones

The date format in Oracle does not contain time zone information, but the database does. To find out the time zone set, execute this query:

SELECT dbtimezone FROM dual;

DBTIME
------
-04:00

The time zone can be updated with the command:

ALTER database SET TIME_ZONE = '-05:00';

where you can specify the offset from Greenwich mean time or a valid time zone from the list in the v$timezone_names view. Note that this is one of the few of the ‘v$’ views which are plural.

Switching Time Zones

The function new_time is used to convert a time to different time zones. To illustrate this we’ll look at entry 5 from the dates file.

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 02:30 PM

This database is in US Eastern time but we want to display the time in US Central.

SELECT entry, to_char(new_time(entry_date, 'EST', 'CST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 01:30 PM

Here we clearly see the time converted to Central. Note that the new_time function is performed on the date field, not on the to_char. Now let’s grab this time in Pacific time:

SELECT entry, to_char(new_time(entry_date, 'EST', 'PST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 11:30 AM

Now we see not only the time converted, but also the time of day has gone from PM to AM.

Now let’s take a look at entry 6:

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=6;

6 09/20/05 11:15 PM

We’ll again assume this timestamp is in US Eastern time, but let’s convert it this time to Greenwich Mean Time.

SELECT entry, to_char(new_time(entry_date, 'EST', 'GMT'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=6;

6 09/21/05 04:15 AM

This shows not only the change in hours, but that the date of this entry is displayed properly for its time zone.

Of course the new_time function can be used on inserts in the same way. This is useful if you are allowing input from people in different geographical regions. Here we convert an entry made in Pacific Time to Eastern:

INSERT INTO dates
VALUES (7,
new_time(to_date('09/22/05 10:28 AM', 'MM/DD/YY HH:MI AM'), 'PST', 'EST'));

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=7;

7 09/22/05 01:28 PM

So we have converted 10:28 AM Pacific to 1:28 PM Eastern so all our entries in the table are consistent. Of course when performing the insert we need to put the to_date function within the new_time function so the text string is converted to a date format before we try to convert it.

oracle, sql, dba, database administration, database development

SQL Join Syntax

I have always found the different types of joins a bit confusing, but now thanks to a little experimenting I think I have a handle on it. This is geared toward Oracle, but most of this is ANSI SQL, so should work in other databases as well. Some of these features may not be available in Oracle pre-9i.

Assume these two tables:

SELECT * FROM faculty;

ID FIRST_NAME LAST_NAME
1 Jon Emmons
2 Zach Tirrell
3 Evelyn Stiller

SELECT * FROM class;

CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 CS3600 1
2 CS3020 2
3 CS2000  
4 CS1100 1

Inner Join

The most common (and simple) join. This will select the rows which satisfy the join condition; however if a row exists in one table but does not have a counterpart to fulfill the join condition those rows (from either table) will be ignored.

SELECT *
FROM faculty, class
WHERE faculty.id = class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
2 Zach Tirrell 2 CS3020 2
1 Jon Emmons 4 CS1100 1

Here we see the three rows where faculty.id had a match to class.instructor_id.

Left Join

A left join, a.k.a. left outer join will return all the row combinations which meet the join condition plus any rows from the first table which do not meet the guard condition.

SELECT *
FROM faculty LEFT JOIN class
ON faculty.id=class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
1 Jon Emmons 4 CS1100 1
2 Zach Tirrell 2 CS3020 2
3 Evelyn Stiller      

Now we see all the results we saw in the inner join, but we additionally see the row from faculty (id 3, Evelyn Stiller) which has no corresponding row in class.

Right Join

Also referred to as right outer join, this will show all row combinations which meet the join criteria, but will additionally show any rows from the second table which do not have counterparts in the first.

SELECT *
FROM faculty RIGHT JOIN class
ON faculty.id=class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
2 Zach Tirrell 2 CS3020 2
      3 CS2000  
1 Jon Emmons 4 CS1100 1

Now we see rows from the class table which do not have a faculty counterpart.

Outer Join

The outer join, or full outer join can be thought of a left join and right join. Rows which meet the join condition will of course be displayed, additionally, rows from both the first and second table referenced will be displayed.

SELECT *
FROM faculty FULL OUTER JOIN class
ON faculty.id = class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
1 Jon Emmons 4 CS1100 1
2 Zach Tirrell 2 CS3020 2
3 Evelyn Stiller      
      3 CS2000  

Now we see all the rows from both the tables, joined where the join condition is met, or with corresponding null values where the join condition failed.

oracle, sql, dba, database administration, database development