Monthly Archives: May 2013

( A Small Hint ) how to show XML data stored as CLOB in LONG version on Oracle


    • I have XML strings stored as CLOB.  When I ran a query to retrieve it, the Toad 10.6.1 Data Grid shows “(HUGECLOB).”  In Toad, it use to show the actual value.

    1. Go to View | Toad Options
    2. Expand Data Grids | Data
    3. On the right-hand side, check ‘Preview CLOB and LONG data’
    4. Click on OK and refresh your Schema Browser

    NOTE: Also verify that View Menu | Toad Options | Schema Browser | Data Tab | “Don’t select BLOB/CLOB fields” check box is NOT checked.

Adding Logout button in Application Bar

There is a requirement as client asked us to add a Log out button directly in application bar instead of going to File-> Log out. Also it should be an image instead of button.
I have followed the following steps to achieve this requirement.


  • First you need to add the custom image to the <SIEBEL_ROOT>PUBLIC\< language_folder>\IMAGES folder. Image I used is ‘LogOut_New.png’
  • Find the Container Web Page for the application you want to add the button. As I am using eCommunication, It is “CC Container Page”.
  • Navigate to Webpage in object explorer,choose “CC Container Page” and click “WebPage Item” child object Type.
  • Create a new Webpage Item with below properties:
    • Name : LogoutButton
    • Type : ImageButton
    • Caption-String Override : <img src=”images/LogOut_New.png” alt=”logout” height=”24″ width=”24″ border=”0″ align=”absmiddle”>
    • Method Invoked : Logoff
    • Item Identifier : 14

Compile Changes and launch application. you will see the log-out button on top right corner of the application as shown in below image.

Dynamic SQL Tracing in Application

While debugging we might want to know the SQL generated for the particular event. So we increase the log level to 5 OR we enable sql spooling.
In both cases, it is difficult to find the sql’s generated only for the particular event. The following configuration will help you out to turn on/off sql spooling and there by analyzing the sql generated only for the events.

The Idea here is to create a command button on application toolbar and then upon pressing button invoke a business service which will turn on/off sql trace.Follow the below steps to configure dynamic sql trace.

Step 1: Create a Business Service with the following code.

