Skip Headers

Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 13 Using Stored Procedures and Triggers

Oracle Database Lite enables you to use stored procedures written in Java, C, C++ and C#. The following sections describe how you can load and define these stored procedures in the Oracle Lite database:

- Section 13.1, "Overview of Stored Procedures and Triggers"

- Section 13.2, "Using Java Stored Procedures in Oracle Database Lite"

- Section 13.3, "Creating Java Stored Procedures"

- Section 13.4, "Using Triggers With Java Stored Procedures"

- Section 13.5, "Tutorial for a Java Stored Procedure Invoked By a Trigger"

- Section 13.6, "Converting Datatypes Between Java and SQL For Stored Procedures"

- Section 13.7, "Executing Java Stored Procedures from JDBC"

- Section 13.8, "Using C++ Stored Procedures"

- Section 13.9, "Using .Net Stored Procedures"

- Section 13.10, "Loading and Defining C, C++ or C# Stored Procedures"

## 13.1 Overview of Stored Procedures and Triggers

A stored procedure is a method that is stored in Oracle Database Lite. The procedure can be invoked by applications that access the database. Stored procedures can return a single value, a row, or multiple rows.

A trigger is a stored procedure that executes when a specific event occurs, such as a row update, insertion, or deletion. An update of a specific column can also fire a trigger. Triggers, however, cannot return a value. A trigger can operate at the statement-level or row-level.

- A statement-level trigger fires once per triggering statement, no matter how many rows are affected.

- A row-level trigger fires once for every row affected by the triggering statement.

Note:

For more information on general knowledge of creation and management of stored procedures, see the Oracle Database documentation.

## 13.2 Using Java Stored Procedures in Oracle Database Lite

To create a Java stored procedure, perform the following:

1. Create the class that you want to store in Oracle Database Lite. You can use any Java IDE to write the procedure, or you can simply reuse an existing procedure that meets your needs.

   When creating the class, consider the following restrictions on calling Java stored procedures from SQL DML statements:

   - When called from an `INSERT`, `UPDATE`, or `DELETE` statement, the method cannot query or modify any database tables modified by that statement.

   - When called from a `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement, the method cannot execute SQL transaction control statements, such as `COMMIT` or `ROLLBACK`.

   Note:

   Any SQL statement in a stored procedure that violates a restriction produces an error at run time.

2. Provide your class with a unique name for its deployment environment, since only one Java Virtual Machine is loaded for each Oracle Database Lite application. If the application executes methods from multiple databases, then the Java classes from these databases are loaded into the same Java Virtual Machine. We recommend that you prefix the Java class name with the database name to ensure that the Java class names are unique across multiple databases.

3. If you are executing any DML statements in your Java stored procedure, then—in order for these statements to exist within the same transaction—you must pass an argument of type `java.sql.Connection` as the first argument in the method. You must have the `Connection` object in order to prepare and execute any statements. Oracle Database Lite supplies the appropriate argument value of the Oracle Lite database `Connection` object for you; the application executing the method does not need to provide a value for this parameter.

Once created, Java stored procedures must be loaded into any database—including the Oracle Lite database—with either the `loadjava` Java utility or with the SQL command, `CREATE JAVA`. In Oracle Database Lite, once the Java stored procedure is loaded, you define how the table can use the methods of the stored procedure in one of the following ways:

- The table can invoke the methods within the Java stored procedure directly when you attach it to the table with with the `ALTER TABLE ATTACH JAVA` SQL command. When you attach the Java stored procedure, then the table inherits all methods of the Java stored procedure class.

  When you attach the Java class to a table then the static methods in the class become table-level stored procedures of the table and the non-static (instance) methods become row-level stored procedures.

- Execute the `CREATE FUNCTION` or `CREATE PROCEDURE` SQL command defines a call specification which declares a Java method so that it can be called from SQL. The call specification tells Oracle which Java method to invoke when a call is made.

- Execute the `CREATE TRIGGER` SQL command which specifies that the Java stored procedure executes when a specific event occurs on the table, such as an insert, update, or delete.

### 13.2.1 Load and Define Java Stored Procedures in an Oracle Lite Database

When you want to use Java stored procedures in an Oracle Lite database, the administrator must manually load and define the stored procedures either through utilities or within a SQL script, as follows:

1. Load the Java class into the Oracle Lite database with either the `loadjava` command-line utility or the SQL statement `CREATE JAVA`.

   The `loadjava` utility automates the task of loading Java classes into the database. Using `loadjava`, you can load Java class, source, and resource files, individually or in archives.

2. Define the methods in the class that you want to call from SQL. As described in Section 13.2, "Using Java Stored Procedures in Oracle Database Lite", you can attach the entire stored procedure for all methods to be defined, or create the call specification with either the `CREATE FUNCTION` or `CREATE PROCEDURE` commands.

   Note:

   For more information, see Section 13.3.1, "Using Load and Define for Java Stored Procedures".

## 13.3 Creating Java Stored Procedures

Oracle Database Lite supports the following development models for creating Java stored procedures:

- Section 13.3.1, "Using Load and Define for Java Stored Procedures"

- Section 13.3.2, "Using Attach to Define the Java Stored Procedure"

In addition, see the following sections for additional information about managing your Java stored procedures:

- Section 13.3.3, "Calling Java Stored Procedures From a Multithreaded C or C++ Application"

### 13.3.1 Using Load and Define for Java Stored Procedures

You only need to define procedures that should be callable from SQL. Many stored procedures are only called by other stored procedures, and do not need to be defined. Only static methods are supported when you do load and define Java stored procedures.

As referred to in previous sections, perform the following to create Java stored procedures:

1. Develop a Java class that contains the methods you want to store. Make sure that the class compiles and executes without errors.

2. Load the Java class into Oracle Database Lite with either the `loadjava` utility or the `SQL CREATE JAVA` command.

3. Define any static methods in the Java class that you want to make accessible to SQL by creating call specifications for these methods. By defining a method, you associate a SQL name to the method. SQL applications use this name to invoke the method.

   This model is supported by Oracle database, which enables you to utilize skills and resources you have already developed in implementing Oracle database enterprise applications and data. There is the following difference:

   - In Oracle Database Lite, you cannot define a method that is mapped to a `main` method.

   - In the Oracle database, call specs that define `main` methods are permitted.

4. Invoke the stored procedure through a SQL DML statement.

5. If you no longer intend to use the stored procedure, you can drop it from the database.

Note:

The load and define development model only supports Java static methods. To store static and non-static (instance) methods, you must attach the class to database tables, as described in Section 13.3.2, "Using Attach to Define the Java Stored Procedure".

The following sections describe in detail how to perform these functions:

- Section 13.3.1.1, "Loading Java Stored Procedure Classes Into the Oracle Lite Database"

- Section 13.3.1.2, "Defining Stored Procedures to SQL Using Create Function or Create Procedure"

- Section 13.3.1.3, "Calling Defined Stored Procedures"

- Section 13.3.1.4, "Dropping Defined Stored Procedures"

- Section 13.3.1.5, "Example Using the Load and Define Model"

#### 13.3.1.1 Loading Java Stored Procedure Classes Into the Oracle Lite Database

To load Java classes into the Oracle Database Lite database, you can use one of the following:

- Section 13.3.1.1.1, "loadjava"—The `loadjava` database command-line utility automates the task of loading Java classes into Oracle Database Lite and Oracle databases.

- Section 13.3.1.1.2, "Using CREATE JAVA"—The SQL statement `CREATE JAVA` loads Java classes manually.

##### 13.3.1.1.1 loadjava

The `loadjava` command-line utility creates schema objects from files and loads them into the database. Schema objects can be created from Java source files, class files, and resource files. Resource files may be image files, resources, or anything else a procedure may need to access as data. You can pass files to `loadjava` individually, or as ZIP or JAR archive files.

Oracle Database Lite does not keep track of class dependencies. Make sure that you load into the database, or place in the `CLASSPATH`, all supporting classes and resource files required by a stored procedure. To query the classes that are loaded in the database, you can query the `okJavaObj` meta class.

Note:

The table name and column names are case sensitive.

Syntax

      loadjava {-user | -u} username/password[@database]

         [-option_name -option_name ...] filename filename ...

Arguments

  This section discusses the `loadjava` arguments in detail.

User

  The `user` argument specifies a username, password, and database directory in the following format:

      <user>/<password>[@<database>]

  For example:

      scott/tiger@ ORACLE_HOME\Mobile\Sdk\OLDB40\Polite.odb

Options

  Oracle Database Lite supports the following options that are listed and described in Table 13-1.

  Table 13-1 Options

  Option | Description
  --- | ---
  `-force | -f` | Forces files to be loaded, even if a schema object with the same name already exists in the database.
  `-verbose | -v` | Directs `loadjava` to display detailed status messages while running.
  `-meta | -m` | Creates the meta information in the database but does not load the classes. This is useful when the classes are in a .jar file and are not loaded into the database.

When specifying multiple options, you must separate the options with spaces. For example:

    -force -verbose

The Oracle database supports additional options. If used with Oracle Database Lite, the additional options are recognized but not supported. Using them does not result in an error.

To view the options supported by Oracle database, see the `loadjava` help information using the following syntax.

    loadjava {-help | -h}

Filenames

  On the command line, you can specify as many class, source, JAR, ZIP, and resource files as you like, in any order. You must separate multiple file names with spaces, not commas. If passed a source file, `loadjava` invokes the Java compiler to compile the file before loading it into the database. If passed a JAR or ZIP file, `loadjava` processes each file in the JAR or ZIP. It does not create a schema object for the JAR or ZIP archive. The `loadjava` utility does not process a JAR or ZIP archive within another JAR or ZIP archive.

  The best way to load files is to place them in a JAR or ZIP and then load the archive. Loading archives avoids the complications associated with resource schema object names. If you have a JAR or ZIP that works with the JDK, then you can be sure that loading it with `loadjava` also works, and you can avoid the complications associated with resource schema object naming.

  As it loads files into the database, `loadjava` must create a name for the schema objects it creates for the files. The names of schema objects differ slightly from filenames, and different schema objects have different naming conventions. Class files are self-identifying, so `loadjava` can map their filenames to the names of schema objects automatically. Likewise, JAR and ZIP archives include the names of the files they contain.

  However, resource files are not self-identifying; `loadjava` derives the names of Java resource schema objects from the literal names you enter on the command-line (or the literal names in a JAR or ZIP archive). Because classes use resource schema objects while executing, it is important that you specify the correct resource file names on the command line.

  The best way to load individual resource files is to run `loadjava` from the top of the package tree, specifying resource file names relative to that directory. If you decide not to load resource files from the top of the package tree, you must be aware of how `loadjava` derives a name for your resource.

  When you load a resource file, `loadjava` derives the name of the resource schema object from the file name that you enter on the command line. Suppose you type the following relative and absolute pathnames on the command line:

      cd \scott\javastuff

      loadjava options alpha\beta\x.properties

      loadjava options  \scott\javastuff\alpha\beta\x.properties

  Although you have specified the same file with a relative and an absolute pathname, `loadjava` creates two schema objects:

  - `alpha\beta\x.properties`

  - `\scott\javastuff\alpha\beta\x.properties`.

  The `loadjava` utility generates the resource schema object's name from the file names you enter.

  Classes can refer to resource files relatively (for example, `b.properties`) or absolutely (for example, `\a\b.properties`). To ensure that `loadjava` and the class loader use the same name for a schema object, pass `loadjava` the name of the resource that the class passes to the `java.lang.Object.getResource` or `java.lang.Class``.getResourceAsStream` method.

Instead of remembering whether classes use relative or absolute resource names and changing directories so that you can enter the correct name on the command line, you can load resource files into a JAR file, as follows:

    cd \scott\javastuff

    jar -cf alpharesources.jar alpha\*.properties

    loadjava options alpharesources.jar

Or, to simplify further, put both the class and resource files in a JAR, which makes the following invocations equivalent:

    loadjava options alpha.jar

    loadjava options \scott\javastuff\alpha.jar

Example

  The following loads a class and resource file into Oracle Database Lite. It uses the `force` option; if the database already contains objects with the specified names, `loadjava` replaces them.

      c:\> loadjava -u scott/tiger@c:\Olite\Mobile\Sdk\OLDB40\Polite.odb -f Agent.class\
      images.dat

##### 13.3.1.1.2 Using CREATE JAVA

To load Java classes manually, use the following syntax:

    CREATE [OR REPLACE] [AND RESOLVE] [NOFORCE]

       JAVA {CLASS [SCHEMA <schema_name>] |

       RESOURCE NAMED [<schema_name>.]<primary_name>}

       [<invoker_rights_clause>]

       RESOLVER <resolver_spec>]

       USING BFILE ('<dir_path>', '<class_name>')

The following apply to the `CREATE JAVA` parameters:

- The `OR REPLACE` clause, if specified, recreates the function or procedure if one with the same name already exists in the database.

- For compatibility with the Oracle database, Oracle Database Lite recognizes but ignores the `<resolver_spec>` clause. Unlike the Oracle database, Oracle Database Lite does not resolve class dependencies. When loading classes manually, be sure to load all dependent classes.

- Oracle Database Lite recognizes, but ignores, `<invoker_rights_clause>`.

Example

  The following demonstrates a `CREATE JAVA` statement. It loads a class named `Employee` into the database.

      CREATE JAVA CLASS USING BFILE ('c:\myprojects\java',

         'Employee.class');

#### 13.3.1.2 Defining Stored Procedures to SQL Using Create Function or Create Procedure

After loading the Java class into the Oracle Lite database using `loadjava` or `CREATE JAVA`, define any static method in the class that you want to call from SQL by creating a call specification for it. The call specification maps the Java method's name, parameter types, and return types to SQL counterparts.

Note:

Another method to define the Java stored procedure is to attach it, as described in Section 13.3.2, "Using Attach to Define the Java Stored Procedure". The attach method defines the entire stored procedure and you can store both class-level (static) methods and object-level (non-static) methods.

You do not need to define every stored procedure, only those that serve as entry points for your application. In a typical implementation, many stored procedures are called only by other stored procedures, not by SQL users.

To create a call spec, use the SQL commands `CREATE FUNCTION` for methods that return a value or `CREATE PROCEDURE` for methods that do not return a value. The `CREATE FUNCTION` and `CREATE PROCEDURE` statements have the following syntax:

    CREATE [OR REPLACE]

       { PROCEDURE [<schema_name>.]<proc_name> [([<sql_parms>])] |

       FUNCTION [<schema_name>.]<func_name> [([<sql_parms>])]

       RETURN <sql_type> }

       <invoker_rights_clause>

       { IS | AS } LANGUAGE JAVA NAME

       '<java_fullname>  ([<java_parms>])

       [return <java_type_fullname>]';

       /

The following apply to this statement's keywords and parameters:

- `<sql_parms>` has the following format:

      <arg_name> [IN | OUT | IN OUT]

               <datatype>

- `<java_parms>` is the fully qualified name of the Java datatype.

- For compatibility with the Oracle database, Oracle Database Lite recognizes but ignores the `<invoker_rights_clause>` clause.

- `<java_fullname>` is the fully qualified name of a static Java method.

- `IS` and `AS` are synonymous.

For example, assume the following class has been loaded into the database:

    import java.sql.*;

    import java.io.*;

    public class GenericDrop {

       public static void dropIt (Connection conn, String object_type,

                           String object_name) throws SQLException {

          // Build SQL statement

          String sql = "DROP " + object_type + " " + object_name;

          try {

             Statement stmt = conn.createStatement();

             stmt.executeUpdate(sql);

             stmt.close();

          } catch (SQLException e) {

             System.err.println(e.getMessage());}

       }  // dropIt

    }  // GenericDrop

Class `GenericDrop` has one method named `dropIt`, which drops any kind of schema object. For example, if you pass the arguments "table" and "emp" to `dropIt`, the method drops the database table EMP from your schema.

The following call specification defines the method to SQL:

    CREATE OR REPLACE PROCEDURE drop_it (

          obj_type VARCHAR2,

          obj_name VARCHAR2)

       AS LANGUAGE JAVA

       NAME 'GenericDrop.dropIt(java.sql.Connection,

          java.lang.String, java.lang.String)';

       /

Note:

You must fully qualify the Java datatype parameters.

Given that you have a table named `TEMP` defined in your schema, you can execute the `drop_it` procedure from SQL Plus as follows.

    Select drop_it('TABLE', 'TEMP') from dual;

You can also execute the `drop_it` procedure from within a ODBC application using an ODBC CALL statement. For more information, refer Section 13.3.3, "Calling Java Stored Procedures From a Multithreaded C or C++ Application".

#### 13.3.1.3 Calling Defined Stored Procedures

After defining the stored procedure to SQL, call it with a SQL DML statement. For example, assume that this class is stored in the database:

    public class Formatter {

       public static String formatEmp (String empName, String jobTitle) {

          empName = empName.substring(0,1).toUpperCase() +

             empName.substring(1).toLowerCase();
          jobTitle = jobTitle.trim().toLowerCase();

          if (jobTitle.equals("analyst"))

             return (new String(empName + " is an exempt analyst"));

          else

          return (new String(empName + " is a non-exempt " + jobTitle));

      }

    }

Class `Formatter` has one method named `formatEmp`, which returns a formatted string containing an employee's name and job status. Create a call specification for `Formatter` as follows:

    CREATE OR REPLACE FUNCTION format_emp (ename VARCHAR2, job VARCHAR2)

       RETURN VARCHAR2

       AS LANGUAGE JAVA

       NAME 'Formatter.formatEmp (java.lang.String, java.lang.String)

       return java.lang.String';

       /

The call specification defines the method `formatEmp` as `format_emp`. Invoke it as follows:

    SELECT FORMAT_EMP(ENAME, JOB) AS "Employees" FROM EMP

       WHERE JOB NOT IN ('MANAGER', 'PRESIDENT') ORDER BY ENAME;

This statement produces the following output:

    Employees

    --------------------------------------------

    Adams is a non-exempt clerk

    Allen is a non-exempt salesman

    Ford is an exempt analyst

    James is a non-exempt clerk

    Martin is a non-exempt salesman

    Miller is a non-exempt clerk

    Scott is an exempt analyst

    Smith is a non-exempt clerk

    Turner is a non-exempt salesman

    Ward is a non-exempt salesman

Note:

Oracle Database Lite does not support the Oracle database `SQL CALL` statement for invoking stored procedures.

For information on calling stored procedures from C and C++ applications, see Section 13.3.3, "Calling Java Stored Procedures From a Multithreaded C or C++ Application".

#### 13.3.1.4 Dropping Defined Stored Procedures

Oracle Database Lite provides tools and SQL commands for dropping stored procedures. You should use caution when dropping procedures from the database, since Oracle Database Lite does not keep track of dependencies between classes. You must ensure that the stored procedure you drop is not referenced by other stored procedures. Dropping a class invalidates classes that depend on it directly or indirectly.

To remove Java stored procedure classes from Oracle Database Lite that were loaded, use either of the following:

- Section 13.3.1.4.1, "Using dropjava" for directions on how to use the `dropjava` utility

- Section 13.3.1.4.2, "Using SQL Commands" for directions on how to use the `SQL DROP JAVA` statement

To drop call specifications, use either `DROP FUNCTION` or `DROP PROCEDURE`.

##### 13.3.1.4.1 Using dropjava

The `dropjava` command-line utility automates the task of dropping Java classes from Oracle Database Lite and Oracle databases. `dropjava` converts file names into the names of schema objects and drops the schema objects. Use the following syntax to invoke `dropjava`:

    dropjava {-user | -u} username/password[@database]

      [-option] filename filename ...

Arguments

  This section describes the arguments to `dropjava`.

User

  The `user` argument specifies a username, password, and absolute path to the database file in the following format:

      <user>/<password>[@<database>]

  For example:

  `scott/tiger@c:\Olite\Mobile\Sdk\OLDB40\Polite.odb`

Option

  By specifying the verbose option (`-verbose` | `-v`), you can direct `dropjava` to produce detailed status messages while running.

  Oracle database supports additional options. If used with Oracle Database Lite, the additional options are recognized but not supported. Using them does not result in an error.

  For a complete list of supported and recognized options, from the command prompt type:

      dropjava -help

Filename

  For the `filename` argument, you can specify any number of Java class, source, JAR, ZIP, and resource files, in any order. JAR and ZIP files must be uncompressed. `dropjava` interprets most file names the same way `loadjava` does:

- For class files, `dropjava` finds the class name in the file and drops the corresponding schema object.

- For source files, `dropjava` finds the first class name in the file and drops the corresponding schema object.

- For JAR and ZIP files, `dropjava` processes the archived file names as if they had been entered on the command line.

If a file name has an extension other than .java, .class, .jar, or .zip, or has no extension, then `dropjava` assumes that the file name is the name of a schema object, then drops all source, class, and resource schema objects with that name. If `dropjava` encounters a file name that does not match the name of any schema object, it displays an error message and then processes the remaining file names.

##### 13.3.1.4.2 Using SQL Commands

To drop a Java class from Oracle Database Lite manually, use the `DROP JAVA` statement, which has the following syntax:

    DROP JAVA { CLASS | RESOURCE } [<schema-name> .]<object_name>

To drop a call specification, use the `DROP FUNCTION` or `DROP PROCEDURE` statement:

    DROP { FUNCTION | PROCEDURE } [<schema-name>.]<object_name>

The schema name, if specified, is recognized but ignored by Oracle Database Lite.

#### 13.3.1.5 Example Using the Load and Define Model

The following example creates a Java stored procedure using the load and define model.

In this example, you store the Java method `paySalary` in the Oracle Database Lite. `paySalary` computes the take-home salary for an employee.

This example covers the following steps.

- Step 1: Create the Java Class

- Step 2: Load the Java Class into the Database

- Step 3: Define the Function

- Step 4: Execute the Function

More examples of Java stored procedures are located in the <`ORACLE_HOME`>`\Mobile\SDK\samples\jdbc` directory.

Step 1: Create the Java Class

Create the Java class `Employee` in the file `Employee.java`. The `Employee` class implements the `paySalary` method:

    import java.sql.*;

    public class Employee {

       public static String paySalary(float sal, float fica, float sttax,

                                float ss_pct, float espp_pct)  {

          float deduct_pct;

          float net_sal;

            // compute take-home salary

          deduct_pct = fica + sttax + ss_pct + espp_pct;

          net_sal = sal * deduct_pct;

          String returnstmt = "Net salary is " + net_sal;

          return returnstmt;

       } // paySalary
    }

Note:

The keyword "public class" should not be used in a comment before the first public class statement.

Step 2: Load the Java Class into the Database

From mSQL, load the Java class using `CREATE JAVA`, as follows:

    CREATE JAVA CLASS USING BFILE ('c:\myprojects\doc',

    'Employee.class');

This command loads the Java class located in `c:\myprojects\doc` into the Oracle Database Lite.

Step 3: Define the Function

Create a call specification for the `paySalary` method. The following call specification defines the Java method `paySalary` as function `pay_salary`:

    CREATE FUNCTION pay_salary (

    sal float, fica float, sttax float, ss_pct float, espp_pct float)

    RETURN VARCHAR2

    AS LANGUAGE JAVA NAME

    'Employee.paySalary(float, float, float, float, float)

    return java.lang.String';

    /

Step 4: Execute the Function

To execute `pay_salary` in mSQL:

    SELECT pay_salary(6000.00, 0.2, 0.0565, 0.0606, 0.1)

    FROM DUAL;

To execute `pay_salary` in ODBC:

    SQLExecDirect(hstm,

       "SELECT pay_salary(6000.00,0.2,0.0565,0.0606,0.1)

       FROM DUAL);

Because the arguments to `pay_salary` are constants, the `FROM` clause specifies the dummy table `DUAL`. This `SELECT` statement produces the following output:

    Net salary is 2502.6

### 13.3.2 Using Attach to Define the Java Stored Procedure

You can create Java stored procedures by attaching classes to a table and invoking methods in the class by name. Using this model, you can store both class-level (static) methods and object-level (non-static) methods.

For this model, follow these steps:

1. Develop a Java class with the methods you want to store. Make sure that the class compiles and executes without errors.

2. Attach the class to a table using the `SQL ALTER TABLE` command. Once the class is attached, then the methods in the class become table-level or row-level stored procedures of the table.

3. Invoke methods in the class directly from SQL. Identify the method with `table_name.method_name`.

This information is specific to Oracle Database Lite; you cannot attach classes to Oracle database tables as described here. The load and define model for developing stored procedures, described in Section 13.3.1, "Using Load and Define for Java Stored Procedures", only supports class (static) methods. By attaching classes to tables, however, you can store and call Java class and instance methods.

The following sections describe the details for attaching and using Java stored procedures:

- Section 13.3.2.1, "Attaching a Java Class to a Table"

- Section 13.3.2.2, "Table-Level Stored Procedures"

- Section 13.3.2.3, "Row-Level Stored Procedures"

- Section 13.3.2.4, "Calling Attached Stored Procedures"

- Section 13.3.2.5, "Dropping Attached Stored Procedures"

- Section 13.3.2.6, "Example of An Attached Java Stored Procedure"

#### 13.3.2.1 Attaching a Java Class to a Table

To attach a Java class to a table, use the SQL command `ALTER TABLE`, which has the following syntax:

    ALTER TABLE [schema.]table

       ATTACH JAVA {CLASS|SOURCE} "cls_or_src_name "

       IN {DATABASE|'cls_or_src_path '}

       [WITH CONSTRUCTOR ARGS (col_name_list )]

Note:

You can attach either a source file or a class file. Source files are compiled by the Java compiler found in the system path.

Where:

- The `cls_or_src_name` variable specifies a fully qualified name of a class or source file. This includes the package name followed by class name, such as `Oracle.lite.Customer`. Do not include the file extension in the class or source file name. The name is case-sensitive. If you use lowercase letters, enclose the name in double quotes (" "). Make sure that the source or class is in the package specified by `cls_or_src_name`. For example, the source file of the example class `Customer` should contain the line "`package Oracle.lite`;". The class file is stored in the database in the same package. Oracle Database Lite creates the package if it does not already exist.

- If you have already attached the Java class to another table in the database, you can use the `IN DATABASE` clause. If the class has not yet been attached, specify the directory location of the class or source file in `cls_or_src_path`.

- Prior to executing a row-level stored procedure, Oracle Database Lite creates a Java object for the row, if one does not already exist. If the `ALTER TABLE` statement includes a `WITH CONSTRUCTOR` clause, then Oracle Database Lite creates the object using the class constructor that is the best match given the datatypes of the columns included in `col_name_list`. If the `ALTER TABLE` statement does not include a `WITH CONSTRUCTOR` clause, then Oracle Database Lite uses the default constructor.

You can use the ODBC functions `SQLProcedures` and `SQLProcedureColumns` to retrieve information about methods defined in a table.

#### 13.3.2.2 Table-Level Stored Procedures

Table-level stored procedures are the static methods of the attached Java class. Therefore, when executing the method, Oracle Database Lite does not instantiate the class to which it belongs. In a call statement, you refer to table-level stored procedures as table_name.method_name.

Statement-level triggers and `BEFORE INSERT` and `AFTER DELETE` row-level triggers (see "Section 13.4.1, "Statement-Level vs. Row-Level Triggers") must be table-level stored procedures.

#### 13.3.2.3 Row-Level Stored Procedures

Row-level stored procedures are the non-static methods in the attached Java class. To execute a row-level stored procedure, Oracle Database Lite instantiates the class to which the procedure belongs. The arguments to the class constructor determine which column values the constructor uses as parameters to create the class instances. In a call statement, you refer to row-level stored procedures as method_name (without the table qualifier). Row-level triggers can indirectly execute row-level stored procedures.

#### 13.3.2.4 Calling Attached Stored Procedures

After attaching the class to a table using the `ALTER TABLE` statement, you can call it with a `SELECT` statement. Refer to table-level stored procedures as table_name.method_name and row-level procedures as method_name.

For example, to execute a table-level stored procedure:

    SELECT table_name.proc_name[arg_list]

       FROM {DUAL|[schema.]table WHERE condition};

The `proc_name` is the name of the table-level stored procedure. Each argument in `arg_list` is either a constant or a reference to a column in the table. If all the arguments of `arg_list` are constants, the FROM clause should reference the dummy table DUAL.

Execute a row-level stored procedure as follows:

    SELECT [schema.]proc_name[arg_list]

       FROM [schema.]table

       WHERE condition;

If you call a procedure in the form table_name.method_name, and a table or method with that name does not exist, Oracle Database Lite assumes that table_name refers to a schema name and method_name refers to a procedure name. If you reference method_name only, Oracle Database Lite assumes that the referenced method is a row-level procedure. If there is no such procedure defined, however, Oracle Database Lite assumes that method_name refers to a procedure in the current schema.

Note:

Oracle Database Lite does not support the Oracle8i SQL `CALL` statement for invoking stored procedures.

You can use a callable statement to execute a procedure from ODBC or JDBC applications. For more information, see Chapter 10, "JDBC Programming" or Section 13.3.3, "Calling Java Stored Procedures From a Multithreaded C or C++ Application".

#### 13.3.2.5 Dropping Attached Stored Procedures

Oracle Database Lite provides tools and SQL commands for dropping stored procedures. You should use caution when dropping procedures from the database, since Oracle Database Lite does not keep track of dependencies between classes. You must ensure that the stored procedure you drop is not referenced by other stored procedures. Dropping a class invalidates classes that depend on it directly or indirectly.

You use the `ALTER TABLE` command to drop stored procedures, which has the following syntax:

    ALTER TABLE [schema.]table

       DETACH [AND DELETE] JAVA CLASS "class_name"

Note:

You must enclose the class name in double quotes (`" "`) if it contains lowercase letters.

Detaching the Java class does not delete it from the database. To delete the Java class file from the database, use the `DETACH AND DELETE` statement.

If you delete a Java class from the database after invoking it as a stored procedure or trigger, the class remains in the Java Virtual Machine attached to the application. To unload the class from the Java Virtual Machine, commit changes to the database, if necessary, and close all applications connected to the database. To replace a Java class, you must close all connections to the database and reload the class.

#### 13.3.2.6 Example of An Attached Java Stored Procedure

The following example shows how to create a Java stored procedure in Oracle Database Lite. In this example, you attach the Java method `paySalary` to the table EMP. `paySalary` computes the take-home salary for an employee.

This example covers the following steps:

- Step 1: Create the Table

- Step 2: Create the Java Class

- Step 3: Attach the Java Class to the Table

- Step 4: Execute the Method

Step 1: Create the Table

Create the table using the following SQL command:

    CREATE TABLE EMP(Col1 char(10));

Step 2: Create the Java Class

Create the Java class `Employee` in the file `Employee.java`. The `Employee` class implements the `paySalary` method:

    import java.sql.*;

    public class Employee {

       public static String paySalary(float sal, float fica, float sttax,

                                float ss_pct, float espp_pct)  {

          float deduct_pct;

          float net_sal;

            // compute take-home salary

          deduct_pct = fica + sttax + ss_pct + espp_pct;

          net_sal = sal * deduct_pct;

          String returnstmt = "Net salary is " + net_sal;

          return returnstmt;

       } // paySalary

    }

Step 3: Attach the Java Class to the Table

From mSQL, attach the Java class using the `ALTER TABLE` command:

    ALTER TABLE EMP ATTACH JAVA SOURCE "Employee" IN 'C:\tmp';

This command attaches the Java source file for the `Employee` class, which resides in the directory `C:\tmp`, to the EMP table.

Step 4: Execute the Method

To execute the `paySalary` method in mSQL, type the following statement:

    SELECT EMP."paySalary"(6000.00,0.2,0.0565,0.0606,0.1)

       FROM DUAL;

To execute `paySalary` from ODBC, invoke `SQLExecDirect`:

    SQLExecDirect(hstm,

       "SELECT EMP.\"paySalary\"(6000.00,0.2,0.0565,0.0606,0.1)

       FROM DUAL);

This statement produces the following result:

    Net salary is 2502.6

### 13.3.3 Calling Java Stored Procedures From a Multithreaded C or C++ Application

When invoking a Java stored procedure from a multithreaded C or C++ application, you should load `jvm.dll` from the application's `main` function. This resolves a problem that occurs with the Java Virtual Machine's garbage collection when a C or C++ application creates multiple threads that invoke a stored procedure directly or indirectly. The Java Virtual Machine runs out of memory because the threads do not detach from the Java Virtual Machine before exiting. Since Oracle Database Lite cannot determine whether the Java Virtual Machine or the user application created the thread, it does not attempt to detach them.

The `main` function should load the library before taking any other action, as follows:

    int main (int argc, char** argv)

    {
       LoadLibrary("jvm.dll");

       ...

    }

The library loads the Java Virtual Machine into the application's main thread. It attempts to detach any thread from the Java Virtual Machine if the thread detaches from the process. The `jvm.dll` behaves correctly even if the thread is not attached to a Java Virtual Machine.

## 13.4 Using Triggers With Java Stored Procedures

Triggers are stored procedures that execute, or "fire", when a specific event occurs. A trigger can fire when a column is updated, or when a row is added or deleted. The trigger can fire before or after the event.

Triggers are commonly used to enforce a database's business rules. For example, a trigger can verify input values and reject an illegal insert. Similarly, a trigger can ensure that all tables depending on a particular row are brought to a consistent state before the row is deleted.

- Section 13.4.1, "Statement-Level vs. Row-Level Triggers"

- Section 13.4.2, "Creating Triggers"

- Section 13.4.3, "Dropping Triggers"

- Section 13.4.4, "Trigger Example Using the Attach Method"

- Section 13.4.5, "Trigger Arguments"

- Section 13.4.6, "Trigger Arguments Example Using Create Procedure"

### 13.4.1 Statement-Level vs. Row-Level Triggers

There are two types of triggers: row-level and statement-level. A row-level trigger is fired once for each row affected by the change to the database. A statement-level trigger fires only once, even if multiple rows are affected by the change.

The `BEFORE INSERT` and `AFTER DELETE` triggers can only fire table-level stored procedures, since a row object cannot be instantiated to call the procedures. The `AFTER INSERT`, `BEFORE DELETE`, and `UPDATE` triggers may fire table-level or row-level stored procedures.

### 13.4.2 Creating Triggers

Use the `CREATE TRIGGER` statement to create a trigger. The `CREATE TRIGGER` statement has the following syntax:

    CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER} [{INSERT | DELETE |

       UPDATE [OF column_list]} [OR ]] ON table_reference

       [FOR EACH ROW] procedure_ref

       (arg_list)

In the `CREATE TRIGGER` syntax:

- Use the `OR` clause to specify multiple triggering events.

- Use `FOR EACH ROW` to create a row-level trigger. For a table-level trigger, do not include this clause.

- Use `procedure_ref` to identify the stored procedure to execute.

You can create multiple triggers of the same kind for a table if each trigger has a unique name within a schema.

In the following example, assume that you have stored and defined a procedure as `PROCESS_NEW_HIRE`. The trigger `AIEMP` fires every time a row is inserted into the EMP table.

    CREATE TRIGGER AIEMP AFTER INSERT ON EMP FOR EACH ROW

       PROCESS_NEW_HIRE(ENO);

`UPDATE` triggers that use the same stored procedure for different columns of a table are fired only once when a subset of the columns is modified within a statement. For example, the following statement creates a `BEFORE UPDATE` trigger on table T, which has columns C1, C2, and C3:

    CREATE TRIGGER T_TRIGGER BEFORE UPDATE OF C1,C2,C3 ON T

       FOR EACH ROW trigg(old.C1,new.C1,old.C2,new.C2,

       old.C3,new.C3);

This update statement fires `T_TRIGGER` only once:

    UPDATE T SET C1 = 10, C2 = 10 WHERE ...

#### 13.4.2.1 Enabling and Disabling Triggers

When you create a trigger, it is automatically enabled. To disable triggers, use the `ALTER TABLE` or `ALTER TRIGGER` statement.

To enable or disable individual triggers, use the `ALTER TRIGGER` statement, which has the following syntax:

    ALTER TRIGGER <trigger_name> {ENABLE | DISABLE}

To enable or disable all triggers attached to a table, use ALTER TABLE:

    ALTER TABLE <table_name> {ENABLE | DISABLE} ALL TRIGGERS

### 13.4.3 Dropping Triggers

To drop a trigger, use the `DROP TRIGGER` statement, which has the following syntax:

    DROP TRIGGER [schema.]trigger

### 13.4.4 Trigger Example Using the Attach Method

This example creates a trigger that is defined using the attach method, as described in Section 13.3.2, "Using Attach to Define the Java Stored Procedure". For an example of creating triggers using the `SQL CREATE FUNCTION` or `CREATE METHOD`, see Section 13.4.6, "Trigger Arguments Example Using Create Procedure". In the example, you first create a table and a Java class. Then you attach the class to the table. And finally, you create and fire the trigger.

The `SalaryTrigger` class contains the `check_sal_raise` method. The method prints a message if an employee gets a salary raise of more than ten percent. The trigger fires the method before updating a salary in the EMP table.

Since `check_sal_raise` writes a message to standard output, use mSQL to issue the mSQL commands in the example. To start mSQL, invoke the Command Prompt and enter the following.

    msql username/password@connect_string

`connect_string` is JDBC URL syntax. For example, to connect to the default database as user SYSTEM, at the Command Prompt.

`msql system/passwd@jdbc:polite:polite`

At the mSQL command line, create and populate the EMP table as follows.

    CREATE TABLE EMP(E# int, name char(10), salary real,

       Constraint E#_PK primary key (E#));

    INSERT INTO EMP VALUES (123,'Smith',60000);

    INSERT INTO EMP VALUES (234,'Jones',50000);

Place the following class in SalaryTrigger.java:

    class SalaryTrigger {

       private int eno;

       public SalaryTrigger(int enum) {

          eno = enum;

       }

       public void check_sal_raise(float old_sal,

          float new_sal)

       {

          if (((new_sal - old_sal)/old_sal) > .10)

          {

             // raise too high  do something here

             System.out.println("Raise too high for employee " + eno);

          }

       }

    }

The `SalaryTrigger` class constructor takes an integer, which it assigns to attribute `eno` (the employee number). An instance of `SalaryTrigger` is created for each row (that is, for each employee) in the table `EMP`.

The `check_sal_raise` method is a non-static method. To execute, it must be called by an object of its class. Whenever the salary column of a row in `EMP` is modified, an instance of `SalaryTrigger` corresponding to that row is created (if it does not already exist) with the employee number (E#) as the argument to the constructor. The trigger then calls the `check_sal_raise` method.

After creating the Java class, you attach it to the table, as follows:

    ALTER TABLE EMP ATTACH JAVA SOURCE "SalaryTrigger" IN '.'

       WITH CONSTRUCTOR ARGS(E#);

This statement directs Oracle Database Lite to compile the Java source file `SalaryTrigger.java` found in the current directory, and attach the resulting class to the EMP table. The statement also specifies that, when instantiating the class, Oracle Database Lite should use the constructor that takes as an argument the value in the `E#` column.

After attaching the class to the table, create the trigger as follows:

    CREATE TRIGGER CHECK_RAISE BEFORE UPDATE OF SALARY ON EMP FOR EACH ROW

       "check_sal_raise"(old.salary, new.salary);

    /

This statement creates a trigger called `check_raise`, which fires the `check_sal_raise` method before any update to the salary column of any row in EMP. Oracle Database Lite passes the old value and the new value of the salary column as arguments to the method.

In the example, a row-level trigger fires a row-level procedure (a non-static method). A row-level trigger can also fire table-level procedures (static methods). However, because statement-level triggers are fired once for an entire statement and a statement may affect multiple rows, a statement-level trigger can only fire a table-level procedure.

The following command updates the salary and fires the trigger:

    UPDATE EMP SET SALARY = SALARY + 6100  WHERE E# = 123;

This produces the following output:

    Raise too high for employee 123

### 13.4.5 Trigger Arguments

If using attached stored procedures, as described in Section 13.3.2, "Using Attach to Define the Java Stored Procedure", row-level triggers do not support Java-to-SQL type conversion. Therefore, the Java datatype of a trigger argument must match the corresponding SQL datatype (shown in section Section 13.6, "Converting Datatypes Between Java and SQL For Stored Procedures") of the trigger column. However, if you are using the load and define model, Oracle Database Lite supports datatype casting.

