Does runtime processing of stored procedure parameters vary between Oracle releases?

From: Eric Chevalier <etech_at_tulsagrammer.com>
Date: Mon, 23 Jul 2007 18:34:43 -0500
Message-ID: <e0daa3ttve2b1qbr1k1lah5nqn1vsdnm82@4ax.com>

I work for an ISV that acquired several applications last year from another firm (call it "foobar") which wanted to concentrate on its "core competencies". One of those applications is designed to work with either Oracle or MS SQL Server.

During the installation of this product, a script is run to create a number of stored procedures. This script includes:

    CREATE OR REPLACE PACKAGE NS_TYPES AS         TYPE RCT IS REF CURSOR;
    END NS_TYPES;          CREATE OR REPLACE PROCEDURE nsGetGroups(RC1 IN OUT NS_TYPES.RCT) as     BEGIN

      OPEN RC1 FOR
      SELECT groupid, groupname, grouptype, grouprivate, groupdesc
      FROM nsgroup ORDER BY groupid;

    END nsGetGroups;

The application itself is written in C++/MFC. At runtime, the nsGetGroups function is called by the code:

    ret = SQLExecDirect(hStmt2, "{call nsGetGroups()}", SQL_NTS);

When the application is executed against Oracle 10g, the call returns the error:

    ORA-06550: line 1, column 8:.PLS-00306: wrong number or types of

               arguments in call to 'NSGETGROUPS'.     ORA-06550: line 1, column 8:.PL/SQL: Statement ignored."

I can certainly understand the error; the stored procedure is called with no parameters. However, before we acquired the product, "foobar" had sold a copy of the application to a customer running Oracle 8 (and later Oracle 9). The customer appears to be running the application successfully, and "foobar" claims that the source code we recieved is the same source code that was used to build the application as delivered to the customer. This puzzles me, because I would assume that the processing of stored procedures at runtime is pretty much the same from release to release of Oracle. I would *expect* the customer to be getting the same error that I'm getting.

Since I don't have ready access to earlier versions of Oracle, my question for the group: would Oracle 8 or 9 have somehow allowed a call to the nsGetGroups stored procedure to proceed without the REF CURSOR paramater supplied by the caller? (I'd be surprised if the answer is anything other than "no".)

(There is another possibility: "foobar" may *not* have sent us the proper code. We've already identified other source files which were updated by "foobar" but never checked into the source code repository that was sent to us. But that problem is a little outside the scope of this newsgroup!)

Eric

--
Eric Chevalier                          E-mail: etech_at_tulsagrammer.com
                                           Web: www.tulsagrammer.com
    Is that call really worth your child's life?  HANG UP AND DRIVE!

Received on Mon Jul 23 2007 - 18:34:43 CDT