Oracle report server в какой таблице

6

8.2 Reports Server Configuration File

The configuration settings for the Reports Server component of Oracle Reports Services are stored in the XML file rwserver.conf and rwbuilder.conf , located in the directories specified in Table 8-1.

Both files are supported by the rwserver.template file in ORACLE_HOME\reports\conf , which contains default server configuration values on both Windows and UNIX.

The rwserver . conf file is the default server configuration file. The rwbuilder.conf file configures the server instance used in-process by Oracle Reports Builder.

The rwserver . conf and rwbuilder.conf files are nearly identical. The only difference between them is that rwbuilder.conf does not use the persistFile or security configuration elements, described later in this section.

Both of these files are created automatically, under the following circumstances:

The rwserver .conf file is created when a new Reports Server component is created.

The rwbuilder.conf file is pre-configured out-of-the-box.

This section describes:

8.2.1 Reports Server Configuration Elements

The rwserverconf.xsd file provides the following data type definitions for configuring rwserver.conf and rwbuilder.conf elements and attributes:

These elements along with their related attributes and sub-elements are discussed in the following subsections.

Note that these are XML elements, and XML is case-sensitive. Additionally, when you add any of these elements to the rwserver . conf or rwbuilder.conf configuration file, you must follow the order of elements as described in rwserverconf.xsd .

8.2.1.1 ORBPorts

The ORBPorts element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the ORBPorts element may be specified as shown in this example:

To specify a port range:

To specify specific ports:

Optional. By default, CORBA objects use any available port for communication. Since Reports Server uses CORBA for communication, it will use any available free port for communication. If you want Reports Server to use predefined ports instead of random ports, you must include the ORBPorts element in the server configuration file.

The ORBPorts element specifies either a range of ports or specific ports for CORBA communication. When ORBPorts is specified, Reports Server will choose one of the ports from the list specified for ORB internal communication. One port is needed for Reports Server and one for each engine.

The ORBPorts element is used to assign specific ports to Reports Server and engines for running report and other requests. Do not confuse these ports with those you see in Oracle Enterprise Manager through the Ports link, which are ports reserved for Reports Server discovery mechanism and the Oracle Reports Bridge component.

You cannot specify port numbers for individual engines. Each engine picks up the next port number in the list. Suppose you have the maxengine attribute of the engine element set to 5 for rwEng , and URLEng is also enabled, then you must specify a minimum of 7 ports in the ORBPorts element (1 for Reports Server + 5 for rwEng + 1 for rwURLEng ).

The ORBPorts element attribute is described in Table 8-2.

Table 8-2 Attribute of the ORBPorts Element

Range of values

Numbers separated by commas

The port range that can be used for Reports Server and engine communication through CORBA.

The ORBPorts element should be defined only if you have enabled TCP port filtering on your server where Reports Server is running. If port filtering is enabled, you can open few ports for Reports Server, then use ORBPorts to specify them in the server configuration file for Reports Server/engine communication. If any of the ports are not available, Reports Server or engines may fail to start and an error displays.

8.2.1.2 pluginParam

The pluginParam element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the pluginParam element may be specified as shown in this example:

Optional. You can have as many pluginParam elements as you require.

The pluginParam element provides a means of specifying plug-ins that can be used by several built-in destinations such as e-mail, JDBC pluggable data source (PDS), Text PDS, and so on. It is not used by the FTP and WebDAV built-in destinations, and is not available to custom pluggable destinations, such as fax. Now every server has its own textpds.conf, jdbcpds.conf and xmlpds.conf files.

You can specify any plug-in parameter and name it in any way as long as it is supported or required by the built-in destination.

The pluginParam element attributes are described in Table 8-3.

Table 8-3 Attributes of the pluginParam Element

Set for an e-mail destination in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Mail Server .

The name of the plug-in parameter.

See Properties below for information about specifying the enableSSL property when name=»mailServer» .

The value of the specified plug-in parameter.

Describes the type of plug-in being specified.

For text , specify the string that is required to identify the named plug-in parameter, for example, the name of a mail server. Text means the content of the pluginParam element is text, so the getPluginParam() method will return the exact content specified in the element.

For file , specify the directory path and filename of the plug-in parameter file. Use the standards for specifying directory paths appropriate to Reports Server’s host machine (either Windows or UNIX). File means that the content of the pluginParam element is a filename, and the getPluginParam() method will return the content read from the file specified.

For url , specify the full, absolute URL required by the plug-in parameter, for example, the full URL to an FTP site. url means the content of the pluginParam element is a URL, and the getPluginParam() method will return the content read from that URL. The URL you use must reside on the same side of the firewall as Oracle Reports Services.

Note that when you have a default type ( text ), it is not necessary to specify it in the pluginParam string. The example that heads this section does not specify a type because the plug-in parameter, a mail server name, is the default type, text .

You can also optionally enter multiple property sub-elements for the pluginParam element. The only requirement is that they be name/value pairs recognized by the specified plug-in parameter. For example:

In this example, the property sub-element specifies the enableSSL property, which is only applicable to mailServer . If the specified mailServer is SSL-enabled, it rejects plain connection requests, so it is necessary to use SSL Sockets to establish a connection with the specified mailServer and send emails, by default, the value of enableSSL is no for compatibility with prior releases.

8.2.1.3 cache

The cache element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the cache element may be specified as shown in this example:

Optional. You can have a maximum of one cache element in your server configuration file. If no cache element is specified, the default is used ( oracle.reports.cache.RWCache ).

The cache element specifies the Java class that defines the server’s cache implementation. You can use the default cache Java class or develop your own implementation through the Oracle Reports Services Cache API.

