v6/v7 differences - let's share the undocumented ones

In article <93298.141657SCUNNANE_at_estec.bitnet>, <SCUNNANE_at_ESTEC.BITNET> wrote:
>Does anyone know where I can get my hands on a book/document/whatever
>detailing the differences between v6 & v7 ?

    Unfortunately, the Oracle documentation does not do an adequate job of describing the differences. I have found a couple of undocumented differences (one of which is still an open TAR; last week I sent Oracle tech support a simple example program demonstrating the behavior difference but no word yet on whether it's a bug or a feature). I'd like to encourage people to share any findings they have in this area. If you email me, I'll try to eliminate duplicates and summarize.

    The two undocumented differences I have found (Oracle 7.0.12, HP-UX):

• The outer join operator can no longer appear in an OR clause or the IN operator; there is a new error message (ORA-01719) specifically rejecting such statements. Example:

    select ... from t1, t2
    where t1.foo = t2.foo (+)
      or t1.foo = t2.bar (+)

worked in v6 but gives an error on line 3 in v7. Workaround:

    select ... from t1, t2
    where t1.foo = t2.foo (+)
    union ... where t1.foo = t2.bar (+)  

Because UNION does an implicit DISTINCT, you may need to add something to the select list to preserve identical select list value sets if that was a possibility in your original query.

    The other difference involves OCI and PL/SQL. It is currently open TAR 104077.333; I'll post a followup when I get an answer from Oracle.

    The capsule description is that if you declare a cursor in a PL/SQL block, open it, fetch a row, then leave the block without closing the PL/SQL cursor, a second oexec() on the 3GL program cursor without a second parse (via osql3() or the new V7 oparse() call) produces different results in V7 than under V6. In V6, the PL/SQL cursor will produce the same result (assuming the table didn't change, of course) each time. In V7, the PL/SQL cursor appears to persist across oexec() calls; the PL/SQL fetch on the second oexec() retrieves the row that would have been returned by a second PL/SQL fetch executed during the first oexec() call. The workaround is to make sure you close all your PL/SQL cursors. This may involve some work with exception handlers, e.g.

    when others then

        if curs1%ISOPEN then
            close curs1; ...

While there are some benefits to the new behavior, it can cause previously working programs to produce erroneous results. I don't plan to try to take advantage of it unless Oracle declares it a feature, not a bug.

    The program below has been trimmed to reduce bandwidth, but in the original, there was rigorous error checking of all return codes, and that is not the problem. Note that it does not help to add

		if foo%ISOPEN then
		    close foo;
		end if;

just before the 'open foo;' line. However, adding 'close foo;' just after the fetch will produce the same behavior from both V6 and V7. Whether compiled with the V6 or the V7 libraries, when logged on to a V6 database, the two printf() statements will print 'FIRST' twice, while a V7 connection will return 'FIRST' and then 'SECOND'.

/* Table TESTPERSIST has one column, SOMETHING. There are two rows,

   values FIRST and SECOND */

    static char sql_stmt[] =
"declare\n\

    cursor foo is select something from testpersist\n\

        order by something;\n\
 begin\n\

    open foo;\n\
    fetch foo into :DATA;\n\
 end;\n";

    char data[17];
    struct csrdef mycurs, lda; /* Oracle cursor data area */     char hda[256];

    orlon( ... )
    oopen( &mycurs, &lda ... )

/* Parse with osql3 (for backward portability), but results are the same

   with v7 oparse(). */
    osql3( &mycurs, sql_stmt, -1 );

    obndrv( &mycurs, ":DATA", -1, data, 17, 1, -1,

                        (short *) -1, (char *) -1, -1, -1 );
/* Execute the PL/SQL block once */

    oexec( &mycurs );
    (void) printf( "First value = %s\n", data );

/* Execute it again without closing any cursors */

    oexec( &mycurs );
    (void) printf( "Second value = %s\n", data );

-- 
Mike Stein			The above represents the Absolute Truth.
POB 10420			Therefore it cannot possibly be the official
Arlington, VA  22210		position of my employer.