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.
- 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.
Tuesday, May 30, 2006
I received a nice Excel file which can store the scores of the World Cup 2006.
Posted by Dimitri Gielis at 17:52
Friday, May 26, 2006
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!
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.
Posted by Dimitri Gielis at 19:38
Saturday, May 20, 2006
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
Posted by Dimitri Gielis at 16:20
Sunday, May 14, 2006
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|
Posted by Dimitri Gielis at 20:04
Saturday, May 13, 2006
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.
- Application Design and Development
- Business Intelligence/Data Warehousing
- Development DBA
- Traditional Oracle Tools
- Professional Development
- Non-Oracle Development Tools
Posted by Dimitri Gielis at 12:02
Friday, May 12, 2006
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?!
- Two databases: DB 220.127.116.11 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
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.
Posted by Dimitri Gielis at 10:31
Monday, May 08, 2006
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)
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)! ;-)
Posted by Dimitri Gielis at 15:41
Tuesday, May 02, 2006
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 18.104.22.168 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.
Posted by Dimitri Gielis at 14:53