Examples

The ORACLE_HOME\oo4o\codewiz\samples directory will have sample applications incorporating code generated by the wizard.  The following examples below show the generated VB code output from Oracle stored procedures using the OO4O code wizard:

Accessing a PL/SQL Stored Function with Visual Basic and Active Server Pages

Here is an example of a VB subroutine that executes a PL/SQL stored function. Below is the PL/SQL stored function:

FUNCTION GetEmpSal (inEmpno IN NUMBER)
RETURN NUMBER is
    outEmpsal NUMBER(7,2);
BEGIN
    SELECT SAL into outEmpsal from EMP WHERE EMPNO = inEmpno;
    RETURN (outEmpsal);
END;

For a VB class (*.cls) file, the generated code for the GetEmpSal stored function is shown as follows:

Public Function GETEMPSAL(INEMPNO As Variant) As Variant
    OraDatabase.Parameters.Add "INEMPNO", INEMPNO, ORAPARM_INPUT, 2
    OraDatabase.Parameters.Add "result", 0, ORAPARM_OUTPUT
    OraDatabase.Parameters("result").serverType = 2

    OraDatabase.ExecuteSQL ("declare result Number; Begin :result := EMPLOYEE.GETEMPSAL(:INEMPNO);
    end;")

    OraDatabase.Parameters.Remove "INEMPNO"
    GETEMPSAL = OraDatabase.Parameters("result").Value
    OraDatabase.Parameters.Remove "result"
End Function


In a VB class, OraDatabase appears as an attribute of the class. This attribute has to be set before any methods of the class can be invoked. For a VB file (*.bas), the generated code for the GetEmpSal stored function is the same as the VB class file, except for the function declaration:

Public Function GETEMPSAL(INEMPNO As Variant, ByRef OraDatabase As OraDatabase)

  ...

End Function

For an ASP file (*.asp), the function declaration also differs for the GetEmpSal stored function as shown as follows, but the body of the code reamins the same:

Public Function GETEMPSAL(INEMPNO, ByRef OraDatabase)

  ...

End Function

Accessing a PL/SQL Stored Procedure using the LOB type with Visual Basic

This is an example of how a VB file accesses a PL/SQL stored procedure with LOBs. Below is the PL/SQL stored procedure with LOBs:

PROCEDURE getchapter(chapno in NUMBER, chap out CLOB) is
BEGIN
     SELECT chapters into chap from mybook where chapterno = chapno for update;
END;

The generated Visual Basic code for the GETCHAPTER stored procedure is shown below:

Public Sub GETCHAPTER(CHAPNO As Variant, ByRef CHAP As OraCLOB)
    OraDatabase.Parameters.Add "CHAPNO", CHAPNO, ORAPARM_INPUT, 2
    OraDatabase.Parameters.Add "CHAP", Null, ORAPARM_OUTPUT, 112

    OraDatabase.ExecuteSQL ("Begin MYBOOKPKG.GETCHAPTER(:CHAPNO, :CHAP); end;")

    Set CHAP = OraDatabase.Parameters("CHAP").Value

    OraDatabase.Parameters.Remove "CHAPNO"
    OraDatabase.Parameters.Remove "CHAP"
End Sub


Accessing a PL/SQL Stored Procedure using the VARRAY type with Visual Basic

The following is an example of a PL/SQL stored procedure that uses the Oracle collection type VARRAY:

PROCEDURE getnames(deptid in NUMBER, name out ENAMELIST) is
BEGIN
    SELECT ENAMES into name from department where dept_id = deptid for update;
END;

The wizard generates the following Visual Basic code for this stored procedure:

Public Sub GETNAMES(DEPTID As Variant, ByRef NAME As OraCollection)
    OraDatabase.Parameters.Add "DEPTID", DEPTID, ORAPARM_INPUT, 2
    OraDatabase.Parameters.Add "NAME", Null, ORAPARM_OUTPUT, 247, "ENAMELIST"

    OraDatabase.ExecuteSQL ("Begin DEPTPKG.GETNAMES(:DEPTID, :NAME); end;")

    Set NAME = OraDatabase.Parameters("NAME").Value

    OraDatabase.Parameters.Remove "DEPTID"
    OraDatabase.Parameters.Remove "NAME"
End Sub

Accessing a PL/SQL Stored Procedure using the Oracle OBJECT type with Visual Basic

The following is an example of a PL/SQL stored procedure that uses the Oracle Object type:

PROCEDURE getaddress(person_name in varchar2, person_address out address) is
BEGIN
    SELECT addr into person_address from person_table where name = person_name for update;
END;

The wizard generates the following Visual Basic code for this stored procedure:

Public Sub GETADDRESS(PERSON_NAME As String, ByRef PERSON_ADDRESS As OraObject)
    OraDatabase.Parameters.Add "PERSON_NAME", PERSON_NAME, ORAPARM_INPUT, 1
    OraDatabase.Parameters.Add "PERSON_ADDRESS", Null, ORAPARM_OUTPUT, 108, "ADDRESS"

    OraDatabase.ExecuteSQL ("Begin PERSONPKG.GETADDRESS(:PERSON_NAME, :PERSON_ADDRESS); end;")

    Set PERSON_ADDRESS = OraDatabase.Parameters("PERSON_ADDRESS").Value

    OraDatabase.Parameters.Remove "PERSON_NAME"
    OraDatabase.Parameters.Remove "PERSON_ADDRESS"
End Sub