Feed aggregator

Oracle Reports using BI Publisher

Richard Byrom - Mon, 2008-04-14 18:32

I’m currently working as an associate consultant with an organisation called BeLife Ltd. We are interested in showing organisations how they can transform their Oracle Reports using BI Publisher (formerly XML Publisher) and would like to offer a free 1 hour demonstration of this product to anyone that’s interested.

BI Publisher is standard Oracle Financials integrated functionality, but it is only used by a few organisations. Recent feedback suggested that this is because organisations just don’t know how to start with using BI Publisher even though users are eager to experience the benefits. We’d like to show you in an hour how you can get started.

We can show you how to use BI Publisher to generate:

  • Remittance Advices
  • Dunning Letters and Customer Statements
  • General Ledger Reports for downloading direct to Excel, including grouping and formulae (sums, average, maximum, minimums)
  • Management information reports with tables, graphs and conditional formatting.

Whether you’re a super user, end user or implementor if you’re interested in potentially using this reporting tool or know of anyone that may be interested in a demonstration please contact me.

Oracle Reports using BI Publisher

OracleAppsBlog - Mon, 2008-04-14 18:32
Categories: APPS Blogs

Oracle Transparent Gateways - General Description - Part I

Aviad Elbaz - Sun, 2008-04-13 17:15

A lot of companies have several applications based on more than one database system (e.g DB2, SQL Server, Sybase, etc).
Each database system store its own data and naturally there's a need to share data among the various heterogeneous database systems.

Oracle, starting with Oracle Database 9i, offers the "Oracle Transparent Gateways" (Oracle Database Gateways) to allow integration of Oracle database with non-Oracle databases.
Unlike "Oracle Generic Connectivity" that provide a generic solution to connect any ODBC/OLEDB compliant non-Oracle system using ODBC and OLEDB standards, the "Oracle Transparent Gateways" are solutions specifically tailored for each target non-Oracle database system.
The "Oracle Transparent Gateways" communicates using the target database native interface, it's make it possible to access to non-Oracle systems as if they were Oracle databases.

The Transparent Gateway solution composed of two parts:

  • Heterogeneous Services (HS) - this is a general integrated component that make it possible to connect to non-Oracle systems from Oracle database
  • Oracle Database Gateways (agent) - these are specific tailored agents for non-Oracle systems that make it possible to interacts with the target non-Oracle system.

Heterogeneous Services (HS)

This is a generic component for connecting to non-Oracle systems.
It's an integrated component of the database that "extends the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information" (Oracle Doc').

The following services are provided by the Heterogeneous Services (HS):

  • Transaction service
    Responsible for establishing authenticated connection when the non-Oracle system is accessed and close the connection when session end.
    Also responsible for global data integrity using two phase commit protocol, even for non-Oracle systems that do not support two phase commit natively.
  • SQL Service
    Provide the translation capabilities: SQL & data dictionary translations.
    The SQL services uses an information arrived from the Gateway to translate Oracle SQL to the appropriate SQL dialect of the non-Oracle system. Also, references to data dictionary tables in a query will be rewrite by the SQL Service and result with a result set as from Oracle database.
    ** Data type translation performed by the Gateway.
  • Procedural Service
    An interface for executing stored procedures on non-Oracle system.
  • Pass through SQL
    A mechanism for issuing a SQL statement against the non-Oracle system. It is useful when the statement/function/procedure are not supported by the Gateway.

Oracle Database Gateways (agents)

This component responsible for the interface to the remote non-Oracle system.
It's also responsible for SQL mappings and data type conversions.
The Gateway interacts with Heterogeneous Services to make it possible to transparently connect from an Oracle Database to a non-Oracle System.
In contrast to the HS (Heterogeneous Services) which is a generic component, the Gateways are tailored specifically for each target non-Oracle system.
There are Gateways for many systems such as: DB2, Sybase, Informix, SQL Server, IMS, VSAM, Adabas, Ingres, Teradata, to name a few.
The Gateway can be installed on the same server like the non-Oracle system or on the same server like the Oracle system or on a separate server.

Next post I'll show an example of connecting and retrieving data from a SQL Server database to an Oracle database using Oracle Transparent Gateway for Microsoft SQL Server including all configuration required for Transparent Gateway and the source Oracle System.

Related Documents for more information:

- Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit)

- Database Gateways Technical Whitepaper

You are more than welcome to leave a comment.


Categories: APPS Blogs

Customer Refund in Receivables

Krishanu Bose - Sat, 2008-04-12 01:27
There are cases when customers overpay, or pay one invoice twice. In all such cases the customer is due for a refund. The easiest approach would be to ask the customer to absorb the overpayment if the amount is small. However, if the amount is large the customer would no doubt ask for a refund. There are basically three ways to handle customer refunds:
1. Issue a manual check for refund to the customer and record a Debit Memo in Receivables and match this to the receipt.
2. Ask the customer to leave the cash as "on-account" and apply this later to any other open invoice.
3. Setup customer as supplier in Payables, and issue a check out of payables. The third option is more cumbersome but will make bank reconciliation a lot smoother. First create a debit memo in receivables, using a clearing account (the clearing account should always show zero balance). Match the receipt to this Debit Memo. Then create an invoice for this customer (set as supplier in AP), using the same clearing account as the expense account in the invoice. Pay the invoice in the normal way and do a bank reconciliation.
The above process of customer refund is only limited to 11i. In Oracle R12, Oracle Receivables is fully integrated with Oracle Payables to deliver a seamless, automated process to generate check and bank account transfer refunds for eligible receipts and credit memos.

