Oracle Serive Bus: Get file name from proxy service

The following is how to get the file name of a file that is being processed by a proxy service in OSB.

1. We can get the URI via the $inbound context variable with the following XPath:
$inbound/ctx:transport/ctx:request/tp:headers/file:fileName

This results in:
<file:fileName
xmlns:file="http://www.bea.com/wli/sb/transports/file">
c:\temp\input\2635978486281_
test.xml</file:fileName>

2. To get the filename only, use the tokenize function (which returns the characters after the last "\\":
tokenize($inbound/ctx:transport/ctx:request/tp:headers/file:fileName, "\\")[last()]

Auto increment primary key with Oracle Databases

Suppose you have a database and you want each entry to be identified by a unique number. This can be achieved easily in MySQL by specifying "auto_increment" for the column, but Oracle requires a little bit of extra work to get this done.

One way to do it is by creating two database objects, a sequence and a trigger:

1. As a simple example, imagine we have a table called "tbl_test" with two columns, "test_id" and "test_data".
create table tbl_test (test_id number, test_data varchar2(145)); 

 2. Now, we create a sequence for the test_id column. Note that the numbering starts with 1 and is incremented by 1.
create sequence seq_test 
start with 1 
increment by 1 
nomaxvalue; 

3. Next we create the trigger that will automatically insert the next number from the sequence into the test_id column.
create trigger tgr_test 
before insert on tbl_test 
for each row 
begin 
select seq_test.nextval into :new.test_id from dual; 
end;
/

XMLType in SQL Developer

I thought I'd add this as for someone new to SQL Developer, this is not obvious at first glance. When creating a table with column(s) of type XMLType, the type is not available via the standard Create Table wizard. Below are the full steps to use that datatype:

• Right-click on the 'Tables' node in the 'Connections' view
• Select 'New Table'
• Check the 'Advanced' check box located in the top right corner - This will show an alternate 'Create Table' dialogue which hosts considerably more functionality
• In the 'Column Properties' area, select the 'Complex' radio button for datatype
• Choose the SYS schema; the XMLType will be available in the drop down