Skip Headers
Oracle® Objects for OLE Developer's Guide
11g Release 2 (11.2) for Microsoft Windows

Part Number E12245-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 Code Wizard for Stored Procedures

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:

Oracle Objects for OLE Code Wizard Components

The OO4O Code Wizard includes the following components:

Both of these components allow users to convert entire stored procedure packages to OO4O code.

Data Types Supported by the OO4O Code Wizard

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.

Using the OO4O Code Wizard

The OO4O Code Wizard can be used as a command line utility or as a Visual Basic Add-in.

OO4O Code Wizard Command-Line Utility

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


OO4O Code Wizard Visual Basic Wizard Add-in

  1. 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:

    the Connect dialog box
    Description of the illustration connect.gif

  2. 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.

  3. Click OK.

    The wizard displays the Oracle packages and stored procedures available to the user in a tree.

    OO4O Code Wizard for Stored Procedures dialog box
    Description of the illustration wizproc.gif

  4. Select one of the stored procedures or packages displayed.

  5. Enter an output file name or click the Browse... button to navigate to a different directory in which to place the file.

  6. 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.

  7. Click OK.

    A dialog box appears indicating that a new OO4O file was created.

  8. Click Yes to create another file, or click No to return to Visual Basic.

Code Wizard Examples

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

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 

Accessing a PL/SQL Stored Procedure Using the LOB Type with Visual Basic

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 

Accessing a PL/SQL Stored Procedure Using the VARRAY Type with Visual Basic

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 

Accessing a PL/SQL Stored Procedure Using the Oracle OBJECT Type with Visual Basic

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