SBL-DAT-00500 and SBL-EAI-04451: There were more rows than could be returned. Please refine your query to bring back fewer rows.

Reason:

By default any business component query with ForwardBackward mode can fetch up to 10000 records at a time. If any query (generated by eScript or Workflow or any other way) returns more than 10000 records, Siebel throws this error ‘There were more rows than could be returned.’

How to fix:

Below we have mentioned 3 solutions to fix this error. You could choose one as per your requirement.

Solution 1: Use ExecuteQuery with ForwardOnly mode

This is the best solution if it fulfills your requirement. Oracle also suggests not to use ‘ForwardBackward’ mode with ExecuteQuery if it is not absolutely required. Instead of ‘ForwardBackword’, use execute query with ‘ForwardOnly’ mode that does not limit on fetching records.

Solution 2: Refine query

If you have to use ‘ForwardBackward’ mode to meet the requirement, try to add few more conditions so that Siebel fetches less than 10000 records at a time.

If there is an absolute need of fetching more than 10000 records using ‘ForwardBackward’ query mode, then only go for solution 3.

Solution 3: Change ‘DSMaxFetchArraySize’ parameter value

DSMaxFetchArraySize is a subsystem parameter that determines the number of records a business component query with ‘ForwardBackword’ mode can fetch at a time. By default, DSMaxFetchArraySize has value ‘0’ that means business component can return only 10000 records in ForwardBackward query mode. It does not restrict the number of records in ForwardOnly execution mode. If you want to get all rows from ForwardBackward query, set DSMaxFetchArraySize parameter value as -1 (negative 1). But you do it only when you don’t have any workaround because setting DSMaxFetchArraySize to -1 has impact on application performance due to large memory use. Even Siebel Object Manager could crash because of memory exhaustion.

To change this parameter value, follow below steps:

• Login into Siebel application as Administrator
• Navigate to Administration-Server Configuration > Enterprises
• Under ‘Profile Configuration’ tab, search for the profile ‘Server Datasource’
• In the Profile Parameters list, click on ‘Advanced’ button to get all advanced profile parameters
• Query on Alias field for DSMaxFetchArraySize and set the value to -1
• Restart Siebel Server services

Beside ‘DSMaxFetchArraySize’, you check ‘Maximum Cursor Size’ property at Business Component level also. This property also restricts the number of records that Siebel CRM can fetch from server. If ‘Maximum Cursor Size’ property contains any value, it overrides MaxCursorSize parameter, defined in the configuration file. This parameter is applicable only on Oracle or DB2 database.

– Set ‘Maximum Cursor Size’ property value to -1 if you want records until Siebel encounters an end of file
– Set it to ‘0’ to get 10000 records
– If you set any number greater than 0, Siebel fetches that many number of records

Same as DSMaxFetchArraySize, this property also has performance impact. So before setting this property value to -1, check all other possibilities to avoid it.

If your Siebel application is using IBM DB2, then you need to check another two parameters – DSPreFetchSize and DSMaxCursorSize.

Do you have any question? Please feel free to comment below.

Keep in touch, follow TechOneStop on Facebook / Twitter / LinkedIn / Goolge+.

<< The method IsPortableUIMode is not supported            Error has occurred executing a Sql >>

Debugging in Siebel Open UI is the most important and difficult task for any Open UI developer, isn’t it!!

But don’t worry, after reading this article, it will be easier for you to debug script in Siebel Open UI. In Open UI, we write script mainly for Presentation Model, Physical Renderer and Theme. Here we will discuss step by step approach of debugging Siebel Open UI script.

We will use browser developer tool extensively to debug Open UI script. In our previous module on Siebel Open UI theme, you have seen how to use browser developer tool for development purpose. Here you will use the same tool for debugging.

So what is Browser Developer Tool ?

Browser developer tool is a vanilla tool provided by almost all modern browsers like Google Chrome, Mozilla Firefox, IE 8+. It gives an inspector to verify JS or CSS files are downloaded properly on the browser or not. It allows inline modification of HTML and CSS rules to see how changes affect look and feel of the webpage. You can also see variables values, set breakpoints in JavaScript code from developer tool.

To open browser developer tool, right click on any web page and then click on ‘Inspect Element’. You could use shortcut keys also like F12 for Google Chrome, Shift+Ctrl+Q for Mozilla Firefox.

Steps for debugging in Siebel Open UI:

1. Verify JavaScript and CSS files are downloaded properly in the browser
2. Verify Javascript Class is properly loaded
3. Use SiebelJS.Log() function to log messages
4. Use debugger statement to add breakpoints inside script

1. Verify JavaScript and CSS files are downloaded properly in the browser

If your code does not work, first step is to verify all files are downloaded properly in the browser. Best tool to do so is browser developer tool (Inspect Element). You first open Siebel Open UI application and right click to choose ‘Inspect Element’ to open developer tool. If your browser is Google Chrome, then go to ‘Source’ tab and search for files.If there is any error or warning in downloading file, you will see a small red or yellow icon on the top right hand corner. You click on those icons and down the ‘Inspect Element’ window, another window will show all errors.

There may be several reasons behind download failure.

– JS file is not properly defined in Manifest Administration. To know more about Manifest Administration, read our article ‘Manifest File Customization‘

– Error in JS file itself

2. Verify JavaScript Class is properly loaded

To verify JavaScript Class is properly downloaded to the client, go to Inspector tab and type SiebelAppFacede.<First few letters of class name>

– If class is properly loaded, auto complete feature of the console will recognize the class

– If browser does not suggest the class, then verify the class is properly registered in the namespace of JS file or not

3. Use SiebelJS.Log() function to log messages

Use SiebelJS.Log() to print variable values or any other log messages on inspector tab

– Use GetFieldValue() to get values of controls in the Presentation Model

– Use val() to get values of elements in the Physical Renderer

– To see the output of log messages, open browser tool and go to Inspector tab

• All log messages appear in execution order
• If you have used log statement in multiple places, then add file name or any separator among log messages. It will help you to understand which log message is coming from which file.

4. Use debugger statement to add breakpoints inside script

– Almost all modern browsers have built-in breakpoints feature with developer tool. You could add expression on breakpoints also.

– If the developer tool does not give breakpoints functionality or you want to set breakpoints explicitly, then use ‘debugger’ statement in your script.

– ‘debugger’ behaves the same way as breakpoints, code execution will stop at that point.

Few Recommendations :

– Use JavaScript code quality checking tools like JSHint, JSLint to verify JS file before deployment

– Remove debugger and SiebelJS.log() from script before moving to production

– Try to avoid alert() to print variable values

              ♦ While debugger and SiebelJS.log() are invisible to end users, alert() throws alert message to end users

– Test with all browser and device combinations before moving the script to production

Session Highlights – Debugging in Siebel Open UI:

1) Use browser developer tool to verify that the JS or CSS files have been downloaded properly

2) Use SiebelJS.Log() to print variable values or to decide that the code execution has reached certain point

3) Use debugger to add breakpoints

This ends our seventh Open UI training module – Debugging in Siebel Open UI.

To check your Siebel Open UI skill, click here – Siebel Open UI Quiz 

If you have any question about Siebel Open UI Debugging, please feel free to comment below.

Keep in touch, follow TechOneStop on Facebook / Twitter / LinkedIn / Goolge+.

<< Module 6: Siebel Open UI Theme                             Module 8: Presentation Model Methods >>

Watch our YouTube Video on Debugging in Siebel Open UI: