Arbitrary Monitoring  

Definition

This submonitor in the SAP/Oracle Database Monitor lets you display the results of native Oracle select statements. The access is restricted to oracle views and tables with owner SYS and PUBLIC (mainly GV$, V$, and DBA views).

Use

You choose Detailed Analyses  ® Additional Functions  ® Arbitrary Monitoring.

You cannot view history information in this monitor.

Structure

The submonitor consists of an editor screen where you enter the SQL statement and a result screen that displays the result of the SQL statement.

You can choose:

·        SQL Command  ® Parse

This function starts a simple parser to check the syntax. This parser only makes sure that the monitor is able to generate a display structure and display the result of the statement. It also checks the owner of the tables and views that have to be read.

It does not check the complete Oracle syntax. Therefore, it does not guarantee that the statement can be executed.

·        SQL Command  ® Execute

This function starts the parser, executes the statement, and displays the result of the statement.

·        Save as local fileor Load local file

This function lets you save your SQL statement to a local file or load an SQL statement from a local file into the editor.

Syntax

·        A statement must have the following syntax:

SELECT [ hint ] [ { DISTINCT | UNIQUE } | ALL ] select_list

FROM table_reference [, table_reference]...

[ WHERE condition ]

[ hierarchical_query_clause ]

[ group_by_clause ]

[ HAVING condition ]

[ { UNION | UNION ALL | INTERSECT | MINUS } ( subquery )]

[ order_by_clause ]

You can put comments between "/*+" and "*/"

·        A select list must have the following syntax:

{ * |

{[table_alias.]dbfieldname | expression} alias [,[table_alias.]dbfieldname | expression} alias] ... }

An expression within this select list can use a calculation operator such as +, -, *, /, ||. Also unary functions (LN, MIN, AVG ...), null, or numbers, are allowed.

·        A table reference must have the following syntax:

{(select statement) [table_alias] | table [table_alias]}

Otherwise the syntax follows the SQL standard.

Conventions and Restrictions

·        Each column that is specified in the select list will be a column in the output list.

·        If a select list element is specified with a column alias, this alias will be used as header text in the output list. Otherwise the program uses the field name of the select list element as header text. If a select list element is an expression (that is, without a database field), the alias is obligatory.

For every expression that is not a database field, use a column alias.

·        Every column alias that is specified in the select list of a sub-query can be used like a database field name in the select statement.

·        If more than one table is specified in the from clause, the columns are matched to one table for reasons of uniqueness. If a column name occurs in more than one table, uniqueness cannot be guaranteed. In this case you have to specify a table alias before the column name (that is, database field name).

When more than one table is specified and column names that have to be outputted occur in more than one table, use a table alias.