Re: Which one is better? Oracel 9i or DB2 7.2??

From: The Nomad <nobody_at_nowhere.com>
Date: Fri, 03 May 2002 16:08:34 GMT
Message-ID: <6CyA8.386863$K52.62462183@typhoon.tampabay.rr.com>

> Your review seems pretty accurate though I do not know the other products,
in
> the versions you used, well enough to comment on what you wrote. But your
> information on Oracle makes me wonder about its source:
>
> 1. With respect to modifying a table with a trigger on that table: Why
didn't
> you just put PRAGMA AUTONOMOUS_TRANSACTION between the trigger
declaration and
> the word BEGIN.

My statements are with regards to 8i. The documentation was very clear on the topic of mutating tables and the fact that you couldn't do this. That and the fact that the trigger that I created (which worked for DB2 and MSSQL) caused the mutating table exception I mentioned. I never saw the PRAGMA you mentioned. Is this a 9i feature, or did I overlook it? I'd love to be able to avoid trigger crap I currently do in Oracle.

> 2. Result sets can be passed into and out of Oracle procedures with REF
> CURSORS, PL/SQL tables, and arrays. Why did would think there was
something you
> could return from a function that you could not return from a procedure?

A stored function gets called like this (from JDBC)

{? = call somefunc()}

A stored procedure gets called like this (from JDBC)

{call someproc()}

With MSSQL and DB2, stored procedures that "return" a resultset are called just like you were executing SQL. With Oracle 8i, you must call a stored function, and decode the return result as an Oracle cursor before you can get the resultset out of it. As a quick example:

MSSQL

---

CREATE PROCEDURE SOMEPROC
AS
 SELECT * FROM SOMETABLE ORDER BY SOMEFIELD GO

DB2
===
CREATE PROCEDURE SOMEPROC()
RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE cur CURSOR WITH RETURN FOR
    SELECT * FROM SOMETABLE ORDER BY SOMEFIELD;   OPEN CUR;
END ORACLE 8i

---

CREATE OR REPLACE FUNCTION SOMEPROC RETURN CTYPES.SOMETABLECURSORTYPE AS   c1 CTYPES.SOMETABLECURSORTYPE;
BEGIN
  OPEN c1 FOR SELECT * FROM SOMETABLE ORDER BY SOMEFIELD;   RETURN c1;
END; The call from JDBC to get the results here are very different:

MSSQL/DB2

---

import java.sql.*;
... more standard imports and other stuff here...

private void testSomeProc() {
  String someFunc = "{call SOMEPROC}";

     try {
      Connection con = aPool.getPooledConnection(driverName);
        try {
          CallableStatement stmt = con.prepareCall(someFunc);
          try {
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
              try {
                // do stuff here
              } finally {
                rs.close();
              }
            }
          } finally {
            stmt.close();
          }
      } finally {
        con.close();
      }
    } catch (Exception ex) {
        ex.printStackTrace();

    }
}

Oracle 8i

---

import java.sql.*;
import oracle.jdbc.*; // Need Oracle for OracleTypes ... more standard imports and other stuff here...

private void testSomeProc() {
  String someFunc = "{? = call SOMEPROC}";

     try {
      Connection con = aPool.getPooledConnection(driverName);
        try {
          CallableStatement stmt = con.prepareCall(someFunc);
          try {
            stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
            stmt.execute();
            ResultSet rs = (ResultSet)stmt.getObject(1);
            if (rs != null) {
              try {
                // do stuff here
              } finally {
                rs.close();
              }
            }
          } finally {
            stmt.close();
          }
      } finally {
        con.close();
      }
    } catch (Exception ex) {
        ex.printStackTrace();

    }
}

> 3. What tool were you using to create schemas where you couldn't see
compilation
> errors? You don't say. I don't know anyone that uses a GUI tool for schema
> creation that doesn't work better than what you describe.

It's been a while, but I believe it was either the DBA Studio or the Enterprise Management console. But, since it didn't work for me, I abandoned it for SQL Plus.

Marc Received on Fri May 03 2002 - 11:08:34 CDT