function LogTrace(Inputs, Outputs)
var isTraceOn = TheApplication().GetProfileAttr(“IsTraceOn”);
var sFile = “c:\\temp\\Siebel_Trace.Log”;
if( isTraceOn == “TRUE” ){
var oShell = COMCreateObject(“Shell.Application”);
oShell = null;
Step 2: Create a Command Object to call this business service method.
Name: Log Trace
Business Service: Business Service Name created in step1.
Display Name: Log Trace
HTML Bitmap: Log Trace Icon (you can create a bitmap image and associate it to this command)
Method: LogTrace
Target: Server

Step3: Add this command to a Toolbar Item. I used Query Tool bar which is displayed right top corner of the application.
Query for the “HIQuery” Toolbar from Toolbar Object Type.In Toolbar Item Child Object Type add following.
Name: Log Trace
Command: Command Created in step2
Display Name: Log Trace
HTML Type: Link
Position: 30

Compile the change and open application, you will see a button just after the query toolbar.
Press Log Trace Command button once. It will enable the sql trace, then perform the event i.e, click on button or navigate to a new view.
Then press the Log Trace command button. It will disable the sql trace and opens a text file with sql statements executed for that event.

VARRAY on Oracle

Creating a Varray Type:

  1. A varray stores an ordered set of elements.
  2. Each element has an index associated with it.
  3. A varray has a maximum size that you can change dynamically.
You create a varray type using the SQL DDL CREATE TYPE statement.
You specify the maximum size and the type of elements stored in the varray when creating the
The basic Oracle syntax for the CREATE TYPE statement for a VARRAY type definition would be:
CREATE OR REPLACE TYPE name-of-type IS VARRAY(nn)of type
Where name-of-type is a valid attribute name, nn is the number of elements (maximum) in the array, and type is the data type of the elements of the array.
You can change the maximum size of a varray using the ALTER TYPE statement.
SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2OF VARCHAR2(50);
  2  /
Type created.SQL>
SQL> desc addressVarray;
 addressVarray VARRAY(2OF VARCHAR2(50)



In the Oracle environment, array subscripts start from 1, and not from 0 (as in C and Java).
VARRAYs are of fixed length.
You specify the length of the array when you define it.
Arrays of elements of the same type use sequential numbers as a subscript.
VARRAYS can be used both in PL/SQL and SQL.
You should use VARRAYs when you know the size of your data set and that size is very stable.
  type VarrayType is varray(sizeof ElementType;
create or replace type VarrayType is varray(sizeof ElementType;
The size of a VARRAY must be a positive integer and cannot be null.
You cannot create an array of REF CURSORs.
SQL> declare
  2      type month_va is varray(13of VARCHAR2(20);
  3      v_month_va month_va;
  4      v_count_nr number;
  5  begin
  6      v_month_va:=month_va('A','B','C','D','E','F','G');
  7      DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
  9      v_month_va.extend;
 10      v_month_va(v_month_va.last):='Null';
 11      DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
 13      for i in v_month_va.first..v_month_va.last
 14      loop
 15          DBMS_OUTPUT.put_line('v_month_va(i)'||v_month_va(i));
 16      end loop;
 17  end;
 18  /
v_month_va(i): A
v_month_va(i): B
v_month_va(i): C
v_month_va(i): D
v_month_va(i): E
v_month_va(i): F
v_month_va(i): G
v_month_va(i): Null
PL/SQL procedure successfully completed.

Reverse key Index on Oracle

Oracle introduced reverse key index to reduce the block contention(buffer busy waits) in index segment on 2009. Normal index stores the index column value and row-id in index segment. But reverse key index stores the reverse index column value and row-id. Of course, oracle will not reverse the row-id. So we call this as reverse key index.

Let us say, the index column value is 1234. It will be converted as 4321 and stores in index segment. 1235 will be converted as 5321 and stored in index segment. In reverse key index, oracle will not store the actual column values in sequentail order. Instead, it reverse the index column value and will be spreaded across many index blocks. This would avoid index block contention.

Reverse key index would be useful when index column is populating from sequence and concurrent session inserting the data on the table.

What is ideal place to use Reverse key index?

1. In a single instance system, multiple sessions are trying to insert/update the index column at the same time, the index column value is extracting from sequence. Also we are deleting the rows and we are not doing any range scan on reverse key index column. This would be ideal place for reverse key index.

2. In RAC environment, if we have column populated by an increasing numbers, concurrent session inserting the rows from different RAC instance, the index block will have contention between nodes. The data are deleted time to time according to some rules which leave some old data undeleted in the table. Also there is not much range scan on this table. This would be ideal place for reverse key index.

If we use regular index for above two scenario, during the concurrent insert, oracle stores the index values in the sequence order and it would end up storing multiple sequential index values in the same block. This would lead into block contention when multiple insert happens at the same time with in the same block. Another thing, when we delete the old rows, the block will not be moved to free list until all rows are deleted in that block.

Reverse key index will resolve the above said issues.

When we use reverse key index, the empty space(empty space would happen when we delete old rows in the table) in the block will be refilled. Because, reverse key index stores the column value in reverse order. So column value will not be stored in sequential order.You will be able to use the empty space for different values with reverse key indexes.

When we use reverse key index, index block contention will be reduced. The reverse key index will scatter the entries accross different blocks during inserting and your many concurrent sessions will not have index block contention issues.

How do we create reverse key index?

scott@orcl> create index idx_rev
2 on employee(empno) reverse;

Index created.


How do we covert regular index to reverse key index?

scott@orcl> create index idx_rev on employee(empno);

Index created.

scott@orcl> alter index idx_rev rebuild reverse;

Index altered.


How do we covert reverse key index to regular index?

scott@ordb> alter index idx_rev rebuild noreverse;

Index altered.


Restriction on Reverse key index?

Reverse key index does not support range scan. Since the index column values are not stored in the sequential order.