1 Reply Latest reply on Apr 27, 2002 11:36 PM by 82532

    How to get a recordset in vb to access a oracle store procedure.

    87302
      I have a store procedure(sproc) that needs to be called from a VB Application. I was being told that I would have to create a PL/SQL table in my store procedure for the VB Developer to access information from my store procedure. This is beacasue he wants it as a record set. Is that the way to get info for a VB front end. My sproc is a very basic one

      My sproc looks like

      create or replace PROCEDURE SP_TBLINFO
      (ID out TBLINFO.id%type,
      ACCOUNT_NUMBER out TBLINFO.account_number%type)
      AS
      CURSOR TBLINFO_CURSOR IS
      SELECT * FROM TBLINFO;
      V_TBLINFO_CURSOR TBLINFO_CURSOR%ROWTYPE;

      BEGIN

      OPEN TBLINFO_CURSOR ;
      Loop
      FETCH TBLINFO_CURSOR INTO V_TBLINFO_CURSOR;
      EXIT WHEN TBLINFO_CURSOR %NOTFOUND;
      ID := V_TBLINFO_CURSOR.id;
      ACCOUNT_NUMBER:= V_TBLINFO_CURSOR.account_number;
      END LOOP;
      CLOSE TBLINFO_CURSOR ;

      END SP_TBLINFO;


      Thanks.
        • 1. re:How to get a recordset in vb to access a oracle store procedure.
          82532
          Use a REF_CURSOR, something like the following:

          create or replace package SP_TBLINFO as
          CURSOR TBLINFO_CURSOR IS SELECT * FROM TBLINFO;
          create type TBLINFO_CURSOR_TYPE is TBLINFO_CURSOR%ROWTYPE;
          procedure PROC_TBLINFO(Info out TBLINFO_CURSOR_TYPE);
          end;

          create or replace package body SP_TBLINFO as
          PROCEDURE PROC_TBLINFO
          (Info out TBLINFO_CURSOR_TYPE)
          AS

          BEGIN
          OPEN ACCOUNT_NUMBER as SELECT * FROM TBLINFO ;
          END SP_TBLINFO;

          END;

          Then bind it in OO4O as ORATYPE_CURSOR.