Oracle Procedure Call Using Visual Basic

[Creepers]

This may be a two part question. Part one for this Forum and Part 2 for VB Forum....Here we go

I am tring to write a procedure or function in an Oracle package that will return a recordset (cursor?) which can be retrieved by Visual Basic. Can someone supply an example of a procedure or function that will bring back a recordset (cursor?) which then can be fed into a Visual Basic (ADO) recordset.

Thanks

 

[gunjan14]

Hi,

what is the interface you are using. If you are using ODBC then I do not think you can do this. But if you are using OO4O then there is a way...

Gunjan

 

[Creepers]

Not ODBC. Using ADO. Do not understand OO4O.

 

[gunjan14]

OO4O stands for OLE Objects for Oracle.
Anyway its better to be asked in VB forum.....

 

[sem]

This is quite straightforward:
Suppose you have a procedure proc with out parameter of REF CURSOR type.

Dim rstProc As ADODB.Recordset
Dim cmdProc As ADODB.Command

' Create connection etc

Set cmdProc = New ADODB.Command
cmdProc.CommandText = "proc"
cmdProc.CommandType = adCmdStoredProc

' Set input parameters etc

Set rstProc = cmdProc.Execute

Voila, you have a recordset!

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

Hi,

You may be using ADO but how are you configuring the connection to DB. It must be via ODBC.
Anyway here is the code through which you can use to fetch the cursor back. Remember, that cursor needs to be defined in Procedure as IN OUT parameter

Public Function FetchBranch(Brcode As string,_
                            Brname as string,_
                            Bradd as string) As Boolean
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As Object
Dim llngcnt As Long

    On Error GoTo ErrorHandler
    
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase ("orcl", "scott/tiger", ORADB_DEFAULT)
    OraDatabase.Parameters.Add "BranchCode", "Init", ORAPARM_INPUT
    OraDatabase.Parameters("BranchCode").serverType = ORATYPE_VARCHAR
    OraDatabase.Parameters("BranchCode").Value = Brcode
    
    Set OraDynaset = OraDatabase.CreatePlsqlDynaset _
                   ("BEGIN GunjBankPackage.Fetch_Branch_Detail(:BranchCode, :BranchDtl); End;", _
                    "BranchDtl", 0&)
    If Not (OraDynaset.BOF And OraDynaset.EOF) Then
        llngcnt = 1
        OraDynaset.MoveFirst
        While Not OraDynaset.EOF
            Brcode = OraDynaset.Fields(0).Value
            Brname = OraDynaset.Fields(1).Value
            If Not IsNull(OraDynaset.Fields(2).Value) Then
                Bradd = OraDynaset.Fields(2).Value
            Else
                BrAdd = ""
            End If
            OraDynaset.MoveNext
        Wend
        FetchBranch = True
    Else
        FetchBranch = False
    End If
    
    Set OraDynaset = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    
    Exit Function
    
ErrorHandler:
    FetchBranch = False
    Set OraDynaset = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
End Function

Now your PL/SQL code looks something like this. It is inside package

create or replace PACKAGE GunjBankPackage
as

  -- General purpose cursor
  TYPE ret_cv_gen is REF CURSOR;

  PROCEDURE Fetch_Branch_Detail
  (
      pmBranchCode IN GUNJBRANCH.BRAN_BRANCHCODE%Type ,
      BranchRet IN OUT ret_cv_gen
  );
end gunjbankpackage;
/

create or replace PACKAGE BODY GunjBankPackage
as
  PROCEDURE Fetch_Branch_Detail
  (
    pmBranchCode IN GUNJBRANCH.BRAN_BRANCHCODE%Type,
    BranchRet IN OUT ret_cv_gen
  )
  IS
  BEGIN
      if BranchRet%ISOPEN
      then
          close BranchRet;
      end if;

      open BranchRet for
      select a.BRAN_BRANCHCODE, 
             a.BRAN_BRANCHNAME, 
             a.BRAN_BRANCHADD.ADD_HOUSENO,
             a.BRAN_BRANCHADD.ADD_STREET, 
             a.BRAN_BRANCHADD.ADD_LOCATION,
             a.BRAN_BRANCHADD.ADD_CITY,
             a.BRAN_BRANCHADD.ADD_PIN,
             a.BRAN_BRANCHADD.ADD_STATE, 
             b.STATENAME, 
             a.BRAN_BRANCHCNTRY,
             c.CNTRYNAME, 
             a.BRAN_BRANCHASSETS, 
             a.BRAN_BRANCHHEAD, 
             a.BRAN_BRANCHDESG
      from   GUNJBRANCH a, 
             GUNJBANKSTATE b, 
             GUNJBANKCOUNTRY c
      where  a.BRAN_BRANCHCODE = pmBranchCode
      and    b.STATECODE = a.BRAN_BRANCHADD.ADD_STATE
      and    c.CNTRYCODE = a.BRAN_BRANCHCNTRY;

  EXCEPTION
      when NO_DATA_FOUND then
          Null;
  END Fetch_Branch_Detail;

This works (I think) with only Oracle 8i and above so if you have oracle version below 8i then it will not be supported.

Hope this helps
Gunjan