Skip Headers

Oracle Intelligent Agent User's Guide
Release 9.2.0.2

Part Number A96676-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

3
Job and Event Scripts

Topics covered in this document include:

Scripting Language

The Tcl Language with OraTcl extensions is used to write the job and events scripts. Tcl is used for the scripts because it fulfills the necessary requirements, such as:

Tcl Language Description

Tcl originated with Dr. John Ousterhout from the University of California, Berkeley, California. Tcl, current release version 7.5, stands for Tool Command Language.

Tcl is both a language and a library. Tcl is a simple textual language that is intended primarily for issuing commands to interactive programs, such as text editors, debuggers, illustrators, and shells. Tcl has a simple syntax and is programmable. Tcl users can write command procedures to provide more powerful commands than those in the built-in set.

Tcl is also a library package that can be embedded in application programs. The Tcl library consists of a parser for the Tcl language, routines to implement the Tcl built-in functions, and procedures that allow each application to extend Tcl with additional commands specific to that application. The application program generates Tcl commands and passes them to the Tcl parser for execution. Commands may be generated by reading characters from an input source, or by associating command strings with elements of the application's user interface, such as menu entries, buttons, or keystrokes. When the Tcl library receives commands it parses them into component fields and executes built-in commands directly. For commands implemented by the application, Tcl calls back to the application to execute the commands. In many cases commands will invoke recursive invocations of the Tcl interpreter by passing in additional strings to execute. Procedures, looping commands, and conditional commands all work in this way.

An application program gains several advantages by using Tcl for its command language.

Tcl was designed with the philosophy that one should actually use two or more languages when designing large software systems. One for manipulating complex internal data structures, or where performance is key, and another, such as Tcl, for writing small scripts that tie together the c programming pieces and provide hooks for others to extend. For the Tcl scripts, ease of learning, ease of programming and ease of integrating are more important than performance or facilities for complex data structures and algorithms. Tcl was designed to make it easy to drop into a lower language when you come across tasks that make more sense at a lower level. In this way, the basic core functionality can remain small and one need only bring along pieces that one particular wants or needs. For more information on Tcl/Tk, access the following web sites:

OraTcl Description

Agent jobs and event scripts require both host system access for handling files and devices, launching programs, executing operating system functions, and accessing Oracle databases. OraTcl was developed to extend Tcl for Oracle usage and SNMP accessing. The categories of OraTcl functions are:

For descriptions of the OraTcl functions and variables, see "OraTcl Functions and Parameters".

Example: OraTcl Script

The following example illustrates the basic use of OraTcl.

#
# monthly_pay.Tcl 
#
# usage: monthly_pay.Tcl [connect_string]
#  or    Tcl -f monthly_pay.Tcl [connect_string]
#
# sample program for OraTcl
# Tom Poindexter
#
# example of sql, pl/sql, multiple cursors
# uses Oracle demo table SCOTT.EMP
# uses id/pass from command line, 
# or "scott/tiger" if not specified
#
# this example does not illustrate efficient sql!
# a simple report is produced of the monthly payroll
# for each jobclass
#
global oramsg
set find_jobs_sql { select distinct job from SCOTT.EMP }
set monthly_pay_pl {
begin 
select sum(sal) into :monthly 
from SCOTT.EMP
where job like :jobclass;
end;
}
set idpass $argv
if {[string length $idpass] == 0} {
  set idpass "scott/tiger"
}
set lda [oralogon $idpass]
set cur1 [oraopen $lda]
set cur2 [oraopen $lda]
orasql $cur1 $find_jobs_sql
set job [orafetch $cur1]
while {$oramsg(rc) == 0} {
  set total_for_job [lindex [oraplexec $cur2 $monthly_pay_pl :monthly "" 
:jobclass "$job"] 0]
   puts stdout "Total monthly salary for job class $job = \$$total_for_job"
  set job [orafetch $cur1]
}
oraclose $cur1
oraclose $cur2
oralogoff $lda
exit

Server Message and Error Information

