When not to use APEX?

When I did some talks last year, I got the question "When would you NOT use Oracle Application Express (APEX)?".

I created a draft message in my blog posts as I found it interesting getting this question. I never posted it, but now I decided to post something about it.

During the last days the question popped up again, more than once, so I decided to ask for new ideas ;-)

I find it a difficult question for a person who's passionate about a technology!

So what do I normally answer?

As some smart people often say, ask questions! Why would you like to do this? What's it what you would like to achieve? etc. So during the first minutes I ask questions ;-)

Most of the time they insist to give more answers. It can't be true that you can use APEX for everything! To be honest, I don't think APEX is a good fit in every situation, but to name these situations that's something else.

If I would say... I wouldn't use APEX:

• when your team exists out of JAVA and/or .NET developers
• when the requirements are a rich client application
• when you don't have a data centric approach
• when you need to program for a specific device (calculator, hospital equipment etc.)
  
• when you've high trough put of data (like the typical old Cobol programs)

Or some more...

• when you hate the Oracle database (because you want to restart another database every week and love the blue screens - job protection)
  
• when you don't know nothing about PL/SQL, html, css and javascript and don't want to learn it (learning .Net is so much easier)
  
• when you're a freelancer and want a long term project to finish a job (doing it in APEX you'll get only half the money as you would spent only half the time to have the same result)
• when you want different plugins to install just to get your application running (only a browser is too easy!)
• when you want to change technology very often (java, bc4j, swing, uix, jsf, adf, ...)
  
• when your company has too much money to spent on an application and the infrastructure (although Apex Evangelists can offer you a FREE service to help you spend the money)

Or ... What would you answer?

(would the above ALWAYS be correct? Of course not...)

APEX by Example - Default value of Shuttle

I got some questions about setting the default value of a shuttle. The example can be seen here.

Basically a Shuttle are two lists: on the left you'll see all values, which you can select by moving it to the right. If you already want some values on the right when you open the page, how do you do that?

The APEX components in my example: a Region, a Shuttle item and a Computation.
Creating the region and the Shuttle isn't that hard. The query of the Shuttle is:

SELECT ename, empno FROM emp ORDER by 1

The computation is of type "PL/SQL Function Body". The actual code looks like this:
What does it do?
It fills an array "emp_list" with all the values you want to have already selected (on the left in the shuttle). In my case the first 3 records.

The most important part is the last line APEX_UTL.TABLE_TO_STRING. Which transforms the array in a way so the shuttle can understand it. A string, with in between the values a ':'.

That's just one way of doing it. In APEX you can follow different ways to accomplish the same result. Hope it helps some of you.

My 2008 Schedule

Just as last year I'll be speaking on some major Oracle events.

The biggest reason for me to go to these events is meeting others. I really like talking to and having drinks with other passionate users of Oracle technology. It's always interesting to hear about their experience, know the person behind the blog I'm reading or just to come into contact with that one person reading this blog ;-)

If you're at one of the following events and want to meet, drop me a mail or feel free to put a comment on this post:

• March 18-20: European Apex Training Days - London (UK)
  
• April 13-17: IOUG Collaborate 2008 - Denver, Colorado (US)
  
• June 15-19: ODTUG Kaleidoscope 2008 - New Orleans (US)
  
• September 21-25: Oracle OpenWorld 2008 - San Francisco (US)
  

I'm also talking at some smaller events (mostly one day seminars) in the BeNeLux, but the dates are not fixed yet.

Hope to see you at one of the events,

Dimitri

PS: I always like to put a picture in my blog post, but I didn't want to add an obvious one this time. Finally I decided to go for Smeagal (Lord of the Rings), somebody an idea why?

APEX vs ADF (BEA?): Round 2

After the first round in Europe, there will be a second round in the States!

On the last day of ODTUG Kaleidoscope 2008 we'll have a new "fight"... This time my opponent is not Lucas Jellema, but Peter Ebell. Peter is also a big name in the Java world. He was one of the key persons in the JHeadstart team and he has a broad knowledge about Java, BPEL and SOA.

All details still need to be defined, but the approach will be not that different than what we did in Nieuwegein. Although I heard with Peter it can become bloody! ;-)

As a side note, did you see Oracle acquired BEA?

I'm happy I'm concentrating on the Oracle database and APEX is living in that. It doesn't change that much. The (Oracle) Java world is moving so quickly! I wonder if Peter will talk about deploying ADF to BEA Weblogic. But I suppose these days are exciting moments for the Oracle Java people.

My Top 15 Oracle DB 11g NF for APEX Developers: 14

This post is part of the series of blog posts about "My Top 15 Oracle Database 11g New Features for APEX Developers". Already published articles: 15-Virtual Columns.

14 - SQL Access Advisor

Why would you like to use this feature?

For some people APEX is a first touch in the Oracle world. People who just start to develop applications may not be that experienced in Oracle yet. They start creating schema's, tables, indexes etc. through the wizard or by copy/pasting from things they have seen.

Unless they've a senior person looking at their code or they hire an external company to get them to the next level, it's not that easy to get recommendations. You can read lots of books or search on the internet, or you could use some features of the Oracle database to help you. Oracle 11g provides a lot of "Advisors", one of them is the SQL Access Advisor.

Maybe you have used SQL Tuning Advisor before? That's nice to tune individual SQL statements, but SQL Access Advisor is even nicer as it looks at a lot more to give you the "right" advice.

What does Oracle say about it?

SQL Access Advisor evaluates an entire workload of SQL and recommend indexes, partitioning, materialized views that will improve the collective performance of the SQL workload.

In other words, where SQL Tuning Advisor looks at one statement, SQL Access Advisor looks at the complete picture. It may be possible that the SQL Tuning Advisor recommends creating an index, but SQL Access Advisor would recommend to not create the index, but to create a materialized view or partition as it looked at the entire workload, including considering the cost of creating and maintaining the index.

Syntax

You would need to create a plsql script which calls the dbms_advisor package or use Enterprise Manager to guide you through the steps of defining the workload and creating for ex. SQL Tuning Sets. More information can be found here.

Example

A very simple example to tune a SELECT statement:

BEGIN
DBMS_ADVISOR.quick_tune(
   advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
   task_name    => 'emp_dept_quick_tune',
   attr1        => 'SELECT e.ename, d.dname, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno AND e.sal >= 2300 ORDER BY e.sal DESC');
END;
/

I would encourage you to use Enterprise Manager to access SQL Access Advisor. The wizards will guide you to all the necessary steps.

The SQL Access Advisor is located in the Advisor Central - SQL Advisors section of EM.

As I already configured a Quick Tune for my SELECT statement through dbms_advisor, it's listed in the screen of Enterprise Manager.

When you click on the outcome of SQL Advisor Quick Tune, you get a nice graph with what the initial cost was for the select statement and what it could be like if you would implement the recommendation.

Especially the above screen makes it worth to use Enterprise Manager to tune your schema. In my example I only used the SQL Access Advisor to tune one statement (I could also have done it with the SQL Tuning Advisor), but tuning your entire schema isn't that different. You would specify the workload (even an hypothetical workload), the advisor would look at all that information and would give you of course more information as I had with one statement, but the principle is the same.

Behind the Scenes

The following views can be used to display the SQL Access Advisor output without using Enterprise Manager or the get_task_script function: DBA_ADVISOR_TASKS, DBA_ADVISOR_LOG, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS

Other useful information

There's another nice example of using SQL Access Advisor written by Arup Nanda here.

Conclusion

Having SQL Access Advisor available doesn't mean a good database design isn't important anymore! I would recommend taking time to get the design right from the start. As the database gets more and more complex, and you're having more and more data, it's nice to have a feature like SQL Access Advisor which can help you to look at the performance.

Maybe this features is more used by DBAs, but I think it's also nice for a developer to sometimes have a look at this. And let's face it, some of us are responsible for everything (dba/development) ;-) 

My Top 15 Oracle DB 11g NF for APEX Developers: 15

I thought it a good idea to write a series of blog posts about some new features of the Oracle 11g database I find useful in an APEX project.

