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

This tutorial from the Oracle Technology Network outline the use of the three types of variables available in SQLPlus. Bind variables, substitution variables and system variables are all explained in this succinct yet thorough tutorial. The best I’ve seen on the subject.

SQL*Plus Substitution Variables from Oracle Technology Network.

For information on other Oracle web resources, check out my other article on the topic.

oracle, dba, database, database administrator, rdbms, database development, database design

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

With the growing popularity of distance education (The University of Phoenix claims to be the largest accredited school in the US) I wonder how long it will be until you can “outsource” your class work.

Several schools offer distance education in a model which is designed to accommodate students who never set foot on a campus. While this is compelling to the university, who does not have to provide a classroom, lights, heat, parking, and it is compelling to the student who is freed from the restrictions of class times and travel, does this model open the door to a new form of cheating?

An industrious company based somewhere that labor is cheap, could offer a “tutoring” service for online education. If someone had the means (price of enrollment plus a couple hundred dollars per course), they could enroll in an online course for their undergraduate, masters, or even doctoral degree and pass off their username and password to a proxy scholar. The proxy scholar then would complete the class work from anywhere in the world, and the class is applied to the degree of the purchaser.

Sound far fetched? In the game industry “farming” is common practice. Farming is when players play the game, by the rules, and once they acquire items within the game they sell them for real money! To avoid the problems with selling something that is intellectual property of the game producers, farmers sell their time invested in obtaining the item, but essentially the purchaser is buying the item. For more information on this, check out borkweb.com’s article World of Warcrack and the future of MMOGs

Perhaps more concerning is this example of three teachers at a technical college in Georgia who are being accused of this exact crime. While it is currently unclear if money changed hands, it is believed that another individual not associated with the college completed course work under the name of these three teachers.

So how can colleges combat these “proxy scholars?” Of course there are academic integrity policies in place, but they are unlikely to detour the student looking for the easy way out. PKI solutions and biometrics have their own scary side-effects and anyone who thinks they can’t be forged is not paying attention.

I am afraid we will only hear more about this type of fraud. Hopefully institutions offering distance education can develop a way to mitigate this outsourcing of class work before it devalues the online work that so many students are legitimately achieving.

Why won’t my database start when I send the dbstart command?

There is a known bug with the dbstart command on Oracle 9i (and possibly later) servers with databases with server parameter files (spfiles). This bug will cause your database not to start if there is no pfile (initSID.ora) in the default location. You will typically see the error below, followed by the sound of users whining.

bash-2.05$ dbstart

Can't find init file for Database "ORADB".
Database "ORADB" NOT started.

While there are a few options to remedy this, here are my two favorite.

Solution 1: Create a dummy pfile

I like to create a dummy pfile in the default location ($ORACLE_HOME/dbs/init$ORACLE_SID.ora) that contains only the following:

################################################################################
# This database uses an SPFILE to acquire it's parameters for startup.
# The spfile is located at $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
# This init file is here only as a placeholder so dbstart and dbshut
# work normally. For more info on spfiles see
# http://www.lifeaftercoffee.com/?p=76
# JE 1/6/2005
################################################################################

With this in place (for each database) the dbstart command works normally.

Solution 2: Create a pfile from the spfile

If you connect to the database, running or not, and execute the SQL command CREATE pfile FROM spfile; a pfile will be created in the default location. On startup the pfile will be ignored as long as there is an spfile. This allows dbstart to behave normally and also gives you a backup of the spfile; however this can become confusing and the pfile will not be kept up to date by the database.

For more information on spfiles and pfiles see my article Oracle pfile and spfile for parameters.

oracle, dba, database administration, database

« Previous PageNext Page »