OraTcl creates and maintains a Tcl global array oramsg to provide feedback of Oracle server messages. oramsg is also used to communicate with the OraTcl interface routines to specify NULL return values and LONG limits. In all cases except for NULLVALUE and MAXLONG, each element is reset to NULL upon invocation of any OraTcl command, and any element affected by the command is set. The oramsg array is shared among all open OraTcl handles.


Note:

oramsg should be defined with the global statement in any Tcl procedure that needs it.


oramsg Elements

The following are oramsg elements.

oramsg (agent_characterset)

The character set of the Agent, such as US7ASCII. This is used with the convertin and convertout verbs to convert character sets. See "convertin" and "convertout".

oramsg (db_characterset)

The character set of the database, such as US7ASCII. This is used with the convertin and convertout verbs to convert character sets. See "convertin" and "convertout". This variable can only be used after an oralogon function within a Tcl script.

oramsg (collengths)

A Tcl list of the lengths of the columns returned by oracols. collengths is only set by oracols.

oramsg (colprecs)

A Tcl list of the precision of the numeric columns returned by oracols. colprecs is only set by oracols. For non-numeric columns, the list entry is a null string.

oramsg (colscales)

A Tcl list of the scale of the numeric columns returned by oracols. Colscales is only set by oracols. For non-numeric columns, the list entry is a null string.

oramsg (coltypes)

A Tcl list of the types of the columns returned by oracols. coltypes is only set by oracols. Possible types returned are: CHAR, VARCHAR2 (Version 7), NUMBER, LONG, rowid, DATE, RAW, LONG_RAW, MLSLABEL, RAW_MLSLABEL, or unknown.

oramsg (errortxt)

The message text associated with rc. Because the oraplexec function may invoke several SQL statements, there is a possibility that several messages may be received from the server.

oramsg (handle)

Indicates the handle of the last OraTcl function. The handle, a mapping in memory used to track commands, is set on every OraTcl command except where an invalid handle is used.

oramsg (jobid)

The job Id of the current job. Defined for job scripts only.

oramsg (language)

The NLS language of the Console, such as AMERICAN_AMERICA.US7ASCII.

oramsg (maxlong)

Can be set by the programmer to limit the amount of LONG or LONG RAW data returned by orafetch. The default is 32K Bytes. The maximum is 64K (Version 6) or 2147483647 (Version 7) bytes. Any value less than or equal to zero is ignored. Any change to maxlong becomes effective on the next call to orasql. See notes on MAXLONG usage with orafetch.

oramsg (nullvalue)

Can be set by the programmer to indicate the string value returned for any NULL result. Setting oramsg(nullvalue) to DEFAULT will return 0 for numeric null data types, such as INTEGER, FLOAT, and MONEY, and a NULL string for all other data types. NULLVALUE is initially set to default.

oramsg (oraobject)

Contains the object upon which this script is acting. Defined for event scripts only.

oramsg (orahome)

The ORACLE_HOME directory.

oramsg (oraindex)

A Tcl list of the SNMP index values from the snmp.ora configuration file.

oramsg (orainput)

A Tcl list that contains the names of the job's input files. Probably most jobs will not need input files, but a job which invokes SQL*Plus with a SQL script, or Export with a specification file, would use this feature. Defined for job scripts only.

oramsg (rc)

Indicates the results of the last SQL command and subsequent orafetch processing. rc is set by orasql, orafetch, oraplexec, and is the numeric return code from the last OCI library function called by an OraTcl command.

See the Oracle9i Database Error Messages for detailed information.Typical values are listed in Table 3-1, "Error Messages"

.
Table 3-1 Error Messages
Error Meaning
0000 Function completed normally, without error.
0900 - 0999 Invalid SQL statement, invalid sql statements, missing keywords, invalid column names, etc.
1000 - 1099 Program interface error. For example, no sql statement, logon denied, or insufficient privileges.
1400 - 1499 Execution errors or feedback.
1403 End of data was reached on an orafetch command.
1406 A column fetched by orafetch was truncated. Can occur when fetching a LONG or LONG RAW, and the maxlong value is smaller than the actual data size.
oramsg (rows)

The number of rows affected by an insert, update, or delete in an orasql command, or the cumulative number of rows fetched by orafetch.

oramsg (starttime)

The time at which the job was scheduled to be started. Defined for jobs only.

Event to Fixit Job Tcl Array