Accrual Balance Display in Self Service HR

RameshKumar Shanmugam - Fri, 2008-04-11 19:18
One of the comon requirement that often times come in the Absence Managment is HR team want their employee to be able to see their available accrued/PTO balance.
As an employee it is always good to know how much vacation balance an employee is left with

Here is a simple setup that can enable your employee to view the available balance through the Self Service absence Management

Step 1: Define the Absence element
Step 2: Link the Element based on the Eligibilty Criteria
Step 3: Define the Absence Type
Step 4: Define the Accrual Plan
Step 5: Attach the Accrual Plan to the Employee
Step 6: Complete the setup for the Self Service HR
Step 7: Make sure employee is able access the absence Management Functionality

Setup steps to enable the Entitlement Balance in the Absence Management

Step 1: Create an Element set with the type as ‘Run Set’
Step 2: Attach the element set to the Profile option HR: Accrual Plan Element Set Displayed to User at the Responsibility level (Employee Self Service)
Step 3: Bounce the Appache

Now Navigate to the Employee Self Service > Absence Management > (T) Entitlement Balance

Try this out
Categories: APPS Blogs


Yasin Baskan - Fri, 2008-04-11 12:26
Last week I read a post in Andy C's blog about the service called Disqus. It is a service to keep track of comments in blogs. Later he made another post about it.

I have been looking for a solution to keep track of blog comments, both mine and other people's. Not all blogs have the option to subscribe to the comments, when you comment on a post you need to check later if someone commented further. I want a central repository where I can see all my comments on a post, all comments made by others on the same post and all comments made on my own blog.

I tried Cocomment before but I was not satisfied with it. So I decided to give Disqus a try and enabled it on this blog. Laurent Schneider decided to try it too.

Then Andy made another post about the concerns of some people about Disqus (one being Tim Hall). Their concerns make sense. Who owns the comments one makes, the commenter or the blog owner? Is it sensible to store the comments to your blog not in your blog database, but elsewhere? What if Disqus is not there next year, what if Disqus is inaccessible for some time? Is it possible to export the comments from Disqus and import them back to the blog?

Some of these may be irrelevant if you are using a public blogging service, like Blogger, because it means you are already storing your posts and comments somewhere else. The question "What if Blogger is not there next year?" comes to mind for example.

A solution suggested by Tim Hall is to dual post the comments. The comments will be in the blog and on Disqus also, this need the blogging service to provide a way to do it.

Another solution can be a strong export-import utility in Disqus. That way you can export the comments and put them back to the blog whenever you want. Disqus currently has an export utility but as far as I have read it is not reliable for now.

While I agree with these concerns I liked what Disqus provides. The one-stop page for all comments, the threading of comments, being able to follow other users are primary features I like. So, I will stick with it, at least for now.

Collect in 10G

Yasin Baskan - Fri, 2008-04-11 04:29
Collections can be a great help in speeding up the PL/SQL programs. By using bulk collect operations it is possible to get great performance improvements.

In 9.2 we needed to use the bulk collect clause to fetch rows into a collection. 10G brings a new function called COLLECT, which takes a column as a parameter and returns a nested table containing the column values. Using this we can get the data into a collection without using bulk collect.

Here is a very simple demo of this new function.

YAS@10G>create table t as select * from all_objects;

Table created.

YAS@10G>create or replace type name_type as table of varchar2(30);
2 /

Type created.

YAS@10G>set serveroutput on

1 declare
2 v_names name_type;
3 begin
4 select cast(collect(object_name) as name_type) into v_names from t;
5 dbms_output.put_line(v_names.count);
6 dbms_output.put_line(v_names(1));
7* end;

PL/SQL procedure successfully completed.

One difference between this and bulk collect is, since this a sql function we need a sql type for this, it cannot be used with local PL/SQL types.

1 declare
2 type name_type is table of varchar2(30);
3 v_names name_type;
4 begin
5 select collect(object_name) into v_names from t;
6* end;
select collect(object_name) into v_names from t;
ERROR at line 5:
ORA-06550: line 5, column 35:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 5, column 9:
PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got -
ORA-06550: line 5, column 2:
PL/SQL: SQL Statement ignored

Another difference, and a more important one, is the performance difference between these two constructs. Using Tom Kyte's runstats package, I did a test to compare the two. I ran the test several times, the results were similar.

1 declare
2 v_names name_type;
3 begin
4 runStats_pkg.rs_start;
6 for i in 1..1000 loop
7 select object_name bulk collect into v_names from t;
8 end loop;
10 runStats_pkg.rs_middle;
12 for i in 1..1000 loop
13 select cast(collect(object_name) as name_type) into v_names from t;
14 end loop;
16 runStats_pkg.rs_stop;
18* end;
Run1 ran in 1329 hsecs
Run2 ran in 4060 hsecs
run 1 ran in 32.73% of the time

