Stored Oracle Procedures, Parameters & ODBC API workaround

From: <rschauer_at_ix.netcom.com>
Date: Sat, 29 Aug 1998 06:26:16 GMT
Message-ID: <6s86u8$iru$1@nnrp1.dejanews.com>

I recently ran into some of the same problems I've read on several forums that deal with getting a parameter value back to a VB 4.0/5.0 application that is calling a stored Oracle procedure. Well I stumbled onto a solution that allows you to call a stored procedure and get back return parameter value(s) without having to use ODBC API calls. You could use regular jet engine calls, such as opendatabase, execute, and openrecordsets.

You do need to have Oracle 7.1 or higher to do this trick. What you need to do is have an Oracle package that contains a package variable that holds the return value and a function that reads this variable and returns the package variable's value. You can execute the procedure with an execute method using dbSQLPassThrough to pass the procedure the in parameters. The procedure then does its logic, and prior to returning it assigns the output parameter value to this package variable. The next VB statement then does a select statement to get the return value. It's a "select package.ret_func from dual" select statement. This is why you need a Oracle version 7.1 or later. This method does require two separate calls to Oracle, but I don't think the overhead is too much to invoke an Oracle function to return a package variable. That package variable should be in the Global Shared pool area in memory on the Oracle server, so performance should be fast. Here's an pseudo-code (exact and correct syntax is not guaranteed):

VB code

  mydatabase.execute "BEGIN My_Pkg.Do_Something('First','Second','Third'); END;", dbsqlpassthrough

  set myrecordset = mydatabase.openrecordset("Select my_pkg.get_rc from dual"); /* Get the return value as any recordset field in an ordinary select statment... You can even treat it as a snapshot. */

Oracle PL/SQL code:

Create or Replace Package My_Pkg AS

  rc number;

  PROCEDURE DO_SOMETHING (param1 IN varchar2,

                          param2 IN varchar2,
                          Param3 IN varchar2);

  FUNCTION GET_RC AS NUMBER;   PRAGMA RESTRICT_REFERENCES(GET_RC, WNDS); /* WNDS = Write No Database State */ /* if you don't do the pragma restrict references, Oracle will tell you you can't put the user function in a select statement */

END My_Pkg;

Create or replace Package body My_pkg as

  PROCEDURE Do_Something(param1 IN varchar2,

                         param2 IN varchar2,
                         param3 IN varchar3) AS
    BEGIN
      /* what ever it does, put it here */
      IF successful THEN
        My_Pkg.rc := 1;
      ELSE
        My_Pkg.rc := 0;
      END IF;

    END Do_Something;

  FUNCTION Get_RC RETURN NUMBER AS
    BEGIN
      RETURN My_Pkg.rc;
    END Get_Rc;
END; End of PL/SQL code.

Note that the package variable, My_Pkg.rc will hold this value for the length of your Oracle session, no other Oracle session can modify the value or see it. Of course if you call the procedure again it will change the value.

So if you don't want to learn ODBC API or spend hours coding 'low-level' ODBC API routines, then you can use this method.

Enjoy.
Rick

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Sat Aug 29 1998 - 01:26:16 CDT