Code Wizard Examples

The ORACLE_BASE\ORACLE_HOME\oo4o\codewiz\samples directory contains 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 follows, but the body of the code remains 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 (shown below).

PROCEDURE getchapter(chapno in NUMBER, chap out CLOB) is

BEGIN

SELECT chapters into chap from mybook where chapterno = chapno
for update;

END;

This is the generated Visual Basic code for the GETCHAPTER stored procedure:

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