Feed aggregator

Pipelined functions in oracle 9i

Adrian Billington - Tue, 2007-05-22 03:00
Functions can now stream data as virtual tables. May 2002 (updated May 2007)

What is Timeout?

Fairlie Rego - Fri, 2007-05-18 22:29
The problem with semi documented APIs like DBMS_SPACE is that some of the subprograms might not work as you might expect them to.
For example the object_space_usage procedure has a parameter called timeout_value which I would assume to mean the amount of time after the procedure will abort if it cannot complete its space calculations. But this does not work as I would expect it to

SQL> set timing on
SQL> set serveroutput on

SQL> declare
v_space_used number;
v_space_allocated number;
dbms_space.object_space_usage('SCOTT','EMP','TABLE',0,v_space_used, v_space_allocated,'',TRUE,5);
dbms_output.put_line('SPACE USED = '||v_space_used);
dbms_output.put_line('SPACE ALLOCATED = '||v_space_allocated);

SPACE ALLOCATED = 68034756608

PL/SQL procedure successfully completed.

Elapsed: 00:00:36.12

So even if I specify a timeout value of 5 the procedure completes in 36 seconds.

This behaviour reproduces on and

Updated Oracle SQL Developer Migration Workbench Early Adopter Release

Donal Daly - Thu, 2007-05-17 05:18
We have updated the early adopter release of Oracle SQL Developer Migration Workbench today on OTN. You can get it from here. This is our final preview release, before we go production. We are now functionally complete for this initial production release and are now focused on fixing our final "show stopper" bugs. We have had good feedback from our user community via our feedback application and also via the Workbench forum. They have uncovered a number of bugs, most of which we have now addressed (Oracle9i as a repository and Access data migration issues for example) and the remaining ones we will resolve prior to production. I encourage everybody to update to this latest release and continue to provide us with feedback.

I have been working extensively with different builds of the Migration Workbench these past couple of weeks as we closed in on our goal to refresh the early adopter version. In my "biased" opinion is it looking much stronger and I would like to outline some of the new features in this updated early adopter release.

Quick Migrate
In the orginal Migration Workbench we had a wizard driven approach to simplify migrations and I felt it was important to bring this functionality back. With our Quick Migrate wizard, I believe we have improved from the original wizard, since we will leverage our least priviliege migration capabilities, assume sensible defaults and create/remove our migration repository.

So if you have a schema on SQL Server or a single Access mdb file to migrate to an existing Oracle schema, this should be the easiest and quickest migration option for you. Another nice feature, if you are doing an access migration, is that we have added command line support to our exporter so, we will automatically launch the correct Access exporter for the Access connection that you specify.

Offline Capture
This was a popular feature with our consultants and partner technical services folks, with the original Workbench, as it allowed them to work remote from the customer/partner. We have now added back in that feature.

Migration Reports
We have added in some initial migration reports available under Reports->Shared Reports. This will be an area we will add to into the future, as we can mine our rich metadata repository to provide you with useful information. If you have suggestions for additional reports let me know. I will also publish more details about our repository, so you can develop your own migration reports as well. Maybe we should have a competition for the best contributed report? I think we have a couple of 1GB USB keys left over from our Database Developer Day in Dublin I could use as prizes.

Translation Scratch Editor
We have reworked this feature extensively. I originally wanted to add a feature that would enable you to validate our translated SQL. As we worked through different iterations of how best to implement this feature, we came up with the idea about leveraging our existing Worksheet capabilities, which I think is very cool and I am very pleased with how this turned out.

We have also done a lot of work to improve incremental capture and improve our filtering capabilities from our early adopter release. We have integrated our MySQL parser from the original Migration Workbench and will extend its capabilities in subsequent releases to be as functional as our new TSQL parser and also support SQL statement level translation. (workaround for now, within the scratch editor, is just wrap the SQL statement in a procedure). We have also implemented the ability to update your Access mdb file, to create link tables to point to your newly migrated schema. This was also a feature of the original workbench. We hope to add some additional usability tweaks to create an ODBC OSN on the fly and provide a select list of known Oracle DSN. Hopefully that will make it in before production as well.

We have made fixes to ensure correct generation order for pl/sql procedures to resolve dependencies, so more pl/sql procedures should compile correctly first time. We made improvement to handle inline DDL statements correctly. Temporary tables, normal tables and other DDL are lifted out of the body of the procedure/function and are created separately.

Now for the final bug fix push by the development teams in Dublin and Bangalore. Our QA team, have been doing a good job verifying our fixes and closing off our bugs. The teams have been working hard on this for many months now and I believe we are in touching distance of reaching our goal. It will be very exciting for me personally to see this second generation migration tool reach production. We'll all need some time off when this is done to recharge...

Classpath conflicts

Janusz Marchewa - Thu, 2007-05-17 01:47
One problem kept bugging me for three weeks. It was quite irrational - I couldn't extend the functionality of the application, because changes in services (that were EJB3 session beans) were not visible. The problem occurred when deploying to embedded OC4J, but, surprisingly, not when deploying to OC4J standalone...

After getting lots of JBO-25221 (method not supported) and java.lang.NoSuchMethodError I thought I would investigate the class used at runtime. I found that the method that should be there wasn't there at runtime. So I decided to find out where did the class come from. Bertrand Delacretaz has an example of checking when was a Java class compiled. Small modifications to his example and the mysterious class is unmasked:

MyClass.class.getResource( "MyClass.class" ).openConnection().getURL().toString()

I was shocked when I saw the output - it turned out that another project that should have a small 2-class jar in my application had packed also a bunch of classes from the same model. The model was referenced by both projects as a dependency and the deployment profile had a magic checkbox selected:

That resulted in a silent classpath conflict I was not aware of... Finally, the problem is gone :)
Categories: Development

... is not a registered tag in that namespace?

Janusz Marchewa - Tue, 2007-05-15 13:49
Today JDeveloper broke my project. I don't know what exactly happened, but putting an ADF Faces tag inside a page resulted in JSP compilation errors, e.g.:

Error: http://xmlns.oracle.com/adf/faces:panelPage is not a registered tag in that namespace.

After a few minutes of trying different things I opened the web.xml file and found the problem:


Now that was confusing... Removing that piece of code helped. But how did it appear here? JDev only knows...

One OT to mention here: the code you see above was escaped (to be displayed properly by Blogger) using a very simple and effective tool I've come across recently. Check out Dietmar Aust's blog.
Categories: Development

BMW Oracle racing scores against Luna Rossa (Prada)

Clemens Utschig - Tue, 2007-05-15 12:57
It's amazing, it's love, and it's passion - sailing, the ocean, the winds, the gybes, the will to win.

All this is America's Cup, the race for the old bowl.

JFK said this a while back ..
"We are tied to the ocean. And when we go back to the sea, whether it is to sail or to watch - we are going back from whence we came." --USA President John F. Kennedy, at a dinner for the crews in Newport, RI on the eve of the 1962 America's Cup Match.

today BMW Oracle got back into the race, by amazing tactics, and a sheer wonder of teamwork - 1:1 - let's wait for tmrw

APEX reports and SQL Developer

Jornica - Sun, 2007-05-13 08:08

Both Kris Rice and Dimitri Gieles blogged about new APEX reports in SQL Developer 1.1.3 . Minor detail: those reports are build for Application Express version 3.0.1 , which is not released yet.

I installed the new version of SQL Developer, and tried to run the APEX reports (location in the reports tree: All reports, Data dictionary Reports, Application Express). Surprisingly, I did not get the message Application Express 3.0.1 or higher required. In a few cases I received an error: ORA-00904 invalid identifier.

To find the SQL behind the reports a simple copy and paste of the report did not work i.e select the Application report, right click and select copy. Select the User Defined Reports, right click and paste from, nothing happened. Even export of the report(s) did not work.

After some searching I found the report SQL hidden in a file: oracle.sqldeveloper.report.jar (location sqldeveloper\extentions directory). This .jar file includes an apex30.xml, this file contains all the APEX reports in XML format (you can unzip the XML file with an unzip utility). The next step is to import the apex30.xml and then you will see all the reports in the User Defined Reports branche as well.

Changing the SQL is left as an exercise to the reader... ;-)

I just don’t get Ruby on Rails

John Stegeman - Fri, 2007-05-11 13:56

In addition to all of the cool toys (robots and helicopters) shown at the Java Toy Show (general session of JavaOne 2007 on Friday morning), a NetBeans guy from Sun got up and did a demo of Netbeans 6.0 and JRuby on Rails (a scripting-languaged based web application framework). He created a Ruby on Rails application, created persistence classes from a database using a wizard, created a simple web page showing the database information, and ran it all from within the IDE. Judging by the audience reaction, people were impressed. My reaction, on the other hand, was, as we used to say as kids, “big whoop.” Haven’t we been doing this in JDeveloper for, I dunno, 8 years or something?

Extending ADF's FacesPageLifecycle

Janusz Marchewa - Fri, 2007-05-11 10:14
Recently I've encountered strange exceptions while testing my application. I pressed the Submit button and sometimes everything was fine, while at times the action bound to the button was not invoked, but errors were reported:

JBO-29000: null

Unfortunately, there was no trace of this exception and I didn't even know where should I look for it. Then I came up with and idea: let's decorate the process of displaying errors and find out more about this exception. To achieve this, I had to extend ADF's FacesPageLifecycle:

package januszm.adf.util;

import javax.faces.context.FacesContext;

import oracle.adf.controller.faces.lifecycle.FacesPageLifecycle;

import oracle.binding.AttributeBinding;

public class MyPageLifecycle extends FacesPageLifecycle {
protected void addMessage(FacesContext context, AttributeBinding binding,
Throwable error) {
super.addMessage( context, binding, error );
// the easiest thing we can do here

Another question is: how can we tell ADF to use our lifecycle? That's easy too - we extend ADFPhaseListener:

package januszm.adf.util;

import oracle.adf.controller.faces.lifecycle.ADFPhaseListener;
import oracle.adf.controller.v2.lifecycle.PageLifecycle;

public class MyPhaseListener extends ADFPhaseListener {
protected PageLifecycle createPageLifecycle() {
return new MyPageLifecycle();

Finally, we need to substitute the original ADFPhaseListener with our class in faces-config.xml:


That allowed me to locate the problem - the exception was thrown in the line containing:


because sometimes the field wasn't set...

The conclusion is simple - if a library throws a weird exception, don't blame the library, but check YOUR code first.
Categories: Development

Well, I made a list

Herod T - Thu, 2007-05-10 09:51

I read Eddie Awad's blog all the time. Once again, I was quite surprised when I went scrolling through the listing of the Top Oracle Blogs Ranked by Technorati Authority and, low and behold. I am on the list.

#124 out of 130 but, on the list none the less.

The Easiest way to get Chart of Accounts Segment Descriptions

Jo Davis - Wed, 2007-05-09 21:44
Such as account descriptions or cost centre descriptions as well as the value:

,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1),1,40) segment1_desc
,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2),1,40) segment2_desc
,DECODE(gcc.segment3,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3),1,40)) segment3_desc
,DECODE(gcc.segment4,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4),1,40)) segment4_desc
,DECODE(gcc.segment5,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5),1,40)) segment5_desc
,DECODE(gcc.segment6,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,6,gcc.segment6),1,40)) segment6_desc
,DECODE(gcc.SEGMENT7,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,7,gcc.segment7),1,40)) segment7_desc
,DECODE(gcc.SEGMENT9,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,8,gcc.segment8),1,40)) segment8_desc
,gcc.chart_of_accounts_id chart_of_accounts_id
gl_code_combinations gcc

Otherwise you have to do a hideous number of joins back to FND_FLEX_VALUES_TL and generally hard-code in value set ids :)

Have a fun day

The Falcon Has Returned

Peeyush Tugnawat - Wed, 2007-05-09 17:14

The Falcon Has Returned
This is a very interesting story about the endangered Falcon (Peregrine) that returned to Oracle for nesting.

Read more here:
Falcon Story at UC Santa Cruz