OraTcl creates and maintains a Tcl global array trigevent to pass a Tcl array to an Enterprise Manager Fixit job. The trigevent array can also be used from within the Fixit job itself.

trigevent Element

The following are trigevent elements:

trigevent (name)

Name of the event that caused the Fixit job to be fired.

trigevent (object)

Target or node in your network.

trigevent (arguments)

Arguments to the triggering event (varies according to the event)

trigevent (results)

Results of the event. For example, this element returns the number 35 indicating 35 percent for the CPU Utilization event test.

trigevent (severity)

Severity of the event as indicated by the following numbers: -1 (Clear), 1 (Warning), 2 (Alert)


Warning:

trigevent only works with Fixit jobs. If this array is passed to a non-Fixit job, trigevent will be undefined (NULL) and the job will fail. For this reason, the trigevent array should always be checked before its elements are dereferenced.


Example

The following example shows a Fixit job that implements two separate tasks:

The Fixit job in this example is associated with the CPUUTIL event test, which monitors for specific levels of CPU activity. For this event test, the parameters are set as follows:

When the event containing the CPUUTIL event test is triggered, the associated Fixit job is executed. The Fixit job generates the following output:

First task executed: Information from the trigevent array is displayed.

Event name: /oracle/host/perf/cpuutil
Event object: aholser-sun
Event arguments: {1} {20} {10}
Event results: 63
Event severity: 2

Second task executed: Operating system command top -d1 -ocpu is executed.

last pid: 26420;  load averages:  0.64,  0.56,  0.48    13:16:00
111 processes: 110 sleeping, 1 on cpu

Memory: 128M real, 7080K free, 89M swap in use, 912M swap free

  PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND
  727 root       1  30    0  128M   19M sleep  17:22 10.54% Xsun
25915 aholser    4  31    0   12M 5032K sleep   4:47 10.32% dbsnmp
  823 aholser    1  34    0   10M 4368K sleep   2:11  7.37% dtterm
26402 aholser    1  34    0  976K  872K sleep   0:03  3.57% find
26415 aholser    4  34    0   11M 4304K sleep   0:00  3.11% dbsnmp
26403 aholser    1  23    0  976K  872K sleep   0:02  2.60% grep
25914 aholser    4  34    0   11M 4832K sleep   0:56  2.56% dbsnmp
26419 aholser    1  -5    0 1576K 1368K cpu     0:00  1.64% top
  894 aholser    1  33    0 5904K 3456K sleep   7:07  0.86% view_server
  159 root       5  23    0 3176K 1976K sleep   6:21  0.29% automountd
26418 aholser    1  25    0  920K  760K sleep   0:00  0.28% sh
 1007 root       3  33    0 1840K 1400K sleep   0:40  0.06% cachefsd

Use of Tcl with the Intelligent Agent

Tcl scripts are used by the Intelligent Agent for jobs and events. While both are Tcl scripts, they are distinct in the Agent and in the user interface.

Jobs are scripts scheduled to run once or multiple times. They typically cause side-effects, such as starting up a database, performing a backup, or sending output to the screen via the puts command, and can potentially have long execution times. Jobs can have output files and input files, such as a SQL script, while event scripts do not. Note that output files on Unix, DOS, or OS/2 are stdout redirected.

Event scripts, on the other hand, are used uniquely for detecting exceptions. A Tcl event script can monitor databases, host systems, or SQL*Net services by using a variety of means. If the script determines that a certain condition has occurred, it can send a return code to the Agent that states the severity of the event. Event scripts tend to run more frequently than jobs and so they are expected to have relatively short execution times. Also, it is assumed that event scripts do not cause any side effects.

While both jobs and events use Tcl to accomplish their tasks, they are very different in nature and as such have different execution environments. Specifically, on UNIX systems, jobs are forked into a separate process, while events are usually executed in-line with the Agent code.

The Tcl interpreter state is saved between executions and the value of Tcl global variables is preserved, for inline event scripts only, to give the illusion of a virtual process. This allows an event script to maintain a history so that the event does not get raised over and over again. For example, after you have notified the console that a value has gone above 90, you can refrain from notifying it again until the value goes below 80 and then back above 90. Database connections using the oralogon function are cached across all inline event scripts, so that repeated event scripts that use the same connect string can utilize the same connection.

Not all commands and global variables are available to both jobs and events. Jobs will not have the oraobject global variable that tells an event what service it is running against. Events will not have the orainput global that jobs use for SQL*Plus scripts.

NLS Issues and Error Messages

When a user registers for an event or schedules a job, the user's language preference is available to the Agent. There is a special remote procedure call which reports the language and current address of each console user. The Agent proceeds to issue an ALTER SESSION command to the specified language every time the oralogon function is called. This means that any subsequent messages or output coming from the Oracle server will be in the user's language. In addition, character set conversion is explicitly not done on the Agent, so that the Console can do it on the user's side.

If an event script or a job script fails execution, an error message is sent back to the Console in the user's language. Typically this will be an Oracle message returned by one of the Oracle Tcl extensions, if the verb was given inadequate parameters. For example oralogon might return the error: "ERROR: ORA-01017: invalid username/password; logon denied" if it is given an incorrect connect string. However, the error message could also be a Tcl specific message, such as: "ERROR: Tcl-00456: division by zero error", which will be stored in a message file and thus can be returned in the user's preferred language. The default language used by the Agent will be American English if no user language preference is specified or if an error message text does not exist in the user's language.

OraTcl Functions and Parameters

This section lists the OraTcl functions and parameters. Functions or other words that appear in OraTcl syntax are shown in this font: function. Parameters in square brackets `[option]' are optional, and the `|' character means `or'. All parameters are passed into the functions and are IN mode.

SQL and PL/SQL functions

RDBMS administration functions

SNMP accessing functions

Communication with the Intelligent Agent and other Oracle software functions

Character set conversion and error handling functions

General purpose utility functions

Common Parameters

The following parameters are used in multiple OraTcl functions and the descriptions are provided in this section.

column

The column name that is the LONG or LONG RAW column.

connect_string

A valid Oracle database connect string, in one of the forms:

name | name/password | name@n:dbname | name/password@n:dbname
destaddress

destaddress is the destination address of the Agent.

filename

The name of the file that contains the LONG or LONG RAW data to write into the column or the name of the file in which to write the LONG or LONG RAW data.

logon-handle

A valid cursor-handle previously opened with oraopen. The handle is a mapping in memory used to track functions.

rowid

The Oracle database rowid of an existing row, and must be in the format of an Oracle rowid datatype.

table

The Oracle database table name that contains the row and column.

convertin

Purpose

This function converts the parameter string from the client's (Console) character set to the destination character set. The function returns the converted string.

Syntax

convertin dest_characterset string

Parameters

dest_characterset

Destination character set. For jobs or events, use $oramsg(agent_characterset). See "oramsg Elements".

string

The string that is converted.

Comments

The client and the Agent node may use different languages or character sets. It is the responsibility of the Tcl script developer to perform the character set conversion. In general, all the job or event input parameters should be converted unless they are guaranteed to be ASCII.

convertout

Purpose

This function converts the parameter string from the destination character set to the client's (Console) character set. The function returns the converted string.

Syntax

convertout dest_characterset string 

Parameters

dest_characterset

Destination character set. For jobs or events, use $oramsg(agent_characterset). See "oramsg Elements".

string

The string that is converted.

Comments

The client and the Agent node may use different languages or character sets. It is the Tcl script developers' responsibility to perform the character set conversion. In general all the job or event output should be converted unless they are guaranteed to be ASCII.

msgtxt

Purpose

This function returns message text in the client's (Console) language and characterset for the given product name, facility and message number. The output is in the format of "FACILITY-ERROR : MESSAGE TEXT".

Syntax

msgtxt product facility error_no 

Parameters

product

Product name. For example, rdbms.

facility

Facility name. For example, ora.

error_no

Error number. For example, 1101.

Comments

This function is used to put out error messages in the job output file. The message will be displayed in the client's (Console) language.

msgtxt1

Purpose

This function returns a message in the client's (Console) language for the given product name, facility and message number. The output is in the format of "MESSAGE TEXT".

Syntax
msgtxt1 product facility error_no 
Parameters
product

