User-defined Parameters

You can define a number of User-Defined Parameters that can be used throughout AQT.

Comparison to Substitutional Parameters

User-Defined Parameters are not to be confused with substitutional parameters described in Adding parameters to a query. The use of ? in an SQL statement to indicate a substitutional parameter is a standard part of the SQL interface to your database. The query (complete with ?s) is passed to your database for processing. The database responds to AQT with the numbers (and types) of the parameter values that need to be supplied. AQT will prompt you for these.

The use of substitutional parameters has a number of advantages:

• this is the only way that a LOB can be loaded into a database
• there is a slight performance advantage if you are running a query frequently. Some databases cache frequently-run SQL - a parameterised query is more likely to be cached than a non-parameterised query.
• substitional parameters are used by the Data Loader for fast loading of the data.

There are some disadvantages of substitutional parameters:

• a parameter can only be used for a column value. It cannot be used to substitute a table or column name, or other parts of your SQL.

Use of User-Defined Parameters

User-defined parameters provide an alternate mechanism for using parameters in your SQL.

• parameter values are substituted prior to the SQL being passed to the database.
• parameters can be included in any part of the SQL, including table and column names.

Parameters are indicated with a $ followed by the parameter name. Example:

select * from customers where cust_code = '$custcode'

• There is no end-delimiter for the parameters. Parameter replacement is "dumb". AQT just replaces the $ + parameter name with the parameter value. If you have a parameter called cust (with value SMITH) and a parameter called custcode (with value JONES) then $custcode will be substituted as SMITHcode
• If a parameter is not found, it will not be replaced. You will not get a warning.
• Parameter names are case dependent (so $cust will be replaced, but $Cust will not).
• If you want to see what your SQL looks like after the parameters have been replaced, from the Run SQL window go Tools > Show SQL after parameter replacement
• You can configure AQT to recognise parameters when they start with : (colon) rather than $. This is useful if you are running a block of code that uses host variables.

Note that, unlike substitutional parameters, you need to enclose string values in quotes.

Enabling the use of User-Defined Parameters

For batch-mode AQT, User-defined Parameters are always enabled.

For online AQT, User-defined Parameters are (by default) not enabled. You can enable this by (from the Run SQL window), going Tools > Parameters, then checking Enable Parameters. Click on OK. This setting is saved between sessions, so you only have to do this once.

Parameter Character

On the User-defined Parameters window, you can also specify whether parameters are to be indicated with a $ or a colon. This is done with the Indicate parameters with option. By default, this will be a $. The only choices are $ or colon.

Defining User-Defined Parameters

Parameters can be defined in three ways:

• from the Run SQL window, go Tools > Parameters. This will show you the User-Defined Parameters window. This displays all your existing parameters and their values. You can add / delete / modify parameters using this window. Click on OK to save these changes.
• using the SetParm command. This is a scripting statement for setting a parameter to a particular value. If the parameter doesn't already exist, it is created.
• using the QueryParm command. This is a scripting statement for prompting the user for the value of a parameter. If the parameter doesn't already exist, it is created.

Parameter values are saved to disk between AQT sessions. The file they are saved in is parms.txt in your Default Directory. The parms are saved whenever AQT shuts down successfully, or when OK is clicked on the User-Defined Parameters window.

Parameters are often used when running batch scripts. Any changes to parameters done during batch scripts are not saved.

Referencing Another User-Defined Parameter

A parameter can reference parameter.

Example:

In this, the value of rundate is the name of another parameter.

When you specify:

select * from accounts where transaction_date > '$rundate'

the following will be run:

select * from accounts where transaction_date > '2007-03-31'

Using the function Tools > Show SQL after parameter replacement is useful for seeing how parameters will be resolved.