Table 13-2 describes how trigger arguments work in each type of column.

Table 13-2 Trigger Arguments

Trigger Argument | New Column Access | Old Column Access
--- | --- | ---
`insert` | Yes | No
`delete` | No | Yes
`update` | Yes | Yes

Note:

Triggers that have a `java.sql.Connection` object as an argument may be used only with applications that use the relational model.

### 13.4.6 Trigger Arguments Example Using Create Procedure

The following example shows how to create triggers that use IN/OUT parameters.

1. First, create the Java class `EMPTrigg`.

       import java.sql.*;

       public class EMPTrigg {

          public static final String goodGuy = "Oleg";

          public static void NameUpdate(String oldName, String[] newName)

          {

             if (oldName.equals(goodGuy))

                newName[0] = oldName;

          }

          public static void SalaryUpdate(String name, int oldSalary,

                 int newSalary[])

          {

             if (name.equals(goodGuy))

                newSalary[0] = Math.max(oldSalary, newSalary[0])*10;

          }

          public static void AfterDelete(Connection conn,

                            String name, int salary) {

             if (name.equals(goodGuy))

                try {

                   Statement stmt = conn.createStatement();

                   stmt.executeUpdate(

                    "insert into employee values('" + name + "', " +

                              salary + ")");

                   stmt.close();

                } catch(SQLException e) {}

            }

        }

2. Create a new table EMPLOYEE and populate it with values.

       CREATE TABLE EMPLOYEE(NAME VARCHAR(32), SALARY INT);

       INSERT INTO EMPLOYEE VALUES('Alice', 100);

       INSERT INTO EMPLOYEE VALUES('Bob', 100);

       INSERT INTO EMPLOYEE VALUES('Oleg', 100);

3. Next, load the class into Oracle Database Lite.

       CREATE JAVA CLASS USING BFILE ('c:\myprojects', 'EMPTrigg.class');

4. Use the `CREATE PROCEDURE` statement to define the `EMPTrigg` methods that you want to call:

       CREATE PROCEDURE NAME_UPDATE(

          OLD_NAME IN VARCHAR2, NEW_NAME IN OUT VARCHAR2)

          AS LANGUAGE JAVA NAME

          'EMPTrigger.NameUpdate(java.lang.String, java.lang.String[])';

          /

       CREATE PROCEDURE SALARY_UPDATE(

          ENAME VARCHAR2, OLD_SALARY INT, NEW_SALARY IN OUT INT)

          AS LANGUAGE JAVA NAME

          'EMPTrigger.SalaryUpdate(java.lang.String, int, int[])';

          /

       CREATE PROCEDURE AFTER_DELETE(

          ENAME VARCHAR2, SALARY INT)

          AS LANGUAGE JAVA NAME

          'EMPTrigger.AfterDelete(java.sql.Connection,

                  java.lang.String, int)';

          /

5. Now, create a trigger for each procedure:

       CREATE TRIGGER NU BEFORE UPDATE OF NAME ON EMPLOYEE FOR EACH ROW

          NAME_UPDATE(old.name, new.name);

       CREATE TRIGGER SU BEFORE UPDATE OF SALARY ON EMPLOYEE FOR EACH ROW

          SALARY_UPDATE(name, old.salary, new.salary);

       CREATE TRIGGER AD AFTER DELETE ON EMPLOYEE FOR EACH ROW

          AFTER_DELETE(name, salary);

6. Enter the following commands to fire the triggers and view the results:

       SELECT * FROM EMPLOYEE;

       UPDATE EMPLOYEE SET SALARY=0 WHERE NAME = 'Oleg';

       SELECT * FROM EMPLOYEE;

       DELETE FROM EMPLOYEE WHERE NAME = 'Oleg';

       SELECT * FROM EMPLOYEE;

       UPDATE EMPLOYEE SET NAME='TEMP' WHERE NAME = 'Oleg';

       DELETE FROM EMPLOYEE WHERE NAME = 'TEMP';

       SELECT * FROM EMPLOYEE;