Product name. For example, rdbms.

facility

Facility name. For example, ora.

error_no

Error number. For example, 1101.

Comments

This function is used to put out confirmation messages in the job output file. The message will be displayed in the client's (Console) language.

oraautocom

Purpose

This function enables or disables automatic commit of SQL data manipulation statements using a cursor opened through the connection specified by logon-handle.

Syntax
oraautocom logon-handle {on | off}
Parameters
logon-handle

See "Common Parameters".

Comments

oraautocom raises a Tcl error if the logon-handle specified is not open.

Either on or off must be specified. The automatic commit feature defaults to off.

oracancel

Purpose

This function cancels any pending results from a prior orasql function that use a cursor opened through the connection specified by logon-handle.

Syntax
oracancel logon-handle
Parameters
logon-handle

See "Common Parameters".

Comments

oracancel raises a Tcl error if the logon-handle specified is not open.

oraclose

Purpose

This function closes the cursor associated with logon-handle.

Syntax
oraclose logon-handle
Parameters
logon-handle

See "Common Parameters".

Comments

oraclose raises a Tcl error if the logon-handle specified is not open.

oracols

Purpose

This function returns the names of the columns from the last orasql, orafetch, or oraplexec function as a Tcl list. oracols may be used after oraplexec, in which case the bound variable names are returned.

Syntax
oracols logon-handle
Parameters
logon-handle

See "Common Parameters".

Comments

oracols raises a Tcl error if the logon-handle specified is not open.

The oramsg array index collengths is set to a Tcl list corresponding to the lengths of the columns; index coltypes is set to a Tcl list corresponding to the types of the columns; index colprecs is set to a Tcl list corresponding to the precision of the numeric columns, other corresponding non-numeric columns are a null string (Version 7 only); index colscales is set to a Tcl list corresponding to the scale of the numeric columns, other corresponding non-numeric columns are a null string (Version 7 only).

oracommit

Purpose

This function commits any pending transactions from prior orasql functions using a cursor opened with the connection specified by logon-handle.

Syntax
oracommit logon-handle
Parameters
logon-handle

See "Common Parameters".

Comments

oracommit raises a Tcl error if the logon handle specified is not open.

oradbsnmp

Purpose

This function retrieves SNMP MIB values.

Syntax
oradbsnmp get | getnext object_Id
Parameters
object_Id

object_Id can be either an actual MIB object Id, such as "1.3.6.1.2.1.1.1.0", or an object name with a possible index attached to it, such as "sysDescr" or "sysDescr.0".

Comments

oradbsnmp is a function for retrieving SNMP MIB values maintained by the Agent, such as the RDBMS public MIB or the Oracle RDBMS private MIB. It does not write to the well-known UDP port for SNMP and obtains its values directly from the Agent's internal data structures. It works if the host does not have an SNMP master Agent running on it. See "orasnmp" for more details on what get and getnext do. There are several reasons why oradbsnmp should be used instead of fetching the values from V$ tables with SQL commands:

orafail

Purpose

This function forces a Tcl script to fail.

Syntax
orafail errormsg
Parameters
errormsg

errormsg can either be a quoted string of text or a string of the form: FAC-XXXXX where XXXXX is an Oracle message number for the given facility, such as VOC-99999.

Comments

The error message will be used for display purposes on the client side.

orafetch

Purpose

This function returns the next row from the last SQL statement executed with orasql as a Tcl list.

Syntax
orafetch logon-handle [commands]
Parameters
logon-handle

See "Common Parameters".

commands

The optional commands allows orafetch to repeatedly fetch rows and execute commands for each row.

Comments

orafetch raises a Tcl error if the logon-handle specified is not open.

All returned columns are converted to character strings. A null string is returned if there are no more rows in the current set of results. The Tcl list that is returned by orafetch contains the values of the selected columns in the order specified by select.

Substitutions are made on commands before passing it to Tcl_Eval() for each row. orafetch interprets @n in commands as a result column specification. For example, @1, @2, @3 refer to the first, second, and third columns in the result. @0 refers to the entire result row, as a Tcl list. Substitution columns may appear in any order, or more than once in the same command. Substituted columns are inserted into the commands string as proper list elements. For example, one space will be added before and after the substitution and column values with embedded spaces are enclosed by {} if needed.

