A common Oracle error when running scheduled reports is: ORA-24373

You experience this failure of a scheduled report running either on demand with the "run now" button or when initiated by the business objects job server via the scheduling mechanism with the following failure "ORA-24373"

From the oracle documentation:

ORA-24373: invalid length specified for statement
Cause: The length specified for the statement is either 0 or too large.
Action: Specify a valid length for the statement.

Not very helpful on the Business Objects side.

In BusinesObjects this is commonly caused because a report requires input from the user in the form of prompt values.

You can identify reports that require prompt values in Web Intelligence because when you refresh a document or a query you will be presented with the prompt dialog box:

When scheduled reports are run "headless" (initiated by the job server) they cannot present the prompt dialog interactively. There is no user to present the dialog to. What needs to be done to correct this situation is to specify the values from the CMC (or InfoView).

In the Central Management Console on the "Process" tab of the report there is a sub-tab labeled "Prompts"
 

If it there are prompt values expected, the prompt tab will be hot (clickable). If there are no prompt values then it will be grayed (non-clickable).

Specifying values here will eliminate the error.

A common place that this is encountered is when using canned reports supplied by Business Objects or a third party vendor. For example, you will run into this when using the auditor package on XI Release 2 for the BO supplied reports. Some of the reports require prompts for users.

The way that "explicitly denied" rights work (on the advanced rights tab) enables us to globally remove rights for groups or individuals.

Important: "explicitly denied" rights will always override an "explicit grant"

For any given right, if a user is a member of any group where that specific right is denied, the net right is denied.

We can set folder rights in CMC at the highest level, the "Settings" level. Doing this will effectively deny the right at all levels (since the "settings" level is the parent folder of all folders). Setting rights at this level avoids needing to individually remove rights at the folder level for various groups.

Go to the "Rights" tab in the settings.

On the rights tab, add a group that you wish to explicitly deny rights.

In our case we created a "non-administrators" group. We wanted to deny all non-administrators certain rights, like scheduling reports. We added all non-admins to the non-administrators group. Added an entry on the rights tab for the non-admin group and set the advanced rights.

On the "Advanced Rights" each individual right (row) can be set to either "Explicitly Granted," "Explicitly Denied," or "Not Specified."

By setting rights that are explicitly denied, it effectively denies the right for any members of the specified group (non-administrators in our case).

Here is a list of the rights that can be set in this way in BusinessObjects:

General Rights

• Add objects to the folder
• View objects
• Edit objects
• Modify the rights users have to objects
• Schedule the document to run
• Delete objects
• Define server groups to process jobs
• Delete instances
• Copy objects to another folder
• Schedule to destinations
• View document instances
• Pause and Resume document instances
• Securely modify rights users have to objects.
• Reschedule instances
• Schedule on behalf of other users
• Allow discussion threads
• View objects that the user owns
• Edit objects that the user owns
• Modify the rights users have to objects that the user owns
• Delete objects that the user owns
• Delete instances that the user owns
• View document instances that the user owns
• Pause and Resume document instances that the user owns
• Securely modify rights users have to objects that the user owns.
• Reschedule instances that the user owns

Desktop Intelligence Rights

• Refresh the report's data
• Refresh List of Values
• Use Lists of Values
• View SQL
• Export the report's data
• Download files associated with the object

Desktop Intelligence Add in

• Download files associated with the object

Report

• Print the report's data
• Refresh the report's data
• Export the report's data
• Download files associated with the report

Web Intelligence Document

• Refresh the report's data
• Edit Query
• Refresh List of Values
• Use Lists of Values
• View SQL
• Export the report's data
• Download files associated with the object

Timeout errors aren't obvious when you encounter them.

There are at least 3 settings in XI that will cause your Web Intelligence reports to time out.

1. A universe level setting

2. Report Server Setting

3. CCM level startup parameter for the job server

While trying to get scheduled reports running for a client, we encountered some issues due to a report running too long.

Thank heavens that BO provides a nice user friendly error message that says:

"report ran too long."

NOT.

As usual a little detective work was needed test theories.

Informatica Job Processing

In our environment, Informatica runs nightly jobs that in turn kick off business objects processing using file based (triggered) events. Informatica processing is broken down into groups. Each time a group finishes Informatica kicks off a shell script that uses ssh to create the trigger file on the business objects server.

One of the groups was failing.

In our case, we had reports that ran in object packages. The package would fail because of one of the reports in the package failing.

The first time we received an error it appeared to be an Oracle error:

ORA-01013 user requested cancel of current operation

Our resident Oracle expert/dba informed us that this was

Cause: The user interrupted an Oracle operation by entering CTRL-C, Control-C, or another canceling operation. This forces the current operation to end. This is an informational message only.

So from Oracle's perspective, Business Objects was just telling Oracle: "I quit."

After doing some digging we found 3 areas to tweak to increase run time.

1. Universe modifications. There is a setting that can be made in Universe Designer that will limit execution time for a database connection.

The universe access restrictions allows execution time limits be set so that the universe designer can limit access to database resources.

Here is the setting:

It is noteworthy to mention that certain things accessed through Designer are written back to the Business Objects server apart from exporting the universe.

Access restrictions are an exampleof this.

I did to increase the execution time from 10 minutes to 30 minutes. The changes were immediately written to the CMS (without my exporting the universe). There are certain pieces in designer which have varying degrees of association/connection with the universe.

2. Report Server Setting. There is a setting that can be made through the CMC to extend the timeout of the report server.

Here is where to access the setting:

This restriction applies server wide. While the universe access restriction can be controled at the group or user level, this report server setting is set for all jobs that run on the server.

Much less granular control.

3. CCM level setting. There was a problem in XI release 1 having to do with the default CORBA timeout being set unreasonably low for many BO installations.

Only 10 minutes.

The full BO discussion can be read here:

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=c2018041&sliceId=&dialogID=7664327&stateId=1

The fix would involve specifying a longer timeout on the call made to the affected job server… so in our case that would be the Web Intelligence job server.

Since we were running Linux, this would have involved editing one of the unix shell scripts that starts our BO environment and restarting.

I've heard mixed reports as to whether this was fixed with XI release 2.

Taking care of 1 (universe setting) and 2 (report server setting via the Central Management Console) fixed our issue. We didn't need to mess with 3.