I decided to do a "Top 15". It's very difficult to order them, as for one person or project a feature would be number 1, but for another, it would only be found on place 10. So don't see it as I like the feature I put on place 15 less than the one on place 10. I like all new features ;-)

My research... this top 15 came together after reading:

• The Oracle 11g Database New Features Guide
• Oracle Database 11g: The Top New Features for DBAs and Developers by Arup Nanda
• Tim Hall's articles on Oracle Database 11g
• and Tom Kyte's Top 11 things about 11g.

That was the background...

15 - Virtual Columns

Why would you like to use this feature?

If you've an orders table with the unit price and the quantity, wouldn't it be useful to have the total (= unit price * quantity)?

Or when a store has a price list of items they buy, they add 30% to it to get the price they sell the item too. Or getting the initials of a name. Or seeing the date in a specific format. Or... Having that kind of information already in the table may be of help.

What does Oracle say about it?

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

Syntax

Part of the create/alter table statement:

column [datatype] [GENERATED ALWAYS] AS (column_expression) [VIRTUAL] 

[ inline_constraint [inline_constraint]... ]

Example

SQL> CREATE TABLE EMP_WITH_VC (
EMPNO NUMBER(4,0) NOT NULL ENABLE,
ENAME VARCHAR2(10 BYTE),JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
NEW_SAL AS (ROUND(sal*1.10,2)),
TOTAL_SAL_COMM NUMBER GENERATED ALWAYS AS (ROUND(sal*1.10+NVL(comm,0),2)) VIRTUAL,
HIRE_YEAR AS (TO_CHAR(hiredate, 'YYYY')),
PRIMARY KEY (EMPNO)
);

SQL> INSERT INTO EMP_WITH_VC (empno, ename, job, mgr, 

hiredate, sal, comm, deptno)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM EMP; 

If we query the new EMP with Virtual Column table, we'll see all data of the normal EMP table + the new virtual columns (red): the new salary (10% higher), the total package of the salary and commission and the hire year.

SQL> SELECT * FROM EMP_WITH_VC

I wanted to put the examples online on the new APEX 3.1 instance, but it doesn't seem to run on 11g... (as my creation of my table failed). But as you see on the screenshot, it's running on my local machines.

Behind the Scenes

If you look at the user_tab_columns view you'll notice a column called data_default, which contains the expression of the virtual column.

Other useful information

• An index can be created on a Virtual Column (this would be a function based index)
• You can not do DML (insert, update, delete) on a Virtual Column, but you can use it in a WHERE clause
• Virtual Columns are only available on relational heap tables
• Virtual Column expression work only on the same table, so not cross table and they can't contain other Virtual Columns
• You can use Vitual Columns in Constraints (PK, FK, Check)
  
• You can partition on Virtual Columns !

The full documentation about Virtual Columns can be found here.

Scary contacts...

The last couple of hours I got some mails, asking for help. But when I replied I got an "Undelivered Mail Returned to Sender". I'm not sure if they made a mistake or did it deliberate, that's why I blog about it as I can't reach "you".

The strange thing, it was even not from one person and even more bizar they even didn't ask for free help but wanted to pay, which I find great, of course ;-)

But without wanting to offend anybody, I guess it was an early first April joke?

Nevertheless these are some of the messages:

I wish project work for you, urgent dimitri needs fast will pay on project. call now.

Or a message like this:

I have a 1,000,000 Euro project and would be interested in talking to you about it.

So if you're serious please provide the right email address so I can contact you properly.
If it was a joke, thanks, nice for one time ;-)

Here we go again...

I remember the early days where we got some chain letters. You got luck when you would sent that letter to 10 others. I guess the guys started this, did a lot of these things before?

They used the same principle but an up-to-date technology. I got "tagged" by the great Tim's: Tim Hall and Tim Dexter... so I'm "it".

I never did chain letters! But as it's an "Oracle Wave" I would feel guilty to stop it, so here we go...

Eight things about me you may not know:

1. When I was younger I had a crush on Pamela Anderson. In 1997 I even had a site dedicated to the blonde model! Although the early days of internet, I got more visits on that site than on any other site I'm hosting now ;-)
2. I was a fan of Manchester United, especially when Eric Cantona and David Beckham were still there. I even went to the stadium and walked on the field and saw the dressing rooms.
3. As a little kid (around 1983) I played Super Bunny on my fathers Apple II. At that time you had only floppies, but we had already a paddle (sort of joystick)!
4. When I was 10 years old, I bought my first PC (an IBM XT), from that moment all my money went into buying pc stuff.
5. I had only one "real" girl friend and she became my wife.
6. Till 21 I never touched alcohol (beer). I didn't like beer, I was more into fristi! Things change...
7. As a little kid I wanted to become a "Bank Director".
8. When I had to fill in the "poezie album" of somebody, most of them filled in with "Favorite food"; French fries, but I was the only one who filled in; "Chicory with ham and cheese covered with breadcrumbs".

I wanted to start tagging the next 8 people, but then read this...

APEX 3.1 hosted environment updated

I just logged in into the online Oracle Application Express 3.1 hosted evaluation version.
I was surprised it got upgraded. The message says it got updated on January 7th 2008, so only a couple of minutes ago (as in Belgium we're a bit ahead of time).

The icons look more slick I think. The current version is 3.1.0.00.19.

I'm still figuring out what has changed. I guess the APEX-team will tell us in a moment.
What I think changed, when I go to printing I see some more options:

It's always nice to see updates coming out. Thanks APEX Development Team and keep up the good work.

Update - Just got this mail:

First, thank you for all your comments about our public evaluation instance of Application Express 3.1. We wanted to let you know that we just completed an upgrade of the instance to bring it up to date with our development. Many of the issues with Interactive Reports should be fixed now and you will also be able to see and use other new features such as the new Workspace Themes. If you find any new issues, please report them using the feedback application. The link is also available from within the hosted Application Express 3.1 evaluation instance Home page.

Thank You!
The Application Express Team

and the best speaker of the UKOUG 2007 is...

Some people published the feedback they received from their presentation at the UKOUG.

I read first Marco Gralike's post how he felt with his scores. It's always interesting to see how the audience react to your presentation. You learn a lot from the evaluations afterwards. Worst would be you thought you did a great job and afterwards you find out people didn't like it. You can always learn something to make the presentation better!

But when I read John Scott's post about his evaluation I was really impressed with his scores! Unbelievable high scores! I wonder what scores Tom Kyte and Jonathan Lewis get? They must be used to these scores I guess... although I think people start expecting more and more from you when you're at that level. For ex. I saw some presentations of Rich Niemec and I believe he's a confident speaker. But at Collaborate last year he got some though questions from the audience and people expected he would know all that (it was a presentation about the new features of the 11g database). He didn't know some of the questions and some of the public started to become "angry". I don't know anybody who knows everything, but I know a lot of people knowing a lot ;-)

To come back to John's presentation, that was indeed a really good one. Especially the debugging of APEX applications through SQL Developer I found impressive. Immediately after the session a lot of people congratulated him, then you know you did a great job. Congratulations John! I guess you must be close to the "best speaker" award!
If you want to see him presenting soon... London, March, at the European APEX Training Days.

I also did a presentation at UKOUG about the integration of BI Publisher with Oracle Application Express and getting some advanced reports out of it. You find my scores below... Not as high as John, but I'm happy with them ;-)

Start of 2008 and Wii

I hope you all had a nice Old on New.

This year I held it quite quiet. I played some (board-)games with family and had some nice food. I like playing games like Pentagon, The Settlers of Catan, Highscore, Ave Caesar, Poker etc.

Santa Claus also dropped a Wii under our Christmas tree. So games like Wii Sports, Mario Galaxy, Resident Evil, Table Tennis, Mario Kart, Mortal Kombat etc. have no secrets for me anymore ;-)

I especially like the Wii when you can play against others. Do some boxing or playing a game of baseball against your family is so much fun!

If you made a resolution like doing more exercises or a bit more sports, the Wii can be very helpful! You really sweat while playing these Wii games ;-)

As you read this blog, there's a great chance that you're behind your pc for more than 10 hours a day... maybe the above can help you? It did help me to "relax" during my holidays.

Happy New Year!