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

Eric Chevalier wrote:
> 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!

First impression is that they may well believe the source code is the same: But I'd have some serious doubts. I'd suggest finding out by pulling the metadata from both and comparing.

SELECT text
FROM user_source
WHERE name = <proc_name>
ORDER BY line;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org