Name Run1 Run2 Diff
LATCH.qmn state object latch 0 1 1
STAT...redo entries 9 10 1
LATCH.JS slv state obj latch 1 0 -1
LATCH.qmn task queue latch 5 6 1
LATCH.transaction branch alloc 0 1 1
LATCH.sort extent pool 0 1 1
LATCH.resmgr:actses change gro 1 0 -1
LATCH.ncodef allocation latch 0 1 1
LATCH.slave class 0 1 1
LATCH.archive control 0 1 1
LATCH.FAL subheap alocation 0 1 1
LATCH.FAL request queue 0 1 1
STAT...heap block compress 6 5 -1
LATCH.session switching 0 1 1
LATCH.ksuosstats global area 1 2 1
LATCH.event group latch 1 0 -1
LATCH.threshold alerts latch 0 1 1
LATCH.list of block allocation 2 0 -2
LATCH.transaction allocation 2 0 -2
LATCH.dummy allocation 3 1 -2
LATCH.user lock 2 0 -2
LATCH.Consistent RBA 4 2 -2
STAT...calls to kcmgcs 4 6 2
STAT...active txn count during 4 6 2
STAT...cleanout - number of kt 4 6 2
STAT...consistent gets 587,009 587,011 2
STAT...consistent gets from ca 587,009 587,011 2
STAT...consistent gets - exami 4 6 2
LATCH.resmgr:free threads list 3 0 -3
LATCH.PL/SQL warning settings 3 0 -3
LATCH.OS process 6 3 -3
LATCH.compile environment latc 3 0 -3
LATCH.slave class create 0 3 3
LATCH.resmgr:actses active lis 3 0 -3
LATCH.cache buffers lru chain 0 3 3
LATCH.OS process allocation 10 14 4
LATCH.session state list latch 4 0 -4
LATCH.redo allocation 50 46 -4
STAT...consistent changes 17 22 5
LATCH.resmgr group change latc 5 0 -5
STAT...db block gets 17 22 5
STAT...db block gets from cach 17 22 5
LATCH.library cache pin alloca 6 0 -6
STAT...db block changes 26 32 6
STAT...session logical reads 587,026 587,033 7
LATCH.In memory undo latch 23 16 -7
LATCH.session idle bit 10 3 -7
LATCH.mostly latch-free SCN 6 14 8
LATCH.KMG MMAN ready and start 5 13 8
LATCH.lgwr LWN SCN 6 14 8
LATCH.simulator hash latch 34,010 34,001 -9
LATCH.simulator lru latch 34,010 34,001 -9
LATCH.session timer 5 14 9
LATCH.dml lock allocation 10 1 -9
LATCH.undo global data 20 10 -10
LATCH.post/wait queue 10 0 -10
LATCH.active checkpoint queue 4 15 11
LATCH.session allocation 13 2 -11
LATCH.archive process latch 4 15 11
LATCH.library cache lock alloc 16 0 -16
LATCH.object queue header oper 8 32 24
LATCH.client/application info 25 0 -25
LATCH.redo writing 24 51 27
LATCH.active service list 37 81 44
STAT...undo change vector size 2,124 2,200 76
LATCH.channel operations paren 60 197 137
LATCH.cache buffers chains 1,174,359 1,174,189 -170
LATCH.JS queue state obj latch 108 288 180
LATCH.messages 108 291 183
STAT...redo size 2,772 2,964 192
LATCH.checkpoint queue latch 80 282 202
LATCH.enqueue hash chains 269 652 383
LATCH.enqueues 248 645 397
LATCH.SQL memory manager worka 276 944 668
LATCH.shared pool 42 1,029 987
LATCH.library cache lock 170 2,018 1,848
LATCH.library cache pin 2,130 4,064 1,934
STAT...Elapsed Time 1,330 4,061 2,731
STAT...CPU used by this sessio 1,334 4,083 2,749
STAT...recursive cpu usage 1,268 4,064 2,796
LATCH.library cache 2,264 5,074 2,810
LATCH.row cache objects 49 9,015 8,966
STAT...session pga memory 2,097,152 1,441,792 -655,360

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,248,536 1,267,073 18,537 98.54%

PL/SQL procedure successfully completed.

As you see the bulk collect method completes in 1/3rd of the time of the collect function method. Using the collect function hits the library cache harder and uses more latch operations.

The COLLECT function can be used in sql statements to compare collections of columns. In Laurent Schneider's comment on my previous post you can find an example of it.

New Stuff (4) Hit you over the head with a CLOB!

Carl Backstrom - Thu, 2008-04-10 17:24

One problem with APEX is it has issues with dealing with text values of greater than 32k, while there is a fairly simple workaround it does have quite a few pieces and there has to be an easier way to do it.

Well in APEX 3.1 there is now an integrated javascript call to take care of this. This example also shows some of our new namespaced javascript objects and functions.

I've created a working example here.

Setting the CLOB