A Tcl error is raised if a column substitution number is greater than the number of columns in the results. If the commands execute a break, orafetch execution is interrupted and returns with Tcl_OK. Remaining rows may be fetched with a subsequent orafetch function. If the commands execute return or continue, the remaining commands are skipped and orafetch execution continues with the next row. orafetch will raise a Tcl error if the commands return an error. Commands should be enclosed in "" or {}.

OraTcl performs conversions for all data types. Raw data is returned as a hexadecimal string, without a leading "0x". Use the SQL functions to force a specific conversion.

The oramsg array index rc is set with the return code of the fetch. 0 indicates the row was fetched successfully; 1403 indicates the end of data was reached. The index of rows is set to the cumulative number of rows fetched so far.

The oramsg array index maxlong limits the amount of long or long raw data returned for each column returned. The default is 32768 bytes. The oramsg array index nullvalue can be set to specify the value returned when a column is null. The default is "0" for numeric data, and "" for other datatypes.

destaddress may be obtained from the orainfo function. Note that the address provided must be the spawn address of the Agent, the special address on which it listens for file transfer requests, and not the normal address used for all other RPCs.

Additional Information:

For more information on the address of an Intelligent Agent, see the chapter on configuring the Agent in the Oracle Enterprise Manager Installation Guide.

orainfo

Purpose

This function is used by jobs to get configuration information.

Syntax
orainfo destaddress
Parameters
destaddress

See "Common Parameters".

Comments

orainfo fetches Agent configuration information from the Agent at destaddress. If destaddress is not present, then it is fetched from the Agent on the local machine. The Agent configuration is a Tcl list, as follows:

orajobstat

Purpose

This function is used by a job to send intermediate output back to the Console.

Syntax
orajobstat destaddress string
Parameters
destaddress

See "Common Parameters".

string

string can either be a quoted string of text or a string of the form: FAC-XXXXX where XXXXX is an Oracle message number for the given facility, such as VOC-99999. The string is used for display on the client side.

Comments

destaddress is the address of the Agent, not the daemon. This function is issued from a job process, not from within an Agent process. The Agent's address can be obtained with orainfo.

oralogoff

Purpose

This function logs off from the Oracle server connection associated with logon-handle.

Syntax
oralogoff logon-handle
Parameters
logon-handle

See "Common Parameters"..

Comments

oralogoff raises a Tcl error if the logon handle specified is not open. oralogoff returns a null string.

oralogon

Purpose

This function connects to an Oracle server using connect_string.

Syntax
oralogon connect_string
Parameters
connect_string

See "Common Parameters"..

Comments

A logon-handle is returned and should be used for all other OraTcl functions using this connection that require a logon-handle. Multiple connections to the same or different servers are allowed.

Additional Information:

When oralogon is used in an event script, it benefits from the connection cache. It will usually be able to reuse the connections opened by other event scripts against the same database. See "NLS Issues and Error Messages" for details. oralogon raises a Tcl error if the connection is not made for any reason, such as login incorrect or network unavailable. If connect_string does not include a database specification, the value of the environment variable ORACLE_SID is used as the server.

oralogon_unreached

Purpose

This function connects to an Oracle server using a connect string and an optional role. this connection cannot be shared.

Syntax

oralogon connect_string [AS] [SYSDBA|SYSOPER|NORMAL]

connect_string

See common parameters on "Common Parameters"..

Comments

This verb is identical to oralogon except that the returned connection is not shared. Also, an optional role can be specified.

oraopen

Purpose

This function opens an SQL cursor to the server. oraopen returns a cursor to be used on subsequent OraTcl functions that require a logon-handle.

Syntax
oraopen logon-handle
Parameters
logon-handle

See "Common Parameters"..

Comments

oraopen raises a Tcl error if the logon-handle specified is not open. Multiple cursors can be opened through the same or different logon handles, up to a maximum of 25 total cursors.

oraplexec

Purpose

This function executes an anonymous PL block, optionally binding values to PL/SQL variables.

Syntax
oraplexec logon-handle pl_block [:varname value ...]
Parameters
logon-handle

