Exploring Your Database
Running a Stored Procedure
When you select a Stored Procedure window from the Database Explorer the Run button will be shown. Click on this to show the Run Procedure window.

Using the Run Procedure window
When you click on Run from the Database Explorer, you will be shown then Run Procedure window

Enter Values for your Parameters
you can enter values in the IN and INOUT parameters
the text will turn red if you enter an invalid value (character value to long, or numeric value not numeric).
you can click on Set parms to default values. This will set
all character parameters to a zero-length string,
numeric parameters to 0
date/time/timestamp parameters to the current date/time/timestamp.
you can specify that a parameter is NULL by entering (null) or null
in the unlikely event that you want to enter the string “null”, and for this not to be interpreted as a NULL, enter null followed by a space
you can specify a hex value in the format of x'313233'
Running the Procedure
run the procedure by clicking on RUN.
if the procedure returns a result-set, it will be displayed.
if the procedure returns multiple result-sets, these will be displayed on either the same or separate windows, depending on the setting of display multiple result-sets in same window.
Sybase
There are sometimes problems running Procedures with old versions of Sybase and/or the Sybase ODBC Driver. This is described at trouble running Stored Procedures with Sybase.
Running a Procedure with INOUT, OUT and RETURN parameters
Procedures can return data through parameters. This would typically be done when single values, rather than a set of data, needs to be returned.
The Run Procedure window shows the Type of parameter. This can be:
OUT. Data is returned into this parameter.
INOUT. Data is passed to the procedure, plus returned from the procedure, in this parameter
RETURN. Some databases support the use of a Return parameter. This would typically be a return code indicating the success of the procedure.

Returning Parameters into a file
When you specify an Out File, the parameter value is written to the specified file. This is useful if the parameter value is very large, is a unreadable object (such as an image) or you are running the procedure in batch.
It is recommended that a fully-qualified filename is given. If not, the file will be created in the default directory.
Return Values containing x'00' values
If your OUT and INOUT parameters contain x'00 (binary zero) characters, then the data will not display. To circumvent this problem, select Options > Display Limits > Display x'00' characters as spaces.
Displaying result-sets produced by a Stored Procedure
A Stored Procedure can return data in various ways:
as INOUT or OUT parameters
as a set of data, or multiple sets of data
for some databases (such as Snowflake) Procedures return data as a Return parameter. In this respect, Procedures are similar to Functions.
When there are INOUT or OUT parameters, for most databases these will be returned and displayed in Value column in the grid This is discussed in more detail in Running a Procedure with INOUT, OUT and RETURN parameters.
When the Procedure returns a set of data, by default this will be displayed in grid/s at the bottom of the window.

Specifying where the data is displayed

The Grid menu option determines where the returned data will be displayed.
when Show Grid is selected (the default), the data will be displayed at the bottom of the Run Procedure window. When this option is not selected, the data will be displayed in separate Data Display windows
by default, when a procedure is run, any existing grids as the bottom of the Run Procedure window are cleared. When Display Multiple Grids is selected, prior grids are not cleared.
Clear Grids will remove all grids from the window.
Running a Function
For some databases, AQT can also run Functions.
The SQL used by AQT to run the function is different for different databases:
(for Oracle)
select function(parms) from dual
(for DB2)
values function(parms)
(for SQL Server)
select function(parms)
The result of the function will be displayed in a grid at the bottom of the window.

This feature is currently available for DB2/LUW, SQL Server, Oracle, MySQL, MariaDB, Snowflake and PostgreSQL.
Running an Oracle Package
The Run Procedure window is able to run an Oracle Package:
from the Database Explorer window, click on the Package
select the Run button in the toolbar
The Run Procedure window will show the package Sections. Select the Section you wish to run.
AQT can run both Procedure and Function sections.

