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