World Cup 2006 score in APEX

I received a nice Excel file which can store the scores of the World Cup 2006.
As I really love APEX, I thought, let me try to make this "advanced Excel" in APEX.
My goal:
- use only the online version of APEX (http://apex.oracle.com)
- use only APEX, so no other tools
- have at least all "possibilities" of the Excel file
- make something quick and dirty ;-)

After two evenings of work, this is the result. (see also on picture)
You can update the scores of the games, all the rest is "automatically" (till the 1/8 finales).
Basically you have two procedures to calculate the score and to show the flags, and two pages, one to show the results and one to fill in the score of the games. I also made a third page, to upload the flags (little jpegs) into a blob column.
There's still room for improvement; table layout, add fk's, items/regions definition, add automatically calculation for the 1/4 finales and further, improve code etc.

You're free to download the source here. It includes the generation of the tables, the data and the APEX application.

The Orablogs effect!

Just like Doug Burns post, where he was talking about a "Thomas Kyte Effect", I had today something similar! For me it was the "Orablogs / Sergio Effect"...

As like on other days, I was reading Orablogs.
But this day will be kept in my memory! Suddenly I saw the post of Sergio, talking about me and my blog. First I couldn't believe it... but then I looked at my stats of my blog and I saw a hugh increase in visitors!

Thanks Sergio!

I really look up to the posters of Orablogs... especially I'm reading the blogs of Tom Kyte, Sergio Leunissen, Scott Spendolini, Doug Burns, Sue Harper and some others (see the rss feeds on the picture). Seeing myself in one of their posts gave me a warm and pleasant feeling.

APEX 2.2 update

Although I posted the previous post about APEX 2.2 today, the date is kept from the moment I saved it as "Draft".

Here are some other new or improved features in APEX 2.2:

• Application Deployment Packaging
• Component Attributes Screens (eg Regions, Items, Computations, & Processes)
• Copy Application Feature
• Finder Utility
• Monitoring - Application Reports
• Timestamp support
• Report Attributes Page
• Improved Page Debug Messages
• Re-Ordering Widgets
• Online Help

Testing APEX 2.2

As this post is already sitting for one week in my "Drafts", I thought to post it and post an update afterwards...

Initially I thought to post all my findings on my blog, but the last week I didn't have a lot of time. But this two sentences say it all:
- APEX is and stays my N°1 development environment -
- With APEX 2.2 we even get more new or improved features -

After I requested an APEX 2.2 workspace, I was curious to see what had been improved.
I will share some screenshots with you...

The begin screen after logging in.	New Feature: Screen after using the Access Control Wizard Authorization Schemes
Some new themes in APEX 2.2	Improved Page Definition - use of DHTML to show/hide regions

ODTUG 2006 - Washington, DC

From June 17 till 21, I will participate to the Oracle Development Tools User Group. More information about the event - click here
(btw: the ODTUG site is written in APEX)

ODTUG Kaleidoscope 2006 - Clarifying the Developer's Changing Universe is the Premier Conference for Developers Working with an Oracle Database.

TOPICS:

• Application Design and Development
• Business Intelligence/Data Warehousing
• Methodology
• Development DBA
• Traditional Oracle Tools
• Professional Development
• Non-Oracle Development Tools
  

Next to the interesting topics and the nice people I will meet, I'm also looking forward visiting the United States for the first time... I'm staying in the Wardman Park Marriott Hotel (see picture). This is also the hotel where the conference is organised.

I will try to put some notes on my blog after the sessions...

Oracle DB - difference in chars and charset 8i - 9i?!

First time I see this behavior of the Oracle Database... Although when you think about it, I can understand the 8i -> 9i behavior, but the other way around?!

Scenario:
- Two databases: DB 9.2.0.6 with charset UTF8 ; DB 8.1.7 with charset WE8ISO8859P1
- I want to do in the 9i database: create table x91 as select * from x81@db8_link;
- I want to do in the 8i database: create table x82 as select * from x92@db9_link;
- Table structure of x81: c1 VARCHAR2(10), c2 CHAR(5), ...
- Table structure of x92: c1 VARCHAR2(10 CHAR), c2 CHAR(5 CHAR), ...

What happens when you do the create table?
- Table x91 will become c1 VARCHAR2(30), c2 CHAR(15), ...
- Table x82 will become c1 VARCHAR2(30), c2 CHAR(15), ...