For more information on the cache API, refer to the Reports Software Development Kit (RSDK) on the Oracle Technology Network (OTN): on the Oracle Reports page ( http://www.oracle.com/technology/products/reports/index.html ), click SDK .

The cache element attribute is described in Table 8-4.

Table 8-4 Attribute of the cache Element

See the Description column

A fully qualified Java class that implements the oracle.reports.cache.Cache interface.

You can also optionally enter multiple property sub-elements for the cache element. The only requirement is that they be name/value pairs recognized by the implementation class you register under cache . For example, if you use the default cache Java class that is provided with Oracle Reports Services, your configuration entry might look like this:

In the preceding example, cacheSize is measured in megabytes, and cacheDir , which points to the location of the cache, is specified for a Windows platform. On UNIX, use UNIX standards, for example:

The default cache Java class also provides the following properties:

JOCCacheDir uses the Java object cache. For example:

maxCacheFileNumber is the maximum number of files allowed in the cache. For example:

Specify this property value in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Maximum Cached Files .

ignoreParameters lists any report parameters you want to be ignored when Reports Server constructs the cache key. (The cache key is used by Reports Server to determine if an incoming job request matches existing output in the cache.)

To configure the Java Object Cache (JOC) Cache Directory, use the following:

Where host is the machine where the shared folder is available, and shared is the folder name.

8.2.1.4 cluster

In Oracle Reports 10 g Release 2 (10.1.2), Reports Server clustering was deprecated (see A Guide to Functional Changes Between Oracle Reports 6i and 11g for more details), and the cluster element is not valid.

For information about Oracle Fusion Middleware techniques for high availability, refer to Section 2.5, «Setting Up a High Availability Environment».

8.2.1.5 connection

The connection element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the connection element may be specified as shown in this example:

Optional. If you do not specify a connection element in your server configuration file, default values will be used (see Table 8-5). You can have a maximum of one connection element in your server configuration file.

The connection element defines the rules of engagement between the server and the clients connected to it.

The connection element attributes are described in Table 8-5.

Table 8-5 Attributes of the connection Element

Set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Connection Idle Timeout (min) .

Allowable amount of time in minutes the connection can be idle.

Set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Maximum Connections .

The maximum number of requests that Reports Server can service simultaneously. Requests in excess of the maxConnect value return a Java exception.

The connection element also includes the orbClient sub-element, described in Section 8.2.1.17, «orbClient».

8.2.1.6 destination

The destination element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the destination element may be specified as shown in this example:

Optional. If you do not enter a destination element in the server configuration file, the provided destination classes will be used (printer, e-mail, file, cache, and Oracle Portal—which is an exception in that it requires an entry in the server configuration file so that you may specify the userid and password the server will use to log in to the portal). You can have from zero to multiple destination elements in your server configuration file.

Use the destination element to register destination types with the server.

You need not register the following default destinations:

You may want to register the following default destination:

Oracle Portal: The entry for this destination is created by default in the server configuration file, but it is commented out. To start using this destination, you must uncomment the destination entry, and also provide appropriate property values (for example, the value for the portalUserid property).

You must register any new destination types you create through the Oracle Reports Services Destinations API.

For more information on the destination API, refer to the Reports Software Development Kit (RSDK) on the Oracle Technology Network (OTN): on the Oracle Reports page ( http://www.oracle.com/technology/products/reports/index.html ), click SDK .

The destination element attributes are described in Table 8-6.

Table 8-6 Attributes of the destination Element

A fully qualified Java class that is a subclass of Reports Server Destination Java class ( oracle.reports.server.Destination ). Allowable values include:

Identifies the destination type; for example:

You can also optionally enter multiple property sub-elements for the destination element. The only requirement is that they be name/value pairs recognized by the Java class that is a subclass of the Reports Server Destination Java class. For example:

In this example, the property sub-element provides connect information to enable Reports Server to access Oracle Portal. The encrypted attribute is included to automatically invoke encryption on the portalUserid value the next time Reports Server is started.

For portalUserid database connection strings, both the thin ( scott/tiger@testhost.mydomain.com:1521:iasdb ) and Oracle Call Interface ( scott/tiger@ordb ) JDBC formats are supported.

Should your destination implementation require additional information, specify the information in the pluginParam element.

8.2.1.7 environment

The environment element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the environment element may be specified as shown in this example:

Optional. You can have as many environment elements as you require.

The environment element defines the characteristics (that is, environment variables) that you want to use to establish a particular runtime environment. You may include as many environment elements as you need (for example, one for each language/territory you must support). Inside an environment element, you can add as many envVariable elements as required.

By referencing the environment element’s id, you invoke its settings. You can reference an environment element id from:

The defaultEnvId attribute of the engine element in the Reports Server configuration file, to apply the corresponding environment settings to that engine when it starts up. For more information, refer to Section 8.2.1.9, «engine».

The command line keyword, ENVID , of your report’s job request, which makes the environment settings only effective for that particular report job request.

The environment element attribute is described in Table 8-7.

Table 8-7 Attribute of the environment Element

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Default Env ID .

The name of the environment.

The environment element includes one or more envVariable sub-elements, described in Section 8.2.1.8, «envVariable».

8.2.1.8 envVariable

The envVariable element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the envVariable element may be specified as shown in this example:

Each envVariable is specified as a name–value pair. They can be either standard environment variables or user-defined environment variables.

envVariable is a sub-element of the environment element.

The envVariable element attributes are described in Table 8-8.

Table 8-8 Attributes of the envVariable Element

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Add .

The name of the environment you wish to use (for example, NLS_LANG ).

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Add .

The value you want to assign to the environment variable identified with the name attribute (for example, Japanese_Japan.JA16SJIS).

8.2.1.9 engine

The engine element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the engine element may be specified as shown in this example:

Required. You must have at least one engine element in your configuration file.

The engine element identifies the fully qualified Java class that starts an engine and provides a number of attributes that set operational controls on the engine. You can use the default engines provided with Oracle Reports Services or develop your own implementation through the Oracle Reports Services Engine API. As an example of a custom engine, you may have developed an engine to execute an operating system command should an event occur in your database.

For more information on the engine API, refer to the Reports Software Development Kit (RSDK) on the Oracle Technology Network (OTN): on the Oracle Reports page ( http://www.oracle.com/technology/products/reports/index.html ), click SDK .

The engine element attributes are described in Table 8-9.

Table 8-9 Attributes of the engine Element

A keyword, unique within a given configuration XML file that identifies a particular engine element. This can be a text string or a number, for example:

A fully qualified Java class that implements two interfaces: oracle.reports.engine.Engine and oracle.reports.engine.EngineInterface .

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Maximum Engines .

The maximum number of this type of engine that can run on the server.

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Minimum Engines .

The minimum number of this type of engine that is maintained by the server.

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Maximum Job Before Restart .

The number of jobs the engine can run before the engine is terminated, and, if necessary, a new engine is started. This feature is available to thwart memory leaks.

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Maximum Idle Before Shutdown (min) .

The number of minutes of allowable idle time before the engine is shut down However, the current number of engines should be higher than minEngine .

For example, if minEngine is 0 , maxIdle is 30 , and one engine has been running but unused for 30 minutes, that engine will shut down. If, under the same conditions, minEngine is 1, the active engine will not shut down, even if it has been idle for 30 minutes.

The number of milliseconds of allowable waiting time between when the server launches an engine and the engine calls the server back.

If the machine that hosts the server is very fast, you can reduce this number for faster performance.

Set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Engine Response Timeout (min) .

Default: null (no timeout)

The maximum amount of time (in minutes) for an engine to update the status of the job while running a report in your environment. If it takes longer than this amount of time to update the job status for some reason (for example, due to the engine hanging or a long blocking SQL query), Reports Server terminates the job.

The number of engines you want Reports Server to start at initialization.

When running a report using rwrun , retain the default value of initEngine=»1″ . Because rwrun can run only one report at a time, any other setting may result in the report not being run.

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: JVM Options .

The Java Virtual Machine (JVM) options to be used by Reports Server when it starts an engine in the JVM. For example, you can use this attribute to specify the starting heap size and maximum heap size for the JVM, additional classpath entries, and so on.

If this attribute is not specified, the engine running in the server environment uses the JVM options specified by the value of the REPORTS_JVM_OPTIONS environment variable. For more information, see Section B.1.53, «REPORTS_JVM_OPTIONS».

The directory path to the Java class specified in the class attribute. To specify the directory, use the conventions required by the server platform, for example:

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Default Env ID .

(Optional attribute) The default environment within which Reports Server starts an engine. The attribute takes an ID associated with an environment element in the server configuration file.

When defaultEnvId is specified, Reports Server starts an engine with the environment variables specified in the referenced environment element plus whatever environment variables that Reports Server is running under.

If defaultEnvId is not specified, Reports Server spawns engines with the environment settings in force at startup time.

You can also optionally enter multiple property sub-elements for the engine element. The only requirement is that they be name/value pairs recognized by the Java class that implements the Oracle Reports engine.

Table 8-10 Properties of the engine Element

Set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Reports Source Directory .

The default directory you will use for report definition files. It overrides path information specified in the REPORTS_PATH environment variable.

The directory specified by sourcedir is not given access by default. It used only to search for files, and Oracle Reports handles any associated security settings separately if and when the target files are located.

See the example that follows this table.

Set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Reports Temp Directory .

The name and location of the temporary directory Oracle Reports Services will use for its temporary files. If this value is unspecified for a default engine, Oracle Reports Services uses the temporary directory specified in the REPORTS_TMP environment variable. If REPORTS_TMP is not specified, Oracle Reports Services uses your operating system’s default temporary directory.

See the example that follows this table.

Set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Keep Database Connection .

Used by the default runtime engine implementation (that is, oracle.reports.engine.EngineImpl ).

YES The default runtime engine retains the existing database connection information.

NO The default runtime engine discards the existing database connection information and reconnects with the userid specified for the job.

The keepConnection property does not affect reports deployed using either rwbuilder or rwrun .

This property will be migrated if a rwserver.conf file used in previous releases (for example, 9.0.2.x) runs in the current environment.

Set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Enable Engine Diagnostics .

Introduced in Oracle Reports 10 g Release 2 (10.1.2) for engine logging. Diagnoses whether or not a specific function in a report run completed successfully. The diagnostic log provides information on important checkpoints or tasks in the engine during a report run. This information is useful in cases where the engine stops responding, resulting in «hanging» jobs.

YES Diagnostic information is written to the diagnostic.log file.

The engine diagnosis option provides more detailed information than report tracing, which is typically used to debug the execution of a report to provide information such as the file currently formatting, or report trigger currently running.

See the example that follows this table.

Example of sourcedir and tempDir properties : If you use the default engine Java class that is provided with Oracle Reports Services, your engine configuration entry might look like this (in a Windows environment):

The classPath attribute is not specified because this configuration uses the default engine class.

Example of diagnosis property : To enable the engine diagnosis option, your engine configuration element might look like this:

8.2.1.10 job

The job element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the job element may be specified as shown in this example:

Required. You must have at least one job element.

The job element works in collaboration with the engine and security elements. Use job to identify a job type and specify which engine and which security implementation should be used with that type of job. For example, you may have developed an engine to execute an operating system command should an event occur in your database. Using Oracle Reports Services’s event-driven publishing API, you identify the event as a specific job type. When the event occurs, the job type information is sent to Reports Server, which looks up the job type under the job element in its configuration file, and follows the direction provided in the element’s attributes to the engine (and, if applicable, security implementation) specified for that type of job.

The job element attributes are described in Table 8-11.

Table 8-11 Attributes of the job Element

References the ID entered for the engine that will process this job type. Available IDs are specified under the engine element in the server configuration file using the id attribute. The id is a unique keyword (that you devise) within a given configuration XML file that identifies a particular engine.

Describes the type of job to be processed by the server. You can enter any type of job, as long as Reports Server has an engine to process it.

The database authentication functionality provided in Oracle Reports is available only when jobType=report . This is the job type of the default engine ( rwEng ) provided with Oracle Reports Services. The database authentication functionality is not implemented when jobType specifies a different value (for example, for a custom engine that you develop yourself). This is because a custom engine may require a different format for the connect string, while the Oracle Reports database authentication functionality limits the connect string to the Oracle Reports format user/password@dbname used for the default engine.

References the ID entered for the security mechanism that will be applied to this job type. Available IDs are specified under the security element in the server configuration file.

Set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Job Retries .

When jobType=»report» , specifies the number of times to retry a job after the initial run, if the job fails due to an engine crash or unexpected error. The job is resubmitted to another engine the number of times specified.

This attribute is ignored if the job is explicitly cancelled or when jobType=»rwurl» (URL engine).

If an invalid value is specified, this attribute is ignored and the default value of 0 is used.

If JOBRETRY is specified on the command line, it takes precedence, and the retry attribute is ignored.

8.2.1.11 jobRecovery

The jobRecovery element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the jobRecovery element may be specified as shown in this example:

Optional. To enable the job recovery mechanism, add the jobRecovery element to the server configuration file. The job recovery mechanism is disabled by default.

The jobRecovery element includes the auxDatFiles attribute. When auxDatFiles=yes , Oracle Reports enables a more resilient job recovery mechanism for maximal retrieval of jobs in case the original .dat file is corrupt due to some reason. When auxDatFiles=yes , Reports Server creates the following two auxiliary files in addition to server_name .dat (the main .dat file):

datfilename _offset.dat contains the auxiliary information of jobs in the main .dat file, which helps in retrieving jobs from the main .dat file.

datfilename _sc.dat contains all scheduled jobs information (in addition to the information stored in main .dat file).

If the job recovery mechanism is enabled, Reports Server on startup reads the main .dat file with the help of the datfilename _offset.dat file using the auxiliary information stored in it. If the main .dat file is corrupt and Reports Server cannot retrieve all the jobs information, it starts reading the datfilename _sc.dat file and recovers the scheduled jobs for this file. Thus, datfilename _sc.dat serves as a backup file, which results in maximum possibility of recovery of scheduled jobs in case of corruption of the main .dat file.

If Reports Server fails to find the datfilename _offset.dat file (for example, when the jobRecovery element is enabled for first time) when the job recovery mechanism is enabled, it reads the jobs from the main .dat file and creates the other two auxiliary files from scratch.

The server_name .dat , datfilename _offset.dat , and datfilename _sc.dat files form a unique triplet, and the auxiliary files are valid only when the job recovery mechanism is enabled. If the auxiliary files are found when the job recovery mechanism is disabled, Reports Server deletes these files from the file system to maintain the integrity between these files. For this reason, you must always handle these three files together (for example, if you are copying a file from one machine to another, you must copy these three files together).

The jobRecovery element attribute is described in Table 8-12.

Table 8-12 Attribute of the jobRecovery Element

8.2.1.12 jobStatusRepository

The jobStatusRepository element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the jobStatusReposity element may be specified as shown in this example:

Optional. You can have a maximum of one jobStatusRepository element in your server configuration file.

The jobStatusRepository element specifies the Java class that implements a job status repository. It provides an additional means (over the persistFile element) of storing job status information.

The persistFile is a binary file and, therefore, cannot be used to publish job status information within your application. The jobStatusRepository element provides a means of including status information in your application by providing additional ways of storing it.

The default class, oracle.reports.server.JobRepositoryDB , stores information in a database. Use the Oracle Reports APIs to create your own implementation of the Reports Server Job Repository interface ( oracle.reports.server.JobRepository ) that stores information wherever you wish.

The jobStatusRepository element attribute is described in Table 8-13.

Table 8-13 Attribute of the jobStatusRepository Element

Set to the default value in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Enable Job Repository DB .

A fully qualified Java class that implements the Reports Server Job Repository Java class ( oracle.reports.server.JobRepository ).

You can also optionally enter multiple property sub-elements for the jobStatusRepository element for passing options into the repository. The only requirement is that they be name/value pairs recognized by the class you specify in the server configuration file.

The jobStatusRepository element might look like this in your server configuration file:

Oracle Reports uses the dbconn property of the jobstatusrepository element to connect to the database when updating the log information about job queues.

8.2.1.13 log

The log element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the log element may be specified as shown in this example:

Optional. You can have a maximum of one log element in your server configuration file.

The log element is available for backward compatibility. It invokes the generation and population of a reports log file. The log file is automatically generated and stored in the following path (the path is the same for Windows and UNIX):

The log element attribute is described in Table 8-14.

Table 8-14 Attribute of the log Element

Describes the type of jobs that are logged. This is in addition to the default server activities that are logged. Choose from the following options:

allJobs : All jobs will be logged

succeededJobs : Only jobs that ran successfully will be logged

failedJobs : Only jobs that failed will be logged

noJob : No jobs will be logged

8.2.1.14 jobRepository

The jobRepository element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the jobRepository element may be specified as shown in this example:

Required in a high availability (HA) environment. Optional in a non-HA environment. You can have a maximum of one jobRepository element in your server configuration file.

The jobRepository element enables you to store all job information in the database instead of the file system (that is, in DAT files). This element is mandatory if you want to use high availability (HA), because Reports Servers in the group share job information, which is possible only if the job information is stored in the database, and not individual DAT files.

The jobRepository element has no attributes.

jobRepository requires only one property sub-element, repositoryconn . and the jobRepository element enables you to store all job information in the database or the file system (that is, in DAT files).

8.2.1.15 notification

The notification element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the notification element may be specified as shown in this example:

Optional. If you do not enter a notification element in the configuration file, the notification function is disabled. You can have from zero to multiple notification elements in your configuration file.

Use the notification element to specify a Java class that defines the type of notification that should be sent when a job succeeds or fails. You can use the default notification class, which provides for notification through e-mail, or design your own with the Oracle Reports Notification API.

For more information on the notification API, refer to the Reports Software Development Kit (RSDK) on the Oracle Technology Network (OTN): on the Oracle Reports page ( http://www.oracle.com/technology/products/reports/index.html ), click SDK .

The notification element attributes are described in Table 8-15.

Table 8-15 Attributes of the notification Element

A keyword, unique within a given configuration XML file, that identifies a particular notification element. This can be a text string or a number, for example:

See the Description column

A fully qualified Java class that implements the Reports Server Notification Java class oracle.reports.server.Notification .

If you use the default email notification implementation, use the pluginParam element to specify the outgoing SMTP mail server to be used to send the mail. Use the command line keyword notifysuccess and notifyfailure to specify the email address where notification should be sent (for more information, see Appendix A, «Command-Line Keywords»). For example, you can include these commands in your runtime URL:

With the default e-mail implementation, you can specify only one address for each type of notification . You can specify one or both types of notification . You can send notification each to the same address or each to a different addresses.

A notification element in the server configuration file might look like this:

The succNoteFile and failNoteFile properties are set in Oracle Enterprise Manager on the Reports Server Advanced Configuration page: Email notification file for success and Email notification file for failure .

Some mail servers may validate the sender’s domain name. If the notification fails because of this domain name validation, then you must add the following property as part of the notification element:

With the default notification implementation, it’s not necessary to specify a path to the success or failure text files, provided they’re in the default location: ORACLE_HOME \reports\templates . Otherwise, enter the directory path along with the filenames according to the requirements of the platform that hosts the server.

8.2.1.16 oidconnection

The oidconnection element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the oidconnection element may be specified as shown in this example:

The oidconnection element specifies Oracle Internet Directory connection pooling parameters for Reports Server. In a production environment, you can use this parameter to provide granular control over Oracle Internet Directory connection pooling of Reports Server, namely:

The number of connections to keep open in the pool when Reports Server is initialized.

Upon exhausting the available connections, the number of new connections to be added to the pool when a new request arrives.

The timeout for closing idle open Oracle Internet Directory connections to reduce the resource usage.

The oidconnection element attributes are described in Table 8-16.

Table 8-16 Attributes of the oidconnection Element

Initial number of Oracle Internet Directory connections to be created when Reports Server is initialized.

Number of connections to be incremented when all connections are used up.

Default: «0» (which specifies no timeout)

Time in seconds for which a connection can be idle before it is closed.

Setting much lower or higher values than the default values for these attributes can have a performance impact on Oracle Reports Services. In a typical production environment, the default values are recommended.

For Oracle Reports Servlet ( rwservlet ), you can specify Oracle Internet Directory connection pooling parameters using the oidconnection element in the rwservlet.properties file.

8.2.1.17 orbClient

The orbClient element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the orbClient element may be specified as shown in this example:

Optional. If you do not specify the orbClient element in your server configuration file, the default values will be used.( see Table 8-17)

The orbClient element specifies the name of the public key file that the client will use to connect to Reports Server. Reports Server uses the public key to verify the signature sent by the client when it tries to connect to Reports Server. Reports Server only accepts clients whose signature can be verified through this public key. You can have from zero to multiple orbClient elements in your server configuration file.

orbClient is a sub-element of the connection element

The orbClient element attributes are described in Table 8-17.

Table 8-17 Attributes of the orbClient Element

Identifies the Reports Client to be served by the public and private key.

Identifies the public key file that the client will use to connect to Reports Server. Reports Server uses the public key to verify the signature sent by the client when it tries to connect to Reports Server. Reports Server only accepts clients whose signature can be verified through this public key. The default file is stored in the rwrun.jar file.

Oracle Reports Services provides default client public and private key files, clientpub.key and clientpri.key . These key files are in place for all components of Oracle Reports Services You can regenerate public and private key files to replace the default key pair. To do this, at the command prompt use the following command:

If you regenerate these keys, you can specify the public key file locations with the publicKeyFile attribute, and replace the private key file in ORACLE_HOME \jlib\zrclient.jar . To do this, you must unjar the file, place the regenerated private key into it, and rejar the file.

8.2.1.18 persistFile

The persistFile element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the persistFile element may be specified as shown in this example:

Optional. If you do not specify a file, the server will create one of its own with the default name server_name .dat . You can have a maximum of one persistFile element.

The persistFile element identifies the file that records all job status. It is used by Reports Server to restore the server to the status it held before shutdown.

It is named persistFile because the file remains intact, or persists, even when the server is brought down and restarted.

The server persistent file is created automatically the first time you start the server or the first time you start the server after the current server persistent file has been deleted or renamed. If you want to rename this file but continue using it, enter the new name in the server configuration file before you actually rename the file, then restart the server.

The persistFile element attribute is described in Table 8-18.

Table 8-18 Attribute of the persistFile Element

Default: server_name .dat

The name and, optionally, the path of the server persistent file. You can leave the path off if the file is kept in its default directory:

The path is the same for Windows or UNIX.

8.2.1.19 identifier

The identifier element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the identifier element may be specified as shown in this example:

Optional. You can have a maximum of one identifier element in your server configuration file.

The identifier element is automatically written to the configuration file by the Reports Configuration Assistant when you first install Oracle Reports. The Reports Configuration Assistant sets the values in the form SERVERACCESSKEY/12312312313 , where SERVERACESSKEY is the user name and the random generated number ( 12312312313 ) is the password. This user name and password is then encrypted and written to rwserver.template and targets.xml during the time of configuring Oracle Reports Services. Any Reports Server started after the installation will have this identifier information stored in its configuration file.

For a non-secured Reports Server, the values of the identifier element is used when:

Connecting to a Reports Server through the Reports Queue Manager.

Shutting down a Reports Server through the command line.

In either of these cases, you must provide the authid in the command line that matches the values specified in the identifier element. To provide a specific password (as the password is a pseudo random number), you must do the following:

Edit the server configuration file, rwserver .conf .

Replace the encrypted username/password values generated with custom values.

Restart Reports Server. Reports Server sets encrypted=yes when it restarts.

Edit the targets.xml file and specify the same username and password values that were included in the rwserver .conf file.

You should restart Reports Server, immediately, after making this change. Reports Server automatically encrypts the user name and password and resets encrypted to yes . The values should now read as follows:

For a secure Reports Server, the authentication is done by the security infrastructure; that is, by using the Oracle Internet Directory repository. Thus, you cannot pass the values in the identifier element to shut down a Reports Server or launch Reports Queue Manager through the console window.

This user name and password is also used for accessing Web commands, such as getjobid , getserverinfo , showjobs , and showenv when DIAGNOSTIC=NO in the rwservlet.properties file. When DIAGNOSTIC=NO , Web commands are disabled for everyone except those administrators who have this user name and password.

For more information on Reports Queue Manager, see the Reports Queue Manager online Help . For more information on rwservlet.properties , refer to Section 8.3, «Oracle Reports Servlet Configuration File».

8.2.1.20 property

The property element is defined in rwserverconf.xsd as follows:

See the following element descriptions for information about specifying the property element in rwserver.conf :

8.2.1.21 queue

The queue element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the queue element may be specified as shown in this example:

Optional. You can have a maximum of one queue element in your server configuration file. If you have no queue element, the default maxQueueSize , 1000 , will remain in effect.

Use the queue element to specify the maximum number of jobs that can be held in a completed job queue. Oracle Reports Services has three queue components:

a queue of scheduled jobs

a queue of jobs in progress

a queue of completed jobs

The queue element provides the allowable value for each of these components.

This element is applicable only to the completed job queue. Thus, if the number of jobs exceeds the specified maximum value, that completed job queue will automatically purge its oldest jobs. The scheduled job queue and the in-progress job queue remain unaffected.By default reports server queue size is 1000 jobs.

If you increase the queue size to more than 3000, and use Reports Queue Manager ( rwrqm.exe ) to monitor the queue, Queue Manager may fail. When a queue size of 3000 or greater is required, use Oracle Enterprise Manager or Oracle Reports Servlet ( rwservlet ) to manage and monitor the Reports Server jobs queue.

For more information, see the Reports Queue Manager online Help .

The queue element attribute is described in Table 8-19.

Table 8-19 Attribute of the queue Element

Set in Oracle Enterprise Manager on the Reports Server Basic Configuration page: Queue Size .

The maximum number of jobs that can be held in a given reports job queue.

8.2.1.22 folderAccess

The folderAccess element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the folderAccess element may be specified as shown in this example:

The folderAccess element defines read and write access to file system folders for both secured and non-secured Reports Server, Reports Application (in-process Reports Server), or Oracle Reports Runtime.

The folderAccess element has no attributes. It includes two sub-elements:

read : specifies the folder(s) to which the Reports Server, Reports Application (in-process Reports Server), or Oracle Reports Runtime has read access only. The value of this sub-element is set in Oracle Enterprise Manager on the Advanced Configuration page: Enable File System Access Control > Read Directories .

write : specifies the folder(s) to which the Reports Server can write. The value of this sub-element is set in Oracle Enterprise Manager on the Advanced Configuration page: Enable File System Access Control > Write Directories .

In the example above, the report definition files located in c:\myreports and c:\orawin\reports\samples are allowed to run only. Similarly, when destype=file , the output file can be created only in c:\myoutputs ( desname=c:\myoutput\test.pdf ).

Note : Blank or * in the read or write sub-element specifies global access. Separate directory names with a semicolon (;).

8.2.1.23 security

The security element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the security element may be specified as shown in this example for 11 g :

For backward compatibility, the security element may be specified as:

Optional. If you do not enter a security element in the configuration file, Reports Server is not secure. You can have from zero to multiple security elements in your configuration file.

The security element identifies the fully qualified Java class that controls server access. You can use the default security class provided with Oracle Reports Services, or develop your own implementation through the Reports Server Security API.

For more information on the security API, refer to the Reports Software Development Kit (RSDK) on the Oracle Technology Network (OTN): on the Oracle Reports page ( http://www.oracle.com/technology/products/reports/index.html ), click SDK .

The security element attributes are described in Table 8-20.

Table 8-20 Attributes of the security Element

A keyword, unique within a given configuration XML file that identifies a particular security element. This can be a text string or a number, for example for 11 g :

For backward compatibility,

See the Description column

Default for 11g: oracle.reports.server.RWJAZNSecurity

Default for backward compatibility: oracle.reports.server.RWSecurity

A fully qualified Java class that implements Reports Server Security Java interface ( oracle.reports.server.Security ). The default relies on security features available through Oracle Portal.

You can associate multiple properties with the security element. The only requirement is that they be name/value pairs recognized by the Java class that implements Reports Server security.

The value of all the properties is set by the Installer upon installation. Reports Server uses this entity to connect to Oracle Internet Directory and Portal. Components of the Oracle Fusion Middleware can all connect to Oracle Internet Directory and Oracle Portal, but each component may have different privileges in the directory. Hence, each component needs to identify itself through its own entity name to Oracle Internet Directory when it connects. The Oracle Reports Services entity is of the following format:

8.2.1.24 proxyServer

The proxyServer element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the proxyServer element may be specified as shown in this example:

Element that specifies the name, port and protocol of proxy server to be used in order to connect to external network.

The proxyServer element attributes are described in Table 8-20.

Table 8-21 Attributes of the proxyServer Element

the host name of the proxyserver. example.

the port where the proxyserver is listening on.

the protocol used by the proxyserver.

8.2.1.25 domain

The domain element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the domain element may be specified as shown in this example:

Element that specifies the name of the proxy server for which proxy setting should not be used. The domain element has no attributes.

8.2.1.26 bypassProxy

The bypassProxy element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the bypassProxy element may be specified as shown in this example:

The element that provides a list of domains which specifies name of the proxy server for which proxy setting should not be used

The bypassProxy element has no attributes. It includes the domain sub-element (see Section 8.2.1.25, «domain»).

8.2.1.27 proxyServers

The proxyServers element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the proxyServers element may be specified as shown in this example:

Element that specifies a list of proxy servers used by reports server.

The proxyServers element has no attributes. It includes the proxyServer sub-element (see Section 8.2.1.24, «proxyServer»).

8.2.1.28 proxyInfo

The proxyInfo element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the proxyInfo element may be specified as shown in this example:

Element specifying proxy servers used by reports and the bypass hosts for which proxy should not be used.

The proxyInfo element has no attributes. It includes two sub-elements:

8.2.1.29 webLayout

The webLayout element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the webLayout element may be specified as shown in this example:

webLayout element is required to run a report to weblayout using Reports Builder.

The webLayout element attributes are described in Table 8-22.

Table 8-22 Attributes of the webLayout Element

the OHS main port.

the location of the web.war where Reports Application is deployed

8.2.1.30 dbProxyKey

The dbProxyKey element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the dbProxyKey element may be specified as shown in this example:

The dbProxyKey consists of the name and database parameters. It is obtained from the server configuration file based on the database mentioned in the userid commandline parameter.

The dbProxyKey element attributes are described in Table 8-23.

Table 8-23 Attributes of the dbProxyKey Element

the name of the key.

the name of the database.

8.2.1.31 dbProxyConnKeys

The dbProxyConnKeys element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the dbProxyConnKeys element may be specified as shown in this example:

The dbProxyConnKeys element has no attributes. It includes the dbProxyKey sub-element (see Section 8.2.1.30, «dbProxyKey»).

8.2.1.32 jobThresholds

The jobThresholds element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the jobThresholds element may be specified as shown in this example:

jobThreshold consists of two attributes, longRunning and PotentialRunAway . For more information, See Table 8-24.

Table 8-24 Attributes of the jobThresholds Element

the cut-off time for a job after which it is considered a long run job.

a job that takes relatively longer time.

the cut-off time for a currently running job after which it is considered as a potential runaway job.

a job which has relatively lesser chance of successful completion.

8.2.1.33 server

The server element is defined in rwserverconf.xsd as follows:

In rwserver.conf , the server element may be specified as shown in this example:

Required. You can have a maximum of one server element in a given configuration file.

The server element opens and closes the content area of the server configuration file. In terms of the file’s hierarchy, all the other elements are subordinate to the server element.

The server element attribute is described in Table 8-25.

Table 8-25 Attribute of the server Element

the version of the Reports Server

8.2.2 Dynamic Environment Switching

Dynamic environment switching enables you to dynamically change the environment after Reports Server is started, or for a specific job request. This means that one instance of Reports Server can serve reports with any arbitrary environment settings, such as language, currency, and display settings.

To enable dynamic environment switching, you must add an environment element to your Reports Server configuration file to establish a particular runtime environment. Once you have an environment element established, you can switch to its settings in either of the following ways:

Set the value of the defaultEnvId attribute of the engine element in the Reports Server configuration file to the id of the environment element, to apply the environment settings to that engine when it starts up. For more information, refer to Section 8.2.1.9, «engine».

Set the value of the ENVID command line keyword to the id of the environment element, to make the environment settings effective for the current report job request. For more information, refer to Section A.6.5, «ENVID».

8.2.2.1 Examples

The following examples illustrate the use of dynamic environment switching.

Suppose that you want to run reports in Japanese from your Reports Server. An environment conducive to running reports in Japanese would include:

The currency unit ( NLS_CURRENCY ) would be set to Yen (¥), the currency of Japan.

If Reports Server is running on UNIX, then DISPLAY must be set.

To begin, you would have to add an environment element to your Reports Server configuration file that looks something like the following:

Once the environment element is in place, you could request a report with Japanese output in either of the following ways:

Use the defaultEnvId attribute of the engine element in the Reports Server configuration file as follows:

The value JP identifies the environment element in the Reports Server configuration file. The initial engines will be spawned with the environment settings specified in this environment element.

Set the ENVID command line keyword, as follows:

When the URL is submitted to Reports Server, it detects the optional ENVID keyword and matches the specified id (in this case, JP ) to the corresponding id of the environment element in its configuration file. If Reports Server already has an engine running with these characteristics, it will reuse the existing engine to process the job. If not, then it spawns an engine using the current environment plus the three environment variables specified in the JP environment element. If spawning a new engine would cause Reports Server to exceed its maxEngine setting, Reports Server shuts down an engine before starting a new one. An engine may be shut down even though it has not exceeded its engLife setting. Once Reports Server has an engine with the correct environment running, the job is processed by that engine and the output is routed to the specified DESTYPE .

If you do not pass ENVID with the job, Reports Server processes the request using an engine started with the defaultEnvId environment. If defaultEnvId is not specified for the engine element in your Reports Server configuration file, then the engine will inherit the settings with which the Reports Server instance was started.

The following example illustrates how to use this environment switching feature to run an Arabic report on the same Reports Server that was used to run the Japanese report in Example 1.

Add another environment element to the Reports Server configuration file as shown below:

The Arabic report has to be submitted to Reports Server with the following command line:

Since the job is submitted with ENVID=AR , Reports Server finds or starts an engine with the environment specified by element AR in the Reports Server configuration file. The job is processed by the new engine and the output is distributed to the specified destination.

The following example illustrates how the environment switching feature could be used in conjunction with a JSP report; that is, without Oracle Reports Servlet ( rwservlet ).

Suppose that you have the following environment elements in the Reports Server configuration file:

If your JSP report uses a format mask such as the following, it means the currency, grouping, and decimal symbols can change according to the environment:

To run the report using the UK symbols for currency, grouping, and decimal, you would use the following URL:

Note: You could place ENVID=UK into a key in the cgicmd.dat key map file (for more information, see Section 18.13, «Using a Key Map File»).

8.2.2.2 Usage Notes

Although this feature is ideal for handling reports of various languages, its application can be much broader. You could use it in any situation where a report requires a particular environment to execute correctly.

Reports Server will start one or more engines per environment id as and when it gets requests for specific environments. The total number of engines, however, cannot exceed the maxEngine specified for that engine type. It is recommended that you set maxEngine to a value greater or equal to the number of environment elements specified in the Reports Server configuration file.

defaultEnvId can also be applied to pluggable engines other than rwEng . Reports Server will spawn the pluggable engine with the specified environment id.

For engines used by the in-process Reports Server, the order of precedence for environment variables from highest to lowest is as follows:

reports.sh (UNIX only)

If you have modified your current reports.sh file, you should save it and, after installing Oracle Reports, merge your modifications into the version of reports.sh installed with the latest version. The latest reports.sh contains some required changes.

environment element in the Reports Server configuration file

Go to the WebLogic Administration Console, navigate to the Server Start tab and specify the oracle.home and oracle.instance parameters.

The system settings and registry (Windows only)

For engines used by the standalone server, the order of precedence for environment variables from highest to lowest is as follows:

Oracle Reports 6i

In this tutorial you will learn about Introduction to Oracle Reports Builder, Report file storage formats, Oracle Reports Builder Tools, Report Wizard, Triggers in Reports, Types of Triggers and Case Study – Creating a Tabular report.

Introduction to Oracle Reports Builder

Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from the database, format, display and print quality reports. Reports can be stored in File or Database (Report Builder Tables).

Report file storage formats

.rdf Report
• Binary File Full report definition (includes source code and comments)
• Modifiable through Builder. Binary, executable Portable if transferred as binary.
• PL/SQL recompiles on Open/Run

.rep Report
• Binary Run-Only File
• No source code or comments. Not modifiable binary, executable.
• Report Executables

RWBLD60 Report Builder
RWRUN60 Report Runtime
RWCON60 Report Converter/Compiler [File => Administration => Compile (rdf to rep)/Convert]

Oracle Reports Builder Tools

Oracle Reports Builder comes with the following components
• Object Navigator
• Property Palette
• Data Model Editor
• Layout Model Editor
• Parameter Form Editor

Object Navigator
The Object Navigator shows a hierarchical view of objects in the report. Each item listed is called a node and represents an object or type of object the report can contain or reference.

Property Palette
A Property Palette is a window that displays the settings for defining an Oracle reports object.

Data Model Editor
To specify data for a report, a data model should be defined. A data model is composed of some or all of the following data definition objects.

Queries
Queries are SQL Select statements that fetch data from the oracle database. These statements are fired each time the report is run.

Groups
Groups determine the hierarchy of data appearing in the report and are primarily used to group columns selected in the query. Oracle report automatically creates a group for each query.

Data Columns
Data columns contain the data values for a report. Default data columns, corresponding to the table columns included in each query’s SELECT list are automatically created by oracle reports. Each column is placed in the group associated with the query that selected the column.

Formula Columns
Formulas can be entered in formula columns to create computed columns. Formulas can be written using PL/SQL syntax. Formula columns are generally preceded by CF_ to distinguish from other columns.

Summary Columns
Summary columns are used for calculating summary information like sum, average etc. This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.

Data Links
Data links are used to establish parent-child relationships between queries and groups via column matching.

Layout Model Editor
A report layout editor contains the following layout objects

Frames
Frames surround other layout objects, enabling control of multiple objects simultaneously

Repeating Frames
Repeating frames acts as placeholders for groups (I.e repeating values) and present rows of data retrieved from the database. Repeating frames repeat as often as the number of rows retrieved.

Fields
Fields acts as placeholders for columns values. They define the formatting attributes for all columns displayed in the report.

Boilerplate
Boilerplate consists of text (label of the column) and graphics that appear in a report each time it is run.

Parameter Form Editor
Parameter form is a runtime form used to accept inputs from the user.

Parameters
Parameters are variables for a report that accept input from the user at runtime. These parameter values can then be used in the SQL select statements to retrieve data conditionally. Oracle reports creates a set of system parameters at runtime namely report destination type, number of copies etc.

Report Wizard

• When we create a default Tabular Report using report wizard, the wizard will take you through the below mentioned pages
• Report Style Tabular, Form-Like, Mailing Label, Form Letter, Group Left, Group Above, Matrix, Matrix with Group
• Query Type Choose whether to build a SQL query or an Express query.
• Data Enter a SELECT statement to retrieve the report data
• Displayed Fields Select the fields that you want to display in the output.
• Fields to Total Select the fields that you want to summarize.
• Labels for Fields Alter the labels that appear for each field and the width of each field.
• Template Select the template that you want to use for this report. A template contains standard information such as company logo, date, and so on.

Note: The above steps are different for each report style.

Group Left & Have an additional page: ‘Groups’
Group Above styles
Matrix Reports styles Have 3 additional pages: ‘Matrix Rows’ ‘Columns’ ‘Cells’
Mailing Label & Have 4 pages: ‘Report Style’ ‘Data’
Form Letter styles ‘Text’ ‘Template’

The difference between Mailing Labels and Form Letters is, Mailing Label shows multiple records on one page while Form Letter shows one record on each page.

Triggers in Reports

Types of Triggers

Formula Triggers: Formula triggers are PL/SQL functions that populate columns of type Formula.

Format Triggers: Format triggers are PL/SQL functions executed before the object is formatted. These triggers are used to dynamically change the formatting attributes and used to conditionally print and not to print a report column value. These triggers return Boolean values TRUE or FALSE. If the return value of the format trigger is FALSE, the value is not displayed.

Action Triggers: Action triggers are used to perform user-defined action. These triggers do not return any value.

Validation Triggers: Validation triggers are PL/SQL functions that are executed when a parameter value is entered and the cursor moves to the next parameter. These triggers return Boolean value TRUE / FALSE.

Report Triggers: Report triggers enable execution of PL/SQL functions at specific time during execution and formatting of report.

Before Parameter Form
Fires before the Runtime Parameter Form are displayed. Can access the PL/SQL global variables, report level columns and manipulate accordingly.

After Parameter Form
Fires after the Runtime Parameter form are displayed. Used to validate the parameter values.

Before Report
Fires before the report is executed but after the queries is parsed and date is fetched.

Between Pages
Fires before each page of the report are formatted, except the very first page. This page is used to customize page formatting.

After Report
Fires after the report previewer are exited, or after report output is sent to a specified destination.

Case Study – Create a Tabular report

After invoking the report builder and connecting to the database invoke Report wizard.

Click on Tools… Report Wizard… to start the report wizard for a new report. Report wizard shows the following tab pages to enter information required for report.

Style Totals Data
Labels Fields Template

In the Style tab select ‘Tabular’ as the report style and Click Next

The Data tab allows creation of an SQL statement using Query Builder or to enter the SQL statement in the multi line edit box provided in the Data tab.
Click Next

This will take you to the next tab if your SQL statement syntax is correct.

Fields tab is used to specify the fields that must be displayed in tabular format. Select all fields by clicking on >> icon.
Click Next

The Totals tab is displayed that allows creation of Summary columns using aggregate functions.
This report does not include totals for the selected fields and thus Click Next.

The Labels tab is displayed that enable us to change the labels of the columns.
Click Next

The Templates tab is displayed that enable us to create report from templates. There are number of pre-determined templates available in Oracle Reports Builder.

Select Corporate 1 as the template. Click Finish

Click on File. Save to save the report, specify the report name and click OK.

Author Description

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Oracle Forms and Reports Services 11gR2 Configuration Notes

This article is a collection of useful notes on a variety of topics related to configuration of Oracle Forms and Reports Services 11gR2.

Configuration of Forms and Reports Services 12c is similar to 11gR2, but many of the files are in different locations.

Installation

This site contains a number of articles describing the installation of WebLogic Server 11g and Oracle Forms and Reports Services 11gR2 on Linux and Windows. You can find them here.

Configuration Utility

If you choose a software-only installation, you can initiate the configuration steps using the following command.

This is also useful if your configuration phase fails and you want to rerun it after making the necessary changes to fix the issues.

Environment Variable Settings

The basic WebLogic environment variable settings are described in the installation guides, but the following list accounts for most of the scenarios you are likely to come across on the server, including forms compilation from the command line. Remember to adjust the FORMS_PATH and TWO_TASK variables as required.

I tend to use FR_HOME and FR_INST in place of ORACLE_HOME and ORACLE_INSTANCE as I think they are more descriptive. It’s worth setting both as many documents don’t follow my preference.

A number of the sections below assume these environment variables have been set.

Configuration Files

Many of the 11gR2 Forms and Reports Services configuration files are the same as those in AS10g, but their locations differ. It is often easier to find things by searching below the $MW_HOME, as shown below.

The files I tend to visit the most are listed below.

I usually make one of the «tnsnames.ora» files a symbolic link to the other, so I don’t accidentally get them out of sync.

A fuller list of configuration files can be seen here.

A couple of the important files are described below.

formsweb.cfg

The «formsweb.cfg» file is one of the most important configuration files as it allows you to define multiple configurations, or environments, within a single software installation. You can see a copy of the default «formsweb.cfg» file here.

Typically, each separate environment will have it’s own section in the «formsweb.cfg» file, where environment-specific settings are placed. You only need to specify a value in a Named Configuration if it differs from that in the default configuration. For example, a named configuration might be simple, like the following.

The «archive» entry includes the «my_icons.jar», which assumes you have placed you icons in this JAR file and the JAR file is placed in the «$FR_HOME/forms/java» directory.

A very useful entry is the «envFile» parameter, that can associate an environment file with a named configuration. This allows each named configuration run with a unique set of environment variables, including the FORMS_PATH variable amongst others. Any custom environment files should be derived from the «default.env» file, described below.

Changes to this file to not require a restart of any components. The changes can be done directly to the file, or through Enterprise Manager.

default.env

The «default.env» file defines the forms runtime parameters for a specific environment. All custom environment files should be derived from this file. You can see and example of the «default.env» file here.

Environment variables specified in an «.env» file override existing environment settings. If an environment variable is not defined in the «.env» files, the value from the session that started the server process will be used.

Changes to these «.env» files do not require a restart of any components. The changes can be done directly to the file, or through Enterprise Manager.

It’s is worth explicitly setting the NLS_LANG environment variable in the «.env» file to match the NLS details of the database the forms connect to, thereby removing any implicit character conversions.

Compiling Forms

Forms, menus and libraries are compiled using the frmcmp.sh or frmcmp_batch.sh scripts. You can find these in the «$FR_INST/bin/» directory. Running the command without any parameters displays the usage information. Some examples of the basic syntax are shown below.

Remember, frmcmp.sh requires and X Server, so you will need to SSH tunnel ( ssh -X oracle@server ) or set your DISPLAY environment variable ( export DISPLAY=mypc:0.0 ).

You can easily script the compilation of all your forms-related objects, as shown by the following script.

Compiling Forms From a Non-Oracle User

In Forms 11g using SUDO is the recommended way to compile forms as a non-Oracle user.

As the root user, edit the «/etc/sudoers» file with the following entry (as a single line).

You can now run the compile commands from the user «myuser» by using the «sudo -u oracle» prefix. All environment variables discussed above must be set for this to work. An example is shown below.

The TWO_TASK environment variable does not work correctly using this method, so you should use the «username/password@service» method for database authentication.

Forms Compilation and the 11g Function Result Cache

If Oracle Forms reference database functions that use the 11g Function Result Cache, they may get compilation failures such as the following.

This is caused by a bug discussed in MOS [ID 1276725.1] in the version of the PL/SQL client shipped with Oracle Forms. You can either patch the client, or comment out the RESULT CACHE clause in the database function, compile the form, then un-comment the RESULT CACHE clause in the database function.

The bug only affects compilation, not runtime, so it doesn’t prevent you from using the Function Result Cache.

Forms Builder

If you have set the environment variables described above, using the X Server based Forms builder should be as simple as issuing the frmbld command.

Compiling Reports

Reports are compiled using the rwconverter.sh script. You can find this in the «$FR_INST/config/reports/bin/» directory. Running the command without any parameters displays the GUI screen. An example of the command line syntax is shown below.

Reports Builder

If you have set the environment variables described above, using the X Server based Reports builder should be as simple as running the rwbuilder.sh command.

Webutil Configuration

• Download the «jacob.jar» (v1.14.3) file from http://sourceforge.net/projects/jacob-project/files/jacob-project/1.14.3/.
• Unzip the contents of the file.
• Copy the «jacob.jar» to the «$FR_HOME/forms/java» directory.
• Copy the «jacob-1.14.3-x86.dll» to the «$FR_HOME/forms/webutil/win32» directory.
• Copy the «jacob-1.14.3-x64.dll» to the «$FR_HOME/forms/webutil/win64» directory.
• Sign the «jacob.jar» file. (See below)
• Add «$FR_HOME/forms/java/frmall.jar» to the CLASSPATH entry in the «default.env» file. This should be done with Enterprise Manager, but it works fine if you manually edit the file. If you use custom environment files, you should add it to those also.
• Add «frmwebutil.jar» and «jacob.jar» to the «WebUtilArchive» entry in the «formsweb.cfg» file for any configurations that require it, for example.
• After making changes, restart the WLS_FORMS managed server and OPMN.

Additional configuration is possible by editing the «$FR_INST/config/FormsComponent/forms/server/webutil.cfg» file.

Jar File Security Warning Hangs

Unsigned or self-signed jar files cause a browser security warning dialog to appear.

On some clients the behavior of this dialog is rather erratic, sometimes hanging and preventing you from taking the appropriate action. This seems to relate to two MOS notes.

The solution is to alter the order of the Jar files set by the «archive» and/or «WebUtilArchive» entries in the «formsweb.cfg» file. In the case of the «jacob.far» file, if you have it listed in the «archive» parameter, you will probably need to make the following change.

If you have instead listed it in the «WebUtilArchive» parameter, you will probably need to make the following change.

You should not need to restart any services for this to take effect.

Sign JAR Files

Edit the «$FR_INST/bin/sign_webutil.sh» file, setting the appropriate values. If you want to generate a new certificate and keystore you must enter the following information, adjusted as desired.

If you created a keystore for securing your managed servers, you can reuse that by editing the following settings to match the details of your existing keystore.

Issue to the following command to sign the specified JAR file. In this case it is the jacob.jar file.

If you attempt to re-sign a JAR file that is already signed, it will contain multiple signatures in the «META-INF» directory, which will probably result in the following error.

To fix this, unpack the JAR, remove the signatures from the «META-INF» directory and re-pack the JAR. The following example shows how this can be done for the «jacob.jar» file.

Once you’ve stripped the signatures from the JAR file you will need to resign it.

I’ve combined this method, along with the signing of the JAR file into following script, which I save as «resign_jars.sh».

This will strip and resign any JAR file passed as a parameter to the «sign_jar» function.

REP-52262: Diagnostic output is disabled.

Reports 11.1.2.1 turns off the diagnostics output by default. If you need to turn it on, do the following.

Edit the «$DOMAIN_HOME/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.2/configuration/rwservlet.properties» file as follows.

Restart WLS_REPORTS server.

Once you have finished testing, you will probably want to remove the new entry and restart the WLS_REPORTS server.

In-Process Reports Server not Starting Properly

After a clean start of the Forms and Reports Services, the in-process reports server should start automatically on the first attempt to run a report. In practice, this does not seem to happen. There are a number of ways to kick the in-process reports server into touch.

The «startserver» URL can be used to start the in-process reports server. Adding the following commands to the end of your startup script will start make sure it starts as expected.

If you have enabled the diagnostics pages (discussed earlier), any call to a diagnostics page will start the in-process reports server. Adding the following commands to the end of your startup script will start make sure it starts as expected.

You can also start the process using the Reports application page in Oracle Enterprise Manager. Either use the «Monitoring > Start Reports Application» option or the «Administration > Start In-process Reports Server» option.

Key Mappings

The shortcut keys used by Forms can be altered by editing the key mapping resource file for your specific language. The default US/English language uses one of the following resource files, depending on the character set being used.

A description of key mappings can be found here.

Reports Fonts

The fonts available for the Reports server are defined in the following files.

The fonts referenced in these configuration files must be present in the following directory.

After making any changes, restart the Forms and Reports Services.

SSL Configuration

SSL configuration of Forms and Reports Services can seem a little tricky as there are a number of different elements to consider, including:

• The AdminServer, WLS_FORMS and WLS_REPORTS managed servers. Explained here.
• The JAR files used by the Forms and Reports Services. Explained here.
• The Oracle HTTP Server (OHS). See below.

To configure the Oracle HTTP Server (OHS), do the following steps, which assume you have already configured the managed servers for SSL using a keystore as described here.

Create a new wallet.

Load the contents of the previously created keystore into the new wallet.

Edit the «$FR_INST/config/OHS/ohs1/ssl.conf» file, altering the path to the wallet as shown below.

Restart the Oracle HTTP Server.

The Oracle HTTP Server is now using the certificate in the wallet to support HTTPS traffic.

Практические занятия

Дизайнер можно вызвать с помощью пиктограммы или из командной строки.

После вызова появляется диалоговое окно, в котором предлагается сделать выбор – использовать для построения отчета мастер, либо строить новый отчет вручную. Выбираем последнее :

При этом в окне Дизайнера открывается Объектный Навигатор.

Объектный Навигатор показывает существующие объекты и типы объектов,

используется для вызова таблиц атрибутов объектов, а также для создания

и переименования объектов.

2. Соединение с базой данных

Для соединения с базой данных выберите в меню File -> Connect .

Появится диалоговое окно Connect .

Соединение с базой данных можно сделать

из командной строки при вызове Дизайнера.

3. Определение данных

Определение данных делается в редакторе модели данных, который можно

вызвать из Объектного Навигатора.

Дважды щелкните на пиктограмме узла модели данных. Появится редактор

Выберите инструмент SQL Query , щелкнув на нем в палитре инструментов.

Перейдите в рабочую область редактора модели данных и щелкните один раз.

Появится запрос Q _1 .

Двойной щелчок на запросе вызывает окно для ввода и редактирования запроса.

Запрос можно ввести в окне SQL Query Statement ,

импортировать из файла – закладка Import SQL Query и

построить с помощью специального построителя запросов – закладка

Воспользуемся первым способом.

3. Создание макета по умолчанию

Для создания макета воспоьзуйтесь инструментом Report Wizard ,

который может быть вызван с помощью приведенной пиктограммы на инструментальной панели редактора модели данных.

Введите заголовок отчета и выберите табличный стиль отчета:

Выберите поля которые будут отображаться в отчете:

Введите метки и ширину столбцов:

Данная закладка определяет выбор шаблона.

В данном примере предопределенные шаблоны использовать не будем.

После окончания работы с Report Wizard ( Finish ) отчет запускается к выполнению и появляется в окне предварительного просмотра:

4. Редактор макета

Для того чтобы вызвать редактор макета, дважды щелкните мышью на его пиктограмму в Объектном Навигаторе.

5. Сохранение и запуск отчета

Выберите File -> Save или пиктограмму Save . Появится диалоговое окно Save .

Сохраните отчет в файле Les 1. rdf .

Для запуска отчета из Навигатора или из какого-либо редактора выберите инструмент Run .

Появится окно формы параметров. В этом окне можно ввести значения параметров перед исполнением отчета.

Oracle Reports имеет несколько системных параметров. Можно включить

любые или все системные параметры в форму параметров или создать

Урок 2. Создание отчета с разбивкой, формульных и

вычисляемых столбцов

Данный отчет является улучшением отчета les 1. rdf .

Изменение модели данных

Необходимо сгруппировать всю информацию, относящуюся к одному отделу.

Для этого надо создать новую группу с информацией об отделе. Отбуксируйте столбец OTDEL так, чтобы он находился выше группы G _1 . Образовалась новая группа G _2. Затем в эту группу отбуксируйте столбец otdel _ id .

Обновление макета отчета

Макет необходимо обновлять каждый раз при изменении иерархии данных,

при удалении или добавлении столбцов.

Создайте макет табулярного отчета по умолчанию (урок 1).

Создание вычислений

Не все столбцы должны выбираться из базы данных. Для выполнения вычислений

можно создать собственные столбцы. Такие столбцы вычисляют свои значения, основываясь на выражениях PL / SQL или на значениях столбцов базы данных.

Есть два типа вычислений —

Формулы Значения вычисляются с использованием выражения PL / SQL .

Формулы могут оперировать со значениями столбцов

Итоги Значения вычисляются с использованием функций,

предоставляемых ORACLE REPORTS .

Итоги подводятся по значениям нескольких записей.

Для создания формульного столбца используется инструмент

Formula Column в палитре инструментов. Щелкните один раз на инструменте

Formula Column , затем щелкните в группе G _1 . По двойному щелчку мыши вызовите окно свойств и переименуйте столбец в oklad $ .

Выберите Formula : Edit для вызова редактора программных модулей.

Введите следующий текст функции:

Откомпилируйте данную функцию ( Compile ) и закройте редактор программных

Создание Группового итога

Для создания итоговых полей используется инструмент Summary Column .

Создайте суммарный столбец в группе G _2 . Вызовите его таблицу атрибутов, переименуйте его, в списке значений Reset At выберите G _2 .

Чтобы создать итог, который вычисляет сумму по отчету, сделайте следующее:

Выберите инструмент Summary Column и щелкните на свободном месте

Вызовите таблицу атрибутов созданного столбца.

Убедитесь, что уровень сброса итога установлен в Report .

Окончательно модель данных выглядит так, как показано на рисунке.

Вид макета после обновления:

Вид отчета после запуска:

Урок 3. Оформление отчета

В качестве оформления отчета можно добавить красочный заголовок, нумерацию страниц , текущую дату , колонтитул , создать рисунки с помощью инструментов рисования и т.п.

Добавление выделенного заголовка

Для этого перейдем в редактор Layout и выберем режим редактирования

окружения отчета — кнопка Margin на панели инструментов.

В верхней части отчета создайте текстовое поле(кнопка Text на левой

панели) с содержанием «Отчет о личном составе по отделам», используя

палитру красок раскрасьте его по своему вкусу.

Создайте статический объект — прямоугольник с скругленными краями.

Разместите его за статическим текстом, используя меню

Arrange \ Send to Back , раскрасьте этот прямоугольник по вашему вкусу…

Добавление текущей даты и нумерация страниц

Для этого на инструментальной панели редактора макета существуют специальные инструменты:

При этом вы выбираете местоположение и формат из списка доступных, формат можно также задать собственный .

Для добавления даты и нумерации страниц можно также воспользоваться инструментом Field :

Источником данных для нового поля в макете могут быть все данные из модели данных – колонки, вычисляемые столбцы, системные и пользовательские параметры, а также так называемые системные переменные: