Feed aggregator

Oracle OpenWorld 2008

Pawel Barut - Thu, 2008-07-17 15:44
Written by Paweł Barut
It's only 2 month left to Oracle OpenWorld 2008. Few news about this conference and my first time ever participation. Last week I've registered for OOW2008 as Blogger. Yesterday I've received confirmation email - I was accepted. I'm really looking forward to be in San Francisco and meet with all Oracle geeks, enthusiast and other bloggers.
I still need to apply for US Visa, and organize my trip. And of course choose sessions in which I would like to participate.

There is also one good news for people in Eastern Europe, Post Soviet Republics, Middle East & Asia. People from those countries can get discount on registration fee.
Attendees will need to enter EMKT as their priority code during "Step 2" of the registration process.

The countries from the EE, CIS & MEA regions which qualify for the discount are:

African Operations (except South Africa), Albania, Armenia, Azerbaijan, Belarus, Bosnia & Herzegovina, Bulgaria, Croatia, Czech Republic, Cyprus, Estonia, FYR Macedonia, Georgia, Hungary, Iraq, Iran, Jordan, Kazakhstan, Kyrgyzstan, Latvia, Lebanon, Lithuania, Malta, Moldova, Montenegro, Oman, Palestine, Poland, Romania, Russia, Serbia, Slovakia, Slovenia, Syria, Tajikistan, Turkey, Turkmenistan, Ukraine, Uzbekistan, and Yemen.

Do not wait. Register for Oracle OpenWorld 2008 !


Related Articles on Paweł Barut blog:

Categories: Development

Dynamic Date Filtering in Interactive Reports

Anthony Rayner - Wed, 2008-07-16 06:26
Update: Please note, in Application Express 4.0, it is now possible to define a 'Row Filter', where you can reference SYSDATE directly from the filter. For further information, please see the 'Adding a Row Filter' section of the user guide.

Whilst the filtering part of interactive reports currently supports a fair number of options, there isn't currently the facility to specify a dynamic date filter using SYSDATE. A common use case for this would be a dashboard report where you want to display all the overdue items in a dataset. In the interactive report filter definition, it would look something like this...

But unfortunately, we do not currently support the use of SYSDATE in the Expression field. So what can you do? I will discuss 3 possible workarounds for achieving this.

Note: Method 1 does not require any changes from a developer perspective, methods 2 and 3 require some developer work.

Method 1
Use the 'Is in the last' operator, and then specify some arbitrarily large amount (100 years).

This could then be saved by the user as a 'Named Report' for future use. Although this is the quickest and easiest method, it isn't very intuitive for the user to come up with and also it's not 100% water tight to use this filter, as it might be possible that a date is entered outside of the arbitrary range specified.

Method 2

It's possible to specify filters on interactive reports via the URL. This uses the 'IR%' prefix in the 'Item Names' argument in the URL syntax, as detailed in the documentation. This method involves dynamically provisioning a link to the interactive report page and could be implemented by creating a SQL report to look like a menu, like this:

1) Add a region of type 'SQL Report' and for the 'Region Source', specify:
select sysdate from dual
2) Ensure the following is set during the wizard creation:
- Report Template: template: [theme number].Borderless
3) Go the 'Report Attributes' page and specify the following:
- Pagination Scheme: No pagination.
- Untick the 'Show' checkbox for the SYSDATE column.
- Click 'None' for 'Headings Type'.
3) Add a column link to the report by going the 'Report Attributes' page and clicking the 'Add Column Link' link from the 'Tasks' menu on the right. For this column link, specify the following values:
- Link Text: 'Overdue Reviews'
- Page: 2
- Clear Cache: '2,CIR'
- Item 1 Value: #SYSDATE#
Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.

This generates a link like this:

Of particular interest here are...
  • Clear Cache = '2,CIR'
    This clears the cache for page 2, and also uses the new 'CIR' parameter, which clears all settings that could already have been applied to the interactive report (including any default report settings).

  • Item Names = 'IRLT_REVIEW_DATE'
    This can be broken down as follows:
    • 'IR': This is used to set interactive report filters dynamically over the URL.

    • 'LT': The characters directly after the 'IR' prefix are equivalent to the 'Operator' value in the interactive report 'Filter' dialog, so in this case 'LT' specifies a 'Less than' operator. Note: You could also use 'LTE' which would specify a 'Less than or equal to' operator. See here for more options.

    • 'REVIEW_DATE': Then after the underscore, this is the column name on which to apply the filter, equivalent to the 'Column' value in the interactive report 'Filter' dialog. In this case we are filtering on the 'REVIEW_DATE' column.