And the best part is the live webcam of Saddie and her four newborn chicks at Oracle HQ
Check it out here!

Insider for Oracle - funky GUI bits

Hampus Linden - Wed, 2007-05-09 15:47
We've been doing a lot of application benchmarking and tuning at work in the last couple of months as we are prepare for a new major release. I've been involved in the Oracle side of things and have spent a lot of time tracking down expensive (or plain weird) SQL-statements and trying to optimize the Oracle databases we use.

After spending about 6 - 7 hours a day running my normal sqlplus scripts over and over trying different things, monitoring benchmark runs I got a bit bored of text output and had a quick google for some sort of basic v$whats_up GUI tool and found this little cool little app called Insider for Oracle, it a great app for doing spot-on monitoring, sort of like Spot light from Quest, but without the hassle. Insider tries to be a more proactive tool with round the clock monitoring, but for that, it's no good.

The thing I find it useful for is when we start a benchmark I can trail what's going on with the system. See new SQL statements, wait events, SGA usage, PGA usage, hot segments etc. Nothing I can't do from sqlplus but it makes my tasks a bit easier.
Price wise it's quite a bargain for what I use it for, the license is for each database on the application "workspace" (i.e. how many databases you want to look at at once). And for what I use it for just looking at one DB at a time, that works out pretty cheap, 599USD or so. Cheap as chips.
I've found a couple of bugs (and submitted bug reports accordingly) and I've even submitted a couple of enhancement suggestions.
Check out their company blog.

Dave Chappell choins Oracle as Chief SOA Technologist

Clemens Utschig - Wed, 2007-05-09 11:28
Wow - what a blast, the so called "Pope of ESB" (according to one of our customers) joined Oracle last week - see here.

Being back from 4 weeks in Europe to give training, help customers, as well as speak at several conferences - I met Dave yesterday first time as oracle employee at java one - rocks!

Also, some weeks ago I have been interviewed by the theenterpriseedgeitarchitect.com on SOA, and what value it can bring into organization, when not viewed as "yet another IT pet project".

You can read the whole interview here

Date columns

Herod T - Tue, 2007-05-08 13:02

I have a good friend from a previous job who is an Oracle DBA, he is kind of shy and doesn't like a big presence on the net. I have blatantly stolen work from him in the past, including some excerpts from this particular document (always with his knowledge). But the poor bugger had the lack of foresight to send me this complete PDF document. So, I quickly decided I had better post it just to show him blogging isn't all evil and invitation to flaming.

That said, Jeremy's quick write up on oracle date columns that he did for his internal support staff. Absolutely nothing earth shattering or ground breaking, but I can't see any errors with it and it is good to give to people who can't speak "DBA". Yes, I have used this in the past elsewhere as well. But this is the complete PDF.

JDeveloper 11g Tech Preview is out!

Janusz Marchewa - Tue, 2007-05-08 02:41
Check out Shay Shmeltzer's blog for details: http://blogs.oracle.com/shay/2007/05/07#a365
Categories: Development

OpenSolaris SXDE upgrade time

Hampus Linden - Mon, 2007-05-07 16:04
It's been a while since I last played with Solaris Express Community Edition, so long that it actually changed names. There are now two versions, one for "random people" and one version for developers. SXCE is not released as often as SXDE but is more tested and stable, SXDE also comes with a lot more goodies, like Sun Studio 11 and Netbeans.
Anyway, the install of SXDE b63 under VMWare workstation 6 RC was as easy as ever.
However, at first boot I almost thought the VM was frozen for a minute or so, after grub it just sits there with a cursor in the corner of the screen for about 2 minutes before kicking off the kernel. All good after that though.
One thing I wanted to test was the new simplified resource capping in Solaris Zones. It was very straightforward and easy to use.
A couple of extra commands in zonecfg and the zone is capped to one core and 512Megs of memory. No more messing around with projects, except if you want really fine grained capping that is.

Took a screenshoot:


Herod T - Mon, 2007-05-07 09:24

APEX is great, APEX is a life saver... OK, job saver?

