How to Pass Data Between an Application and PL/SQL

This section covers how to pass data between an application and PL/SQL.

Refer to Bind Variables in Oracle Database PL/SQL Language Reference.

Using Bind Variables from an Application

You can use ":var" notation for bind variables to be passed between your application (such as a C or Java application) and PL/SQL. The term bind variable (or sometimes host variable) is used equivalently to how the term parameter has historically been used in TimesTen, and bind variables from an application would correspond to the parameters declared in a PL/SQL procedure or function specification.

Here is an example using ttIsql to call a PL/SQL procedure that retrieves the name and salary of the employee corresponding to a specified employee ID. In this example, ttIsql essentially acts as the calling application, and the name and salary are output from PL/SQL:

Command> VARIABLE b_name VARCHAR2 (25)
Command> VARIABLE b_sal  NUMBER

Command> BEGIN
          query_emp (171, :b_name, :b_sal);
         END;
         /
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_name
B_NAME               : Smith
Command> PRINT b_sal
B_SAL                : 7400

See "Examples Using Input and Output Parameters and Bind Variables" for the complete example.

See "PL/SQL Procedures and Functions" for how to create and define procedures and functions.

See Parameter Binding and Statement Execution in Oracle TimesTen In-Memory Database C Developer's Guide and Preparing SQL Statements and Setting Input Parameters in Oracle TimesTen In-Memory Database Java Developer's Guide for additional information and examples for those languages.

Note:

The TimesTen binding mechanism (early binding) differs from that of Oracle Database (late binding). TimesTen requires the data types before preparing queries. As a result, there will be an error if the data type of each bind parameter is not specified or cannot be inferred from the SQL statement. This would apply, for example, to the following statement:
```
SELECT 'x' FROM DUAL WHERE :a = :b;
```
You could address the issue as follows, for example:
```
SELECT 'x' from DUAL WHERE CAST(:a as VARCHAR2(10)) = 
                           CAST(:b as VARCHAR2(10));
```
For duplicate parameters, the implementation in PL/SQL in TimesTen is no different than the implementation in PL/SQL in Oracle Database.
The term "bind parameter" as used in TimesTen developer guides (in keeping with ODBC terminology) is equivalent to the term "bind variable" as used in TimesTen PL/SQL documents (in keeping with Oracle Database PL/SQL terminology).

IN, OUT, and IN OUT Parameter Modes

Parameter modes define whether parameters declared in a PL/SQL subprogram (procedure or function) specification are used for input, output, or both. The three parameter modes are IN (the default), OUT, and IN OUT.

An IN parameter lets you pass a value to the subprogram being invoked. Inside the subprogram, an IN parameter acts like a constant and cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter.

An OUT parameter returns a value to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it.

An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. Typically, an IN OUT parameter is a string buffer or numeric accumulator that is read inside the subprogram and then updated. The actual parameter that corresponds to an IN OUT formal parameter must be a variable, not a constant or an expression.

Note:

TimesTen supports the binding of associative arrays (but not varrays or nested tables) as IN, OUT, or IN OUT parameters. See Using Associative Arrays from Applications.

See Examples Using Input and Output Parameters and Bind Variables.