This method works well, but does require some sort of separate menu or link to go to the interactive report, which steps out of the normal use-case for interactive reports.

Method 3
The final method requires the developer to code a derived column to calculate if the record is overdue in the report SQL. A snippet something like:
when review_date < sysdate then 'Yes'
end) review_overdue
Once this has been done by the developer, the user could then choose to apply a filter on this column as shown in the screenshot:

This could then be saved by the user as a 'Named Report' for future use.

Note: With methods 1 and 3, the developer could also default these reports for users, saving the report as 'Default Report Settings' in the 'Save Report' dialog. This means that when any user logs into and sees this report, they will default to these filters. One consideration here though, when the developer saves the 'Default Report Settings', you cannot specify a 'Name' for the report, so the user would see the filter as it is coded without a named tab.

So method 3 would be my preferred choice, because it works and I believe provides the most user friendly solution for the users whilst keeping within the standard use-case for interactive reports. One consideration of this approach though would be that no index would be used on that column (see comments).

A couple of questions for the community...
  • Would you find it useful to be able to filter by SYSDATE?

  • Would you find it useful to be able to filter not just by a value, but by another column value?

  • What else would you like to see incorporated into Interactive Reports to make them even better?

Further Reading...
General overview and information on interactive reports
Marcie Young's Advanced Interactive Report Tutorial
APEX documentation page on dynamic interactive report filtering via the URL
David Peake's (APEX PM) blog post on 'Advanced Interactive Reports

Categories: Development

System 9 Gadgets

Oracle EPM Smart Space - Tue, 2008-07-15 16:55

A while ago I created some System 9 gadgets for the Windows Vista sidebar.   The project went well but when Hyperion was bought by Oracle it kind of got lost and forgotten.  Well I found the source while going through some old files and I decided to dust them off.  I removed the old branding and made sure they worked with the newest version of System 9 (11.1). 



There are 2 gadgets, one that lets you browse the System 9 repository and another that stores favorite reports that you set in the first.  The gadgets are great for the user that opens the same key reports on some sort of periodic basis.

I think these are pretty useful and some users may want them.   Next steps are to figure out how to post them and to see if I can quickly convert them to Google gadgets.  I will update the blog when I get them posted.

Categories: Development

Import APEX application via SQL Developer

Duncan Mein - Mon, 2008-07-14 13:43
I will be honest and admit that I have not been SQL Developers biggest fan since it's release a few years ago. Having always used 3rd party products such as TOAD and PL/SQL Developer, I found certain things a little irritating and not overwhelmingly obvious when forced to use SQL Developer.

Recently however, I found an absolute god send of a feature in SQL Developer and that was the ability to import / export APEX applications. This means that the Web GUI for such actions is no longer required.

Very simply, you connect to your parsing schema's database account via SQL Developer, right click on the Application Express tree directory and select Import Application. This opens a simple wizard and off you go.

One other cool feature of this is that you can open the details window and see exactly what your import is doing, something that is not possible when using the web GUI.

Download SQL Developer from: OTN

I Guess I was Wrong

Oracle EPM Smart Space - Fri, 2008-07-11 09:26

About a month or so back I did a post on Silverlight and that post lead to a comment on iPhone support.  At the time I completely punted on my response saying that the mobile market will have to narrow before you will see any sort of BI applications for the phone.  Well I am officially eating crow and admitting it.  Here is a press release from yesterday…

Oracle Announces Oracle® Business Applications for iPhone Available on Apple App Store

Ok, Ok I was wrong, and I admit it but the real news here is that this helps make my case about User Experience being a key driving point for future BI applications.  If users get this great experience on their phones they will also expect it on their desktop or in their browser.

Categories: Development

Something is VERY busy

Claudia Zeiler - Fri, 2008-07-11 01:20
I have been suspecting that something is causing excess connects/disconnects from our database. To see the number of connects I went in search of the listener.log. By default it is in $ORACLE_HOME/network/log. There was a listener.ora there , but according to it, there hasn't been ANY connections to our database in months!

This is a solaris box. I read that there is a 2G file limit on Solaris. (Maybe that means a limit on log files. Obviously, my data files are much larger than that.).

I issued the lsnrctl command:

lsnrctl >
set log_file listener2.log

Quickly it resumed recording connections, now into listener2.log. It recorded 2 of them EVERY second!!! OK. my suspicion was right. Now to track down exactly WHAT is connecting and disconnecting so much.