Running a DB2 Stored Procedure with Multiple Versions
Both DB2 for z/OS and DB2 for LUW can have multiple versions of Stored Procedures, though they are quite different in the way they are used.
DB2 z/OS
For DB2 z/OS, multiple versions of Stored Procedures are used to maintain a history of prior versions of a routine. This is only available for native SQL procedures.
When you run the routine, you will run the Current Active Version. If you want to go back to an earlier version of the routine, you must first run one of the following commands to change the current active version.
ALTER PROCEDURE procname ALTER ACTIVE VERSION version-id
or
SET CURRENT ROUTINE VERSION version-id
In the Run Procedure window all versions are listed. You must select the version which is the Current Active Version (in case different versions have different number of parameters).
DB2 for LUW
For DB2 for LUW, multiple versions of Stored Procedures are used when you have similar routines with a different number or types of parameters. This is similar to function overloading, familiar to most programmers.
When you invoke a procedure, the number of parameters you use will determine which version of the procedure is run.
In the Run Procedure window all versions are listed. When you click on a procedure you will see the number of parameters. You will be able run any version of the procedure.
Running a MySQL Procedure with OUT parameters
OUT parameters for MySQL procedures are returned in pseudo variables. To view the value of these variables, you must run a query to return these values.
Example:
call film_in_stock(1,2,@p_film_count);
select @p_film_count;
Here:
the call statement runs the procedure, passing values for the first two parameters
the third parameter is in OUT parameter. A pseudo variable is specified here to receive the value
the select statement will display the value of the return pseudo variable
AQT will handle this situation automatically by using SQL similar to the above.
Running a PostgreSQL Stored Procedure or Function
PostgreSQL procedures behave quite differently to procedures in other types of databases. When the procedure has INOUT or OUT parameters, these values are returned in a result-set, rather than returning the values in the parameter.
In the following example, this procedure has two OUT parameters. The procedure returns a result-set consisting of 1 row and 2 columns. AQT will read the grid and populate the Values column in the parameters grid.

Procedure or Function returning a result-set (refcursor)
A routine can return a set of data by having an OUT parameter of type refcursor (for a Procedure) or a return value of type refcursor (for a Function) .
When you run the routine, instead of getting the data, you get the name of the refcursor (<unnamed portal 13> in this example).

The data for can be retrieved by reading cursor, with the following:
run the following: fetch all from "<unnamed portal 13>"
you must run both the
procedure / function calland the
fetchwithin the same transaction.
Once the transaction has finished, the cursor is deleted; any attempt to read the cursor will give:
ERROR: cursor "<unnamed portal 13>" does not exist
The method normally recommended for running a Procedure / Function and returning the results, is to use the following:

However, this method is problematic as, until you have done the call, you don't know the name of the cursor for the fetch statement.
Specifying the cursor name
You can get around the problem with the cursor name by specifying the name to be used for the refcursor. To do this, you will need to amend the definition of the routine as follows:
In the case of a Procedure, specify the refcursor with a mode of INOUT rather than OUT. Example:
Procedure test.proc_getemp(IN dept character varying, INOUT outdata refcursor)
In the case of a Function, include a refcursor as a parameter to the function. Example:
Function test.func_getemp(dep character varying, outdata refcursor) RETURNS SETOF refcursor
When you call the routine, you can now give the name for the cursor.
select test.func_getemp('A00','cursor01')
The result is:

Because the cursor now has a fixed name, you can run the routine and display the results with:

Using AQT
AQT will deal with the above complexities. You can run PostgreSQL procedures and functions with AQT and it will display the data returned by refcursors without any extra steps required by yourself.

Running a Snowflake Stored Procedure
Return Values
Snowflake procedures do not have OUT or INOUT parameters. Instead, the result is returned as either a single value or as a table. In both cases, the result is shown in the data grid.
Procedure Parameters need to be specified twice
Due to a problem with the Snowflake ODBC Driver, AQT needs to specify parameter values twice. You will notice this when you use Copy to SQL window and see the SQL that AQT uses for running the procedure.
This problem may be corrected in later versions of the Snowflake ODBC Driver.
Running a Procedure from the SQL Window or from a Batch script
When you click on Copy to SQL Window, AQT will create a script to run the procedure as an SQL script.

This script can be used in the SQL window, or in a batch script.
Notes
enter the parm values using the value clause
parmname is an optional clause and is not used by AQT. It is included only to help you identify the parameters.
ptype is an important clause used to identify OUT and INOUT parameters. This should not be omitted.