Oracle Database Differences

If you come from an OpenSource database background (MySql/PostgreSql), there will be certain things that require workarounds for use with OracleDatabases.

Here's the list so far:

---

If you don't like:

• SQL*Plus - Ugh. Maybe this thing is handy once you're used to it, but it's certainly not intuitive for new users.

Try:

• fsql - at http://www.oopdreams.com/frank/fsql - Very similar to mysql's shell (readline and all!)
• perl/DBI's dbish - Also readline'd, however, slightly more geared towards programatic use rather than interactive use (for example, the default output format is comma-separated values)
• TOAD (ToadTool -- the Tool for Oracle Application Developers) -- http://www.toadsoft.com/'' (JeffGrigg highly recommends this)

---

If you wish you could:

  select * from foo
  limit 10

Instead, do this:

  select * from foo
  where rownum <= 10

(SQL is designed to give you complete result sets. Automatic truncation at some particular size tends to be a non-standard extension. In fact, the above query returns the first 10 rows of the table foo in an arbitrary and not always predictable order.)

In Oracle8i, release 8.1 and up, you can use order by in a subquery. Therefore, to return the top two salaries of each department, using the class oracle table EMP:

 select sal, deptno
 from ( select sal, deptno
           from emp
          order by SAL desc )
 where rownum <= 2
 order by deptno, sal

See also: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:495221712170 for more discussion on "limit" type queries using modern versions of Oracle databases.

---

If you wish you could:

• Declare an autoincrementing column (that is, just simply "insert" and have a key, for example, automatically created).

Instead, do this:

• Create a sequence and trigger explicitly.

Automatic generation of system assigned values is not standardized across databases. In Oracle, you should use "sequence number generators." You'll create them independently of the tables and use them in the INSERT statements. Typically, you'll follow an INSERT with a "SELECT <seqnogen>.CURVAL FROM DUAL" to get the system assigned primary key value it assigned.''

Or use a trigger, with the INSERT statement using the RETURNING option. See also: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:500421805606