## 13.5 Tutorial for a Java Stored Procedure Invoked By a Trigger

In this tutorial, you create a Java class `EMAIL`, load the class into Oracle Database Lite, define its method to SQL, and create a trigger for the method. The `EMAIL` class appears in the source file `EMAIL.java`, and is available in the Java examples directory at the following location.

<`ORACLE_HOME`>`\Mobile\Sdk\Samples\JDBC`

`EMAIL` has a method named `assignEMailAddress`, which generates an email address for an employee based on the first letter of the employee's first name and up to seven letters of the last name. If the address is already assigned, the method attempts to find a unique email address using combinations of letters in the first and last name.

After creating the class, you load it into Oracle Database Lite using mSQL. For this example you use the SQL statement `CREATE JAVA`. Alternatively, you can use the `loadjava` utility to load the class into Oracle Database Lite. After loading the class, you define the `assignEMailAddress` method to SQL.

Finally, you create a trigger that fires the `assignEMailAddress` method whenever a row is inserted into `T_EMP`, the table that contains the employee information.

As arguments, `assignEMailAddress` takes a JDBC connection object, the employee's identification number, first name, middle initial, and last name. Oracle Database Lite supplies the JDBC connection object argument. You do not need to provide a value for the connection object when you execute the method. `assignEMailAddress` uses the JDBC connection object to ensure that the generated e-mail address is unique.

- Section 13.5.1, "Start mSQL"

- Section 13.5.2, "Create a Table"

- Section 13.5.3, "Create a Java Class"

- Section 13.5.4, "Load the Java Class File"

- Section 13.5.5, "Define the Stored Procedure"

- Section 13.5.6, "Populate the Database"

- Section 13.5.7, "Execute the Procedure"

- Section 13.5.8, "Verify the Email Address"

- Section 13.5.9, "Create a Trigger"

- Section 13.5.10, "Commit or Roll Back"

### 13.5.1 Start mSQL

Start mSQL and connect to the default Oracle Database Lite. Since the Java application in this tutorial prints to standard output, use the DOS version of mSQL. From a DOS prompt, type:

`msql system/mgr@jdbc:polite:polite`

The SQL prompt should appear.

### 13.5.2 Create a Table

To create a table, type:

    CREATE TABLE T_EMP(ENO INT PRIMARY KEY,

       FNAME VARCHAR(20),

       MI CHAR,

       LNAME VARCHAR(20),

       EMAIL VARCHAR(8));

### 13.5.3 Create a Java Class

Create and compile the Java class `EMAIL` in the file `EMAIL.java` in `C:\tmp`. `EMAIL.java` implements the `assignEMailAddress` method. The code sample given below lists the contents of this file. You can copy this file from the following location.

<`ORACLE_HOME`>`\Mobile\Sdk\Samples\JDBC`

    import java.sql.*;

    public class EMAIL {

       public static void assignEMailAddress(Connection conn,

                int eno, String fname,String lname)

                throws Exception

       {

          Statement stmt = null;

          ResultSet retset = null;

          String emailAddr;

          int i,j,fnLen, lnLen, rowCount;

          /* create a statement */

          try {

             stmt = conn.createStatement();

          }

          catch (SQLException e)

          {

             System.out.println("conn.createStatement failed: " +

             e.getMessage() + "\n");

             System.exit(0);

          }

          /* check fname and lname */

          fnLen = fname.length();

          if(fnLen > 8) fnLen = 8;

          if (fnLen == 0)

             throw new Exception("First name is required");

          lnLen = lname.length();

          if(lnLen > 8) lnLen = 8;

          if (lnLen == 0)

             throw new Exception("Last name is required");

          for (i=1; i <= fnLen; i++)

          {

             /* generate an e-mail address */

             j = (8-i) > lnLen? lnLen:8-i;

             emailAddr =

                   new String(fname.substring(0,i).toLowerCase()+

                   lname.substring(0,j).toLowerCase());

             /* check if this e-mail address is unique  */

             try {

                retset = stmt.executeQuery(

                       "SELECT * FROM T_EMP  WHERE email = '"+

                       emailAddr+"'");

                if(!retset.next()) {

                   /* e-mail address is unique;

                   * so update the email column */

                   retset.close();

                   rowCount = stmt.executeUpdate(

                       "UPDATE T_EMP SET EMAIL = '"

                       + emailAddr + "' WHERE ENO = "

                       + eno);

                   if(rowCount == 0)

                      throw new Exception("Employee "+fname+ " " +

                              lname + " does not exist");

                   else return;

                }

             }

             catch (SQLException e) {

                while(e != null) {

                   System.out.println(e.getMessage());

                   e = e.getNextException();

                }

             }

          }

          /* Can't find a unique name */

          emailAddr = new String(fname.substring(0,1).toLowerCase() +

               lname.substring(0,1).toLowerCase() + eno);

          rowCount = stmt.executeUpdate(

               "UPDATE T_EMP SET EMAIL = '"

               + emailAddr + "' WHERE ENO = "

               + eno);

          if(rowCount == 0)

             throw new Exception("Employee "+fname+ " " +

                  lname + " does not exist");

          else return;

       }

    }

### 13.5.4 Load the Java Class File

To load the `EMAIL` class file into Oracle Database Lite, type:

    CREATE JAVA CLASS USING BFILE

       ('c:\tmp', 'EMAIL.class');

If you want to make changes to the class after loading it, you need to:

1. Drop the class from the database, using `dropjava` or `DROP` `JAVA` `CLASS`

2. Commit your work

3. Exit mSQL

4. Restart mSQL

This unloads the class from the Java Virtual Machine.

### 13.5.5 Define the Stored Procedure

You make the stored procedure callable from SQL by creating a call specification (call spec) for it. Since `assignEMailAddress` does not return a value, use the CREATE PROCEDURE command, as follows:

    CREATE OR REPLACE PROCEDURE

       ASSIGN_EMAIL(E_NO INT, F_NAME VARCHAR2, L_NAME VARCHAR2)

       AS LANGUAGE JAVA NAME 'EMAIL.assignEMailAddress(java.sql.Connection,

    int, java.lang.String,

         java.lang.String)';

### 13.5.6 Populate the Database

Insert a row into `T_EMP`:

    INSERT INTO T_EMP VALUES(100,'John','E','Smith',null);

### 13.5.7 Execute the Procedure

To execute the procedure, type:

    SELECT ASSIGN_EMAIL(100,'John','Smith')

      FROM dual

### 13.5.8 Verify the Email Address

To see the results of the `ASSIGN_EMAIL` procedure, type:

    SELECT * FROM T_EMP;

This command produces the following output:

    ENO  FNAME                M LNAME                EMAIL

         ---- ------------------   - -------------------- --------

         100  John                 E Smith                jsmith

### 13.5.9 Create a Trigger

To make `ASSIGN_EMAIL` execute whenever a row is inserted into T_EMP, create an `AFTER INSERT` trigger for it. Create the trigger as follows:

    CREATE TRIGGER EMP_TRIGG AFTER INSERT ON T_EMP FOR EACH ROW

      ASSIGN_EMAIL(eno,fname,lname);

A trigger named `EMP_TRIGG` fires every time a row is inserted into `T_EMP`. The actual arguments for the procedure are the values of the columns `eno`, `fname`, and `lname`.

You do not need to specify a `connection` argument.

#### 13.5.9.1 Testing the Trigger

Test the trigger by inserting a row into T_EMP:

    INSERT INTO T_EMP VALUES(200,'James','A','Smith',null);

#### 13.5.9.2 Verify the Email Address

Issue a `SELECT` statement to verify that the trigger has fired:

    SELECT * FROM T_EMP;

       ENO FNAME                M LNAME                EMAIL

       --- -------------------- - -------------------- --------

       100 John                 E Smith                jsmith

       200 James                A Smith                jasmith

### 13.5.10 Commit or Roll Back

Finally, commit your changes to preserve your work, or roll back to cancel changes.

## 13.6 Converting Datatypes Between Java and SQL For Stored Procedures

Oracle Database Lite performs type conversion between Java and SQL datatypes according to standard SQL rules. For example, if you pass an integer to a stored procedure that takes a string, Oracle Database Lite converts the integer to a string. For information about row-level triggers arguments, see Section 13.4.5, "Trigger Arguments". For a complete list of Java to SQL datatype mappings, see Section 10.5.1, "Mapping Datatypes Between Java and Oracle".

Note:

In Oracle database, `DATE` columns are created as `TIMESTAMP`. Also, note that `TIMESTAMP WITH TIME ZONE` data type is not supported.

You must specify trigger methods accordingly.

Java does not allow a method to change the value of its arguments outside the scope of the method. However, Oracle Database Lite supports `IN`, `OUT`, and `IN/OUT` parameters.

Many Java datatypes are immutable or do not support `NULL` values. To pass `NULL` values and use `IN/OUT` parameters for those datatypes, a stored procedure can use an array of that type or use the equivalent object type. Table 13-3 shows the Java integer datatypes you can use to enable an integer to be an `IN/OUT` parameter or carry a `NULL` value.

Table 13-3 The Java Integer Datatypes

Java Argument | Can Be IN/OUT | Can Be NULL
--- | --- | ---
`int` | No | No
`int[]` | Yes | Yes
`Integer` | No | Yes
`Integer[]` | Yes | Yes
`int[][]` | Yes | Yes

You can use mutable Java datatypes, such as `Date`, to pass a `NULL` or an `IN/OUT` parameter. However, use a `Date` array if a stored procedure needs to change the `NULL` status of its argument.

Note:

Passing a `NULL` when the corresponding Java argument cannot be `NULL` causes an error.

### 13.6.1 Declaring Parameters for Java Stored Procedures

The return value of a Java method is the OUT parameter of the procedure. A primitive type or immutable reference type can be an IN parameter. A mutable reference type or array type can be an IN/OUT parameter. Table 13-4 shows the Java type to use to make the corresponding Oracle Database Lite parameter an IN/OUT parameter.

Table 13-4 Java Types for Oracle Database Lite IN/OUT Parameters

For IN/OUT parameters of type... | Use...
--- | ---
Number | `Integer[]` or `int[]`
Binary | `byte[]` or `byte[][]`
String | `string[]`

If the stored procedure takes a `java.sql.Connection`, Oracle Database Lite automatically supplies the argument using the value of the current transaction or row. This argument is the first argument passed to the procedure.

### 13.6.2 Using Stored Procedures to Return Multiple Rows

You can use stored procedures to return multiple rows. You can invoke stored procedures that return multiple rows only from JDBC or ODBC applications, however. For a stored procedure to return multiple rows, its corresponding Java method must return a `java.sql.ResultSet` object. By executing a SELECT statement, the Java method obtains a `ResultSet` object to return. The column names of the `ResultSet` are specified in the SELECT statement. If you need to address the result columns by different names than those used in the table, the SELECT statement should use aliases for the result columns. For example:

    SELECT emp.name Name,  dept.Name Dept

       FROM  emp, dept

       WHERE emp.dept# = dept.dept#;

Because the return type of a stored procedure that returns multiple rows must be `java.sql.ResultSet`, the signature of that stored procedure cannot be used to obtain the column names or types of the result. Consequently, you should design additional tables to track the column names or result types for the stored procedures. For example, if you embed the preceding SELECT statement in a Java method, the method return type should be `java.sql.ResultType`, not `char Name` and `char Dept`.

Note:

You can only create Java stored procedures that return multiple rows using the attached stored procedure development model, described in Section 13.3.2, "Using Attach to Define the Java Stored Procedure".

#### 13.6.2.1 Returning Multiple Rows in ODBC

To execute a stored procedure that returns multiple rows in an OBDC application, use the following CALL statement, in which P is the name of the stored procedure and a1 through an are arguments to the stored procedure.

    {CALL P(a1,...,an)}

You use a marker (`?`) for any argument that should be bound to a value before the statement executes. When the statement executes, the procedure runs and the cursor on the result set is stored in the statement handle. Subsequent fetches using this statement handle return the rows from the procedure.

After you execute the `CALL` statement, use `SQLNumResultCols` to find the number of columns in each row of the result. Use the `SQLDescribeCol` function to return the column name and datatype.

#### 13.6.2.2 Example

The following example shows how to use ODBC to execute a stored procedure that returns multiple rows. This example does not use the `SQLNumResultCols` or `SQLDescribeCol` functions. It assumes that you have created a stored procedure, which you have defined to SQL as PROC. PROC takes an integer as an argument.

    rc = SQLPrepare(StmtHdl, "{call PROC(?)}", SQL_NTS);

    CHECK_STMT_ERR(StmtHdl, rc, "SQLPrepare");

    rc = SQLBindParameter(StmtHdl, 1, SQL_PARAM_INPUT_OUTPUT,

       SQL_C_LONG,SQL_INTEGER, 0, 0, &InOutNum, 0, NULL);

    CHECK_STMT_ERR(StmtHdl, rc, "SQLBindParameter");

    rc = SQLExecute(StmtHdl);

    CHECK_STMT_ERR(StmtHdl, rc, "SQLExecute");

    /* you can use SQLNumResultCols and SQLDescribeCol here */

    rc = SQLBindCol(StmtHdl, 1, SQL_C_CHAR, c1, 20, &pcbValue1);

    CHECK_STMT_ERR(StmtHdl, rc, "SQLBindCol");

    rc = SQLBindCol(StmtHdl, 2, SQL_C_CHAR, c2, 20, &pcbValue2);

    CHECK_STMT_ERR(StmtHdl, rc, "SQLBindCol");

    while ((rc = SQLFetch(StmtHdl)) != SQL_NO_DATA_FOUND) {

       CHECK_STMT_ERR(StmtHdl, rc, "SQLFetch");

       printf("%s, %s\n", c1, c2);

    }

## 13.7 Executing Java Stored Procedures from JDBC

After creating a Java stored procedures, you can execute the procedure from a JDBC application by performing one of the following:

- Pass a `SQL SELECT` string, which executes the stored procedure, to the `Statement.executeQuery` method.

- Use a JDBC `CallableStatement`.

The `executeQuery` method executes table-level and row-level stored procedures. `CallableStatement` currently only supports execution of table-level stored procedures.

### 13.7.1 Using the executeQuery Method

To call a stored procedure using the `executeQuery` method, perform the following:

1. Create a `Statement` object and assign the value returned by the `createStatement` method with the current connection object.

2. Execute the `Statement.executeQuery` method, passing the `SQL SELECT` string that invokes the Java stored procedure.

The following example executes a row-level procedure `SHIP` on a table named `INVENTORY` with the argument value stored in the variable q. The variable `p` contains the product ID for the product (row) for which you want to execute the stored procedure.

    int res = 0;
    Statement s = conn.createStatement();
    ResultSet r = s.executeQuery("SELECT SHIP(" + q + ")" +
       "FROM INVENTORY WHERE PID = " + p);
    if(r.next()) res = r.getInt(1);
    r.close();
    s.close();
    return res;

If you need to execute a procedure repeatedly with varying parameters, use `PreparedStatement` instead of `Statement`. Because the SQL statements in a `PreparedStatement` are pre-compiled, a `PreparedStatement` executes more efficiently. Additionally, a `PreparedStatement` can accept `IN` parameters, represented in the statement with a question mark `(?)`. However, if the `PreparedStatement` takes a `long` type parameter, such as `LONG` or `LONG RAW`, you must bind the parameter using the `setAsciiStream`, `setUnicodeStream`, or `setBinaryStream` methods.

In the preceding example, if the `SHIP` procedure updates the database and the isolation of the transaction that issues the above query is `READ COMMITTED`, then you must append the `FOR UPDATE` clause to the `SELECT` statement, as follows:

    "SELECT SHIP(" + q + ")" +
       FROM INVENTORY WHERE PID = " +
       p + "FOR UPDATE");

### 13.7.2 Using a Callable Statement

To execute the stored procedure using a callable statement, create a `CallableStatement` object and register its parameters, as follows:

    CallableStatement cstmt = conn.prepareCall(
       "{?=call tablename.methodname() }");
    cstmt.registerOutParameter(1, ...);
    cstmt.executeUpdate();
    cstmt.get..(1);
    cstmt.close();

The following restrictions apply to JDBC callable statements:

- JDBC callable statements can only execute table-level stored procedures.

- Both IN and OUT parameters are supported. However, not all Java datatypes can be used as OUT parameters. For more information, see Section 13.6, "Converting Datatypes Between Java and SQL For Stored Procedures".

- Procedure names correspond to the Java method names, and are case-sensitive.

- As with prepared statements, if the callable statement has a "`long`" type, such as: `LONG`, `LONG` `VARBINARY`, `LONG` `VARCHAR`, `LONG` `VARCHAR2`, or `LONG` `RAW`, you must bind the parameter using the `setAsciiStream`, `setUnicodeStream`, or `setBinaryStream` methods.

  Note:

  When no longer needed, you should reclaim system resources by closing JDBC objects, such as `Resultset` and `Statement` objects.

## 13.8 Using C++ Stored Procedures

A C++ stored procedure is a C++ procedure or function that exists in a DLL outside of Oracle Database Lite. The procedure can be invoked by applications that access the database. C++ stored procedures can return a single value, a row, or multiple rows.

The following sections describe how to create, build, and define a C++ stored procedure:

- Section 13.8.1, "Creating C++ Stored Procedures"

- Section 13.8.2, "Building Your C++ Stored Procedures"

- Section 13.8.3, "Define Your C++ Stored Procedure"

- Section 13.8.4, "C++ Stored Procedure Example"

### 13.8.1 Creating C++ Stored Procedures

When you are creating a C++ stored procedure, you use SODA APIs to access the database and transaction objects. This section demonstrates how to develop your C++ stored procedures.

- Section 13.8.1.1, "C++ Stored Procedure Include File and Procedure Definition"

- Section 13.8.1.2, "Access SODA Objects Within Your C++ Stored Procedure"

#### 13.8.1.1 C++ Stored Procedure Include File and Procedure Definition

When you create the C++ source file, remember to do the following:

- Include the `olcsp.h` include file.

- The following defines the stored procedure or function prototypes. For each, you can have up to 32 parameters.

  - C++ stored procedure prototype:

        OL_CSP_CALL void cproc (const DBData &d1, const DBData &d2, ... , DBData &dN)

  - C++ stored function prototype:

        OL_CSP_CALL DBData cproc (const DBData &d1, const DBData &d2, ... , DBData &dN)

- Use `OL_CSP_CALL` before all of your procedures and functions, as it defines these as `extern "C" __declspec(dllexport)`. This enables the procedures and functions to be called from outside the DLL. For example, the following `sum` procedure uses this declaration:

      OL_CSP_CALL void sum (const DBData &a, const DBData &b, DBData &r)

- You can use the `DBData` object to represent almost any database type. In addition, it is easily cast to the correct datatype. For input parameters in the procedures, you can use `const DBData &`. For input/output parameters, use `DBData &` as the definition of the parameter.

  Once inside the procedure, cast the parameters as shown below:

      OL_CSP_CALL void sum (const DBData &a, const DBData &b, DBData &r)
      {  r = (int)a + (int)b; }

#### 13.8.1.2 Access SODA Objects Within Your C++ Stored Procedure

We use SODA, instead of ODBC, to provide a reliable access to the database and transaction objects. To access the SODA API objects, use the methods defined in the `olcsp.h` include file, as follows:

Note:

For details on the SODA API, see Chapter 12, "Using Simple Object Data Access (SODA)".

- After you retrieve the session object, do not close the connection in side the procedure.

- Retrieve the SODA API `DBSession` object with the `olCSPGetSession()` method, as follows:

      DBSession &sess = olCSPGetSession();

- Retrieve the SODA API `DBSqlSession` object, which is used in preparing and executing SQL statements, with the `olCSPGetSqlSession` method. Once you retrieve the `DBSqlSession` object, you can prepare the SQL statement within a `DBSqlStmt` object. The returned `DBSqlSession` object is created based on the existing ODBC handle. The following example retrieves the SQL Session, prepares and executes a statement:

      DBSqlSession sess = olCSPGetSqlSession();
      DBSqlStmt stmt = sess.prepare("insert into testsql values(?)");
      stmt.execute(DBDataList() << v);

- If this procedure was executed within the trigger, you can retrieve the object on which the trigger was invoked with the `olCSPGetObject` method. This returns a `DBObject` of the trigger object. This will not work for BEFORE CREATE or AFTER DELETE triggers.

- If you want to use ODBC instead of SODA, you can retrieve the ODBC connection handle with the `olCSPGetODBCHdl` method.

- All C++ stored procedures can throw `DBException`. This exception is automatically thrown if any SODA/SODASQL operation fails inside your stored procedure. If you are using a WinCE device, then you must use the ALE library for exceptions. See the ALE documentation for more information.

### 13.8.2 Building Your C++ Stored Procedures

You can either build your stored procedure manually or by using the `olsp.mak` makefile. The following describes both processes:

- Section 13.8.2.1, "Linking in Appropriate Libraries"

- Section 13.8.2.2, "Automatically Build Your Stored Procedure"

- Section 13.8.2.3, "Manually Building Your Stored Procedure"

#### 13.8.2.1 Linking in Appropriate Libraries

When you build your procedure, link in one or more of the following libraries:

- For all builds, link in `olobj40.lib`, which exists in `<Mobile_Server>``Mobile/SDK/lib`.

- If you are using SODASQL in your stored procedure, then link in `sodasql.lib`.

#### 13.8.2.2 Automatically Build Your Stored Procedure

If you have only a single source file, then you can use the `olsp.mak` makefile to build. The resulting DLL is named the same as the source file. This makefile supports building procedures for both the desktop and Windows CE platforms. Set up the following within the makefile before you execute to ensure a proper build:

1. Place the `olsp.mak` makefile in the same location as your source file.

2. If you build C++ stored procedures for a Windows CE device, then before you execute the makefile, you need to run the batch file from embedded visual C++. This sets the appropriate build environment for your windows CE platform. The following example shows execution of the `wcearmv4.bat` batch file:

       C:\EVC4.0\EVC\wce420\bin\WCEARMV4.BAT