See "Common Parameters"..

pl_block

pl_block may either be a complete PL/SQL procedure or a call to a stored procedure coded as an anonymous PL/SQL block.

:varname value

:varname value are optional pairs.

Comments

oraplexec raises a Tcl error if the logon-handle specified is not open, or if the PL/SQL block is in error. oraplexec returns the contents of each :varname as a Tcl list upon the termination of PL/SQL block. oraplexec returns the result set as its return value in a Tcl list.

Optional :varname value pairs may follow pl_block. Varnames must be preceded by a colon, and match the substitution names used in the procedure. Any :varname that is not matched with a value is ignored. If a :varname is used for output, the value should be coded as a null string, "".

The oramsg array index rc contains the return code from the stored procedure.

orareadlong

Purpose

This function reads the contents of a LONG or LONG RAW column and write results into a file.

Syntax
orareadlong logon-handle rowid table column filename
Parameters
logon-handle rowid table column filename

See "Common Parameters"..

Comments

orareadlong returns a decimal number upon successful completion of the number of bytes read from the LONG column.

orareadlong raises a Tcl error if the logon-handle specified is not open, or if rowid, table, or column are invalid, or if the row does not exist.

orareadlong composes and executes an SQL select statement based on the table, column, and rowid. A properly formatted Rowid may be obtained through a prior execution of orasql, such as "SELECT rowid FROM table WHERE ...".

orareportevent

Purpose

This function is used by jobs to report an unsolicited event to the Agent and Event Management system in the Console. The oemevent executable can also be used.

Syntax
orareportevent eventname object severity message [results]
Parameters
eventname

eventname is the name of the event. This is the four-part name of the event in the form:

 /vendor/product/category/name

You can enter any character string but all four parts and the forward slashes (/) are required.

The first two levels of name have special significance and have many predefined strings that Oracle script writers must use:

The eventname is assumed to be in 7-bit ASCII, so that it never changes regardless of platform or language. See eventdef.tcl in the ORACLE_HOME\net8\admin directory (Oracle Enterprise Manager release 1.5.0 on a Windows NT platform) for a list of defined event names.

Note:

The actual event script name may be shortened, upper-cased, or manipulated in other ways to make it a legal, unique filename on a given platform. The format is operating system-specific. For example, /oracle/rdbms/security/SecurityError can be stored as $oracle_home/network/agent/events/oracle/rdbms/security/securityerror.tcl on a Unix system.

object

object is the name of the object that the event is monitoring, such as the database or service name listed in the snmp.visibleservices parameter in the snmp.ora file, or $oramsg(nodename).

severity

severity is the level of severity of the event. For orareportevent, the value is 1 (warning), 2 (alert), or -1 (clear). For oemevent, this is the literal text string alert, warning, or clear.

message

message is a quoted text string that is displayed in the Console, such as "File not found."

[results]

results is any results that may occur from the event. This is a Tcl list with the specific results for the event, such as the tablespace in error or the user who had a security violation.

Comments

This is the method for any job to report an unsolicited event to the Agent, and back to the Console. . For information on the Event Management system, see the Oracle Enterprise Manager Administrator's Guide.

oraroll

Purpose

This function rolls back any pending transactions from prior orasql functions that use a cursor opened through the connection specified by logon-handle.

Syntax
oraroll logon-handle
Parameters
logon-handle

See "Common Parameters"..

Comments

oraroll raises a Tcl error if the logon handle specified is not open.

orasleep

Purpose

This function causes the Tcl script to pause for a number of seconds.

Syntax
orasleep seconds
Parameters
seconds
Comments

orasleep calls slcsleep() for the required number of seconds. There is no default, minimum, or maximum value.

orasnmp

Purpose

This function performs either an SNMP get or getnext operation on the object specified by object_id.

Syntax
orasnmp get | getnext object_Id
Parameters
object_Id

The object_Id can be either an actual MIB object Id, such as "1.3.6.1.2.1.1.1.0", or an object name with an index attached to it, such as "sysDescr" or "sysDescr.0".

Comments