New job...

Anthony Rayner - Thu, 2008-07-10 07:59
Just a quick note to say I've recently made a commitment to Application Express and joined the APEX development team here at Oracle. I am very excited to work for a cracking team on a great product and look forward to blogging much more about it in the future.

Categories: Development

Custom ActiveRecord create, add and delete methods for legacy databases

Raimonds Simanovskis - Wed, 2008-07-09 16:00

In some Ruby on Rails projects I am putting ActiveRecord interface on top of existing legacy databases. It is quite easy to specify legacy table names and primary keys in ActiveRecord models so that ActiveRecord would generate correct SQL SELECT statements to read data from legacy databases.

But it is more difficult to insert, update and delete in legacy databases using ActiveRecord. When using Oracle legacy databases then quite often they have exposed PL/SQL APIs for writing to tables and you are typically not allowed to directly modify tables with INSERT, UPDATE and DELETE statements.

Therefore I created support for custom create, update and delete methods in the version 1.1.3 of ActiveRecord Oracle enhanced adapter which uses also my ruby-plsql gem.

Let’s look at the following example. Assume that we have the following table:

CREATE TABLE test_employees (
  employee_id   NUMBER(6,0),
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25),
  hire_date     DATE,
  salary        NUMBER(8,2),
  version       NUMBER(15,0),
  create_time   DATE,
  update_time   DATE

And we have the following PL/SQL API package that should be used to write to this table:

  PROCEDURE create_employee(
      p_first_name    VARCHAR2,
      p_last_name     VARCHAR2,
      p_hire_date     DATE,
      p_salary        NUMBER,
      p_employee_id   OUT NUMBER);
  PROCEDURE update_employee(
      p_employee_id   NUMBER,
      p_first_name    VARCHAR2,
      p_last_name     VARCHAR2,
      p_hire_date     DATE,
      p_salary        NUMBER);
  PROCEDURE delete_employee(
      p_employee_id   NUMBER);

Then we define ActiveRecord model in the following way:

class TestEmployee < ActiveRecord::Base
  set_primary_key :employee_id
  # should return ID of new record
  set_create_method do
      :p_first_name => first_name,
      :p_last_name => last_name,
      :p_hire_date => hire_date,
      :p_salary => salary,
      :p_employee_id => nil
  # return value is ignored
  set_update_method do
      :p_employee_id => id,
      :p_first_name => first_name,
      :p_last_name => last_name,
      :p_hire_date => hire_date,
      :p_salary => salary
  # return value is ignored
  set_delete_method do
      :p_employee_id => id

And as a result we can use this model in the same way as other ActiveRecord models:

@employee = TestEmployee.create(
  :first_name => "First",
  :last_name => "Last",
  :hire_date => Date.today
@employee.first_name = "Second"

And all writing to the database will be done using defined API procedures.

Currently this functionality is embedded into Oracle enhanced adapter but if somebody needs it also for other databases this functionality could easily be extraced from the adapter.

To get the new release of Oracle enhanced adapter just do:

sudo gem install activerecord-oracle_enhanced-adapter

Source code of Oracle enhanced adapter is located at GitHub and you can submit bug reports and enhancement requests in Lighthouse.

Categories: Development

New Features in Approving Expense Reports in Oracle Internet Expense (iExpense) using AME

Krishanu Bose - Wed, 2008-07-09 07:17
Following features are new in R12 Oracle Internet Expense if you are using AME for Manager Expense report approval.
1. Parallel Approval
With Parallel Approvals, you will be able to route approvals in parallel when expense reports are charged to multiple cost centers, multiple projects, or multiple awards. This will streamline the approvals process and thus ensure users are reimbursed as quickly as possible.
2. FYI Notification
Now, you will be able to define rules in AME to send FYI notifications to managers and others who should be informed of expenses charged to their area of authority, but who do not need to approve the expense report.
3. Aggregation of Amount
You will be able to route expense approvals based on the aggregated amounts charged to cost centers or projects. The approval notification will show both the total amount to be approved, and how much of each expense was charged to the area of approval authority. This feature will ensure that proper approval authority is enforced when an expense report is allocated to many different cost centers, projects, or awards.

Performing a JOIN on groups of values using nested tables

Mike Moore - Tue, 2008-07-08 12:35
-- Join on a nested table example:

-- this example takes 2 tables (c & d). On each table it does
-- a group by, and the details of that group are collected into a nested table.
-- tables c & d are then joined on that nested table

-- I probably would never do this, but I do think it is interesting.


CREATE TABLE d (animal VARCHAR2(5 BYTE), grp NUMBER(6));


INSERT INTO d (animal, grp) VALUES ('cat', 4);
INSERT INTO d (animal, grp) VALUES ('cat', 7);
INSERT INTO d (animal, grp) VALUES ('toad', 7);
INSERT INTO d (animal, grp) VALUES ('frog', 4);
INSERT INTO d (animal, grp) VALUES ('frog', 7);


CREATE TABLE c( animal VARCHAR2(5 BYTE), grp NUMBER(6));

INSERT INTO c (animal, grp) VALUES ('cat', 1);
INSERT INTO c (animal, grp) VALUES ('frog', 1);
INSERT INTO c (animal, grp) VALUES ('frog', 66);
INSERT INTO c (animal, grp) VALUES ('cat', 33);
INSERT INTO c (animal, grp) VALUES ('cat', 44);
INSERT INTO c (animal, grp) VALUES ('cat', 66);
INSERT INTO c (animal, grp) VALUES ('frog', 44);
INSERT INTO c (animal, grp) VALUES ('toad', 44);

SELECT grp, animal FROM c ORDER BY grp, animal;
SELECT grp, animal FROM d ORDER BY grp, animal;


mystr VARCHAR2(80);
FOR cur1 IN
FROM (SELECT grp grp1, CAST(COLLECT(animal) AS string20_table) tab1
(SELECT grp grp2, CAST(COLLECT(animal) AS string20_table) tab2
ON tab1 = tab2
DBMS_OUTPUT.put_line( TO_CHAR(cur1.grp1)
|| '='
|| TO_CHAR(cur1.grp2));
-- final output
-- 1=4
-- 44=7
-- 66=4

How to find, share and back up JDeveloper code templates

Brenden Anstey - Mon, 2008-07-07 20:52

JDeveloper code templates are stored in the JDevHome folder in an XML file called java.tpl. This is one file worth backing up if you have a lot of custom code templates.

The file is in [Drive:]\JDevHome[Version]\system\oracle.jdeveloper.[version] eg.


Vote for my session proposal at Oracle OpenWorld

Raimonds Simanovskis - Mon, 2008-07-07 16:00

oow.pngIf you still have not voted for my session proposal “Using Ruby on Rails with Oracle E-Business Suite” then please do so :) Voting deadline is near – July 13th.

In any case I am going to be at Oracle OpenWorld conference in San Francisco in September. So if some of my blog readers will also be there and would like to have some chat with me about Oracle and Ruby then please let me know.

Categories: Development

Siebel Web Service Invocation Error

Peeyush Tugnawat - Mon, 2008-07-07 10:05

Issue: Error code: SBL-EAI-04313 while invoking a Sibel Web Service from BPEL.

Error Details:

“There is no active Web Service with operation named 'http://siebel.com/asi/:SiebelAccountInsert'.(SBL-EAI-04313”

   <siebelf:siebdetail xmlns:siebelf="http://www.siebel.com/ws/fault" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
      <siebelf:logfilename xmlns:siebelf="http://www.siebel.com/ws/fault" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">EAIObjMgr_enu_0024_25165939.log</siebelf:logfilename>
      <siebelf:errorstack xmlns:siebelf="http://www.siebel.com/ws/fault" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
         <siebelf:error xmlns:siebelf="http://www.siebel.com/ws/fault" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
            <siebelf:errorcode xmlns:siebelf="http://www.siebel.com/ws/fault" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">SBL-EAI-04313</siebelf:errorcode>
            <siebelf:errorsymbol xmlns:siebelf="http://www.siebel.com/ws/fault" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">IDS_EAI_WS_OP_NOT_FOUND</siebelf:errorsymbol>
            <siebelf:errormsg xmlns:siebelf="http://www.siebel.com/ws/fault" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">There is no active Web Service with operation named 'http://siebel.com/asi/:SiebelAccountInsert'.(SBL-EAI-04313)</siebelf:errormsg>


I was able to resolve this issue by doing the the following:

1. Login to Siebel

2. Browse to Administration - Web Services

3. Within Siebel WS Administration, make sure that the status of WS is ‘Active’


4. Make sure that the Binding is set to SOAP_DOC_LITERAL


5. and finally Clear the Cache by clicking the “Clear Cache” button


Introduction to EclipseLink

Debu Panda - Mon, 2008-07-07 04:24
Doug Clarke has written a nice article that introduces EclipseLink.

I'm beginning to distrust Oracle

Claudia Zeiler - Mon, 2008-07-07 01:35
I am learning that I have been trusting Oracle too much. I have figured that Oracle has top rate developers, so when they offer a facility like ASMM or GATHER_STATS_JOB. I assume that it will be done well.

Now I am learning that ASMM has been allocating our shared pool too large, and has been adjusting sizes not once an hour, but once a minute.

I am not happy with the statistics that the 'wonderful' automatic GATHER_STATS_JOB has been collecting. Also the job has been causing contention with some of our production jobs.

The one that seems really strange to me is Log_buffer. I allocated 28M to log_buffer. According to Oracle, LGWR should be writing, at a maximum, when log_buffer is 1M full. Instead, I see that it is writing 2M at a time. I am going to have to figure that one out.

I expect that when Oracle offers to automate these processes, that this is their own product, so the calculations will be well done. I expected, but I am wrong.

OTL - Authorized Delegate

RameshKumar Shanmugam - Sun, 2008-07-06 00:55

Authorized Delegate: This functionality is similar to the timekeeper functionality but has few difference

  • Timekeeper uses the PUI where as Authorized Delegate uses Self Service
  • Timekeeper can access the people who are assigned to the Timekeeper group where authorized Delegate is controlled by the HR:Security Profile
  • Timekeeper has seeded responsibility where the authorized delegate the user must build the custom responsibility using the Seeded Menu 'Authorized Delegate Timecard Entry’

This functionality is good to use when a single person need to enter time for fewer number of employees, if there are more number of employees it is always advisable to use timekeeper

Try it out

Categories: APPS Blogs

Silverlight and EPM

Oracle EPM Smart Space - Tue, 2008-07-01 13:42

So we went on a mission to see if we could build our first Rich Internet Application for BI and EPM and here is what we did and what we found out.

We used Silverlight for our first try.  We selected Silverlight over other RIA technologies because; one we knew C# and two there is a pretty good beta available for Silverlight.  We are considering other technologies but wanted to start with what we could use and get running the fastest.

We started with lots of research and came up with two projects.  The first project consisted of building what we called a Provider DLL (thanks Mike) that talked HTTP and XML to the existing Smart View back-end.  This gave us access to the Planning and FM repositories (Forms, Business Rules and Calculations) and access to Essbase (Data and Calculations).  The second project was the UI, this was done with a mix of Expression Blend and Visual Studio.  This turned out pretty awesome and was very easy but we did find that there is not a whole lot out there as far as controls go for BI and EPM.  We had to build all of our own controls.  Here are some screen shots.




The really cool part about the application is the async nature of RIA’s.  Basically everything is async and this really makes for a rich and crisp UI experience…  No browser lockups, fast operations, run multiple operations at once, etc.  Overall RIA’s seem to have a place in more than just consumer applications and we will continue to explore the possibilities of applying this technology to BI and EPM.  Stay tuned for more on this topic.

Categories: Development

Another Site Specific Browser

Oracle EPM Smart Space - Tue, 2008-07-01 09:36

I got a comment pointing me in the direction of another site specific browser Bubbles.  This is a Windows SSB that has many of the same features as the ones I spoke about in my earlier post on this topic.  This one also has some cool extension capabilities that enhance the SSB experience.  For example you can get an extension for Yahoo mail and you will get System tray notifications when you get new mail.  I applied this to Workspace and every thing worked great, here is a screen shot:


The extensions could be powerful in the context of BI and EPM.  The first thing that comes to mind is notifications like the ones in Smart Space or some how tie into the notifications in Workspace.  For now I just like having Workspace always running in my system tray.

Categories: Development

Why do we not report by the Gregorian calendar?

Dylan Wan - Tue, 2008-07-01 02:37

  1. The number of days is different in each calendar month.
  2. The week and month cannot be aligned. The number of weekends is different in each calendar month.
  3. The number of working days is different in each calendar month. It ends up that the number of days in each quarter is also different.
  4. The period closing day will fall into different days in each period. The accounting department prefers always close the period by a given day in a week, such as Wednesday or Friday.

Categories: BI & Warehousing

13 Period Calednar

Dylan Wan - Tue, 2008-07-01 02:14

In this posting, I will discuss the following topics:

* What is the 13 period calendar? * Who uses the 13 period calendar? * How is it different from the 4-4-5 calendar?

Read the rest of this entry »

Categories: BI & Warehousing


Subscribe to Oracle FAQ aggregator