function clob_set(){
var clob_ob = new apex.ajax.clob(
var rs = p.readyState
if(rs == 1||rs == 2||rs == 3){
}else if(rs == 4){
}else{return false;}


What's happening in the previous piece of javascript is I'm creating a apex.ajax.clob object. This object only takes one parameter when being initialized which is a function , or pointer to a function, to call when the XMLHTTP object's readyState changes. In that return function ,and for all built in APEX asynchronous AJAX calls, p is the XMLHTTP object.

Once the apex.ajax.clob object is created you just call set method giving it a string ._set(String Value);

It will automatically create a collection in your session CLOB_CONTENT and populate the CLOB001 column.

You can then use that in a page or application level process , usually by calling a doSubmit() and submitting the page.

The p.responseText on successful population of the CLOB will be SUCCESS.

Getting the CLOB

function clob_get(){
var clob_ob = new apex.ajax.clob(
var rs = p.readyState
if(rs == 1||rs == 2||rs == 3){
}else if(rs == 4){
}else{return false;}

Getting a CLOB is much the same as setting one. Create the apex.ajax.clob object setting the function to call when p.readyState and then call the ._get() method which doesn't take any parameters.

This solution only deals with one clob at a time and the clob is alway put into the CLOB_CONTENT collection, though dealing with multiple clobs is also much easier, more on that later ;). But it is much easier to work with than the old workaround.

I just watched 2001: A Space Odyssey in the last week in HD. If you've never seen or it's been awhile or never seen it in HD do yourself a favor and check it out it's an amazing movie, though it looks like they were a little off on the dates.

Oracle Apps Marketing for Upgrade projects - A small tip

Krishanu Bose - Wed, 2008-04-09 13:53

It has been my observation that a lot of time and effort goes waste knocking the wrong door for Oracle Apps project. Then the question arises, how to know which door to knock. Obviously, one should not waste precious resources uselessly pursuing organizations that are running on a fully supported version of Oracle Apps like R12, 11.5.10, 11.5.9, unless the organization comes to you with a view to upgrade to R12 or 11.5.10. Please refer chart below to find the support time lines for different releases of Oracle E- Business suite and plan accordingly which organizations to target and whom to avoid.

Some New Thoughts for an Old Friend (iSetup)

Solution Beacon - Wed, 2008-04-09 10:25
Oracle has given an old friend a facelift in Release 12. Among some of the nips and tucks you will find the ability to create custom selection sets, comparison reports between multiple environments and a new process flow that promotes “Transformation” to a process all by itself. In addition to congratulating “Transformation” on it’s new found level of achievement, let’s dive in and discover what

Relational algebra: division in sql

Yasin Baskan - Wed, 2008-04-09 04:42
There was a question in one of the Turkish Oracle mailing lists which got me interested. The question simply was:

I have a table holding the parts of a product and another table holding the suppliers of these parts. How can I find the suppliers which supply all the parts?

Someone suggested using the division operation of relational algebra but did not provide how to do it in Oracle. So I started with the Wikipedia link he provided to solve the problem in sql.

Here are the tables:

SQL> create table parts (pid number);

Table created.

SQL> create table catalog (sid number,pid number);

Table created.

SQL> insert into parts select rownum from all_objects where rownum<=5;

5 rows created.

SQL> insert into catalog values (10,1);

1 row created.

SQL> insert into catalog select 1,pid from parts;

5 rows created.

SQL> select * from catalog;

---------- ----------
10 1
1 1
1 2
1 3
1 4
1 5

So, the supplier which has all the parts is 1. How do we find that?

The division in relational algebra is done by some steps which are explained in the link. Let's follow those:

The simulation of the division with the basic operations is as follows. We assume that a1,...,an are the attribute names unique to R and b1,...,bm are the attribute names of S. In the first step we project R on its unique attribute names and construct all combinations with tuples in S:

T := πa1,...,an(R) × S

In our case we have the table CATALOG as R, the table PARTS as S. So if we write a sql to find out the above relation we need a cartesian join.

select sid,pid
from (select sid from catalog) ,parts

This give us all suppliers combined with all parts.

---------- ----------
10 1
1 1
1 1
1 1
1 1
1 1
10 2
1 2
1 2
1 2
1 2

---------- ----------
1 2
10 3
1 3
1 3
1 3
1 3
1 3
10 4
1 4
1 4
1 4

---------- ----------
1 4
1 4
10 5
1 5
1 5
1 5
1 5
1 5

We now have all the possibilities for the supplier-part relation.

The second step is:

In the next step we subtract R from this relation:

U := T - R

To subtract the table CATALOG we need the MINUS operator.

select sid,pid
from (select sid from catalog) ,parts
select sid,pid from catalog;

---------- ----------
10 2
10 3
10 4
10 5

After we had all the possibilities we subtracted the ones which are already in the CATALOG table and we got the ones which are not present in the table.

On to the next step:

Note that in U we have the possible combinations that "could have" been in R, but weren't. So if we now take the projection on the attribute names unique to R then we have the restrictions of the tuples in R for which not all combinations with tuples in S were present in R:

V := πa1,...,an(U)

This step just gets the supplier id's from the previous query.

select sid from (
select sid,pid
from (select sid from catalog) ,parts
select sid,pid from catalog


Now we have the supplier id which does not supply all of the parts. The next step is obvious, to find the other suppliers (the remaining ones supply all the parts).

So what remains to be done is take the projection of R on its unique attribute names and subtract those in V:

W := πa1,...,an(R) - V

To do this we need to subtract the previous query from the table CATALOG.

select sid from catalog
select sid from (
select sid,pid
from (select sid from catalog) ,parts
select sid,pid from catalog


In some pages (like this one) the same operation is done using a different sql (obtained by using "not exists" instead of "minus").

select distinct sid from catalog c1
where not exists (
select null from parts p
where not exists (select null from catalog where pid=p.pid and c1.sid=sid));

This one is harder for me to understand in the first look, I am more comfortable following the steps above.

It is great to follow the steps of relational algebra to solve a problem in sql. It helps very much in understanding the solution. Relational algebra rocks!

Solaris and Tamil - Latex/Xetex

Siva Doe - Tue, 2008-04-08 17:40

This is a continuation blog about my pet project of localising Solaris (Tamil, in particular). Recently had a chat with a friend of mine who has already authored a book (on Science) in Tamil and that is when he mentioned he uses Latex on Linux for his work. That started me to find out where Solaris is in this area.

I found that TexLive , a distribution of TeX that included support for Solaris and also had Xetex. Xetex supported Unicode fonts, that meant that I could use any Tamil font that I had. I initially tried to build TexLive 2007 from source code. Though the build was successful, there were some runtime errors about some missing files. Later, I downloaded the huge DVD iso image and installed it locally. Now, it is easy to do typesetting in Solaris too. Obligatory screenshot.

One point is Xetex seems to be slower on Solaris than Linux (both x86) only. I will have to see what leads to the slowness.

APEX Tip (the unwashed masses)

Carl Backstrom - Tue, 2008-04-08 13:16
Sometimes you only want a region / item /tab /list etc to show up if your in development mode.

Sure you can add a authentication and authorization setup and take care of this but a quick and dirty way is set the condition to PL/SQL Function Body Returning a Boolean and use this code.

if apex_application.g_edit_cookie_session_id is not null then
return true;
return false;
end if;

For a production application please take the time to set up a proper authorization rule, but for development this is simple and works.

Considering this is a quick and dirty fix/tip I guess it's the unwashed one :)

New Stuff (2) x01 and friends

Carl Backstrom - Tue, 2008-04-08 00:03
So I mentioned in my last post one of my favorite new features in Application Express 3.1 was being able to pass temporary values to OnDemand process, and I showed an example using x01.

Of course passing one value is useful but not as useful as passing 10-13 of them so lets meet the rest.

  • x01-x10
  • g_widget_mod
  • g_widget_action
  • g_widget_action_mod

As you can see there are 10 generic variables, x01-x10 plus the placeholders for some more specific functionality, sure you can use g_widget_mod , g_widget_action , g_widget_action_mod to pass just random values but they are there for a different use which I will go over on in another post.

You can see an example of these variables and code running here.

OnDemand process (GLOBAL_AJAX)

htp.p('wwv_flow.g_widget_mod : ' ||wwv_flow.g_widget_mod);
htp.p('wwv_flow.g_widget_action : ' ||wwv_flow.g_widget_action);
htp.p('wwv_flow.g_widget_action_mod : ' ||wwv_flow.g_widget_action_mod);
htp.p('wwv_flow.g_widget_num_return: ' ||wwv_flow.g_widget_num_return);
htp.p('wwv_flow.g_x01 : ' || wwv_flow.g_x01);
htp.p('wwv_flow.g_x02 : ' || wwv_flow.g_x02);
htp.p('wwv_flow.g_x03 : ' || wwv_flow.g_x03);
htp.p('wwv_flow.g_x04 : ' || wwv_flow.g_x04);
htp.p('wwv_flow.g_x05 : ' || wwv_flow.g_x05);
htp.p('wwv_flow.g_x06 : ' || wwv_flow.g_x06);
htp.p('wwv_flow.g_x07 : ' || wwv_flow.g_x07);
htp.p('wwv_flow.g_x08 : ' || wwv_flow.g_x08);
htp.p('wwv_flow.g_x09 : ' || wwv_flow.g_x09);
htp.p('wwv_flow.g_x10 : ' || wwv_flow.g_x10);

New Stuff (Q & A)

Carl Backstrom - Tue, 2008-04-08 00:01
Most of my last couple posts have focused on new APEX 3.1 features. And there have been some good questions in the comments. So I figured I'd go through some of them in a post to make the answers a little easier to find.

Question 1What is the difference between add() and addParam()

They are used for passing values in slightly different ways and with different results.

.add() should only be used when dealing with a page or application items, anything else will cause an error, things you set with .add() will also automatically be set in the session state.

.addParam() should be used with what we term as parameters, the main difference being is they are not set in session and are only available for that particular request.

Can I set those global variable's values like common items in PL/SQL:
... or is there any more concise way to do it ?

No you can't set these into the session in PL/SQL. Remember those values are temporary and will not be saved past each specific request. If you need to save values from one of these that's the perfect reason to use a application level item, or collection.

Question 2
There's something I don't get.
Why do you need a temp item at all?

Mainly because it's much easier to build reusable/generic AJAX components that can be used in multiple applications. It's also going to be a big part of how the AJAX component for custom item and regions types will be implemented, more on that later. Don't get me wrong there are very good reasons sometimes to use an application or page item but many times it's not.

A quick rule of thumb is if you want to save a value into session use the application or page items, if you just passing values, use the generic ones.

Question 3There is also g_widget_name, g_widget_num_return and g_clob_01. Could you explain them also?

g_widget_num_return I just plain missed this, my bad. Many times when I was building something using these new handy parameters it just seemed like I was also adding a number to set the number of return values, instead of wasting one of my x01-x10 we added another one. I've update my code example and the blog posting to show this.

I skipped g_widget_name on purpose as it will be used in a slightly different way in the future. You can absolutly use it now to pass values and it it won't break anything, in fact I'm working on an example that does just that.

g_clob_01 That one at the moment is unused, and might be removed, so I'd stay away from it. If you need to hold on to a specific CLOB the best way is to create a collection and use the clob column in that.

Thanks, for the questions, Matjaz , Anonymous and Mark

Also Doug Gault yet again is the first to to figure out one of my slightly obscure cultural references , good job Doug!

The Supply Chain Problem

Mary Ann Davidson - Mon, 2008-04-07 19:46

I recently participated in a Defense Science Board study that examined foreign influence over the supply chain of software. The study noted that, even as vendors need worldwide access to technological talent to enable them to create commercial software solutions benefiting the US Department of Defense, there is an increased risk that the supply chain of software may be compromised by adversaries, such as hostile nation states. Working on that task force brought supply chain issues front and center in my thinking for a number of months.


Supply chain security issues are on many people's minds these days.  More and more regulations impact IT operations either directly or indirectly, such as the Sarbanes-Oxley Act, the Gramm-Leach-Bliley Act, the Health Insurance Portability and Accountability Act (HIPAA), various breach disclosure laws such as California SB1386, and information security laws like Minnesota's adoption of some of the payment card industry (PCI) standards. (And these are just the US laws.) Customers are being pressured to establish (from documentation to demonstration) they are "more secure" and are in turn pressuring their supply chain - software vendors -  to prove that the enterprise software they provide is secure. Vendors are being asked everything from "What features and functions do you have to help meet regulatory requirements?" to "How do you embed security within your software development lifecycle?"  This is a good thing, and how markets are supposed to work.


In the vendor community, there is a low rumble of discontent about our supply chain's current lack of a "secure development lifecycle." I'm not talking about other software suppliers (for example, vendors who supply toolkits or components we embed) though at Oracle, we do vet these suppliers' security practices before we incorporate their technologies into our code. 


What I mean by supply chain is the universities who supply CS graduates to IT vendors. There is no "secure development lifecycle" in the vast majority of universities' degree programs - that is, security is not "baked into" graduates of relevant programs (e.g., computer science) throughout their degree programs. And that is a problem, perhaps the problem plaguing the software industry. All the other security remediation taking place in the software supply chain (such as multiple security point solutions, vulnerability analysis services, and patch management offerings) largely stems from the fact that most software was neither designed nor built to be secure. And to that point, developers don't code software from the perspective of an attacker. Many believe security is a task for someone else ("it's behind the firewall so we don't have to worry!"); but their code is a target and will only be more of one in the future.


CS majors graduate from long, labor-intensive degree programs without, in most cases, knowing even first principles of secure coding and secure engineering practice. They are not stupid, but ignorant.  They aren't being taught secure development practice because in many cases, their professors do not know it, or do not know the material well enough to teach it, or do not view it as a priority; I've heard a number of professors admit as much. Also, many professors are tenured and thus non-responsive to market forces. They don't have to change because they have the ultimate job security, which means that many can continue to teach Buggy Whip Design 101 instead of moving into the 21st century.  I can say this because I spent the first 18 years of my life living in university towns: my dad was a department chair, associate dean, and then dean of the faculty. I think "tenured" was one of the first words I learned to spell.


Last year, I got fed up enough with Oracle having to train otherwise bright and capable CS grads in secure coding 101 that I sent letters to the top 10 or so universities we recruit from (my boss came up with the idea and someone on my team executed on it - teamwork is a wonderful thing). Specifically, we sent the letters to the chairmen of the department of computer science (or equivalent) and copied the deans of the schools with oversight of the CS departments. In the letter, we stated that Oracle expends significant resources training CS graduates in secure coding practices. We described the impact to us and to our customers of avoidable, preventable security defects, and why the insecurity of commercial software is a national security problem. We also pointed out that SANS has developed an assessment for secure coding practice. And we stated that in the future, Oracle would give preference in hiring to those universities that emphasize secure coding practices.


I am sorry to state that only one of those universities we wrote to responded to my letter (specifically, one department chairman responded), and the one that did (while stating that they did have courseware pertaining to security practice) wanted funding from Oracle to develop a more robust class. Having grown up at universities, I know very well that universities as a group tend to be really well endowed. In English, this means they have all the money in the world to do things like "teach better" except that as a group, professors' fortunes rise or fall with getting money to Do More Research (quite often, much of which has already been done before, or better).


While I appreciate the University of X's CS department chairman getting back to me (and the fact that they had at least some material on secure coding practice), I see no reason to pay them to do work they should be doing, anyway. In particular, paying a university to develop a class on secure coding that only they teach is not going to solve this problem. Nor - despite excellent intentions - are the NSA's Centers of Excellence in Information Assurance going to solve the problem.


We need a revolution - an upending of the way we think about security -and that means upsetting the supply chain of software developers.  I suppose I am revolutionary-minded because I am finishing reading a book on the American Revolution (Liberty, by Thomas Fleming), but there is a point beyond which tinkering with existing structures of government is not enough. There is a principle at stake (like "taxation without representation is tyranny").  If the powers that be don't grasp the principle, the only choice is to "secede."  Maybe the principle that I want universities to grasp is the one the Marine Corps has: "Every Marine is a rifleman." Every Marine can fight - they don't "outsource" rifle handling to others if they are attacked. (Imagine how different the IT space would be if every developer thought and coded defensively and every product could self-defend. I bet the average Marine gunny sergeant could whip universities into shape in about 16 weeks or less.)


Like some of the publications circulated by the Sons of Liberty in the buildup to the American Revolution, I found my "letter to universities" idea struck a responsive chord. A fellow vendor asked for a copy of the letter. Someone in a quasi-government organization (who was keenly interested in the assurance problem) wanted a copy of the letter to go back to universities to prove to them that their "customers" needed them to change. Two people armed with my letter is a start, but it's not enough to start a revolution.


Forthwith, I have taken the liberty (after expunging the name of the university to which it was originally addressed) of PDFing one of my letters to universities from last year, and publishing it on the Oracle web site at: http://www.oracle.com/security/docs/mary-ann-letter.pdf


In so doing, I consider this to be both an open letter to my fellow vendors, and an open letter to universities.


To the vendor community, just as customers are demanding more of us in security (and rightly so), we must demand more of our suppliers. It is inefficient and wasteful for each of us to train CS graduates in secure coding practice - yet Oracle and many other vendors expect secure coding practice as part of our development processes (and if we aren't doing that, then we need to do it). More to the point, the cultural transformation - that CS graduates are responsible for the security and safety of the code they write - must happen in universities. Take my letter, modify it as you will, and start holding university CS programs' feet to the fire to improve. To quote Ben Franklin after signing the Declaration of Independence: "We must all hang together, or most assuredly we will all hang separately."


Also, vendors, if you have secure coding class material, work with the organizations that are trying to fix the problem. SANS, for example, is working on material for faculty members to use in teaching secure coding practice (Oracle is participating in this).  The Department of Homeland Security's Software Assurance Forum (next meeting in early May) has people working on a Common Body of Software Knowledge, as well as other training work. As I write this, I am working through the details of getting a tutorial Oracle developed on SQL injection prevention released to universities gratis. Those who have done it tell me that if you make secure coding courseware available, at least some CS professors will teach it.


Vendors can also express their concerns to the Association for Computing Machinery (ACM) - the accreditation body for CS degree programs. (Mahalo nui loa to Scott Charney of Microsoft, who did just that a couple of years ago and got a number of us in industry to sign the letter.) I note that the sooner we can get to a basic secure coding class everyone can use (phase 1), the harder it will be for ACM to refuse to change their accreditation program, especially if enough vendors complain to them. Let's make it easy to say "yes" and hard to say "no."


To universities, I cannot but contrast the education of engineers with that of computer science majors. Engineers know that their work product must above all be safe, secure and reliable. They are trained to think this way (not pawn off "safety" on "testers") and their curricula builds and reinforces the techniques and mindset of safe, secure and reliable product. (A civil engineer who ignores the principles of basic structures - a core course - in an upper level class is not going to graduate, and can't dismiss structures as a "legacy problem.")


Universities, you must start with a basic secure coding/secure development practice class that is required for all CS majors.* You must then revamp the fabric of every single class so that security becomes part and parcel of each class. If a student's "elegant technical solution" in an upper level class is hackable, the student shouldn't get a great grade: in fact, maybe hackable homework should be grounds for failure - kind of like a bridge design that would collapse under loading would get a failing grade in the Civil Engineering Department. I knew a professor at Stanford who routinely had his students "red team" and "blue team" each other's homework (and his class wasn't even a security class). I'd thank him if I could remember his name. Secure development practice needs to be embedded within the fabric of every class, not just in a single class that students file and forget.


Universities, think more broadly about the application of security to your classes. (I have learned more about this problem just since I sent the original letters.) For example, think about all the process engineers designing control systems for pharmaceutical companies, chemical plants, utilities, and more. Do you think that security is embedded within the fabric of each and every course that they take? No, it isn't. (True and scary story from a colleague about a guy who insisted that his PC - which had a control system interface on it - was not Internet accessible. Oh really, what is that instant messaging window doing open on your desktop?) 


I also offer a personal anecdote about the difference between "taking a class" and immersing yourself in a language in support of my argument. Many readers (well, the 5 people who read my blog regularly, which includes my parents) know that I love the Hawaiian language. Something delightful happened when I moved beyond reading the Hawaiian language textbook and started making Hawaiian part of my daily life. I read the Bible in Hawaiian instead of English. I read Hawaiian-language books (like the story of Kamapua'a, the Hawaiian pig-god, and the Kumulipo -  the Hawaiian creation chant). I sing along to Hawaiian songs. I found that once I moved beyond "conversational exercises" and immersed more of my life in the language, I started thinking in Hawaiian. (For example, I can form a sentence without stopping to think, "does that noun take an a-form possessive or an o-form possessive?"**) Immersion in a subject or language works because it changes the way you think. Single classes do not work - at least, they don't work if you want to develop fluency or change your mindset.


I am hopeful that working together, vendors and universities can help create a revolution from within, for the benefit of all.


If change is slow to happen, or there is resistance to change, vendors can also help create an impetus behind this effort by going to legislators - such as those who serve on the House of Representatives Science and Technology Committee - and ask them to consider tying research money (for example, funds dispensed through the National Science Foundation (NSF)) to computer science curricula reform. Perhaps universities' CS departments would have the time and motivation to fix their curricula if they weren't (and I am not making this up) wasting time and grant money on how to wave a cell phone in front of a professor's door to get access to the room.  If all else fails, "money talks." The power of the purse can effect positive change (ask any kid whose allowance is withheld until he learns to clean up his messy room).


Since I am on a history kick anyway, I should point out that the US Federal Government has had a significant role in the development of the software industry. The government, especially the Defense Department, successfully used the "power of the purse" to rapidly develop the computer industry in its early stages, and can continue to use its positive influence to change the way universities develop curricula. So anybody who thinks that the entity handing out money (the government) shouldn't help use that lever to help make us more secure (by insisting that universities they fund fix a root cause of IT insecurity) needs a history refresher.


Universities are not evil but they are generally not responsive to market forces, due to a) an endless source of research money often not tied to anything approaching pragmatic results and b) tenured faculty that do not have to change because there is no impetus to change nor penalties if they don't change. We as vendors should help them change through both the "carrot" of donating our time, expertise and support for changing the curricula, so that relevant degree programs have the "secure development lifecycle" in producing graduates that we as vendors are expected to have as suppliers, and the "stick" of using accreditation and funding (or funding cutoff) to help force needed change. When Great Britain refused to accede to the principle of "taxation without representation is tyranny," the colonies seceded. We did not get our independence from Great Britain by asking more nicely for it.


Our world is more technologically based than ever before. All customers rely on IT as infrastructure, and are being driven by regulation to insist on a "secure software supply chain." Producing secure software does indeed require a secure supply chain, not limited to but including university graduates whose computer-related degree programs have security principles and practices embedded within every element of their degree programs. Perhaps what I have said above is harsh, but I offer it as Tough Love. We simply - and collectively -  must evolve to defensive mindsets delivering defensible code lest none of us survive in a hostile world.


"We must all hang together, or most assuredly we will all hang separately."



Disclaimer: Large portions of the above blog were originally written for an Oracle Magazine column I do regularly, "All Secure." The elegant journalistic term for "self-plagiarism" is "repurposing," and anyway, it's not plagiarism if you steal from yourself.


* I'd be remiss in not mentioning a few (among many) bright spots working on the supply chain problem at the university end: Gene Spafford at Purdue (always on anyone's bright spot list and has been for years), Samuel Redwine at James Madison University (who has labored long and mightily on a software security body of knowledge), and Neil Daswani at Stanford (who has published a book Foundations of Security: What Every Programmer Needs To Know available at http://tinyurl.com/33xs6g and who graciously sought me out to give me a copy). I am barely giving these fine gentleman credit for a lot of hard work to improve university curricula in this area, and I know there are others who are also similarly engaged whom I have not credited. Thank you, all.


** If you really want to know, o-form possessives are used for things that are inalienable or are your birthright. Emotions, for example (like aloha - love), means of conveyance (like papa he'e nalu - surfboard), parents, gods, are all inalienable and thus take an o-form possessive: He makuahine maika'i ko'u. (I have a good mother.) Things that are alienable or that you acquire (spouse, children) take a-form possessives: He ipo 'olu'olu ka'u. (I have a nice sweetheart.) It was a big day in my life when I could start rattling off sentences without thinking about what kind of possessive to use.


For More Information:


Book of the Week:


Aircraft Carriers at War: A Personal Retrospective of Korea, Vietnam, and the Soviet Confrontation  By Admiral James L. Holloway III, USN (Ret.)




ADM Holloway (disclaimer: a family friend, so I am justifiably prejudiced in his favor) has had an amazing career: an officer during WWII (present at the Battle of Surigao Straight outlined in Last Stand of the Tin Can Sailors) he then qualified as a naval aviator, serving throughout the Korean and Vietnam Wars.  He also served as Chief of Naval Operations. He is fine leader, a fine person and a long time contributor to naval history and thought. There has been so little written about the Cold War from a military perspective that this book is doubly welcome: written by a great leader and warrior who was there. (Hey, all the reviews are glowing - I am just gilding the lily.)


Another true hero has died: Jacob DeShazer, who was one of the Doolittle Raiders who "struck back" at Japan after Pearl Harbor by bombing Tokyo on April 18, 1942. (Japan subsequently decided to "finish" the Pacific fleet at Midway, where they lost the war.)  DeShazer endured unbelievable hardships - torture and deprivation - as a POW of the Japanese but forgave his captors after becoming a Christian, and returned to Japan to serve as a missionary for 30-odd years. Rest in peace, faithful warrior.




The Defense Science Board Task Force Report on Mission Impact of Foreign Influence on DoD Software:




Web site for the House Science and Technology Committee (express yourself!):




The educational board of ACM (complain to them!) can be found at:




More on the Hawaiian language (including a-form and o-form possessives):




The SQL injection tutorial I mentioned (anyone can take it):




Last - but far from least - the SANS organization web site:



Face front, true believer!

Carl Backstrom - Sun, 2008-04-06 13:29
Well this is interesting Marvel and Oracle.

Gotta love the link direct link to APEX.
Makes the job kinda surreal.

extra points if you know the quote!

Themes and Theme Testing

Carl Backstrom - Fri, 2008-04-04 19:15
I've created an application here that lists out all the themes contained in APEX along with a thumbnail. Each image links to a copy of our theme testing application running that particular theme, the theme testing application is just the regular sample application with extra pages to cover different template and item types.

When we build out themes for APEX we build very generically and the theme testing application is what we use to test against. Feel free to download the theme testing application and use to test your own themes or theme variations against.

Vikas used to host applications showcasing the themes but I figured we (APEX team) should take the time update them with every version change and Theme Testing Application change. Like I've stated before I have almost no moral issues with 'borrowing' a good idea , but I did contact Vikas and ask first ;).

If you think we are missing a use case in that application please drop a line in the comments, or better yet comp one out on apex.oracle.com and put a link in the comments. Remember we build very generically so the themes can handle as many data and usage variations as possible.

Video from Euruko 2008

Raimonds Simanovskis - Fri, 2008-04-04 16:00

I made short video from Euruko 2008 conference where you can see Matz, Koichi, JRuby guys, DrNic ar me as well :)

I posted my presentation slides in my previous post.

Categories: Development

LinkedIn Oracle Contractors Group

Richard Byrom - Thu, 2008-04-03 17:46

Join Contractors on the Oracle LinkedIn and Community Groups I recently created an Oracle Contractors Group on LinkedIn. The purpose of this group is to create a network for contractors to talk to each other as well as discuss and refer work opportunities. If you’re interested in joining sign up to the LinkedIn group and I’ll approve your membership. 


Subscribe to Oracle FAQ aggregator