Home > SQL Developer: Unit Testing > Using Variable Substitution...
You can use variable substitution in validation actions to write dynamic validations that provide a result based on the values of input and output parameters of a procedure or function, or on the return value of a function. You can specify strings in the following format in validation actions:
For input parameters: {
PARAMETER_NAME}
For example, if an input parameter is named EMP_ID
:
SELECT ... WHERE employee_id = {EMP_ID} AND ...;
For output parameters: {
PARAMETER_NAME$}
For example, if an output parameter is named SALARY
:
SELECT ... WHERE {SALARY$} < old_salary;
For the return value: {RETURNS$}
For example, if a function returns a numeric value:
SELECT ... WHERE {RETURNS$} > 1;
What is actually substituted is the string representation of the parameter value (for text substitution), or the underlying data value of the parameter (for bind substitution, using the syntax :
param-name
). The following example shows both styles of substitution (text style and bind style):
DECLARE l_PARAM1 DATE; bad_date EXCEPTION; BEGIN l_PARAM1 := :PARAM1; IF '{PARAM1}' <> TO_CHAR(l_PARAM1) THEN RAISE bad_date; END IF; END;
As a simple example of text-style variable substitution:
If P1 is a parameter of type NUMBER and has the value 2.1
, the string {P1}
will be replaced by the string 2.1
.
If P1 is a parameter of type VARCHAR2 and has the value ABC
, the string '{P1}'
will be replaced by the string 'ABC'
. (Note the single-quotation marks around {P1} in this example.)
You can use variable substitution for all types of validation actions except Compare Tables. For the applicable validation action types, variable substitution is performed as follows:
For Query Returning Row(s) and Query Returning No Row(s), substitution is performed on the SQL query.
For Compare Query Results, substitution is performed on both the source and target SQL queries.
For Boolean Function and User PL/SQL Code, substitution is performed on the PL/SQL block.