So you can see that the initial data/char length is multiplied by 3 in both cases!
I suppose because charset UTF8 can have 3 bytes per character and WE8ISO8859P1 has only 1 byte per character.
When you look into user_tab_columns for table x92 in the 9i db you've data_length/char_col_decl_length 30 and for char_length 10. In 8i you don't have char_length...
--
SELECT table_name, column_name, data_type, data_length, char_col_decl_length,
char_length -- only 9i
FROM user_tab_columns
WHERE table_name IN ('X92', 'X91')
AND data_type IN ('CHAR', 'VARCHAR2')
ORDER BY column_name;
--

The Solution to copy the table?
- Create the table manually with the right length for the columns
- Do the insert of the data afterwards

I can understand the behavior when I'm in the 8i database (because he doesn't know char_length), but when I'm in the 9i database, I thought it would work "correctly"...
(btw: I've in my init.ora parameter nls_length_semantics = CHAR)

You may try yourselves... Download my test script here.

Oracle Export / Import problem with CLOB's

I’m currently reorganising some databases…
As I do it for the x time, I’m working on “auto-pilot”.
But suddenly I received an error:
--
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TOOLS_DATA' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "D_JOB" ("O_ID" NUMBER(10, 0), "D_TITLE" VARCHAR2(250), "D_OUTPUT" CLOB, "D_PERCENTAGE" NUMBER(10, 0), "D_LASTUPDATE" NUMBER(10, 0), "D_OWNER" NUMBER(10, 0), "D_STATUS" VARCHAR2(20), "D_PRIORITY" NUMBER(10, 0), "D_TYPE" VARCHAR2(20), "D_TIME" VARCHAR2(5), "D_DATE" DATE, "D_CLASS" VARCHAR2(250)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TOOLS_DATA" LOB ("D_OUTPUT") STORE AS (TABLESPACE "TOOLS_DATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
--

What I did:

• create a full export of the source database
• create all the necessary users, roles and tablespaces on the target database
  (use of naming conventions, from dictionary to locally managed tablespace, change of parameters etc.)
• import from -> to user on the target database
  (Actually I'm doing the import twice, as the database characterset changed. I create first the structure without data, afterwards change the tables char columns from byte to char and finish with the import of the data)
  

Almost all tables were imported fine, but the tables with some CLOB’s in weren’t!
As I changed the default tablespace of the user (and the old tablespace didn’t exist anymore) the import couldn’t find the tablespace and gave the above error.

I suppose I forgot that if you’ve tables with clobs in, the default tablespace of the user isn’t automatically taken… to solve the problem, I had to recreate the tables manually with the right storage clause (tablespace).

I learn a new thing every day (although I think for this, I just forgot this behaviour of Oracle)! ;-)

Promoting APEX

Although my current mission has nothing to do with APEX (Application Express - old name is HTMLDB) I use it almost every day at the customer!

My current mission exists out of two parts: the first part is helping the DBA's with the support of the existing environments, the second part exists out of doing upgrades (8i to 9i), migrations and reorganization/clean-up of most of their databases (for DEV, TEST and LIVE). You may see it as a "typical" (long term) DBA mission.

This customer is using a lot of Excel files for the overview of the environment and especially for the planning of the database upgrades. Although they've today mainly 8.1.7 databases I saw the opportunity to promote APEX.

I started with the creation of four new 9.2.0.6 development databases, as all the old "database per application" will be merged into one of the four. On one of them I also installed APEX and I started creating a little application to replace all their Excel files and give them even more possibilities in a shorter time. As I also liked to do a clean-up of the databases I needed to ask the responsible persons what they still needed in the databases.

I created a dynamic script that searched all the info (which users, roles, tablespaces etc.) in the meta-data of the existing databases (via db-links) and stored it in some tables. Just with one click I had a complete overview of their environment! I could have used the Excel files they had, but where they up-to-date? Did they have all the information? Was it the latest version of the file? Who had which Excel file?

Afterwards I started building the little application. I even built some security in. I can see and update everything, they can't! ;-) Below some screenshots of this little app:


The advantages to use APEX instead of Excel were:
- Only ONE source of information
- Up-to-date all the time
- Available to all users immediate
- I can control what they can or can not change
- Easy for me to do queries to find the necessary information, I can use the "Oracle force" whenever needed
- The users and management have at any time an overview what is done

Next to the creation of this little application which is used by a lot of persons and management, I did some more to promote APEX.
I started to give a little demo of APEX to two users, after a day, there was already somebody else asking me to show "that magical application"! The next day there were again some others... The word was spread just like that! Today some people are even using APEX at home (together with Oracle XE)!

It gives me a lot of satisfaction to see that. I think it's easy for you to do it too, so I thought to share this experience with you.