Argument Modes

Bind arguments can have one of three modes:

 

IN

Read-only value (the default mode)

 

OUT

Write-only variable

 

IN OUT

Can read the value coming in and write the value going out

When you are executing a dynamic query, all bind arguments must be of mode IN, except when you are taking advantage of the RETURNING clause, as shown here:

CREATE OR REPLACE PROCEDURE wrong_incentive ( company_in IN INTEGER, new_layoffs IN NUMBER )IS sql_string VARCHAR2(2000); sal_after_layoffs NUMBER;BEGIN sql_string := 'UPDATE ceo_compensation SET salary = salary + 10 * :layoffs WHERE company_id = :company RETURNING salary INTO :newsal'; EXECUTE IMMEDIATE sql_string USING new_layoffs, company_in, OUT sal_after_layoffs; DBMS_OUTPUT.PUT_LINE ( CEO compensation after latest round of layoffs $' || sal_after_layoffs);END;

Besides being used with the RETURNING clause, OUT and IN OUT bind arguments come into play mostly when you are executing dynamic PL/SQL. In this case, the modes of the bind arguments must match the modes of any PL/SQL program parameters, as well as the usage of variables in the dynamic PL/SQL block.

Here are some guidelines for the use of the USING clause with dynamic PL/SQL execution:

· A bind variable of mode IN can be provided as any kind of expression of the correct type: a literal value, named constant, variable, or complex expression. The expression is evaluated and then passed into the dynamic PL/SQL block.

· You must provide a variable to receive the outgoing value for a bind variable of mode OUT or IN OUT.

· You can bind values only to variables in the dynamic PL/SQL block that have a SQL type. If a procedure has a Boolean parameter, for example, that Boolean cannot be set (or retrieved) with the USING clause.

Let's take a look at how this works with a few examples. Here is a procedure with IN, OUT, and IN OUT parameters:

PROCEDURE analyze_new_technology ( tech_name IN VARCHAR2, analysis_year IN INTEGER, number_of_adherents IN OUT NUMBER, projected_revenue OUT NUMBER, ) ... ;

Because I have four parameters, any dynamic invocation of this procedure must include a USING clause with four elements. Because I have two IN parameters, the first two of those elements can be literal values or expressions. The second two elements must be the names of variables, as the parameter modes are OUT or IN OUT. Here is an example of a dynamic invocation of this procedure:

DECLARE devoted_followers NUMBER; est_revenue NUMBER;BEGIN EXECUTE IMMEDIATE 'BEGIN analyze_new_technology (:p1, :p2, :p3, :p4); END;' USING 'Java', 2002, devoted_followers, est_revenue;END;