3. Set the `MOBILESDK` environment variable—which defines the Oracle Lite Mobile SDK directory. For example,

       MOBILESDK=C:\oracle\ora90\mobile\sdk

4. Within the makefile, define `CDEFINE` (compiler defines) and `LFL` (linker flags) macros for your Windows CE platform.

5. If you are building .Net procedures, then set the `NETFRKDIR` environment variable in the makefile to point to your .Net Framework directory

6. If building for the Compact Framework, then define the `CFK` macro and set the `CFSDKDIR` environment variable in the makefile to point to your Compact Framework SDK directory.

7. Execute the makefile, as follows:

       nmake -f olsp.mak MyProc.dll [macros] [options]

   The macros that you can use are as follows:

   - For debug mode, use the macro `DEBUG=/DDEBUG`.

   - For compact framework, use the macro `CFK=1`.

8. Copy the new DLL, such as `MyProc.dll`, into a directory on the system path. If your platform is a WinCE device, copy this DLL into the `\Windows` directory.

For example, if your source file is `MyProc.cpp` or `MyProc.cs`, then this makefile builds `MyProc.dll` for you.

#### 13.8.2.3 Manually Building Your Stored Procedure

If you have more than one source file for the stored procedure, then you must manually build. Keep in mind the following:

1. Because you are using the `export "C"` declaration on the stored procedure, which supports the procedure being able to throw exceptions, use the `/EHc-` compiler flag when building the stored procedure.

2. If you are using the `SODASQL` in the procedure, then link with either the `olobj40.lib` and `sodasql.lib`.

### 13.8.3 Define Your C++ Stored Procedure

Define the methods in the class that you want to call from SQL with a call specification, which is created with either the `CREATE FUNCTION` or `CREATE PROCEDURE` commands.

Perform the following to define C++ stored procedures:

1. Define any methods in the C++ class that you want to make accessible to SQL by creating call specifications for these methods. By defining a method, you associate a SQL name to the method. SQL applications use this name to invoke the method.

2. Invoke the stored procedure through a SQL DML statement.

Define any static method in the class that you want to call from SQL by creating a call specification for it. The call specification maps the method's name, parameter types, and return types to SQL counterparts.

To create a call spec, use the SQL commands `CREATE FUNCTION` for methods that return a value or `CREATE PROCEDURE` for methods that do not return a value. The `CREATE FUNCTION` and `CREATE PROCEDURE` statements have the following syntax:

    CREATE [OR REPLACE]
       { PROCEDURE <proc_name> [([<sql_parms>])] |
       FUNCTION <func_name> [([<sql_parms>])]
       RETURN <datatype> }
       AS LANGUAGE CPLUSPLUS NAME
       '<lib_name>::<func_name>)';

       /

Where:

- `<proc_name>` is a SQL procedure name; `<func_name>` is the name of the function in the DLL used for this procedure.

- `<sql_parms>` can be a maximum of 32 arguments. All arguments passed to the procedures are given as DBData values to the function, which must cast the arguments to the appropriate data type. The syntax has the following format:

      <arg_name> [IN | OUT | IN OUT] <datatype>

- `<datatype>` is the datatype.

- `<lib_name>` is the name of the DLL where the function is delcared, without the `.dll` extension.

For example:

The following call specification defines the method to SQL:

    CREATE PROCEDURE bu1 (
        oc1 int,
        nc1 int,
        oc2 int,
        nc2 int)
      AS LANGUAGE CPLUSPLUS
      NAME 'CSPLib::bu1';

       /

### 13.8.4 C++ Stored Procedure Example

The following examples show how to create, build and define the stored procedures.

- Section 13.8.4.1, "C++ Stored Procedure and Trigger Example One"

- Section 13.8.4.2, "C++ Stored Procedure and Trigger Example Two"

- Section 13.8.4.3, "JDBC Calling a C++ Stored Procedure Example"

#### 13.8.4.1 C++ Stored Procedure and Trigger Example One

The following example does the following:

1. Creates the t1 table.

2. Creates the call specification of `bu1` for the C++ stored procedure `bu1` in the `CSPLib.dll`.

3. Creates a `BEFORE` `UPDATE` trigger, `foo`, which calls the `bu1` C++ stored procedure before the values of `c1` and `c2` in the table are updated.

    create table t1(c1 int, c2 int);

    create procedure bu1(oc1 int, nc1 int, oc2 int, nc2 int)
       as language cplusplus name 'CSPLib::bu1';

    create trigger foo before update of c1,c2 on t1
      for each row bu1(old.c1,new.c1,old.c2,new.c2);

The following demonstrates how the trigger is executed, which in turn invokes the C++ stored procedure:

    insert into t1 values(1,2);
    insert into t1 values(10,2);

    --trigger fired here
    update t1 set c1 = 10, c2 = 20 where c1 = 1;
    update t1 set c1 = 100 where c1 = 10;

#### 13.8.4.2 C++ Stored Procedure and Trigger Example Two

The following example does the same as Example 1, but with a more complicated trigger. The trigger and procedure are dropped at the end of this example.

    create table t3(c1 int, c2 int);

    create procedure bc2(tabref varchar, tranid int, opseq int, c1 int, c2 int) as language cplusplus name 'CSPLib::bc2';

    --special trigger columns here
    create trigger foo2 before insert on t3 for each row bc2(OL__TABLEREF, OL__TRANSID, OL__OPSEQ, new.c1,new.c2);

    --trigger fired here
    insert into t3 values(1,2);
    insert into t3 values(10,20);
    insert into t3(c1) values(100);
    insert into t3(c2) values(100);

    drop trigger foo2;
    drop procedure bc2;

#### 13.8.4.3 JDBC Calling a C++ Stored Procedure Example

The following example shows JDBC invoking a C++ stored procedure through the `CallableStatement`.

    //The following statement creates the procedure TESTINOUT1 with in out parameters
    stmt.execute("CREATE OR REPLACE PROCEDURE TESTINOUT1(A IN OUT INT,
         B IN OUT DOUBLE PRECISION, C IN OUT VARCHAR, D IN OUT DATE,
         E IN OUT TIME, F IN OUT BINARY) AS LANGUAGE CPLUSPLUS
       NAME CSPLib::testInOut");

    CallableStatement cstmt =            conn.prepareCall("{call TESTINOUT1(?, ?, ?, ?, ?, ?)}");

    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.registerOutParameter(2, Types.DOUBLE);
    cstmt.registerOutParameter(3, Types.VARCHAR);
    cstmt.registerOutParameter(4, Types.DATE);
    cstmt.registerOutParameter(5, Types.TIME);
    cstmt.registerOutParameter(6, Types.BINARY);

    //setting parameters to null values
    cstmt.setNull(1, Types.INTEGER);
    cstmt.setNull(2, Types.DOUBLE);
    cstmt.setNull(3, Types.VARCHAR);
    cstmt.setNull(4, Types.DATE);
    cstmt.setNull(5, Types.TIME);
    cstmt.setNull(6, Types.BINARY);

    for(int i = 0; i < 5; i++) {
     //executing the procedure. The parameters will be modified inside the procedure
     cstmt.execute();
     int a = cstmt.getInt(1);
     double b = cstmt.getDouble(2);
     String c = cstmt.getString(3);
     Date d = cstmt.getDate(4);
     Time e = cstmt.getTime(5);
     byte [] f = cstmt.getBytes(6);
    }

## 13.9 Using .Net Stored Procedures

The .Net environment enables you to create stored procedures from any .Net language, such as C++, C#, C, and Visual Basic .Net. You create procedures and functions based on methods of a .Net class that is stored in an external DLL. Unlike C++ procedures, you don't need a fixed signature. Instead, the procedure can receive arguments and return values for any supported data type.

Note:

Windows CE devices have the following limitations:

- You cannot pass delegates to native code when using the Compact Framework.

- You cannot start the .Net runtime from native code. The only way to use .Net on a Windows CE device is to start it within a C# application before the stored procedures are invoked.

The following sections detail how to build your .Net stored procedures:

- Section 13.9.1, "Creating the .Net Source for Your Stored Procedure"

- Section 13.9.2, "Building Your .Net Stored Procedures"

- Section 13.9.3, "Define Your .Net Stored Procedure"

- Section 13.9.4, "Dropping .Net Stored Procedures"

- Section 13.9.5, ".Net Stored Procedure Example"

### 13.9.1 Creating the .Net Source for Your Stored Procedure

When you are creating a .Net stored procedure, you can use Oracle-specific .Net extension classes to access the database and transaction objects. The .Net extension classes discussed in the following sections are `OracleData`, `OracleDataRow`, and `OracleSPManager`.

The following sections demonstrate how to develop your .Net stored procedures:

- Section 13.9.1.1, "Defining Methods, Imports and Namespace"

- Section 13.9.1.2, "Access and Modify Database Using .Net Extension Classes In Stored Procedures"

- Section 13.9.1.3, "Access and Modify Database Using OracleSPManager Inside Triggers"

#### 13.9.1.1 Defining Methods, Imports and Namespace

1. When you create your .Net source file, be sure to import the `Oracle.DataAccess.Lite` namespace.

2. All stored procedures are declared as public static methods of the class.

The following example defines public static methods and includes the `Oracle.DataAccess.Lite` namespace:

    using System;
    using Oracle.DataAccess.Lite;

    public class SPClass
    {
     //function which multiplies two integers
      public static int multiply(int a, int b)
      {
       return a * b;
      }

                      //returns string length, as in C++ procedure example
      public static int strlen(string s)
        {
     return s.Length;
        }

                    //stores sum of first two arguments in the third argument
                    //as in C++ procedure example
                    public static void trigSum(int a, int b, out int c)
                    {
                        c = a + b;
                    }
    public static void testInOut1(ref int dInt, ref double dDouble,
                    ref string dStr, ref DateTime dDate, ref DateTime dTime,
                    ref byte [] dBin)
            {
                    dInt += 10;
            dDouble += 12.34;
            dStr += "aaaaa";

            dDate = dDate.AddYears(1);
            dDate = dDate.AddMonths(1);
            dDate = dDate.AddDays(1);

            dTime = dTime.AddMinutes(1);
            dTime = dTime.AddSeconds(1);

            int len = dBin == null ? 0 : dBin.Length;
            byte [] newBin = new byte[len + 5];
            if (dBin != null)
                            Array.Copy(dBin, 0, newBin, 0, len);
            for(int i = len; i < newBin.Length; i++)
                            newBin[i] = (byte)'x';
                dBin = newBin;
                    }
    }
    }

#### 13.9.1.2 Access and Modify Database Using .Net Extension Classes In Stored Procedures

The following are Oracle-specific .Net extension classes:

- OracleData—A .Net version of the SODA `DBData` class.

- OracleDataRow—encapsulates and Oracle Lite database row.

OracleData

The `OracleData` object is a .Net version of the SODA `DBData` class.

Data types that are supported by Oracle Database Lite can be used in the `OracleData` object. These types can be implicitly cast to other compatible types; the cast must still follow normal database SQL casting rules. Casting between scalar types are implicit; casting to array types are explicit. If you try to cast an incompatible type, and `OracleException` is thrown.

