7 Code Wizard for Stored Procedures

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

Example

OO4OCodeWiz -o empfile.asp scott/tiger@Exampledb employee.example 

Option

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:

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.

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:

   
   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.

   
   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.

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