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 |