#1
  1. The Basketball Star
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2001
    Location
    H-Town
    Posts
    550
    Rep Power
    14

    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.
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  4. #3
  5. The Basketball Star
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2001
    Location
    H-Town
    Posts
    550
    Rep Power
    14
    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.
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    ...my boss wants to move to Oracle because it's more powerful 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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. The Basketball Star
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2001
    Location
    H-Town
    Posts
    550
    Rep Power
    14
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Australia
    Posts
    111
    Rep Power
    15
    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
    Real programmers code in binary
  12. #7
  13. The Basketball Star
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2001
    Location
    H-Town
    Posts
    550
    Rep Power
    14
    So if I had dates in a column called subdate, I wanted it in that format, I'd do:
    Code:
    TO_DATE(subdate, 'Month dd, YYYY');
    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.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Australia
    Posts
    111
    Rep Power
    15
    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/s...slr.1.toc.html

    wabirdman
    Real programmers code in binary
  16. #9
  17. The Basketball Star
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2001
    Location
    H-Town
    Posts
    550
    Rep Power
    14
    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:
    Code:
    TO_CHAR(subdate,'Month dd, YYYY')
    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.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Australia
    Posts
    111
    Rep Power
    15
    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
    Real programmers code in binary
  20. #11
  21. The Basketball Star
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2001
    Location
    H-Town
    Posts
    550
    Rep Power
    14
    Yeah, I had asked a little while back. It seems that Oracle is not so bad after all.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Australia
    Posts
    111
    Rep Power
    15
    I'm glad that you are seeing the light

    wabirdman
    Real programmers code in binary
  24. #13
  25. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    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.

IMN logo majestic logo threadwatch logo seochat tools logo