Exploring Your Database

Running a Stored Procedure

Table of Content

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 call and the fetch within 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:

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.