Home » Open Source » MySQL » mysql vet -> oracle newbie
mysql vet -> oracle newbie [message #170669] Thu, 04 May 2006 13:45 Go to next message
chovy
Messages: 6
Registered: May 2006
Junior Member
Any guides on the basic differences between mysql and oracle?

I've noticed quite a few already, with basic sql instructions.

for example mysql's AUTO_INCREMENT column type is not to be found in Oracle.


Re: mysql vet -> oracle newbie [message #170671 is a reply to message #170669] Thu, 04 May 2006 14:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Best to read the Oracle Concepts Guide. In the sticky (first post in this forum) is a link to the documentation.
If you are a vet in any kind of sql, that would be the place to start. It explains the basics of Oracle (not SQL, but the way Oracle works).

Please do not hesitate to ask more (more specific Wink) questions might they arise.

hth
Re: mysql vet -> oracle newbie [message #170672 is a reply to message #170669] Thu, 04 May 2006 14:20 Go to previous messageGo to next message
chovy
Messages: 6
Registered: May 2006
Junior Member
thanks, i just found the searchable docs here: http://www.orafaq.com/links/Technical_Resources/Manuals/


...although, I'm now trying to simulate mysql's autoincrement...found a good example here:

http://jen.fluxcapacitor.net/geek/autoincr.html


How do I view/edit an existing trigger or sequence?
Re: mysql vet -> oracle newbie [message #170673 is a reply to message #170672] Thu, 04 May 2006 14:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Best way would be to use a tool like sql developer

Re: mysql vet -> oracle newbie [message #170674 is a reply to message #170669] Thu, 04 May 2006 14:30 Go to previous messageGo to next message
chovy
Messages: 6
Registered: May 2006
Junior Member
thanks.

Do i really have to create a trigger for each table i want an incremental index on?
Re: mysql vet -> oracle newbie [message #170675 is a reply to message #170673] Thu, 04 May 2006 14:34 Go to previous messageGo to next message
chovy
Messages: 6
Registered: May 2006
Junior Member
Frank wrote on Thu, 04 May 2006 12:22

Best way would be to use a tool like sql developer




only v9+ is supported by sql developer Sad
Re: mysql vet -> oracle newbie [message #170681 is a reply to message #170674] Thu, 04 May 2006 15:36 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
If you want to have an auto-increment field perform exactly the same as your mySQL, you'll need a trigger to automatically add a value when you insert data into the table.

Oracle triggers are valuable tools as they let you a wide variety of things based on changes to the table you've attached the trigger to. In addition to auto-increment fields, I've used triggers to copy an old record to a history table prior to making an update to the information.

If you're willing to make some changes to the SQL code from your mySQL version to an Oracle version, you can always use "<sequence_name>.NEXTVAL" in your insert statement. Thus, if you have a table with the columns SEQUENCE, DATA1, DATA2, in mySQL, the insert statement is insert into TABLE (DATA1, DATA2) values ('some text','some name'). Oracle would be insert into TABLE (SEQUENCE, DATA1, DATA2) values (table_seq.NEXTVAL, 'some text','some name').

The good thing about using an Oracle sequence is that you don't have to start with 1 every time.

Hope this helps,
Ron
Re: mysql vet -> oracle newbie [message #170729 is a reply to message #170674] Fri, 05 May 2006 00:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
chovy wrote on Thu, 04 May 2006 21:30

thanks.

Do i really have to create a trigger for each table i want an incremental index on?

I'm afraid so. Also, take into consideration that a sequence is not gap-free. An auto-incremented ID-column should not have a requirement to be gap-free, but it is something a lot of new (Oracle-)users wonder and complain about.
Re: mysql vet -> oracle newbie [message #170891 is a reply to message #170675] Fri, 05 May 2006 16:31 Go to previous messageGo to next message
Littlefoot
Messages: 21575
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
chovy wrote on Thu, 04 May 2006 21:34

only v9+ is supported by sql developer Sad
Lower Oracle versions are not longer supported by Oracle corp. (although there are still many Oracle 8 databases in use worldwide; I know several 7 databases, and probably there are others too). As you are a newbie in Oracle world and the lowest downloadable version is 9i, why would you be concerned in a fact that SQL Developer supports Oracle 9+ only?
Re: mysql vet -> oracle newbie [message #170894 is a reply to message #170891] Fri, 05 May 2006 17:01 Go to previous messageGo to next message
chovy
Messages: 6
Registered: May 2006
Junior Member
i'm using an earlier version of oracle. i suspect it's irrelevant though unless sequence syntax has changed.

yes, i'm new to oracle.

one thing i don't know is how to format the results of a query, tabs, etc.
Re: mysql vet -> oracle newbie [message #170896 is a reply to message #170894] Fri, 05 May 2006 17:20 Go to previous message
Littlefoot
Messages: 21575
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are other GUI products (such as TOAD) too; however, SQL Developer is free (so far).

Read more about formatting query results in SQL*Plus User's Guide and Reference.
Previous Topic: MYSQL to Oracle SQL conversion
Next Topic: Will MySQL kill Oracle?
Goto Forum:
  


Current Time: Fri Jul 10 05:33:48 CDT 2020