Bind variables let you use the same SQL statement multiple times with different values, reducing the number of times a SQL statement needs to be parsed.

For example, when you insert four rows in a table, you can parse the SQL statement once, and bind and execute the SQL statement for each row. One SQL statement can have zero or more bind variables.

To execute passthrough SQL statements with bind variables, you must:

1. Open a cursor.

2. Parse the SQL statement on the non-Oracle system.

3. Bind the variables.

4. Execute the SQL statement on the non-Oracle system.

5. Close the cursor.

Figure 3-1 shows the flow diagram for executing nonqueries with bind variables.

The syntax of the non-Oracle system determines how a statement specifies a IN bind variable.

For example, on an Oracle system you define bind variables with a preceding colon. For example:

...
UPDATE emp
SET sal=sal*1.1
WHERE ename=:ename;
...

In this statement, ename is the bind variable. On non-Oracle systems, you may need to specify bind variables with a question mark. For example:

...
UPDATE emp
SET sal=sal*1.1
WHERE ename= ?;
...

In the bind variable step, you must positionally associate host program variables (in this case, PL/SQL) with each of these bind variables. For example, to execute the preceding statement, use the following PL/SQL program:

DECLARE
  c INTEGER;
  nr INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb;
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c,
        'UPDATE emp SET SAL=SAL*1.1 WHERE ename=?');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@salesdb(c,1,'JONES');
  nr:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@salesdb(c);
  DBMS_OUTPUT.PUT_LINE(nr||' rows updated');
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c);
END;

The non-Oracle system can support OUT bind variables.

With OUT bind variables, the value of the bind variable is not known until after the execution of the SQL statement.

Although OUT bind variables are populated after executing the SQL statement, the non-Oracle system must know that the particular bind variable is an OUT bind variable before the SQL statement is executed. You must use the BIND_OUT_VARIABLE procedure to specify that the bind variable is an OUT bind variable.

After executing the SQL statement, you can retrieve the value of the OUT bind variable using the GET_VALUE procedure.

A bind variable can be both an IN and an OUT variable.

This means that the value of the bind variable must be known before executing the SQL statement, but you can change the value after the SQL statement is executed.

For IN OUT bind variables, you must use the BIND_INOUT_VARIABLE procedure to provide a value before executing the SQL statement. After executing the SQL statement, you must use the GET_VALUE procedure to retrieve the new value of the bind variable.