Oracle Intelligent Agent User's Guide Release 8.1.7 Part Number A85251-01 |
Library | Service | Contents | Index

# 3
Job and Event Scripts

Topics covered in this document include:

- Scripting Language

- Server Message and Error Information

- Use of Tcl with the Intelligent Agent

- NLS Issues and Error Messages

- OraTcl Functions and Parameters

## 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:

- Host system access for handling with files and devices, launching programs, and executing operating system functions.

- SQL and PL/SQL functions for accessing the RDBMS.

- RDBMS administration functions.

- SNMP accessing, both for the database MIB variables that the Agent itself supports, and for external MIBs, like the host's or other SNMP-enabled services.

- Communication with the Oracle Intelligent Agent and other Oracle software, such as Oracle Trace.

- A syntax for describing job and event scripts that:

  - Can be used to drive the user interface.

  - Provide information on the nature of the job or event, and any input or output.

  - Allow access to the Oracle message file system for NLS support.

### 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 provides a standard syntax. After you learn Tcl, you are able to issue commands easily to any Tcl-based application.

- Tcl provides programmability. All a Tcl application needs to do is to implement a few application-specific low-level commands. Tcl provides many utility commands plus a general programming interface for building up complex command procedures. By using Tcl, applications do not need to re-implement these features.

- Extensions to Tcl provide mechanisms for communicating between applications by sending Tcl commands back and forth. The common Tcl language framework makes it easier for applications to communicate.

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:

- http://sunscript.sun.com/

- http://www.neosoft.com/tcl

- http://www.scriptics.com/resource/doc/papers/

##### Note:

World Wide Web site locations often change and the addresses may not be available in the future.

### 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:

- SQL and PL/SQL functions

- RDBMS administration functions

- SNMP accessing

- Communication with the intelligent Agent and other Oracle software

- Character set conversion and error handling verbs

- General purpose utility functions

For descriptions of the OraTcl functions and variables, see OraTcl Functions and Parameters on page 3-14.

#### Example: OraTcl Script

The following example illustrates the basic use of OraTcl.

    #!/usr/local/bin/Tcl -f

    #

    # 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} {

      oraplexec $cur2 $monthly_pay_pl :monthly "" :jobclass "$job"

      set total_for_job [lindex [orafetch $cur2] 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 on page 3-15 and convertout on page 3-17.

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 on page 3-15 and convertout on page 3-17.

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 (ocifunc)

The number OCI code of the last OCI function called by OraTcl. See the Programmer's Guide to the Oracle Call Interface for descriptions.

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 Oracle Error Messages and Codes Manual 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 (sqlfunc)

The numeric OCI code of the last SQL function performed. See the Programmer's Guide to the Oracle Call Interface for descriptions.

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:

- Tcl script using trigevent to pass an array to a Fixit job.

      global trigevent
      if {[info exists trigevent]} {
        puts "Event name: $trigevent(name)"
        puts "Event object: $trigevent(object)"
        puts "Event arguments: $trigevent(arguments)"
        puts "Event results: $trigevent(results)"
        puts "Event severity: $trigevent(severity)"
      } else {
        puts "Not a fixit"
      }

- Execution of an operating system command

      top -d1 -ocpu

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:

- Alert Threshold = 20

- Warning Threshold = 10

- Fixit Job = Option is selected in the property sheet.

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

  #####

  oraautocom | oracancel | oraclose | oracols | oracommit
  orafetch | oralogoff | oralogon | oraopen | oraplexec
  orareadlong | oraroll | orasql | orawritelong |

- RDBMS administration functions

  #####

  orastart | orastop

- SNMP accessing functions

  #####

  oradbsnmp | orasnmp

- Communication with the Intelligent Agent and other Oracle software functions

  #####

  orafail | orainfo | orajobstat | orareportevent

- Character set conversion and error handling functions

  #####

  convertin | convertout | msgtxt | msgtxt1

- General purpose utility functions

  #####

  orasleep | oratime

### 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 database specific jobs or events, use $oramsg(db_characterset). For node specific jobs or events, use $oramsg(agent_characterset). See oramsg Elements on page 3-6.

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 database specific jobs or events, use $oramsg(db_characterset). For node specific jobs or events, use $oramsg(agent_characterset). See oramsg Elements on page 3-6.

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 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 on page 3-14.

##### 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 on page 3-14.

##### 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 on page 3-14.

##### 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 on page 3-14.

##### 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 on page 3-14.

##### 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 on page 3-31 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:

- The Agent maintains a cache of MIB values fetched from the V$ tables to avoid burdening the RDBMS excessively. oradbsnmp is often faster than SQL and imposes less overhead on the system.

- When SGA access is implemented, it will be transparent to this function, for those MIB variables that are fetched directly from the SGA.

- In the case of getnext, the next object_id is the next object_id within the private and public RDBMS MIBs, and not one of another MIB. It is impossible to retrieve system-specific information using this function; use orasnmp.

### 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 on page 3-14.

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 on page 3-14.

##### 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:

- A list of databases monitored by this Agent. The list includes the database name, ORACLE_HOME, and SID for each database.

- The Agent's normal RPC address, a tnsnames (TNS) string.

- The Agent's file transfer address, a TNS string.

### 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 on page 3-14.

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 on page 3-14.

##### 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 on page 3-14.

##### 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, up to a maximum of six total connections.

##### Additional Information:

The connection limit is covered in the operating system-specific notes. 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 on page 3-13 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.

### 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 on page 3-14.

##### 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 on page 3-14.

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.

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 on page 3-14.

##### 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:

- Level one is the definer of this script, typically the integrating company name such as oracle, or user for unspecified customers.

- Level two is the name of the product to which this script is related, for example rdbms, office, agent, osgeneric, sqlnet, or hpux. All Oracle services have defined names which 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 on page 3-14.

##### 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 on page 3-6.

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 on page 3-21 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 on page 3-14.

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 on page 3-14.

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 on page 3-14.

##### 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 on page 3-14.

##### 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.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index