Oracle® Objects for OLE Developer's Guide 10g Release 2 (10.2) Part Number B14309-01 |
|
|
View PDF |
The Oracle Objects for OLE (OO4O) Code Wizard generates OO4O code that executes Oracle PL/SQL and Java stored procedures.
The wizard generates code into individual Microsoft Visual Basic or Active Server Page and VBScript subroutines from existing Oracle stored procedures and packages. Additionally, the wizard can generate complete implementations of COM Automation objects in the form of VB class files. The generated COM Automation object methods act as client stubs for the execution of stored procedures contained in a given package. All the OO4O code necessary for input/output parameter binding and stored procedure execution is automatically generated.
The wizard can be used as a command-line utility or as a Visual Basic add-in. The wizard automates the entire process of accessing stored procedures using COM interfaces, thereby significantly reducing development time and the likelihood of programming errors.
Note:
The Code Wizard requires Visual Basic 6.This chapter contains these topics:
The OO4O Code Wizard includes the following components:
A command line utility, OO4OCodeWiz.exe
, that converts PL/SQL and Java stored procedures to OO4O code.
A Visual Basic Add-in wizard that guides you through a series of steps to generate OO4O code for PL/SQL and Java stored procedures. The wizard displays Oracle packages and stored procedures from a tree control so that the user can choose which items to generate code.
Both of these components allow users to convert entire stored procedure packages to OO4O code.
The code wizard supports all data types, except for PL/SQL tables. When a PL/SQL table is used, an unsupportedType
key word is used instead, and the generated code does not compile.
The output code may have to be modified for handling Null
values. For example, when a VB variable is initialized to a parameter value, an isNull()
check may have to be added if Null
values are expected. Null
values are correctly handled for VB variables of type Variant
and Object
.
The OO4O Code Wizard can be used as a command line utility or as a Visual Basic Add-in.
The OO4OCodeWiz.exe
is a command-line utility that generates a Visual Basic class, a Visual Basic file, or an Active Server Page/VB Script file from existing PL/SQL or Java stored procedures, as well as packages, within an Oracle database. Call the utility in the following manner:
OO4OCodeWiz [-o output_file] username/password@connect_string package
Where | Specifies the following |
---|---|
username |
User name to log in to the database |
password |
Password for the user name |
connect_string |
Database connection string |
package |
Package name |
stored_procedure |
Stored procedure name (optional) |
Example
OO4OCodeWiz -o empfile.asp scott/tiger@Exampledb employee.example
Option
Option | Description |
---|---|
-o | Specifies the output file name (optional) |
Files Generated
The code wizard uses the information specified on the command line to determine which type of output file to generate.
If a file name and one of the permitted file extensions are specified, then they are used. In the preceding example, an ASP file is generated in the empfile.asp
output. The user can specify the following extensions:
Extension | File Type Generated |
---|---|
.cls |
VB class file |
.bas |
VB file |
.asp |
ASP or VB script file |
.vbs |
ASP or VB script file |
If no file extension is specified, the following rules indicate what type of file is generated, depending on other command-line specifications.
Package names without a stored procedure name generate a .cls
file.
Package names with procedure names generate a .bas
file.
Table 7-1 and Table 7-2 provide examples.
Table 7-1 Package Name Without Stored Procedure Name
File Specified | Command | File Type Generated |
---|---|---|
File name with no file extension generates filename.cls . |
OO4OCodeWiz -o empfile scott/tiger@Exampledb employee |
empfile.cls |
No file name or extension: generates packagename.cls . |
OO4OCodeWiz scott/tiger@Exampledb employee |
employee .cls |
File name with file extension generates filename.fileexten . |
OO4OCodeWiz -o empfile.asp scott/tiger@Exampledb employee |
empfile.asp |
Table 7-2 Package Name With Stored Procedure Name
File Specified | Command | File Type Generated |
---|---|---|
File name with no file extension generates filename.bas . |
OO4OCodeWiz -o empfile scott/tiger@Exampledb employee .example |
empfile.bas |
No file name or extension: generates packagename.bas . |
OO4OCodeWiz scott/tiger@Exampledb employee .example |
employee .bas |
File name with file extension generates filename.fileexten . |
OO4OCodeWiz -o empfile.asp scott/tiger@Exampledb employee .example |
empfile.asp |
Launch the OO4O Code Wizard by selecting Oracle Code Wizard for Stored Procedures in the Add-Ins menu of Microsoft Visual Basic.
The Connect To Oracle Database dialog box appears:
Enter the user name and password to connect to the database. A connection string is required if the database is not installed on the user's local computer.
Click OK.
The wizard displays the Oracle packages and stored procedures available to the user in a tree.
Select one of the stored procedures or packages displayed.
Enter an output file name or click the Browse... button to navigate to a different directory in which to place the file.
Choose the file type from the Component type list. There are three choices: a VB class module (*.cls
), a VB file (*.bas
), or other. The other option generates a VB file (*.bas
), but enables you to specify your own file extension.
Click OK.
A dialog box appears indicating that a new OO4O file was created.
Click Yes to create another file, or click No to return to Visual Basic.
The ORACLE_BASE\ORACLE_HOME
\oo4o\codewiz\samples
directory contains sample applications incorporating code generated by the wizard. The following examples 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
Accessing a PL/SQL Stored Procedure Using the LOB Type with Visual Basic
Accessing a PL/SQL Stored Procedure Using the VARRAY Type with Visual Basic
Accessing a PL/SQL Stored Procedure Using the Oracle OBJECT Type with Visual Basic
This example shows a PL/SQL stored function, GetEmpSal
, and then the Visual Basic (*.cls
) file that the code wizard generates for it.
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;
The generated code for the GetEmpSal
stored function is:
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
The following example shows how a Visual Basic file accesses a 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 following shows 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
The following example shows how a PL/SQL stored procedure 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
The following example shows how a PL/SQL stored procedure 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