Re: Access 2.0 vs. Oracle stored procedure

From: Dave Lindquist <lindquid_at_cadvision.com>
Date: 1996/10/27
Message-ID: <327427ED.18E2_at_cadvision.com>#1/1

Morten Kristiansen wrote:
> I am trying to use "pass through" to call my stored procedure (SP) in
> Oracle. My call looks like this:
>
> DirektDB.Execute Q, DB_SQLPASSTHROUGH (where Q is the SP-call)
>
> This works fine as long as the SP only has input parameters, but when I
> defines some output parameters, I get an error message saying:
>
> PLS-00363: expression '0' cannot be used as an assignment target
>
> It seems like I'm sending the value 0 as an parameter, and I do don't I.
> Because I have defined a variable in the subroutine, and that parameter
> hasn't been set to any value yet, therefore 0, right ?
>
> In C/C++ I should send the address of the parameter (pointer), but how
> can I do something like that in MS-access 2.0 ?? If you got any
> suggestions, I would be very pleased to get them.

Unfortunately, the DB.Execute method does not support the call returning any
values, and certainly does not know about stored procedures.

What you _can_ do is create a temporary table to write the results of your
stored procedure to.

Sample code (would be in your variable 'Q'):

DECLARE
    vResult1 Number;
    vResult2 Varchar2;
BEGIN
    MyStoredProcedure(vResult1,vResult2);     INSERT INTO

        MyTempResultTable
        (Result1, Result2)
    VALUES
        vResult1, vResult2;

END; If your procedure only needs to return _1_ value, and does not perform any database updates, then you could instead create a stored _function_, which you would call by creating a SELECT query like so:

SELECT
    MyStoredFunction(parameters...)
FROM
    Dual

Hope this helps :-)

-- 
Dave Lindquist           <mailto:lindquid_at_cadvision.com>

Royalty free licence to read this message is freely available
to anyone except representatives or employees of Microsoft.
Microsoft may purchase non-exclusive licence to view this
message by paying the sum of $1,000,000 US to the author.  By
reading this message you have agreed to be bound by these terms.

Received on Sun Oct 27 1996 - 00:00:00 CEST