This includes the following data types:

Table 13-5 Data Type For OracleData Object

Data Type |  |  |
--- | --- | --- | ---
int, int[] | byte, byte[]database type binary | short, short[] | bool, bool []
long, long[] | double, double[] | string, string[] |
DateTime, DateTime[] | OracleDataRow, OracleDataRow[] | OracleBlob, Oracle Blob[] (Oracle Lite Blob object) |

For example, the following code shows how you can cast a `String` to an `Integer` using the `OracleData` object:

    string s = "10";
    OracleData d = new OracleData(s);
    int i = d;

OracleDataRow

The `OracleDataRow` object encapsulates and Oracle Lite database row. You can query and modify column values in place, instead of using SQL. The `OracleDataRow` implements indexes on the row, which returns an `OracleData` object.

To create an object query on a table, implement `OracleDataReader`, an Oracle extension of the ADO.Net `DataReader` object, to return `OracleDataRow` objects. The following example uses the `GetDataRow` method of the `OracleDataReader` object to retrieve the desired rows. To set up the query more efficiently, set the `RowQuery` attributes of `Table` and `Filter` before executing the query, as follows:

Note:

For more information on the ADO.Net classes, see Chapter 11, "Oracle Database Lite ADO.NET Provider".

    OracleConnection conn =
        new OracleConnection("DSN=POLITE;UID=SYSTEM;PWD=MANAGER");
    conn.Open();
    OracleCommand cmd = (OracleCommand)conn.CreateCommand();
    //set properties for row query of table name and where clause
    cmd.RowQuery.Table = "T1"; //table name
    cmd.RowQuery.Filter = "C1 < 10"; //where clause

    //execute the query and retrieve the desired rows
    OracleDataReader rd = (OracleDataReader)cmd.ExecuteQuery();

    //While there are rows, process each row
    while(rd.Read())
    {  //Retrieve each row with the GetDataRow method into the OracleDataRow object
        OracleDataRow row = rd.GetDataRow();
        //query and modify in place columns C1 and C2 of the row.
        //implicit conversion to and from OracleData
        // Retrieve the integer in column C1
        int i = row["C1"];
        // Add 5 to the value in C1 and store it in column C2
        row["C2"] = i + 5;
        //convert the value to a string and write it out
        string s = row["C2"];
        Console.WriteLine(s);
    }
    rd.Close();
    conn.Close()

You can retrieve and modify the row by performing the following:

1. Retrieve the row with the `GetDataRow` method of the `OracleDataReader` class.

2. Query and modify the retrieved row within the `OracleDataRow` object.

#### 13.9.1.3 Access and Modify Database Using OracleSPManager Inside Triggers

When you have a stored procedure that is executed by a trigger, the actual row that caused the trigger is accessible through the `OracleSPManager`. Thus, you do not have to create a SQL statement to retrieve the desired row. Instead, use the `GetDataRow` method of the `OracleSPManager` object. Also, you can use the `GetConnection` method of this object to retrieve the current `Connection` object.

The `OracleSPManager` class contains the following static methods, which you can ues to retrieve the connection or the row:

    public static OracleConnection GetConnection();
    public static OracleDataRow GetDataRow();

The following example uses the `OracleSPManager` static methods to retrieve the connection and row:

    public static void log1(int a, int b, int c)
    {
        //get current connection from .Net procedure manager
        OracleConnection conn = OracleSPManager.GetConnection();

        //get current row for trigger
        OracleDataRow r = OracleSPManager.GetDataRow();

        if (r[0] != a || r[1] != b || r[2] != c)
            throw new OracleException(“Invalid row”);

        if (ia == 0 && ib == 0 && ic == 0)
           throw new OracleException(“Invalid row”);

        OracleCommand cmd = (OracleCommand)conn.CreateCommand();
                cmd.CommandText = “INSERT INTO T1_LOG VALUES(?, ?)”;
                cmd.Parameters.Add(new OracleParameter(a));
                cmd.Parameters.Add(new OracleParameter(a + b + c));
                cmd.ExecuteNonQuery();
                //do not close connection here
     }
    }

### 13.9.2 Building Your .Net Stored Procedures

You can either build your stored procedure using Visual Studio .Net or by using the `olsp.mak` makefile. See the Visual Studio documentation for how to build using Visual Studio .Net.

If you want to build using the `olsp.mak` file, follow the directions as given for the C++ stored procedures in Section 13.8.2.2, "Automatically Build Your Stored Procedure". There are a few directions in that section that are specific to the .Net environment, as follows:

1. If you are building .Net procedures, then set the `NETFRKDIR` environment variable in the makefile to point to your .Net Framework directory

2. If building for the Compact Framework, then define the `CFK` macro and set the `CFSDKDIR` environment variable in the makefile to point to your Compact Framework SDK directory.

3. When building the `SPClass.dll` C# class, use the following syntax for the make:

       nmake -f olsp.mak SPClass.dll

4. Move the resulting DLL into the appropriate place, which is either the application directory or in the global assembly cache. Use the `gacutil.exe` executable if you want to install this DLL in the global assembly cache.

### 13.9.3 Define Your .Net Stored Procedure

When you want to define your .Net stored procedure, perform the following:

- Section 13.9.3.1, "Create the .Net Class Object in the Oracle Lite Database"

- Section 13.9.3.2, "Define Methods With a Call Specification"

#### 13.9.3.1 Create the .Net Class Object in the Oracle Lite Database

Before you can create the call specification for the .Net stored procedure, you must first create the class within the Oracle Lite database. Use the following syntax:

    CREATE [OR REPLACE] DOTNET CLASS USING BFILE('AssemblyName', 'ClassName');

Where:

- `AssemblyName` is the assembly file name, such as `SPClass.DLL`.

- `ClassName` is the name of the class, such as `SPClass`. However, if the class is defined within a namespace, prefix the namespace name before the classname, as follows: `MyNameSpace.SPClass`.

For example, the following creates the `SPClass` within the Oracle Lite database.

    create dotnet class using bfile('SPClass.dll', 'SPClass');

#### 13.9.3.2 Define Methods With a Call Specification

Define the methods in the class that you want to call from SQL with a call specification, which is created with either the `CREATE FUNCTION` or `CREATE PROCEDURE` commands.

Perform the following to define your .Net stored procedures:

1. Define any methods in the .Net class that you want to make accessible to SQL by creating call specifications for these methods. By defining a method, you associate a SQL name to the method. SQL applications use this name to invoke the method.

2. Invoke the stored procedure through a SQL DML statement.

Define any static method in the class that you want to call from SQL by creating a call specification for it. The call specification maps the method's name, parameter types, and return types to SQL counterparts.

To create a call spec, use the SQL commands `CREATE FUNCTION` for methods that return a value or `CREATE PROCEDURE` for methods that do not return a value. The `CREATE FUNCTION` and `CREATE PROCEDURE` statements have the following syntax:

    CREATE [OR REPLACE]
       { PROCEDURE <proc_name> [([<sql_parms>])] |
       FUNCTION <func_name> [([<sql_parms>])]
       RETURN <datatype> }
       AS LANGUAGE DOTNET NAME
       '<class_name>.<method_name>)';

       /

Where:

- `<proc_name>` is a SQL procedure name; `<func_name>` is the name of the function in the DLL used for this procedure.

- `<sql_parms>` can be a maximum of 32 arguments. All arguments passed to the procedures are given as DBData values to the function, which must cast the arguments to the appropriate data type. The syntax has the following format:

      <arg_name> [IN | OUT | IN OUT] <datatype>

- `<datatype>` is the datatype.

- `<class_name>.<method_name>` is the name of the class and method that is used for the procedure or function.

For example:

The following call specification defines the method to SQL:

    CREATE PROCEDURE bu1 (
        oc1 int,
        nc1 int,
        oc2 int,
        nc2 int)
      AS LANGUAGE DOTNET
      NAME 'SPClass.bu1';

       /

### 13.9.4 Dropping .Net Stored Procedures

To drop a .Net class object from the database, delete it with the following drop statement:

    drop dotnet class 'ClassName';

### 13.9.5 .Net Stored Procedure Example

The following examples show how to create, build and define the stored procedures.

- Section 13.9.5.1, ".Net Stored Procedure and Trigger Example One"

- Section 13.9.5.2, ".Net Stored Procedure and Trigger Example Two"

#### 13.9.5.1 .Net Stored Procedure and Trigger Example One

The following example does the following:

1. Creates the .Net `SPClass`.

2. Creates the `t1` table.

3. Creates the call specification of `bu1` for the .NET stored procedure `bu1` in the class.method: `SPClass.bu1`.

4. Creates a `BEFORE` `UPDATE` trigger, `foo`, which calls the `bu1` .Net stored procedure before the values of `c1` and `c2` in the table are updated.

    create dotnet class using bfile('SPClass.dll', 'SPClass');

    create table t1(c1 int, c2 int);

    create procedure bu1(oc1 int, nc1 int, oc2 int, nc2 int)
       as language dotnet name 'SPClass.bu1';

    create trigger foo before update of c1,c2 on t1
      for each row bu1(old.c1, new.c1, old.c2, new.c2);

The following demonstrates how the trigger is executed, which in turn invokes the .Net stored procedure:

    insert into t1 values(1,2);
    insert into t1 values(10,2);

    --trigger fired here
    update t1 set c1 = 10, c2 = 20 where c1 = 1;
    update t1 set c1 = 100 where c1 = 10;

#### 13.9.5.2 .Net Stored Procedure and Trigger Example Two

The following example does the same as Example 1, but with a more complicated trigger. The trigger and procedure are dropped at the end of this example.

    create table t3(c1 int, c2 int);

    create procedure bc2(tabref varchar, tranid int, opseq int, c1 int, c2 int) as language dotnet name 'SPClass.bc2';

    --special trigger columns here
    create trigger foo2 before insert on t3 for each row bc2(OL__TABLEREF, OL__TRANSID, OL__OPSEQ, new.c1, new.c2);

    --trigger fired here
    insert into t3 values(1,2);
    insert into t3 values(10,20);
    insert into t3(c1) values(100);
    insert into t3(c2) values(100);

    drop dotnet class 'SPClass';

## 13.10 Loading and Defining C, C++ or C# Stored Procedures

Once you create the C, C++ and C# stored procedures, you still need to load and define them on the client Oracle Lite database. Since C, C++ and C# languages are native code, any stored procedure created from these languages cannot be loaded into the database. However, you still need to perform the following against the Oracle Lite database for these stored procedures to be loaded and defined:

1. Load the stored procedure by copying it directly to the client.

2. For C# .Net stored procedures, create the class within the Oracle Lite database with the `CREATE DOTNET CLASS` SQL command.

3. Define the stored procedures similar to the Java stored procedures by using one of the following methods:

   - Execute the `CREATE FUNCTION` or `CREATE PROCEDURE` SQL command to define a call specification, which declares a method so that it may be called from SQL. The call specification tells Oracle which method to invoke when a call is made.

   - Execute the `CREATE TRIGGER` SQL command to specify that the stored procedure executes when a specific event occurs on the table, such as an insert, update, or delete.

     Note:

     Attach is not supported for C, C++ and C# stored procedures.