SQL Query

The SQL Query collection method allows you to execute a normal SQL query or PL/SQL statement against the database to retrieve data.

Properties

SQL Query - The SQL query to execute. Normal SQL statements should not be semi-colon terminated. For example, SQL Query = "select a.ename, (select count(*) from emp p where p.mgr=a.empno) directs from emp a". PL/SQL statements are also supported, and if used, the "Out Parameter Position" and "Out Parameter Type" properties should be populated.
SQL Query File - A SQL query file. Note that only one of "SQL Query" or "SQL Query File" should be used. For example, %scriptsDir%/myquery.sql. You can upload custom files to the agent, which will be accessible under the %scriptsDir% directory.
Out Parameter Position - The bind variable used for PL/SQL output. Only a number should be specified. For example, if the SQL Query is:
```
DECLARE         
     l_output1 NUMBER;         
     l_output2 NUMBER;     
BEGIN         
     .....         
     OPEN :1 FOR             
          SELECT l_output1,l_output2 FROM dual;     
END;
```
then you can set Out Parameter Position = 1, and Out Parameter Type = SQL_CURSOR
Out Parameter Type - The SQL type of the PL/SQL output parameter.

Available Variables

Variables can be used in collection method properties. Variable names are case-sensitive. To escape '%', use '%%'.

Name	Description
%perlBin%	location of perl binary
%scriptsDir%	directory where scripts are stored
%NAME%	name of target instance
%TYPE%	target type
%DISPLAY_NAME%	display name of target instance
%TYPE_DISPLAY_NAME%	display name of target type
%OracleHome%	Oracle Home Path
%MachineName%	Listener Machine Name
%Port%	Port
%SID%	Database SID