Feed aggregator

Access Migration Tutorial

Donal Daly - Thu, 2007-05-31 13:33
Are you considering migrating that Microsoft Access application to Oracle Application Express? Well read on...

Hopefully you are aware of Oracle SQL Developer and that we have redeveloped the Migration Workbench and integrated it tightly with Oracle SQL Developer. An early adopter version of this is available now and will be production very soon. More of that in a subsequent post.

With Oracle Application Express 3.0 we introduced the Application Migration Workshop to assist with migrating your Access Forms & Reports. When I talk about this solution, I get asked do we have a step by step guide or methodology for such migrations. So, we have produced a migration tutorial to address this and have published it on OTN.

We have taken the Microsoft Access sample application, Northwind Traders and migrated it to Oracle Application Express. The tutorial covers this in step by step detail. Following this tutorial would be a useful exercise for any user that wishes to undertake migrating their applications from Microsoft Access to Oracle Application Express. We have called the converted application Southwind Wholesalers. :-)

You can see it running on apex.oracle.com and we have also provided it as a packaged application so you can examine it in detail.

Run system commands from Oracle with PL/SQL

Hampus Linden - Wed, 2007-05-30 15:55
I friend of mine asked if it was possible to show the exact Linux kernel version on an Oracle server without actually having shell access to the server.
He had full access to Oracle with sysdba/dba roles etc, but not SSH.
I've seen some versions of executing system commands from Java but never really liked the idea of invoking Java for something simple like that.

One way I thought of would be to use dbms_scheduler to execute a job with an executable job_typ. The first problem was to find a way to actually return the standard output from the execution to Oracle.
Ok, so my 'hack' here is a stored procedure (entirely in PL/SQL) that creates a job with dbms_scheduler; calling /bin/sh as the executable and hands it a temporary script to execute. In the script I have a simple redirect to a temporary spool file and then the procedure simply reads and outputs the content of the file. It's a bit of a hack but at least it gets the job done and doesn't use Java.
I haven't drilled down on what kind of permissions you need to actually use the procedure but I suspect it's quite a lot.
The temporary spool file handling in my example is quite poor, but works. :)
A word of warning as usual when using PL/SQL, this code example is a proof of concept. It needs *loads' of error catching etc. in order to be production ready, use with caution.

Example of use:
oracle@htpc:~$ rsqlplus hlinden/password as sysdba

SQL*Plus: Release - Production on Wed May 30 21:55:06 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> @system_run

Procedure created.

SQL> exec system_run('ls -l /home/oracle/bin');
total 12
-rwxr-xr-x 1 oracle dba 797 Nov 5 2006 backup_controlfile.sh

PL/SQL procedure successfully completed.

SQL> exec system_run('uname -a');
Linux htpc 2.6.20-15-generic #2 SMP Sun Apr 15 06:17:24 UTC 2007 x86_64 GNU/Linux

PL/SQL procedure successfully completed.

And here is the procedure code:
CREATE OR REPLACE PROCEDURE system_run(cmd IN varchar2)
script_file varchar2(40) := 'my-temp-script.sh';
script_data varchar2(4000);
MyFile utl_file.file_type;
d varchar2(4000);
dump_file varchar2(40) := '/tmp/my-temp-file.dat';
dump_type utl_file.file_type;
-- Open file
MyFile := utl_file.fopen('TMP',script_file,'w');
-- Write data to file
script_data := '#!/bin/bash' || chr(10) || cmd||'>'||dump_file;
utl_file.put_line(MyFile, script_data, FALSE);
-- Close file
-- Purge old logs, no fun anyway
-- Execute script
-- The job is created as disabled as
-- we execute it manually and will
-- drop itself once executed.
job_name => 'TEST',
job_type => 'EXECUTABLE',
job_action => '/bin/bash',
number_of_arguments => 1,
start_date => SYSTIMESTAMP,
enabled => FALSE);
dbms_scheduler.set_job_argument_value('TEST', 1, '/tmp/'||script_file);
-- Wait for the job to be executed
-- usually done within 1 second but
-- I set it to 2 just in case.
-- Open the output file and
-- print the result.
dump_type := utl_file.fopen('TMP',dump_file,'r');
when others then
end loop;
-- Clean up our temp files
utl_file.fremove('TMP', script_file);
utl_file.fremove('TMP', dump_file);