Object names come from MIB text files. A full network manager, such as OpenView, has a MIB compiler that accepts MIB files and parses the ASN.1, creating a database of all objects in all the MIBs. The Agent needs to be simpler. There is a standard configuration directory which contains one or more two-column ASCII files of the format:

"rdbmsDbPrivateMibOID",   "1.3.6.1.2.1.39.1.1.1.2",
"rdbmsDbVendorName", "1.3.6.1.2.1.39.1.1.1.3",
"rdbmsDbName", "1.3.6.1.2.1.39.1.1.1.4",
"rdbmsDbContact", "1.3.6.1.2.1.39.1.1.1.5",
....

The Tcl interpreter reads these files and does a binary search on them at runtime to resolve an object name to an object_Id.

The index values to use for Oracle services are configured via the snmp.ora file. These indices can also be obtained from the oraindex global variable. See "Server Message and Error Information".

The result of orasnmp is a Tcl list of the form:

{object_id   value}

where object_Id is the object id associated with value. In the case of an orasnmp get, object_Id is the same as object, while for a getnext, it would be the next logical object_Id. It is assumed that the orasnmp operation applies to the local host only. The function actually sends out an SNMP query to the well-known SNMP port on the local host, so it is possible to query MIB variables other than Oracle's, such as those of the host or other applications that support SNMP. An SNMP Master Agent needs to be running on the local host for this function to work. See "oradbsnmp" for an optimized way to retrieve the Oracle database MIB objects. If the Master Agent is not running, this function fails.

orasql

Purpose

This function sends the Oracle SQL statement SQL statement to the server.

Syntax
orasql logon-handle sql_stmt
Parameters
logon-handle

See "Common Parameters".

sql_stmt

sql_stmt is a single, valid SQL statement.

Comments

logon-handle must be a valid handle previously opened with oraopen. orasql raises a Tcl error if the logon-handle specified is not open, or if the SQL statement is syntactically incorrect.

orasql will return the numeric return code 0 on successful execution of the SQL statement. The oramsg array index rc is set with the return code; the rows index is set to the number of rows affected by the SQL statement in the case of insert, update, or delete. Only a single SQL statement may be specified in sql_stmt. orafetch allows retrieval of return rows generated. orasql performs an implicit oracancel if any results are still pending from the last execution of orasql.

Table inserts made with orasql should follow conversion rules in the Oracle SQL Reference manual.

orastart

Purpose

This function starts an Oracle database instance.

Syntax
orastart connect_string [init_file] [SYSDBA|SYSOPER] [RESTRICT] 
[PARALLEL] [SHARED]
Parameters
connect_string

See "Common Parameters".

init_file

init_file is the path to the init.ora file to use.

Comments

The default for init_file is:

ORACLE_HOME/dbs/init${ORACLE_SID}.ora

[SYSDBA|SYSOPER] are role flags for the user starting up the database. [RESTRICT] [PARALLEL] [SHARED] are database options. If [RESTRICT] is specified, database is started in restricted mode.

orastop

Purpose

This function stops an Oracle database instance.

Syntax
orastop connect_string [SYSDBA|SYSOPER] [IMMEDIATE|ABORT] 
Parameters
connect_string

See "Common Parameters".

Comments

[SYSDBA|SYSOPER] are role flags for the user shutting down the database. [IMMEDIATE|ABORT] are the shutdown mode flags.

Note:

Shutdown normal might be expected to fail every time, because the Agent maintains its own connection to the database, but we send a special RPC to the Agent when this is done, which causes it to disconnect from the database.

oratime

Purpose

This function returns the current date and time.

Syntax
oratime
Parameters

None

Comments

None

orawritelong

Purpose

This function writes the contents of a file to a LONG or LONG RAW column.

Syntax
orawritelong logon-handle rowid table column filename
Parameters
logon-handle rowid table column filename

See "Common Parameters".

Comments

orawritelong composes and executes an SQL update statement based on the table, column, and rowid. orawritelong returns a decimal number upon successful completion of the number of bytes written to the LONG column. A properly formatted ROWID may be obtained through a prior execution of the orasql function, such as "SELECT rowid FROM table WHERE ....".

orawritelong raises a Tcl error if the logon-handle specified is not open, or if rowid, table, or column are invalid, or if the row does not exist.