Posted in Oracle on 02/26/2010 11:07 am by mvzoest
Last week I tried to install Oracle Forms 11g. This install created a new Oracle_Home on my computer, which resulted in my database not starting up anymore.
The cause of this was, that my computer did not pick up the right tnsnames.ora file anymore, but instead it picked up the new tnsnames.ora that was created during my Forms installation.
To avoid having to have multiple copies of the same tnsnames.ora file on your computer, you can create a registry key in all Oracle_Homes. This will force your computer to pick up the same file for all your Oracle_Homes and thus allow you to have just a single copy of tnsnames.ora to manage.
To do this, start the Windows Registry editor by using Start -> Run -> Regedit and find your Oracle_Homes under HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE and rightclick on your Oracle_Home entry. Choose New -> String Value and enter the required information similar to the following screenshot (directory on harddrive may differ on your computer).
TNS_ADMIN registry entry
The registry key is called TNS_ADMIN and it’s value is the location of your tnsnames.ora file on the harddrive.
But remember to only add this key to the Oracle_Homes that do not contain your central tnsnames.ora.
Posted in Apex, Oracle, Whitehorses on 02/25/2010 02:27 pm by mvzoest
I did another Whitebook (in dutch). This time the subject is New Features in the APEX 4.0 Early Adopters release.
You can find it at the Whitehorses website.
By the way; besides this personal blog I also publish articles on the blog at the Whitehorses site together with the other Oracle, Java and SOA specialists of the company. These blogs are in english. A direct link to the list of my blogs is in my profile.
Posted in Apex, Whitehorses on 12/24/2009 11:16 am by mvzoest
For my employer Whitehorses I have written a new Whitebook on Application Translations in APEX. This Whitebook was written together with colleague Ome-B, the owner of www.Ome-B.nl and author of the book Oracle Application Express Forms Converter.
Following up on this Whitebook I have posted a blog at blog.whitehorses.nl, to explain how these translations work in the early adopters release of APEX 4.0.
Posted in General on 12/23/2009 12:12 pm by mvzoest
Application Express comes packaged with the OracleXE and Oracle11g database versions. In XE you can get started with APEX right away. In 11g the embedded PL/SQL gateway has to be configured first. These steps take you through the process of running the configuration script apxconf.sql.
Running this script enables you to configure the port for the Oracle XML DB HTTP server and to specify a password for the Oracle Application Express ADMIN account. Then you unlock the ANONYMOUS account.
Perform the following steps:
1. Open a terminal window and enter the following commands:
cd $ORACLE_HOME/apex
sqlplus sys/ as sysdba
@apxconf
2. Enter an administrator password for the Application Express Administrator account and press Enter.
3. Enter 8080 for the port for the XDB HTTP server and press Enter. This is also the default port. Change this number when another application (eg. Tomcat) is running on the same port. Keep in mind that ports below 1024 are not advisable when running Linux/Unix.
(The embedded PL/SQL gateway has now been configured.)
4. Unlock the anonymous user. From your terminal window, enter the following command:
ALTER USER ANONYMOUS ACCOUNT UNLOCK;
And now you’re all set to start developing APEX applications in 11g.
When you later want to change the port on which APEX is configured, you can run the following command:
dbms_xdb.sethttpport('9090');
If you want to check what the current port is, use the following query:
select dbms_xdb.gethttpport from dual;
For more information, visit the Oracle site at www.oracle.com
Posted in Apex on 12/04/2009 12:10 pm by mvzoest
Sometimes an APEX application performs worse than expected. This can have many causes, ranging from wrong parameters in the database (for example the SGA allocation) to less-than-optimal queries.
Besides the tools that a DBA has to check the performance and take action according to his findings, a developer also has some options to use in Application Express.
1. In the Apex Admin application there are some reports under the tab Monitor Activity. In the Page Views by View report that is found in the Page Views section, you can see how long it takes a page to load in the browser. Using this it is possible to see which pages take too long to load.
2. By using the Debug option in the developer toolbar, APEX generates a lot of debug information. Using this information a developer can see how a page is built-up in the browser. In front of each line of debug information is a time-stamp. This information allows a developer to analyze what part of a page is causing the delays.
3. It’s possible to let Application Express generate a trace file from the URL. To do this, simply add &p_trace=YES to the URL and call the page again. This generates a trc file in the user_dump_dest directory (find this using show parameter user_dump_dest). This file can also be analyzed to find where the problems may occur.
Offcourse this list is not a complete overview of all tools that are available to analyze performance problems, but I think you can get a good start.
Posted in Apex on 11/03/2009 02:30 pm by mvzoest
It’s good practice to use audit columns in Oracle to see which user created or modified certain records. Application Express is not different.
Filling audit columns is usually done using Before Insert and Before Update triggers on the related tables. These columns usually are Created_Date, Created_User, Modified_Date and Modified_User. Both date columns are filled with Sysdate, but the user columns are a bit different.
In most environments you can suffice with using the database user by setting the column like this:
:NEW.Created_User := USER;
In Application Express applications that’s not enough when you have an authentication scheme based on a user table. In that case you would like to see the application user that inserted or updated the record in you audit.
A simple and smart way to do this is by using the v() construction in your trigger like this:
:NEW.Created_User := nvl(v('APP_USER'),USER);
When you use this kind of code in your triggers, the audit user is filled with the user that was logged into the application. But when the record is altered directly on the database, you will see the database user.
Posted in Oracle, Whitehorses on 10/28/2009 03:47 pm by mvzoest
This year is the 25th anniversary of Oracle’s branch in The Netherlands. For this occasion Oracle has organised an event in Aalsmeer on november 19th.
My employer Whitehorses is a silver sponsor for this event. You can visit our stand for more information on the company and how we can help you solve your IT-problems.
For more information visit the event’s website.
Posted in Smart Queries on 10/22/2009 09:25 am by mvzoest
On every project I’ve worked for the past few years, there came a question to find all duplicate rows in a table. And every time I mix up some of the code. So now it’s time to post the right code on my blog, so I can always find it back 
SELECT employee,
COUNT(employee) AS Occurrences
FROM emp
GROUP BY employee
HAVING ( COUNT(employee) > 1 )
Posted in Apex, Oracle on 10/06/2009 09:33 am by mvzoest
Oracle has put the Application Express Listener up for download. It’s an early adopters release, so it’s not supported, but I think it’s still worth giving a try.
The download is available at: http://www.oracle.com/technology/products/database/application_express/html/apex_listener_download.html
I think it’s a very significant change in Apex. This allows developers to use their existing web server (like Tomcat or Weblogic), instead of Oracle HTTP Server/mod_plsql or the XDB HTTP protocol server/embedded PL/SQL gateway.
Read the Installation Guide for more information.
-
Tags: Apex, application express, download, http server, listener, oc4j, Oracle, pl/sql.mod_plsql, tomcat, weblogic, xdb
