Oracle equivalents of MySQL commands

From what I've learned about Oracle so far, it seems that Oracle is severly lacking in commands compared to MySQL. Up until now, I've been programming in Perl and using MySQL. Now, my boss wants to move to Oracle because it's more powerful or something. Now I don't mind having to change all my code because SQL in Oracle is a little different than MySQL, but Oracle doesn't seem to have some equivalents.

MySQL has a whole lot of functions to manipulate dates (see here), but Oracle has very few. For instance, is there an Oracle equivalent of DATE_FORMAT() in MySQL? This is a command I use all the time to get the dates changed in to readable format. I don't see how all those websites with Oracle backends use it because it doesn't really have those functions to make things easier.

Also, does Oracle have an equivalent to LIMIT in MySQL? That's also a very important command that I use all the time. I'm really considering not moving from MySQL at all.

Start here for a comparison http://www.dcs.napier.ac.uk/~andrew/sql/
for the date manipulation look at http://safari.oreilly.com/main.asp?...orasql&snode=50
something similar to LIMIT is listed here http://www.arrowsent.com/oratip/tip41.htm

Oracle has at least two things which are very useful:
Subqueries
Triggers

The comparison page is very interesting. Thank you for the link. I've been looking for something like that. Thanks. I guess since I'm not subscribed I cannot view the actual documentation for date manipulation. I could only look at the index. However, I think I'm going to go to Borders and buy the book. I have an Oracle SQL book now, but it seems a little watered down and it's more of a workbook with Q & A, rather than a reference or tutorial book. I'm gonna return it. That last link is good too. I think I've read about something like that somewhere else. Why can't Oracle just have LIMIT. So with that code, I'd have to put all the main stuff in the subquery (complicated WHERE clauses and such) and just the stuff to select in the main query?

As for Oracle's useful features, I suppose subqueries are useful. I found ways to get around them in my code, but it would've been useful in one of my complicated searches. If I can get the date manipulation to work like DATE_FORMAT(), Oracle won't be so bad, I guess. I just want to be able to format the date into something like "February 19, 1984" without all this complicated mess.

Thanks for the links. You're like the link king or something.

Wow! That "or something" covers a lot of ground. Oracle is perhaps one of the most capable DMBS packages available. Even if it doesn't have the same commands as MySQL, I'm sure there are other ways of accomplishing everything you need. In fact MySQL's syntax is somewhat non-standard SQL in a lot of places.

Sorry, but to hear you dismissing Oracle in favor of MySQL, I am convinced you need to learn more about database theory in general. Spend some time reading up on the concepts of Relational Data theory, and things like views, foreign keys, subselects, data integrity constraints such as CHECK, RULE, etc... (seach for Codd's 12 rules, for starters)

Also, I don't know if Oracle has the very useful LIMIT operator in it, but PostgreSQL does. In fact, PostgreSQL has most of the high-end capabilities of Oracle, while keeping the low memory footprint and nice feature set which is more than equivalent with MySQL.

pabloj - IMHO views and foreign keys are a more important distinction than subselects or triggers. Especially views, which can radically expand your application's scope, without altering basic data structure. (MySQL already has a partial implementation of foreign keys)

Interesting note: the person responsible for the LIMIT syntax in SQL is none other than Rasmus Lerdorf, creator of PHP. he first introduced it in mSQL, and it later spread to MySQL and PostgreSQL.

rycamor you're probably right. I know Oracle probably surpasses MySQL in all of those aspects that you mentioned, but for what I want to do it seems that MySQL is better, you know? Maybe if I was moving the other way (Oracle - MySQL), I'd see all the things that MySQL was lacking, but right now it just seems as if Oracle is lacking in the things I need.

Going back to your date question, this is how you change the date to a format that suits you

TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.');

Dont be discouraged, once you have used Oracle for just a short time you will come to learn how good it can be. One thing I have found very useful with Oracle is the tuning of your SQL.

wabirdman

So if I had dates in a column called subdate, I wanted it in that format, I'd do:

Where can I find information on this on the web? If what I did above is possible then, I think I will make the move to Oracle. Thanks for the info, wabirdman.

The format is like this

TO_DATE('char' , 'fmt')

char is either varchar or char datatype
fmt is the date format.

what this is doing is changing the format in to a readable date format

This is an old reference (oracle 7) but it will still be valid for you

http://info-it.umsystem.edu/oracle/...vslr.1.toc.html

wabirdman

Thanks for the link it actually helped. What you were giving me was backward from what I wanted. I wanted to go from an Oracle date to a formated character version of the date. Something like:

which is kinda like what I had except it was supposed to be TO_CHAR. This works just like DATE_FORMAT. Now all I need is a resource that'll show me what all the things mean. Like "Month" will give you "February", "YYYY" will give you "2002", etc.

Thanks again. You've been a really big help.

Glad to have helped.

I am not sure if you know this or not, but standard date format in Oracle

is

25-JAN-2002 DD-MON-YYYY

Good luck and welcome to the world of Oracle

wabirdman

Yeah, I had asked a little while back. It seems that Oracle is not so bad after all.

I'm glad that you are seeing the light

wabirdman

Rycamor, you are right, I didn't mention foreign keys as I think that MySQL 4.0.2 has them, and views (and snapshots) because they are more complex for an oracle beginner.