As we all know, the ANSI SQL standard is now the computer equivalent of English. Its claimed to be universal, major databases claim to speak it; but alas much like English has now splintered into several derivative langauges, SQL has many dialects.
I recently hit this issue when I tried to migrate a simple application from using MySQL to Oracle. Yes I know its going backwards, the reasons for the move were beyond my control. This application made the simplest of simple calls to the database, e.g.
INSERT INTO TABLE1(date, username)
VALUES ("2009-01-01", "fred");
Yes its that basic. One would assume that this would work on all major databases that claim to speak SQL, but you would be wrong. Running this on Oracle (using the thin client) produced an error. Aparently the ; at the end of the statement is not recognised. Hmm… Ever seen the ANSI SQL-92 standard Oracle? So remove it and we move to iteration 1 of the conversion to Oracle
INSERT INTO TABLE1(date, username)
VALUES ("2009-01-01", "fred")
Does it run? No.
What’s wrong this time? The date. Lets see, the date is in ISO 8601 format, so it must need something else. Aha, the TO_DATE function, ok
INSERT INTO TABLE1(date, username)
VALUES (TO_DATE("2009-01-01"), "fred")
Invalid month.
What? 01 is an invalid month? January anyone? Revert to google. Ok, different format for dates. Seems like ISO 8601 dates are not welcome in the land of Oracle.
INSERT INTO TABLE1(date, username)
VALUES (TO_DATE("01-JAN-2009"), "fred")
Success! Unbelievably 3 iterations of this simple SQL statement were required to get it to work on Oracle, when it was previously working on MySQL.
I’ve left out the other example which made use of an autoincrement column in MySQL. Selecting from a sequence from some table named DUAL just makes no sense. Why DUAL? How did the Oracle guys come up with this? It breaks my heart.
Seriously database people, this is the equivalent of me going up to the Queen of England and saying “I CAN HAS CHEEZBURGER?”
2 Responses
Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.
“The great thing about standards is that there are so many to choose from”.
Our product supports multiple database engines out of the box and we hit incompatibilities all the time. We tackle it partially by defining our schema in XML then using XSLT in the build/install process to create engine specific scripts. Some parts we can’t or don’t automate this way though, and those still have to be hand tuned for each engine … I had to navigate the Oracle date formatting maze myself not so long ago.
Dude … take a chillax pill ! …what client are you using … use toad (if you can get a license) … SQL PLUS is fine for me running in the commands using the semi-colon …
no probs …
you have created a table with the table with a column name called ‘date’ … date being a oracle keyword, the database decides to poop itself … ORA-00928s?
SQL> create table TABLE1 (mydate date, myusername varchar2(20));
SQL> INSERT INTO TABLE1(mydate, myusername)
VALUES (’10-jan-2008′, ‘fred’);
blam … no probs … or as the kids would say … brappp brappp brappp
Default string format for date fields is DD-MON-YY
you can use the to_date and to_char functions, which provide support for this ISO-8001 one thing :
select to_char(sysdate,’YYYY-MM-DD’) from dual;
Oracle stores its date data in a special format, and returns data through the to_char function, defaulting the format of the string returned to DD-MON-YYYY …
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html
Also askTom ftw..