As I mentioned before I like APEX, but not having the time to develop in it and learn it well, I get a little lost in the product. The APEX forums are a good place to pick up information, like most forums if you use them properly. I read Dimitri Gielis blog on a regular basis, imagine my surprise when he posts about answering one of my questions.

Thanks again Dimitri!

ODP.NET/ODAC for Vista now available

Christian Shay - Sun, 2007-05-06 23:04
Coming right on the heels of Friday's release of Oracle Database for Windows Vista, we now have released ODAC which is certified for Vista. You can download it here. This release is key because the Oracle Database on Vista downloads announced on Friday only include ODP.NET for .NET 1.x. If you need ODP.NET for .NET 2.x on Windows Vista, or any other of the ODAC products, you will need to download this. It includes all of the ODAC products, including ODP.NET and Oracle Developer Tools for Visual Studio .NET packaged up with a new version of the Oracle installer that is Vista friendly. Only the installer has been upgraded, not the individual ODAC products, so if you are using ODAC on another Windows platform, you don't need to upgrade.

The download includes:
Oracle Developer Tools for Visual Studio .NET 2003
Oracle Developer Tools for Visual Studio 2005
Oracle Data Provider for .NET 1.x
Oracle Data Provider for .NET 2.0
Oracle Database Extensions for .NET 2.0
Oracle Database Extensions for .NET 1.x
Oracle Provider for OLE DB
Oracle Objects for OLE
Oracle Services for Microsoft Transaction Server

Business Agility @ SOA

Peeyush Tugnawat - Sun, 2007-05-06 17:41

Bookmark and Share

Business Agility @ Service Oriented ArchitectureWe live in a constantly changing world. Technology and businesses are changing, emerging, and improving at a faster pace than ever
before. Business want the power to deliver products and services better and more
efficiently, to be able to excel, capture new opportunities, and
sustain competition. Technology innovation and improvements are striving to deliver on these requirements. From a business perspective, dynamics of such fast-changing world in terms of
technology can be daunting in terms of time to market cost, maintenance, training, and support. Replacing or re-writing entire systems
every time for new and improved functionalities is definitely a major concern.

Is IT just an unavoidable cost?Arguments about IT being a cost are not rare in business
world. There is no doubt in my mind that innovations in IT systems have
propelled business abilities and enriched business processes in past years.
Definitely, there is high business value gained from IT systems. At the same
time organizations also have to deal with the "cost" components that
come with IT systems such as maintenance, support, enhancements, training etc.

The argument from my perspective, should not be about the
classification of cost components, but how all the components and their
interactions can be simplified to provide more value to the business.

I think simplicity can help!Simplicity is highly desired in increasingly complex world
on IT. Direct incentives of infusing simplicity in IT architecture are
tremendous and do not require elaboration.

Direct derivative of simplicity in IT architecture approach
is Business Agility.

Simply put, "Simple is Powerful"

SOA translates to business agility. SOA is simple but very powerful architecture approach
for complicated systems and problems of IT world.

As I mentioned in my earlier post based on my experience,
SOA as it relates to software paradigm is an agile architecture approach that
is based on service-oriented principles of composition, abstraction, loose
coupling, discoverability, and amalgamation. SOA inherently empowers
scalability, evolution of services, interoperability, reusability, and

Following diagram identifies high level concepts infused into SOA that help enhance business agility for an enterprise.

Inherent InteroperabilitySOA empowers Inherent Interoperability between systems, business processes, and people (organizations).

Re-usability Reusability factor in case of SOA is multi-fold because of standard based interfaces. Reusability is not new in software; what�s new is reusability across diverse systems, platform, and technologies. SOA components can be reused not only by the inherent system or technology but also by any system or process.

Modularity and CompositionSOA encourages Modularity that enhances the concept of Composition. Composite applications can be build based on changing business requirements and rolled out faster with less cost.

SOA Delivers
SOA enables faster development turn-around time,
faster realization of new inter and intra enterprise business processes, and
cost effective maintenance and upgrades.

All this translates to enhanced Business Agility. And
I would like to call it.....

Business Agility @ SOA

Bookmark and Share


Subscribe to Oracle FAQ aggregator