Multirow Inserts

Robert Vollman - Mon, 2007-05-28 12:36
While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?INSERT INTO table (column1, column2)VALUES (value1, value2), (value1, value2);ERROR at line 1:ORA-00933: SQL command not properly endedUnlike some other databases (DB2, PostgreSQL, MySQL), Oracle doesn't support Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7


Robert Baillie - Mon, 2007-05-28 12:02
And to follow on from the last post... my current personal bests: I figure if I keep them here, at least I'll always know where they are! 5km Run23:44 (Battersea Park, 'Beat the Baton' 28/05/07) 10km Run53:23 (Hyde Park, 'Run London' 08/10/06) Half Marathon2:17:49 (Redcar, 'Tees Valley Half Marathon' 12/03/06) Rubik's cube57 seconds


Rob Baillie - Mon, 2007-05-28 11:45
And to follow on from the last post... my current personal bests:

I figure if I keep them here, at least I'll always know where they are!
5km Run23:44 (Battersea Park, 'Beat the Baton' 28/05/07)
10km Run53:23 (Hyde Park, 'Run London' 08/10/06)
Half Marathon2:17:49 (Redcar, 'Tees Valley Half Marathon' 12/03/06)
Rubik's cube57 seconds


Robert Baillie - Mon, 2007-05-28 11:44
You've gotta have targets. The more I try to motivate myself to do things, the more I realise that if I don't have a target it's incredibly difficult. When I realised this it came as a big surprise to me. I'm really not the sort of person to have a 5 year plan or career goals, but it seems that if I don't set myself an only just achievable goal I find it very difficult to motivate myself to do much. I keep myself fit so that I get the most out of playing football. But just having that in mind isn't enough to get me out and running. If I didn't set myself a target time for a 5km or 10km run and then book a place at a running event, then I'd just sit on my fat arse every night watching TV. OK, so I may be exaggerating my self deprecation, but you get the idea. I find that this affects me in many different aspects of my life. To motivate myself to run I set a target (public) 5km or 10km time (this year it's 22:30 and 50:00 respectively). To motivate myself to learn to do the...


Rob Baillie - Mon, 2007-05-28 11:12
You've gotta have targets.

The more I try to motivate myself to do things, the more I realise that if I don't have a target it's incredibly difficult.

When I realised this it came as a big surprise to me. I'm really not the sort of person to have a 5 year plan or career goals, but it seems that if I don't set myself an only just achievable goal I find it very difficult to motivate myself to do much.

I keep myself fit so that I get the most out of playing football. But just having that in mind isn't enough to get me out and running. If I didn't set myself a target time for a 5km or 10km run and then book a place at a running event, then I'd just sit on my fat arse every night watching TV. OK, so I may be exaggerating my self deprecation, but you get the idea.

I find that this affects me in many different aspects of my life.

To motivate myself to run I set a target (public) 5km or 10km time (this year it's 22:30 and 50:00 respectively).

To motivate myself to learn to do the Rubik's cube, I set myself a target completion time (1 minute - yup, managed it).

To motivate myself to save money I set a target amount to reach by a certain date (nope, not telling you how much).

A friend of mine decided that he'd set himself the target of taking a photo a day for a year and posting it on his site. I may have to steal that idea next year... but until then you can find his here: www.ysr23.com/blog. It really is damn good.

I do the job I do because I just flat out enjoy it. As soon as it becomes too much of a chore I'll move on. And I reckon I'm doing alright career wise in whatever way you choose to measure it. For me the only measure that truly counts is enjoyment, and in the main it's a damn fine job. Well, it is most of the time anyway ;-)

Someone at work once said to me: You know, every now and again Tom Cruise probably gets up in the morning, probably on set, in his trailer and thinks to himself "Damn, gotta do some of that acting shit again today". OK, so he gets paid more in a minute that I do in a year, but you get the point.

And the big thing that keeps my enjoying my job is that I'm still learning new things. I suppose I have a clear target in my career to always keep on learning and to surround myself in people who can teach me. It's probably one of the biggest reasons why I'm so pleased to be working with Extreme Programming. It makes it easy to fulfill that goal. And it works on a clear system of easy to understand targets.

A release to the business has a target set of functionality.
A single story has a clearly defined purpose.
A unit test gives you a goal that must be met, and a clear way of determining the success or failure.

Layers of targets.

And if you're doing XP properly you get to celebrate when you meet those targets.

A brief whoop when the unit test passes.
A handful of jelly beans when the story's complete.
A damn big meal and a piss up when a release hits the business.

OK, so real life targets don't have quite the same level of celebration, but it's the same deal.

Set yourself a clear target and you get clarity of purpose in aiming for it, and the celebration when you pass it.

Cyrus IMAP file system tuning

Hampus Linden - Mon, 2007-05-28 04:59
Been busy, not enough blogging, bla bla bla. I know.
Just a lot of stuff going on at work at the moment, mergers and integrations.

We've had some problems with one of our IMAP servers at work running Postfix and Cyrus IMAPd. A for the job quite well speced machine, dual Xeons and 3x146Gb disk in RAID5 (4x146 in RAID10 would have been nicer). Anyway, the machine has got 50 or so IMAP users and perhaps 100Gb spool data on a ReiserFS partition.
The machines' avg. load has peaked at over 6.00 with about 75% in iowait on a bad day. I suspect that modern fancy e-mail search tools are to blame for the problems, applications building search indexes and such (Apple Mail anyone?).
Monitoring of the server showed quite a lot of inode update activity, even though there isn't *that* much new email coming in.
Must be our old (not so) dear friend atime that's making a little mess, I've used the noatimea and nodiratime mount options in the past with great success. Seen performance improvements of a couple of percent.
The mount-options noatime and nodiratime simply disables the feature to update the access timestamp of a file (and directory). I.e. when someone clicks and reads an email in their mail application the inode atime timestamp is updated. When is this atime timestamp used? Never.
Did a quick online remount of the spool fs with noatime and nodiratime.
The result?
Avg. load hasn't touch 1.00 since. Wow! I was expecting an improvement, but not that big.
Great and easy way to improve performance on IMAP spools.
mount -o remount,noatime,nodiratime /var/spool/imap
And don't forget to update /etc/fstab with the same mount options.

What Makes a Great Oracle Blog?

Robert Vollman - Fri, 2007-05-25 13:27
Along the side of my page, you'll see my favourite Oracle blogs listed. I carefully maintain this list of fellow enthusiasts whose opinions and insights I most especially want to follow among the seemingly hundreds of Oracle blogs that are out there. Studying them, I think you'll find that each of them share the same core qualities listed below.1. AccuracyAccuracy is an absolute must. Just Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com18

Listener passwords: always for 9i, never for 10g

Andrew Fraser - Thu, 2007-05-24 06:32

This page has been moved to http://andrewfraserdba.com/?p=44

Categories: DBA Blogs

J2EE Container Managed Security: How to reference the current user

Brenden Anstey - Wed, 2007-05-23 23:48

When J2EE container managed security is used the User Principal can be referenced in a number of ways:

Expression Language
       <af:outputText value="#{facesContext.externalContext.userPrincipal.name}"/>

Managed / Backing Bean
       ExternalContext ectx = FacesContext.getCurrentInstance().getExternalContext();
String userName = ectx.getUserPrincipal().getName();
System.out.println("Current user: " + userName);

ADF BC Application Module
       String userName = getUserPrincipalName();
System.out.println("Current user: " + userName);

A year already? So long incubator!

Adam Winer - Wed, 2007-05-23 16:20
How time flies... A year ago, we checked ADF Faces into the Apache incubator. Now, we're out of the incubator, we're named Trinidad, and we're officially part of the Apache MyFaces project. You can visit our site, and download nightly builds .

A lot has happened in this past year - a few highlights:
  • A bunch of new committers were added from inside Oracle and, most importantly, from outside Oracle
  • All the ins-and-outs of running an Apache project were ably handled by Matthias Wessendorf
  • Skinning functionality has gotten a lot better, mostly courtesy of Jeanne Waldman
  • Portlet support from Scott O'Bryan
  • Client-side validation now looks much better (no more JS alerts), from Danny Robinson
  • Lots and lots of bugs (300+) were put to ground
  • New components - a spinbox and an outputDocument
  • JSF 1.2 support was implemented (on a branch); the MyFaces implementation of the 1.2 JSF API uses a Trinidad plugin to generate components and tags.
  • And, I got engaged! (The future Mrs. even lets me get away with working on Trinidad at home.)

It all took awhile, and a lot of work, but we're all glad to have reached this point.

plan_table changes

Andrew Fraser - Wed, 2007-05-23 10:16

This page has been moved to http://andrewfraserdba.com/?p=43

Categories: DBA Blogs

Dataguard, documentation/scripts for non-DBAs during failover

Stephen Booth - Wed, 2007-05-23 06:37
We're looking at implementing Dataguard as part of an implementation of Documentum (a document management system from EMC) and I have been asked to look at producing documentation and scripts for non-DBA users to use during a failover. The actual failover of the database itself will be handled by our DBAs, this is for the sys admins, network admins, application admins &c who may need to do Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com1

Don’t mix glue and SQL

John Stegeman - Wed, 2007-05-23 06:20
This post isn’t really related to ADF in particular, but it is of benefit. I’ve seen a number of posts (well, OK 2 of them) on the Oracle JDeveloper forum in the past week where someone “glues” literals into their SQL statements like this:

String sqlStmt = “select x from y where username='” + userName + “‘”;

stmt = new PreparedStatement(sqlStmt, 0);


Now, anyone who reads Ask Tom is already falling out of their chair. The real problem is in the first line of code; first of all, imagine what happens if someone puts this string into userName: x’ or ‘1’ = ‘1

Can you say “SQL Injection?” The second problem with this approach is that for each value of userName, this generates a unique SQL statement, which Oracle has never seen before, and must hard parse. Hard parsing in Oracle, well in most any database, really, is an operation that takes lots of CPU and inherently limits scalability. If you run this query a lot with different values of userName, you’ll bring the system to it’s knees. What the query should do is use binds, like this:

String sqlStmt = “select x from y where username= :1”;

stmt = new PreparedStatement(sqlStmt, 0);

stmt.setString(1, userName);



Now, no matter what that pesky user puts in userName, this code does not expose the security risks as the first one. Additionally, the SQL is the same from call to call (it never changes) – therefore you don’t have the hard parsing problem, either. Now to make the code even better, we could cache the prepared statement and bind/execute on subsequent calls, but I’ll leave that one to you.

Oracle and .NET at Tech·Ed Orlando

Christian Shay - Tue, 2007-05-22 23:40
Oracle will once again have a major presence at Tech·Ed this year (June 4-8).

Be sure to stop by the Oracle booth on the demogrounds floor (aka "Partner Expo") for your own personalized demo of Oracle products. We will have demonstrations for Oracle and .NET, Grid Control, RAC, and App Server.

At the .NET demo station you can get the first look at the upcoming release of the ODAC 11g beta including the following new features:

Visual Studio 2005 integration with Server Explorer, Data Sources Window, Dataset Designer, TableAdapter Configuration Wizard and more
Improved ASP.NET web developer support
Oracle Database script project to provide source control of Oracle scripts
Integration with Query builder and Query Designer
User-Defined Types: Create, explore, modify UDTs and custom class code generation for .NET application
and much more.....

Instant Client Support: Smaller ODP.NET client installation
User-Defined Types: Map Oracle objects and collections to .NET custom types and support REFs to object types
and much more...

ASP.NET Providers


We have a "Bird of a Feather" session, which is not a formal presentation, but more of a discussion with Oracle staffers and your fellow Oracle and .NET developers and DBAs. Come prepared with questions and bring your laptop if you feel like turning it into an installfest!

BOF01: Using Oracle with .NET
Monday, June 4, 2007 at 10:30 AM
Room S331 A

We are also co-presenting a "chalk talk" with Microsoft:

DAT04-TLC - ADO.NET Entity Framework: Provider Model and Integration with Third-Party Databases
Friday, June 8 1:00 PM - 2:15 PM, Blue Theater 12

See you in Orlando!

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


Subscribe to Oracle FAQ aggregator