Grassroots Oracle

My name is Scott and I have a pet tiger

2012-01-27T16:55:00.000+08:00

It seems I drafted this one about 18 months ago, worth sharing...

Me (back in 2000):
"Hi, my name's Scott. I'm your new developer"

My First DBA:
"Here's your db account, just don't change your password to 'tiger'"

Well, it wasn't quite like that, but it was joked about.

While setting up the Apex Listener, using Tomcat - I came across this in the help.

What is the default login for the manager and admin app?

The admin and manager application do not provide a default login. Doing so is a security flaw. You need to edit $CATALINA_HOME/conf/tomcat-users.xml if you are using the default install.

Security is no laughing matter.

Scott

Compound primary keys in Application Express

2012-01-25T08:00:00.000+08:00

As many Apex developers are aware, there is a limitation on the number of columns in a compound primary key when creating forms out of the box.

This was alleviated in 4.1 by the ability to use ROWID to identify the record.

I was asked the question recently on how to get around it back in Apex 3.x
I gave some suggestions, but didn't have the opportunity to investigate at the time. Of course, I was really curious so I gave it a go myself when I had a chance - so here goes.

First I defined and populated a simple table with a primary key containing three columns.

CREATE TABLE three_col_pk
  (my_year   number
  ,my_month  number
  ,my_loc    varchar2(1)
  ,my_data   varchar2(20)
  ,CONSTRAINT my3_col_pk PRIMARY KEY (my_year, my_month, my_loc)
)
/

INSERT INTO three_col_pk values (2011, 12, 'A', 'Scott');
INSERT INTO three_col_pk values (2011, 12, 'B', 'Kylie');
INSERT INTO three_col_pk values (2011, 12, 'C', 'Penny');
INSERT INTO three_col_pk values (2011, 11, 'A', 'Branka');
INSERT INTO three_col_pk values (2010, 12, 'A', 'Chris');
INSERT INTO three_col_pk values (2011, 11, 'B', 'Eddie');
INSERT INTO three_col_pk values (2010, 12, 'C', 'Ray');
INSERT INTO three_col_pk values (2010, 12, 'B', 'Kate');
INSERT INTO three_col_pk values (2011, 10, 'C', 'Lynda');

If you try create a standard Report/Form combination with this table using the wizards you will receive this when attempting to fetch a record:
ORA-01422: exact fetch returns more than requested number of rows

And if you attempt to create a new record, you'll receive this complaint depending on the column that is excluded from the screen:
ORA-01400: cannot insert NULL into ("SAGE"."THREE_COL_PK"."MY_YEAR")

So to get around this problem, the first thing that came to mind was to create a view that contained the rowid and build a Report/Form combination on that. I figured there'd be a few problems arising from this, but one must start.

CREATE OR REPLACE VIEW three_col_pk_vw AS
SELECT rowid my_pk
      ,z.*
FROM three_col_pk z
/

This certainly fixed my Automatic Row Fetch process, which now looked like this:

However, when I tested an update using the Apex form I received this error

ORA-20505: Error in DML: p_rowid=AAAGsHAAFAAAGRvAAA, p_alt_rowid=MY_PK, p_rowid2=, p_alt_rowid2=. ORA-01733: virtual column not allowed here

When I saw this I thought - does the automatic row update also update the primary key?
I have been curious about this in the past so I thought good a time as any to test it out.

Tangent - for players of the PL/SQL Challenge, this was a topic of a recent quiz.

To determine if Apex includes the primary key in the update within the Automatic Row Processing - I created an even simpler table with a primary key and a before update trigger that only fired when the primary key column was updated.
(Does anyone else have trouble consistently remembering create trigger syntax?!)

CREATE TABLE pk_test
  (a_pk number
  ,b date
  ,CONSTRAINT pk_test_pk PRIMARY KEY (a_pk)
)
/

INSERT INTO pk_test VALUES (1, sysdate);

CREATE OR REPLACE TRIGGER pk_test_trg
BEFORE UPDATE OF a_pk ON pk_test
FOR EACH ROW
BEGIN
  INSERT INTO pk_test VALUES (0, sysdate);
END;
/

I needed some way of identifying if the trigger fired. I've always found the idea of mutating tables funny, so I thought I'd just attempt to insert into the triggering table.

Sure enough, when I tested an update using the Apex form I received this:

ORA-20505: Error in DML: p_rowid=1, p_alt_rowid=A_PK, p_rowid2=, p_alt_rowid2=. ORA-04091: table SAGE.PK_TEST is mutating, trigger/function may not see it ORA-06512: at "SAGE.PK_TEST_TRG", line 2 ORA-04088: error during execution of trigger 'SAGE.PK_TEST_TRG'

So a way around that is to remove the automatic row processing created by the wizard and replace it with my own code. For simplicity, I just used the following:

BEGIN
  INSERT INTO three_col_pk
    (my_year
    ,my_month
    ,my_loc
    ,my_data)
  VALUES
    (:P9_MY_YEAR
    ,:P9_MY_MONTH
    ,:P9_MY_LOC
    ,:P9_MY_DATA);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  UPDATE three_col_pk
  SET my_data    = :P9_MY_DATA
  WHERE my_year  = :P9_MY_YEAR
  AND   my_month = :P9_MY_MONTH
  AND   my_loc   = :P9_MY_LOC;
END;

A merge would probably be more appropriate, but I have an issue with merge - for another blog post. And I couldn't be bothered working it out when this rolls off the fingers.

Hope you found that interesting.

Mr Amazing, is gone...

2012-01-23T20:32:00.000+08:00

Random co-incidences happen all the time. Sometimes we notice them, other times we don't. Sometimes they are remembered for a lifetime.

Today I arrived at work to do some on-site training and noticed an ambulance roaring along the other way. The receptionist knows me well and informs me our friend Paul has been taken away to be resuscitated.

My friend, university classmate, four year work colleague, facebook socialite, lover of technology and lego, swing dance extraordinaire, Oracle... well he wrote SQL, bringer of smiles, puzzle solver, workaholic, traveler, magic master and extremely hirsute man... never woke up.

You may know him well from where he's been working since graduating from University. You may know him from chance encounters at the AUSOUG conference. You may know him from his swing dancing passion. You may be one of his 500+ facebook friends, alll of whom I'm sure he knew personally; or you may know him by one of his many other interests as he really did live life to the best he could.

It was a tough day for friends of Mr Amazing (known to everyone else as Paul Verevis), I'm sure everyone (including myself) appreciated the support provided by the wonderful team they nuture there - it was like an extended family.

You can find a little of his legacy on twitter, and he's most certainly trending on facebook.

And finally, I found this one of him and some photo-bomber in my e-mail archive. It was from our paint balling session in August 2005. I'm sure I have a few from our uni days somewhere, but I'll have to have a think about where.

Goodbye, mon ami.

ps - I'm pretty sure one of those adjectives of Paulie written above isn't true, I'm sure those who knew and worked with him will spot from a mile away which one ;-)

No doubt others have all sorts of words of their own to describe him

2012 - Preparation meets opportunity

2012-01-20T09:29:00.002+08:00

"Luck is what happens when preparation meets opportunity." - Seneca

This came on the back of a conversation today with a friend of mine, he sent me an e-mail with another quote from Seneca, a 1st century Roman philosopher - and as I started to write this post I thought it tied in well this post from Michael Crump that I found in my twitter feed - 11 Things every Software Developer should be doing in 2012.

I thought I'd add my mentations...

Get on Twitter - I did this in 2011. I was reluctant at first - another social media stream? Twitter, really? I was slow at first, but now I'm seeing it's value. Aside from the reasons Michael provided, it's a great networking tool.
Ready StackOverflow daily - I haven't been able to get into StackOverflow. I think I'm getting plenty of that information from other sources when I can. Certainly as a beginner I would recommend sites like that, but as the cynical "Dirt" commenter suggested - asking is good, answers - not so much. I can't say I'm fluent enough to know how much chaff is in the responses, but when I was learning I went to an authoritative source - AskTom.
Start a blog - I'm not sure if I'm prepared to do that ;-)
Get out there - I agree 100% Your local user group is a great start. Here in Australia, AUSOUG holds regular events in many states. In WA we try to have a seminar once a month, and we have our annual conference series. Networking gold - plus you learn stuff alone the way and get free beer & pizza ;-)
Carry around a modern phone - While I don't agree with the judgement of people based on what they own, use or wear, it is certainly a useful device in many ways - and just another way to help stay in touch with the industry.
Embrace mobile - His comments about every developer will create a mobile app - really? I don't want to sound lacking in foresight, but surely with the vast array of programming languages around (pardon the pun), many programmers will be successful without going near a contemporary phone. That being said, in the Oracle world - 2012 will be the year of the mobile - think about what's coming in Apex 4.2
Learn at least one design pattern - I'm not sure how applicable this is to the Oracle world. Instead, perhaps study database design a little closer.
Set reachable goals each and every year - Goal setting is a big can of worms. True, I set myself goals, but I don't beat myself up if something gets in the way. Last year I aimed to learn Ubuntu, that's been very slow going. However, along the way I've taken grasp of many other opportunities. And I might look at it more this year, along with plug-ins and mobile.
Learn a different programming language - This could be tough, however I encountered Code Academy recently, and if I get into gear I might use it to become more proficient with JavaScript.
Boost your confidence - I agree with "Dirt" - no brainer. Trouble is, for some it isn't. I was adolescent once battling depression, and it doesn't necessarily come easy. The first time I changed employer I wasn't confident either - just keep your eyes open and be aware.
Read blogs/programming books/magazines - Highly recommended. I have a collection of RSS feeds now, or you could just visit aggregators such as apexblogs.info or orana.info. I have a few programming books on the go, including technical reviews which are win-win. And surely you've subscribed to the Oracle magazine in some form? Don't limit yourself to Oracle or programming sources either. I have many feeds related to my science based interests, and I learn writing skills from them also - besides they can be fun to read.

What do you reckon? Any other big ones not listed?

Scott

And no, the world will not end this year.

DB minimum version with Apex

2012-01-18T09:47:00.000+08:00

(I'm doing a blogging blitz, scheduling posts, reviewing old drafts etc. I'm not sure why I didn't publish this one, I thought I'd just post it untouched)

There are suggestions that Apex 4.1, currently in Early Adopter at tryapexnow.com - may be available for download middish 2011 (I don't like saying summer/winter, because that ignores half the world!)

Some sites are still back in Apex 3.x, shame, but unfortunately I still work for some of those. Hopefully upgrading Apex won't be as daunting and restrictive for most as upgrading db version can be.

Recently I wanted/needed to experiment with some infrastructure settings, so I installed a copy of 10g I had on hand, with the intent of installing Apex 4.0

Unfortunately amongst all the installation tasks, the one I didn't bother to take notice of this time around was 2.1 - Oracle Application Express Installation Requirements: Oracle Application Express version 4.0 requires an Oracle database (Enterprise Edition, Standard Edition or Standard Edition One) that is release 10.2.0.3 or higher. Application Express 4.0 can also be used with Oracle Database 10g Express

So of course, the version I had was 10.2.0.1, not 10.2.0.3

So when you go to log into Apex, you may see something like this. Symptoms also include errors in your FLOWS schema. Something I found interesting about this particular entry was the script Joel used to determine version. He could have used dbms_utility.db_version, v$version, instead he used conditional compilation.

create or replace procedure foo as
begin
$if dbms_db_version.ver_le_10_2 $then
dbms_output.put_line('dbms_db_version.ver_le_10_2');
$elsif dbms_db_version.ver_le_11_1 $then
dbms_output.put_line('dbms_db_version.ver_le_11_1');
$else
dbms_output.put_line('else');
$end
end foo;
/

Ultimately you will need patch 5337014 to step up to 10.2.0.3, run apxremov, then reinstall from that point - not forgetting to have your apex_public_user account ready.

Scott

2011 Blog Review

2012-01-13T01:30:00.000+08:00

I ventured into google analytics the other day to check out some browser stats, the general result is positive, by the way - 42% Firefox, 27% Chrome & 20% IE, Safari only down at 4%.

But what I thought I'd do is have a look at last years stats - it seems to be the thing to do this time of year...

Off the bat I'm happy with over 50% increase in page views compared to 2010. I don't care so much about specific numbers, but it's good to know whatever I'm doing is expanding. I'm quite motivated to write more often this year - I already have a bunch scheduled - once a week every Wednesday is the aim. Plus any random stuff I might put up, usually on a lighter note on Friday.

I'm happy I had the motivation one week while on the road to re-design the blog to bring it into this decade. It looks less tacky and a little bit more thought out. There's still a few little things I want to touch up, but it will do for now. Other projects on the go first...

As for what people are reading, in 2010 here are the top 5 pages (behind root)

Short circuit evaluations - I promoted this topic a bit, I'm a big fan of COALESCE
Another Apex 4.0 feature for Forms - Tree view in Apex 4.0
Why I use Google Chrome for Oracle Apex - because it's awesome and light!
Using JQuery dialog boxes - Very handy
Automating Apex backups from DB - backup backup backup

In 2011 we moved along to more focused pages

Presentations - I think my Apex Security one bumped those stats up
A mash of Oracle related thoughts - lots of distinct topics, looking back
Automating Apex backups from DB - up from fifth, good work backing up!
Useful Oracle Apex URLs - Popular with those learning
Apex in Amazon Cloud - Listen out this year, I'll probably be doing a cloud implementation in 2012 and I'll be sure to blog about it - and probably do a prezi in October.

Most weren't hugely out of the ball park, although last year 1st had 50% more hits than 5th, but it does show what people specifically read I guess, as opposed to strolling past on apexblogs.info

Most visitors are from US by far, Australia distant second, then UK, India, Germany just pipping Netherlands. Canada-Russia equal, then drops away.

Top mobile devices - iPhone, iPad, (not set), Samsung Galaxy S (probably me ;-), HTC Sappire

Windows order magnitude more than Mac, then Linux.

And finally top search terms - all fairly obvious. "Oracle" three times more than "oracle apex jquery datepicker"; some not provided; "triangle circle square", "oracle apex", "apex amazon cloud", "apex post calculation computation", "apex login page", "oracle apex 4.2"

The last one is interesting since it I published my wish list in early Jan, but accidentally published it as December when I first drafted it. It falls in the 2011/12 archive - so I wonder how that gets counted in their queries ;-)

There are no major milestones to come, I haven't reached 200 posts yet, so in time... I'm glad I started.

I'll have to remember to do this next year and compare!

Photo by Robbert van der Steeg - check this one out!

Apex performance issues with v()

2012-01-11T08:00:00.000+08:00

One of the checks in the Apex Advisor is to determine if any instances of the v() function exist within your SQL.

This confirms one of the first Apex best practices I remember hearing, that you should use bind variables instead of v() within your SQL.
It makes sense, since v() is essentially making an interrogation on the session state table (wwv_flow_data).

Patrick Wolf talks more about performance with determinism here.

In the comment thread Patrick also suggests the wonderful scalar subquery solution that works on all db versions
WHERE id = (SELECT v('MY_ITEM') FROM DUAL)

The trouble is, I think it's a difficult situation to test - how would you go about determining how many times Oracle decides to invoke the v() function, and for which parameters?

I came across this case was on a 10.2.0.4 db with Apex 3.1.2. I've added Groucho glasses to the query to protect the innocent/guilty.
It's also somewhat simplified. There were half a dozen instances of v(), but I've highlighted what turned out to be the offending line.

select * from a_6000_row_table p
where (  ...
or exists
  (select null
   from a_user_access_table aa
   where (aa.org_unit in (SELECT * FROM TABLE (InList_fn(a_pkg.genInList(p.year, p.org_unit))))
       or aa.org_unit in (SELECT * FROM TABLE (InList_fn(a_pkg.genDiffInList(p.year, p.id)))))
  and aa.inactive is null
  and aa.staff_id = v('LOCAL_USER_ID')
  and (  (aa.access_type in ('ABC','ABCXYZ'))
   or (   aa.access_type in ('DEF')
      and p.latest_status = 'YIPEE'))
  )

As it turns out line 9 wasn't alone in causing the query to take at least 5 seconds to return 1-10 of 6000 in an interactive report.
The first part of the where clause where it constructs a comma delimited list, converts it into a nested table, then interpreted as a table - also lends some form of hand in slowing down the query.

These function calls could probably be replaced with some form of WHERE EXISTS, but I think the original author had a steel plated encapsulation hat.

When first assessing this query, I thought the problem related to a stragg function in the column list which used a FOR-SELECT-LOOP, but then I spotted the obvious.
So I replaced all the v() references with bind variables, and all of a sudden the 5+ second query went sub one second.

I felt a little social, so I tweeted my little win, and Trent asked if I had more details, and I was curious to isolate the exact improvement as when I've briefly played with performance using v() functions on simple queries I came away unconvinced of any issues but happy to play it safe.

Some tests by themselves

Removing the IN list functions got the query to around 0.7s
Changing v() to a bind variable was around 1s
Changing v() to scalar subquery also gave a 1s query.
Setting entire query to use bind variables got down to about 0.5s

For those tuning experts out there who know much more about the Oracle mechanics than I do, I'd be curious to know if you can shed some light on how/why in particular these improvements came about.
For instance, what do you suppose the relationship between the IN list functions and the use of v() is?

For reference - I squeezed in a function call at line 10 that counted how many times it was called. It was always 6000 times, regardless of bind variable usage until I took out the call to the IN list, then it was 9 records. I didn't isolate the significance of that second number.

I remember when learning Apex it was difficult to determine when to use what syntax to refer to values in session state, so two lessons affirmed here today:

Bind variables work! Limit the use of v() function calls to assignments and conditions in PL/SQL packages
Scalar subqueries are just as awesome.

And I'll just add two more related points

Never (never say never) use &ITEM. syntax in queries - I've got a post in the works on that issue.
Don't forget about the nv() function, allowing you to compare the same datatype if you're dealing with numbers. You need to make your own dv function...

Scott

First events of 2012

2012-01-09T12:34:00.001+08:00

Today is the true new year for many, and Sage Computing Services would like to announce it's first training event for the year open for enrolment.

JDeveloper Workshop
27th February to 2nd March
Perth CBD
Taught by Oracle Ace & Educator of the year - Penny Cookson
Workshop details

If you can't make that, keep the morning of the 29th Feb free. If things haven't changed on me over the new year, AUSOUG will be sending out details of a breakfast at the West Perth Oracle building by one of our favourites - Connor McDonald.

If you're interested in any of our courses, feel free to drop us a line and we can schedule a time frame that suits you.

Scott

Friday Fun: The SAGE Green Pig

2012-01-06T09:41:00.000+08:00

The humble pig. Yep, it's been a while since the Perth conference, but for those of you who were there no doubt you'll remember the Sage Computing Services Green Piggies!

We've set up a little webpage containing a few of our favourite photos of piggies doing some interesting things.

If you happened to have pigged up a pig - take a photo of it doing something silly or useful and mail it through - Penny might decide it's G-rated enough to post on the page!

Scott

My Apex 4.2 wish list

2012-01-04T17:54:00.000+08:00

I was going to start the new year with something a little more light-hearted, but this has been sitting as a draft for a while and I thought I better not hold onto it for too long.

As I'm sure many people do, while developing with the Apex tool, I occasionally come across improvements that I would love to see in a future version of Apex.

And, as I'm sure many people do, I keep meaning to note these little feature improvements down so I can provide feedback to the Apex development team.

Recently I managed to pull my finger out and actually compile a list - some training recently has highlighted some of these features so I made sure I took note when I could.

Some of them are really minor - aesthetic or workflow adjustments that would facilitate more fluid development. Others are probably a touch more complicated... I think most are for the benefit of the developer more than the end user.
Some probably need more thinking over, and some I may have gotten wrong and there isn't a problem anyway - feel free to correct me where I'm wrong. I'm also going to steer clear of any mobile development issues. Here we go...

Minor - little improvements I don't think would take much effort:

Navigation bar entry subscriptions - currently when you attempt to define a subscription for navigation bar entries, the list of values displays the alt image text - not the navigation bar label.
I don't have too many entries with this optional field completed, so it becomes annoying.
Hide quick picks on read only - it would be nice to have a declarative feature that facilitates this - possibly turned on by default.
Currently, if you conditionally set an item to read only, any quick picks are still present and clickable - hence updating the field.
Display date/time in form as read only: I didn't investigate this one in depth - a last minute discovery in v3), but short of changing application date format to date/time - I had a strangely difficult time presenting a date column in a form page as read only with full date/time - I often received ERR-1079 Error in item post calculation computation.
Feel free to direct me to an example of how this done, it wasn't a showstopper so I moved on.

Intermediate - slightly bigger, more involved improvements, but some of them may be easily achieved:

Dynamic quick picks - this is a nice to have as we can fairly easily provide this using shortcuts (such weird terminology!)
However since we have the option to define static quick picks for an item, why not be able to alternatively provide a query to dynamically supply the quick pick options, or associate an LOV shared component.
API for page/item help - it is so often requested to facilitate the user to enter their own page level or even item level help. This requires it's own set of tables, template modification, supporting pages etc.
If we had an API to the Apex core to update these values, all we'd need are pages to allow the user to read/update the help text so we can retain out-of-the-box functionality.
View consolidating all comments - I originally thought this would be handy when playing around with the Forms migration tool. The annotations were great, but I thought how much handier would it be to be able to query one view to scan for relevant annotations.
Ditto (and more-so) for component comments. We have an integrated page level comments that are actively visible to the developer, but as component comments can very easily be lost, ignored, forgotten about - I think it would be useful to run a query over any comments found in any of the application components.
Greater usage of page alias - I think the use of a page alias instead of it's number should be more welcome within the development environment. This could apply to anywhere the link attributes are presented. This may also improve accuracy for users who bookmark pages within the application.
Hierarchical validations - This was the first term I could think of. Currently we can tentatively apply conditions on validations based on failures of prior validations in the sequence using apex_application.g_inline_validation_error_cnt
I think if we were able to define some form of hierarchy for validations, just like breadcrumbs or parent regions, this would make a lot of Apex developers very happy.
I had a quick search for Apex wish lists before posting this, and Dan McGhan used the term validation chaining here.
Validating file uploads - recently I needed to validate a file loaded using the File Browse widget. These validations included file size (we had a small limit, thanks to Report Queries) & mime type.
I got there in the end, but it didn't seem very elegant. I'm not sure how easy it would be to provide settings for upper/lower bounds similar to those that numeric/date fields have.
Minimum date setting - relax the format restriction on the date when using substitution syntax.
Report Layout update process - currently if you need to update a report layout, you need to delete the layout record; re-upload the layout document; then re-associate the layout with any Report Queries.
These are currently managed in separate areas of the Shared Components section, and if I remember correctly it takes 20+ clicks to achieve this process for one report. I jumped with glee when I found out recently I needed to update 50 layouts for 57 reports... not. It can't be too hard to re-jig these maintenance pages to allow a better workflow.
Dictionary views for Report Layouts/Queries - are these the only components without associated dictionary views? I thought I'd be able to whip up a query to see which Report Queries used the same Report Layouts, but alas... I had to come up with a fresh technique.
I got there in the end, and my update process for those 57 reports was impressively efficient, but both these improvements would make life easier.
Form limits - my colleague wanted me to include extending the number of items per page from 100. She encountered the limit recently, but I'm not sure the context -
Multiple item copy - my colleague also would like the ability to copy more than one item at a time.
Sequencing of next / previous buttons - when using the "<" and ">" buttons, ie - next / previous to scroll through Regions / Buttons / Branches etc, there seems to be no discernible order. If there is method to the madness, I haven't yet identified it. It should follow the sequence order. If that's what it's supposed to do, it's not evident in the behaviour.

Major - probably require some form of Apex core infrastructure changes:

Build option enhancement - currently we assign a build option to a given component. This is great if it's new - but often there are minor tweaks on various attributes that we'd like to incorporate into a build. I find this even with template modifications. There are a few annoying workarounds, but I was trying to work out if it would be possible to incorporate a change to the Apex development environment.
What came to mind (without thinking too hard about the underlying core structure - but I don't think it's too far fetched) is instead of (or in addition to) being able to allocate a build option to a component, it would be great to be able to have a select list perhaps on the side-bar, or near the "apply changes" button that would allow the developer to snapshot those changes against a given build.
From another angle, when the developer is reviewing attributes for a component, they could view the current version (by default), or use the option to refresh the view for a given build option.
That seems an decent scenario for the Apex developer - but as I said, I haven't yet put thought to the implications is would have on how Apex would manage this meta-data, and possibly more curious - how it would decide what to render.
Perhaps the build option maintenance needs to be expanded to define chronology - and/or precedence.
Saved IR reports retention - While I have not had too many problems in the environments I've been working in, it's definitely an area that needs attention, and the Apex product managers are aware of it. The basic premise of my issue with it is some life-cycle plans rotate through application IDs - this means there needs to be a method of transferring saved reports in production from one application to another. The export process leans towards the idea of migrating saved reports from one workspace to another - which to me seems limited as saved reports are typically not going to be in your dev/test workspaces.
Expand XML size for Report Queries - this was the cause for my file upload validation request. If you would like to send an image to a BI publisher report via a report query, you are currently limited to a files size of approximately 22k. After a little chat with Tim Dexter, and a lot of trawling through OTN - it seems there is a limitation of 32k per "record" sent via XML.
I can't remember where I put the sole forum reference, but after a quick search I found this 2008 posting by Marc Sewtz acknowledging the limitation and promising an update. It seems this limitation may affect charts also. I did attempt to use the print API instead as it seemed like a great alternative, however I came across what seems like some internal (Oracle end) scripting issues and I ran out of patience to persist.
More declarative options for charts - I'm not a heavy chart user, but it seems the AnyChart infrastructure offers more customisations than the declarative nature of Apex allows. For instance - the graduated colouring on dial charts, and adding links for the same.
Don't get me wrong, it's fantastic that it's possible to customise the XML sent to the engine, but I wonder how much work it might be to add context sensitive settings for the chosen chart style - even if it's done in a flexible manner similar to user-defined attribute pairs for Feedback.

Added since initial posting from comments / forum post - limited to only those I like
Jeff (2012/1/5) - New substitution strings such as APP_PAGE_ALIAS - and variables to return previous page details.
Scott (2012/1/5) - New component in apex_application_install to accept clob that defines an exported Apex application
Trent (2012/1/5) - Subscription module to team management
Scott (2012/1/6) - Install supporting objects (stored as installation scripts) when running app install as script
Wes (2012/1/7) - Filter objects in page definition by conditions; more options with search
Andy 2012/1/7) - Declarative options for sub-totals in reports
Matt (2012/1/12) - region/page level read only; row level cascading lov; extended APIs for tabular forms
Greg (2012/1/15 - Documentation! - this is my favourite. I'm a huge fan of the db documentation, and the Apex documentation is still in its infancy. This includes item help - some are awesome and concise, some are not so much - typos in the help don't help the cause.

Oh, and please revert back to 10g style doco - I hate the new 11g doco with the "expand all" option, and the local doco that attempts to hit the Oracle website - I need to configure an extension to stop that...
Kofi (2012/1/13) - declarative popups
Sas (2012/1/13) - alternate lov source for item
Andy (2012/1/20) - Session expiry notificication

No doubt the Apex development team already have their 4.2 scope fairly set for an early 2012 release thanks to the JQuery mobile hype, but I'm sure there will be time to slot a few of these in if not target already - otherwise I can wait for 4.3 ;-)

Happy new year, everyone!

ScottWe

2012 AUSOUG Conference dates

2011-12-20T11:05:00.000+08:00

Get your diaries out - the dates and locations for next year's conference series in Australia have been formally announced.

Melbourne
InSync12 Conference and Exhibition
August 21 and 22
The Sebel Conference Centre, Melbourne

This is a short tram ride from the heart of Melbourne, with stunning views of Albert Park - where they host the Grand Prix Formula 1.

Perth
Oracle with 20:20 Foresight Conference and Exhibition
October 29 and 30
Hilton Hotel, Perth

This is in the very centre of Perth, walking distance to bus, train, ferry & many other pubs & eateries.

The question is, will we see you in Australia next year?

Scott

Blog re-name & re-design (round 1)

2011-12-14T22:14:00.000+08:00

Some of the more astute of you - or those that may have taken notice to the final "slide" of my Apex security presentation at the AUSOUG conference - may have noticed that I registered a new name for my blog.

There were a few reasons for this. Initially it took some time for me to decide on a name when I decided to start blogging, and in hindsight I thought it needed a re-jig. (my personal gmail account could probably do with the same adjustment)

I also thought that after getting into the groove of blogging, it was time to select a name that suited my particular style and removed the blogspot moniker.

So I've chosen the following:

grassroots-oracle.com

I've updated the about me page to describe this choice a little further. You may also notice I've got some other tabs across the top for common information you might like to view - using bloggers ability to create Pages for this very purpose. Feel free to check them out.

For those of you visiting my actual page as opposed to viewing through an RSS feed or aggregator, you may also notice the blog design has changed dramatically.

I've finally taken advantage of some of the new tools blogger provides. I don't think I've finished yet though. One reason - I'm on the road, it's really late, and I'm using my phone tether to work on the net.
The other reason - I like to sit on things for a while to see how a like it, and see if any other ideas come up. I'm also not quite happy with the colour scheme - I'm a little spoilt for choice and I wasn't quite ready to perform those finer adjustments.

I also think there may be some other ramifications from my template of choice - I notice my favicon has disappeared, and it seems I need to re-apply my syntax highlighting. Ahh, the life of a software developer...

And if you're wondering about the background image in the logo - I took that in my backyard using my smartphone, plus a little trickery - no software involved, except of course adding the text.

I hope you like the adjustments so far. If you have any tips or ideas, feel free to let me know.

A retrospective

2011-12-13T00:01:00.000+08:00

Throughout our contemporary world there are many references to common denominations of time - and they all revolve around the same thing (pardon the pun).

Anniversaries of birthdays and various events are designated by a date in the year. These are also accompanied by decennaries, centennials, etc..

The moon, such an important part of our existence is also used to denote periods of time, albeit sometimes with less exactness.

So I thought why not use another cycle to mark an occasion, and look back over the years... that and I am basically a year late for the decennary.

Our planet experiences a solar cycle of about 11 years. It's still not completely understood why, and the various effects are still being studied - but after an extended dormancy our sun is starting to flare up (another pun!) and instigate various activity here on Earth.

My rambling aside - today marks 11 years since I got my first full-time job thanks to my university degree.

I never pro-actively sought after a job working with the Oracle product - in fact, my first job offer was in Sydney to work on the Collins class submarines using the Ada language - which was the teaching language of choice at ECU at the time.

So with much thought I declined that offer, deciding to stay in Perth - taking a job in a suburb many miles/kilometers from my home... using Oracle, and I hit the ground running.

Fortunately my first employer was often on the bleeding edge when it came to Oracle technology, so I learned a lot in a short amount of time.

With a little hesitation after almost 5 years I left my first employer so I could start exploring other industries (and perhaps work in town, instead of an industrial suburb well out of my way)

Wind forward to today, and I've touched on Forms, Reports, Designer, Portal, Discoverer, EBS, mod plsql, and a seemingly longish gap to Application Express.

I think more importantly, however, I've been active in my local user group. I think when it comes to advancing my career, this has been one of the most important aspects.

Participating in user group events allowed me to meet & greet others in my community, in hindsight allowing me to maintain consistent contact with my future employer (Sage). Ultimately I started presenting papers of my own, rewarding me with a best paper award at this year's AUSOUG conference.

It's also opened a number of other doors, almost like a positive feedback loop. I've reviewed a number of new Oracle books from Packt Publishing; and I've been a technical reviewer in a number of interesting Oracle books from Apress. This is another part of the feedback loop, since I'm learning & reasserting skills while reviewing books I'd want to read anyway.

As for the future? Well, from a technology perspective I think in due time my lovely employer Penny would like me to learn JDeveloper, but in the nearer future I'm more than happy to continue exploring and improving my Oracle Apex skills.

I will certainly be continuing my extra curricular activities. Amongst other tasks, soon I hope to co-author some articles with Penny; I have some more ideas for fresh presentations using the Prezi tool - limiting death by powerpoint!; I hope to finally redesign this blog and "officially" launch the new name, but that may be competing with our yearn to translate our Sage website into Apex - which will offer many new opportunities. I've just got to make sure I don't destroy the wonderful SEO work Chris Muir has put into the static HTML version.

Ultimately, it would be great to start participating in some of the bigger events around the world - UKOUG & ODTUG's Kaleidoscope, for example. It's just a little shame we're here in the most isolated capital city in the world... but it's worth it - I love living here ;-)

Friday Fun - I, Robot

2011-11-25T12:01:00.000+08:00

DECLARE
There is a variety of comics in my RSS feeds, not too many - but what I think are some quality selections ;-)
IF you're interested THEN
I can send a list;

BEGIN
-- The timing is impeccable, but I think this calendar is an awesome purchase for anyone, any time - thought I'd share.
safe_link := 'http://www.lulu.com/product/calendar/calamities-of-nature-2012-calendar/18623636'
READ_IMAGE_FILE(

);

I particularly like July - the second one reminds me of an episode of Family Guy.

Another favourite of mine => I'm a large;

EXCEPTION
Ooh, and check out what my sister does;
-- you might have seen it on penny's laptop at the AUSOUG conference
END;
/

SP2-0079: Procedure created with compilation warnings

SW_INTERNAL> SHOW ERRORS
Errors for PROCEDURE SCOTT_WESLEY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-06009: procedure "SCOTT_WESLEY" has no coherance or distinguishable humour.

SW_INTERNAL>

Wake up, Neo.

Apex e-mail lag

2011-11-23T10:30:00.000+08:00

When you submit e-mail as a process in Oracle Application Express, or using the Apex_Mail API in your own PL/SQL, you are essentially first submitting it to a queue - it won't be e-mailed immediately.

I have seen this minor fact trip a few people when wondering where/why their mail hasn't been delivered.

Prior to Apex 4.x, you would need to run the following SQL to find out details about the background job that manages the Apex mail queue:

select next_date, broken, what
from dba_jobs
where what like 'wwv_flow_mail%';

Apex 4.x brings us into 21st Century Oracle by using the scheduler module instead:

select job_action, next_run_date, last_run_duration, state
from dba_scheduler_jobs
where job_name = 'ORACLE_APEX_MAIL_QUEUE';

So this also means you have a number of options to push the mail queue. You can use the API mentioned ealier; call dbms_scheduler.run_job; or log in to the INTERNAL workspace and Manage Instance -> Mail Queue -> "Send all mail"

You might also be interested in the following Apex dictionary views:

APEX_MAIL_LOG

APEX_MAIL_QUEUE

And if you're looking at setting up an email server on your swiss laptop (the laptop that does everything - yes, I just invented that term), perhaps start here.

That is all,

Scott

AUSOUG EOY Announcements

2011-11-22T12:01:00.000+08:00

Here are some announcements from the Australian Oracle User Group (AUSOUG), near verbatim with some personal commentary ;-)

WA AUSOUG Xmas Function - Lawn Bowling!!

All AUSOUG members are welcome to a fun filled afternoon of teams, prizes, drinks, nibbles, networking...and lots more.
Where: Perth & Tattersall’s Bowling and Recreation Club in East Perth
When: Wednesday, December 07, 2011 04:00pm - 07:00pm
RSVP: By Monday 05th December 5pm by emailing vicepresident.wa@ausoug.org.au

This is available to all our AUSOUG members - take advantage of your membership & come join us for a bit of fun.

If you're a grinch like me, don't worry - it's never too Xmas-y ;-) I find I can skip past the festive components...

AUSOUG WA Welcome New Presenters

AUSOUG are running a program to encourage new presenters. We are getting a group together of newbie presenters with a view to developing and refining a presentation for an AUSOUG meeting and then for the conference. If you are terrified of presenting but would like to give it a try in a group of similar minded people then contact Penny. We will be providing assistance and encouragement in determining the best presentation for you and developing your presentation techniques.

If you participated in the panel discussion in this year's conference, you may recall this being addressed. New presenters have always been encouraged and mentored in some form, we'd just like to remind everyone of the support you would receive if you would like to take the plunge.

When I first started, I had some seasoned presenters more than happy to review my ideas and draft slide decks - even listen to my first practice if I wanted.

AUSOUG AGM

The 2011 Annual General Meeting of the Australian Oracle User Group Limited (AUSOUG) will be held at 4:30 p.m. on Wednesday November 23 at the Crowne Plaza, 16 Hindmarsh Square, Adelaide, preceding the SA Branch Christmas Party. The meeting will also be web cast for members who are not able to attend in person.
All documentation presented at the AGM will be available on the website soon after the event.
Please send any agenda items to secretary@ausoug.org.au.

To register for the web cast, head here.

The AGM isn't just for committee members, it's a forum available to all members of the user group, enabling everyone to have a say in the direction of the group. Please feel welcome to join, or contribute by providing feedback for discussion. We'd love to hear from you - but get your comments in soon!

Scott

Apex 4.2 Statement of Direction

2011-11-18T14:12:00.000+08:00

At the end of September the Oracle Apex development team updated the statement of direction for Application Express 4.2

The list is somewhat smaller than the SOD for 4.1, but it seems like it will be a release that will tie off some loose ends, perhaps allowing the development team to think of grander plans for 5 - or is that wishful thinking?
No, I wouldn't be surprised if there is a 4.3 and maybe a 4.4 first...

Not forgetting the fact that version numbers are probably quite arbitrary and assist marketing more than being a true reflection of functional content. (There are no hard facts here, in fact I couldn't find the article I was going to link to suggesting this notion...)

As for the points in the SOD:

Mobile applications - the only delay in this being included in 4.1 was Oracle waiting for jQuery Mobile to be production ready (announcement of readiness made today!) - not Apex. This is a switch waiting to be flicked.
I saw someone recently suggest 4.2 will be released in first half of 2012 - but do you think I can remember which particular social media mentioned that?
Charts - I'm not currently a heavy user of charts, but I'm sure the newer HTML 5 features will be a dream for many. I wonder though if the Apex team will add more declarative options for the AnyChart features - instead of forcing the developer to rely on customising the XML.
Web services - I'm not currently a heavy user of web services, either. But I know many clients who would love a more declarative & secure nature to web services within Apex.
Application Deployment - to me, this is what desperately needs the most attention in Apex. I have some thoughts on this that I may share in a future post.
Functional/performance improvements - aren't there always? I must say I like discovering the random little features dotted around the development environment.
And I simply won't say no to even better performance.

If the Apex line-up in KScope 12 is any indication, I think next year is going to be when I really get into mobile programming with Apex.

Scott

Apex matures => sequence usage

2011-11-18T08:00:00.000+08:00

Of course many of the new features that come with an Oracle product are well documented. In fact, it's not a bad idea to read through the entire release notes for every Apex release because they also include things like changed behaviour.

Some changes in Apex 4.1 are that minor that you wouldn't even notice them until you fall over it.

Something I found recently was the code generated when choosing to source your primary key from a sequence when building a form.

Previously, the code was a little ugly:

declare
  function get_pk return varchar2
  is
  begin
    for c1 in (select EMP_SEQ.nextval next_val
               from dual)
    loop
        return c1.next_val;
    end loop;
  end;
begin
  :P6_EMPNO := get_pk;
end;

Now it's somewhat simpler:

begin
    if :P6_EMPNO is null then
        select "EMP_SEQ".nextval
          into :P6_EMPNO
          from dual;
    end if;
end;

What they haven't done, however, is clean up the custom function example - and this is a little more noticable.

I noted this in EA for 4.0 - I think they said they've logged the change, but it still hasn't come along. While it's not really promoting this sort of code, it doesn't really have a place anywhere except perhaps an article talking about how bad it is for concurrent user environments.

Perhaps sequence usage will change with 12c?

Idle musings... Scott

AUSOUG 2011 Conference Review

2011-11-08T12:24:00.002+08:00

I think this year was successful. We had a superb line up this year, which inherently meant there were a few clashes on the schedule - especially for the DBAs. My apologies, some of those were hard to avoid due to many changes that were necessary in the final weeks before the conference. (thanks Qantas ;-)

My first year experience as the Program Chair was interesting, and I'm more than happy to take on the job next year. If anyone has any feedback they'd like to provide on the schedule, the conference, or the user group in general - we welcome & encourage all thoughts or suggestions.
In fact, every month we hold a committee meeting, and there is an AGM on Nov 23 that all are welcome to attend - even by webinar. Alternatively, you can send any agenda items to Tony.

We had some issues that were responded to well during the conference, and many others have already been addressed in a committee face-to-face over the weekend. Hopefully we can shake things up a little next year.

Visit here for my small photo gallery. I'm sure Ailsa will have some more professional shots available soon.

As for the sessions I attended:

Day 1
Us exhibitors started early. Too early. Well, my stomach was confused and someone was handing out these t-shirts with SAGE written in big white letters. I listened to Martin & Ian's intro, then went back to help prepare for the next two days.

Chris Muir - Ultimate Sales Pitch for ADF - While I had seem something similar during the Sage JDeveloper half day, I thought I'd listen to the skinny on why people should choose ADF. I must say, it was a pretty good sales pitch, but it made me feel like a Cobol programmer that desparately needs to update their skills!

Gabriel Ilarda OBIEE / Ray Tindall WLS - I started with Gabriel partially because I've been encouraging him to present each year. It seemed his pairing with colleague Dale had him under good control, and it looked pretty good. I had some things to do, so I snuck out and also caught a bit of Ray's look at Web Logic Server. It was going over my head, but he had a captive audience and apparently it was well received.

Alwyn Woo - OLM - Alwyn's another local I've encouraged to present. It was his first presentation and had hintered preparation, so I was impressed to see a clear, well thought out presentation. I asked a random question half way through about remote queries and he responded well.

Jeff Kemp - Simple ETL - I've worked with Jeff before on a project that involved some heavy ETL, and I was curious to hear what he had to see. It ended up being an interesting case study on a ETL project that was relatively small, but Jeff still had plenty of information to share. I was expecting something a little more technical, but to his credit Jeff did point out at the start what the deal was. Tom Kyte would have been impressed - I saw one SQL statement - a merge - that had plenty of good business logic going on. Golden rule - do as many things as possible in SQL - check!

Graham Wood - Oracle Hidden Features - One of many clashes due to such a great line-up. Unfortunately I skipped Penny's winning show - figured I'd seen enough ADF today by going to Chris' presentation earlier, and after seeing Graham in Sydney earlier in the year I was curious to see what sort of features he'd be talking about.

Day 2
My third morning with the alarm at 5am, after a few weeks of burning too much candle on both ends - this time I somehow managed to ignore it. So I missed the Introduction to the ACE program. Instead, after arriving with 10 minutes to spare, I had to find Penny to borrow her laptop (I planned to travel light), find Connor to borrow his pointer (I left my dongle in my laptop at home) and find a Burswood stage-hand (my room must have been 30C!)

Scott Wesley - Apex 4.1 Security - what an awesome presentation ;-) Actually, my run through the night before led me to believe it was going to go pretty well. On the day though I must say I had no idea how many people attended, let alone who was in the audience. Usually I'm reasonably aware of this, but my blinkers were on pretty hard! So thank you for those who voted positively... the new style was (apparently) a winner, and I think I'll stick with it when the topic or content will suit.

Mark Lancaster - 4.1 Minor upgrade or not - Given the conference lead up, I really haven't had a chance to play much with Apex 4.1 (beyond the security features), so seeing Mark cover the differences and features at the level he did was excellent.

Frank Nimphius - Adding Mobile & Web 2.0 UIs to Existing Applications - The Fusion Way - given the mass amount of Fusion content, the schedule adjustments, and my feeling that mobile UI is only going to get more important - I figured I'd check Frank's session out. He's a good speaker, but at this point I had hit a wall so I was struggling to concentrate. I believe Eddie spotted me blinking...

Panel Discussion (User Groups) - I think this was a success. We were always going to be struggling for time, but Vinod kept a tight ship. We had a good discussion with decent participation from the audience. I think we should open up topic ideas for next year. Yury has this session recorded, so stay tuned for visibility.

Chris Muir - Angels in the Architecture - I had seen Graham talk Exadata in Sydney at length, so I skipped Graham to watch Chris talk ADF blueprints. I currently lack the ADF skill to really comprehend what Chris was trying to illustrate, but he did it really well and I could see the rest of the audience was loving it.

Connor McDonald - A year in purgatory - diary of 11.2 RAC upgrade - I find Connor never disappoints, and while his presentation this year wasn't as technical as usual, his tale of hell enthralled the room. He also received a heartfelt applause when he announced (spoiler alert) the upgrade was ultimately successful - on time. Not only that, he kept the audience awake in the last session of the day.

Good work speakers, for having the tenacity to write the presentation; for having the confidence to present it; and the patience to put up with scheduling changes.

Thank you to all the delegates. I hoped you enjoyed this year's conference enough to come back again next year. Thanks for listening and appreciating our speakers share their experiences.

Special thanks to Burke, Penny, Francisco & Chris for assistance with the conference schedule, and those who reviewed the abstracts.

And a big thank you to all the user group committee members & volunteers involved in bringing it all together. This includes the exhibitors that greeted all those attending with a smile.

Let's make next year so much better.

Scott Wesley
AUSOUG 2011 Program Chair
Sage Computing Services

AUSOUG 2011 Photo gallery

2011-11-08T12:24:00.000+08:00

AUSOUG 2011 Photo gallery

Chris explaining how ADF is like a flower...

That looks like Roen front row centre - listening to Gabriel talk BI in what I presume to be a flash Italian suit

The Sage team listening to our Managing Director speak - I was in another room...

(Chris - other row, Eddie, Kylie, Kate, Ray, Branka)

Jeff, that's a naughty word on your slide, there

I think Debra may be trying to comprehend my slightly modified agenda

(Vinod, Tim, Debra, Connor, Graham, Penny, Chris)

I think Stanley would like to know if trampling is a crime...

because if so, these green pigs are GUILTY!

Sage Hat Trick!

2011-11-04T23:18:00.003+08:00

Well done to the Sage team!

With the help of our wonderful support crew, the Sage team won the three best paper awards

Best Ace - Penny Cookson - I missed her talk this year, but I'm sure it was full of rude jokes
Best from OTN Tour - Chris Muir - finally not foiled by just as superb Connor McDonald.
Best Paper - Scott Wesley - me! I'm so happy, particularly since I overslept and made it with only 10 minutes to spare!

I'll blog about my experience with the Prezi tool soon, I've had a few people asking questions.

Now it's bed time.

Cheers,

Scott

Oracle Apex 4.1 Security Presentation

2011-11-04T12:08:00.000+08:00

As promised here is the link to the presentation I gave on 4th November 2011 at the Perth AUSOUG conference.

Update : I was awarded Best Paper for this. Thanks everyone!

I hope you enjoy it, and find it informative.
http://prezi.com/89xxuz4ekmvv/oracle-apex-41-security/

Oracle Apex 4.1 Security on Prezi

Just a shame you don't hear my, err..."charismatic" commentary ;-)

Scott

SAGE Welcomes the 2011 AUSOUG Conference

2011-11-01T23:36:00.001+08:00

This years AUSOUG conference is just about upon us, and I'd like to make a few announcements.

I've created a special page on the Sage Computing website that details our involvement.

We will have a booth in the exhibition centre, so seek us out. In between mingling, feasting, networking and general mucking about you might have a chance to talk with us about resourcing, training or how nice the curry was today.

You can also find members of our team presenting at the following times:

Chris Muir - Thursday 10:30 - Studio 1
The Ultimate Sales Pitch for ADF:
on strategic importance and practical value
Ray Tindall - Thursday 11:30 - Astral 3
Tips, Tricks & Traps for Weblogic Server Installation & Maintenance
Penny Cookson - Thursday 16:15 - The Studio
JDeveloper ADF and the Oracle Database: friends not foes
Scott Wesley - Friday 8:45 - The Studio
Apex 4.1 Security: offering obfuscation options
Penny Cookson, Chris Muir et al - Astral 2
Friday 13:45 - Panel Discussion
Presenting: from novice to expert - How your user group can improve your career.
Submit a question
Chris Muir - Friday 14:45 - Studio 2
Angels in the Architecture:
An ADF Application Architecture Blueprint

Branka promises me our booth will look stunning this year, no doubt outshone by including rest our stunning team (ahem) Kate, Eddie & Kylie.

We will also be reminding delegates of our two upcoming Perth workshops:

Apex 4 Workshop November 9-11
Oracle PL/SQL 11g Workshop 21-23

Talk to us for further details, we should have pamphlets available - right next to our freebies!

Swing by, there might be some other surprises.

**

With my 2011 AUSOUG Program Chair hat on, please check the AUSOUG conference program page for the latest updates - it's been a tough battle keeping the schedule still with the turbulence this year ;-)

To print it was updated 1st November. If you are interested in attending the panel session on Friday just after lunch - think about e-mailing us a question for the panel to discuss.

Something else you haven't seen before - on the second morning we have Dennis Ward introducing the Oracle ACE & Java Champions program at 8.25am, so grab a coffee/tea and come by Astral 3 to find out what the deal is with all the ACEs on the program.

Then later, after lunch listen/join in on a discussion with a panel of experts moderated by kind Vinod Patel - they will be talking about how user groups can help improve your career, and maybe even become an ACE yourself.

**

I look forward to meeting those I haven't met, and greeting those I haven't seen in a while. And a cheerio to those that I saw just the other day!

Bring on the food.

Scott

ps - if there is one presentation you should see, just for something different, check out Connor McDonald at the end of the last day. You'll come out laughing, and ready to find out which bar people are heading to!

Updates to the AUSOUG Conference schedule

2011-10-18T10:38:00.001+08:00

Some of you may have received an e-mail today from the AUSOUG conference manager regarding some examples of new sessions included in the line-up.

This year we've been lucky in that we haven't had to make many adjustments to the program due to issues like international speakers having visa problems. Last week however we had a bit of rearranging to do to fit in the new sessions without creating conflicts.

There haven't been too many adjustments, most of them just different times on the same day - but I know some of you out there only come for the one day, and often select which day based on particular sessions. You might want to visit the conference program page to check out the latest changes, as of 17th October.

If you haven't looked at the line-up for a while, you may notice at 1:45pm on the second day there is a panel discussion titled "Presenting: from novice to expert - How your user group can improve your career"

Our panel members will be

Debra Lilley
Chris Muir
Connor McDonald
Penny Cookson
Tim Hall

And Vinod Patel has kindly accepted to be the ring leader keeping them all in line ;-)

Vinod is also talking to Oracle WA to have a representative from Oracle up there as well.

I'm currently sorting out the agenda with Yury Velikanov, who has just started a LinkedIn and Facebook group for the Perth conference. He's also started a discussion to accept any questions you might like to put to the panel. If you submit them now, we can better prepare for the day and have a better chance of your question being answered. We want to make the session as interactive as we can, and eke out as much advice from our experts as possible!

If you can't make it to the conference, but have a good question you think should put to the group, feel free to post it anyway so others may benefit. I'll comment about the session after the conference and I might be able to share any wealth I can remember.

If the panel isn't your thing, we have an alternative session on that time slot by Bryan Frew from Oracle. He will be talking about an interesting case study on Business Process Management (BPM).

Other additions include

Graham Wood on Exadata - You may have seen him as part of the trio at the Real World Performance day post-Insync. He'll give us a much closer look at what's involved in constructing an Exadata environment. This will be in lieu of Graham's hidden features presentation.
Yury Velikanov - This fellow is back from Oracle Open World, and since there have been all sorts of big announcements from Oracle, we've substituted one of his more appropriate & timely presentations in.
Chris Muir on ADF - If you want to know anything about ADF, Chris is the man to talk to. He is an ACE director for the product, and we now have a second presentation to include from him. He's first presentation on day one will introduce ADF and it's strategic & practical use in our industry; then he'll follow it up on day two with an architectural blueprint on application delivery. Chris is currently instrumental in a large Forms -> JDeveloper migration in Perth. You can find commentary on this here.

Never fear, though - I'm still due to present my chat on options for security in Apex 4.1 first up on day two. I won't be using boring old powerpoint, so come by to check out something a little different ;-)

Scott

Three AUSOUG announcements (incl free breakfast!)

2011-10-12T07:23:00.000+08:00

If you like a free breakfast, then you should visit the Perth Oracle Building on Friday Oct 21 from 7:30-9am. Visiting Victorian branch president Barbara Rabinowicz will be presenting on Oracle Database Security.

Oracle database security is a topic that impacts on ALL Oracle professionals.

Database security is an important aspect of the database which is often overlooked. This presentation will cover the security and auditing aspects of the database. It will include securing methods, monitoring and auditing techniques of the database environment in areas of network security,authentication, authorisation issues, links, replication, database Trojan and more.

The session will include a demo of configuring Oracle Network Encryption.

Oracle Australia: Level 2, 66 Kings Park Road, West Perth
Ticket parking is available in neighboring streets, and it's also not far from the peak of the Red CAT bus route.
RSVP to vicepresident.wa@ausoug.org.au.

I had the pleasure of meeting Barbara at Insync11, and unfortunately I missed out on her talk while over there - so here's everyone's chance from Perth to see her in our final event before the conference, and remember - breakfast is free - and based on the last event, quite tasty.

We have lined up something a little different in one of the keynote slots at the AUSOUG conference this year - we have lined up an informal style panel opening the floor to discussions regarding a career that involves public speaking as an Oracle expert.
Panelists coming (almost) straight from OOW including Yury Velikanov, Debra Lilley, Tim Hall, Connor McDonald & Chris Muir.

More details to come soon.

And we have some updates to the schedule with people bringing even more information straight from OOW - Paul Kangro from Oracle will be lettings us know about

Oracle Enterprise Manager 12C – Managing in the Clouds
The New OVM 3.0 – Virtualization from Oracle

We also have meet the speakers program, facilitating opportunities to have a 15 minute discussion with some of our experts. Email our conference manager for more information or register soon!

The WA committee has also organised an end of year party for all our AUSOUG members. This year it will be fun filled time lawn bowling at Perth & Tattersall's Bowling Club, 2 Plain St Perth from 4pm Wednesday December 7th, 2011. Further details will be available closer to the event.

Hope to see you soon!

Scott

Upgrading to Apex 4.1

2011-10-10T15:10:00.001+08:00

Today I finally upgraded the Apex instance on my laptop to 4.1, and I'm very keen to start playing with the new goodies once I ~~start~~ finish my AUSOUG conference presentation.

When installing/upgrading anything Oracle, it's always a good start reading through the relevant installation notes. In my case, I started here:
Installing/upgrading with Apex Listener
http://download.oracle.com/docs/cd/E23903_01/doc/doc.41/e21673/otn_install.htm#BABEHJHG

I've heard some people are a little daunted sometimes with these notes - it's a shame because they are useful & concise. I've summarised the steps I needed to take below - as out of that page I linked to above - I only needed to apply a small percentage of commands. (I have Apex running on Glassfish on a Windows box)

1) First, I downloaded the software I needed for my system. Googling "download oracle apex" is always the best place to start for getting what you need.

I unpacked it to a fresh folder on my C drive:

2) Then I opened up SQL*Plus, ensuring my "Start in" was set to "c:\apex41", then logged in as SYS.
I ran the following script, which took about 15 minutes to run through and rejig my Apex 4.0 to 4.1
@apexins apex sysaux temp /i/

3) Then I had to copy all the images to my application server folder. Apex 4.1 has some fresh icons & updates to various supporting software like JQuery - so that needed to be updated.
However before I did this I backed up my existing images folder as I had modified some themes.
This is the command I executed:
xcopy /E /I C:\apex41\images C:\glassfishv3\glassfish\domains\domain1\docroot\i

The first time I ran I had permission issues overwriting the files, so I turned off read only for files in the folder. I probably should have thought a little harder over this, but since this laptop is very much localised just for my use, it didn't concern me much.

4) Before I did any cleaning up, I wanted to ensure I could log into the updated 4.1 instance, so I opened up the Apex log in page only to see the following error:
ORA-01658: unable to create INITIAL extent for segment in tablespace APEX

Turns out my tablespace was full, so I added another datafile:

alter tablespace apex
add datafile 'C:\app\Scott\oracle\11.2.0\oradata\sw11g\apex_b.dbf'
SIZE 500M;

From then I was able to log in successfully.

5) Then I decided to clean out any old users I had, so I ran this slightly modified SQL to list the users I could drop:

SELECT 'drop user '||username||' cascade;' ddl
   FROM dba_users 
 WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%') 
   AND USERNAME NOT IN (
        SELECT 'FLOWS_FILES' 
          FROM DUAL 
         UNION 
        SELECT 'APEX_PUBLIC_USER' FROM DUAL 
         UNION
        SELECT SCHEMA s 
           FROM dba_registry
         WHERE comp_id = 'APEX');

DDL
----------------------------------
drop user APEX_040000 cascade;
drop user APEXLIB cascade;
drop user APEX_030200 cascade;

And after executing those drop statements my tablespaces got a little lighter anyway.

6) The only issue I've spotted so far is my modified login page, (thanks to Peter Raganitsch)
When opening my log in page, I noticed the following error in my console window - and my modifications weren't showing;
Error: uncaught exception: Syntax error, unrecognized expression: [src$=apex-logo-white.gif]

A quick google revealed this sort of response:
http://forum.jquery.com/topic/error-after-upgrade-to-jquery-1-5-in-img-src-new-png

So in our case this is probably due to the Apex team upgrading from JQuery 1.4.2 to 1.6.2 - so keep an eye out for that when you test your existing applications, if they use JQuery.

So I had a play with the code in the ApexLib_Loginpage.js script. I had to change this:
var vLoginTitle$ = jQuery("img[src$=apex-logo-white.gif]").parent();
to this, noting the quote usage:
var vLoginTitle$ = jQuery('img[src$="apex-logo-white.gif"]').parent();

This meant lines containing the bold also needed modification (to what you see here)
var vLoginRegionLogoImage$ = jQuery('img[src$="apex-db-apps.png"]');
jQuery('a[href^="f?p=4550:7:"]').parents(".rounded-corner-region").css("display","none");
jQuery('a[href^="f?p=4600:6:"]').parents(".rounded-corner-region").css("display","none");
jQuery('a[href="http://forums.oracle.com/forums/forum.jspa?forumID=137"]').parents(".rounded-corner-region").css("display","none");

So the upgrade took about 15 minutes computing time & about half an hour of my time, plus me sledgehammering JQuery to make it work.

Happy coding! Now, about that presentation...

Scott

Advert: Sage Training for Apex & PL/SQL

2011-10-08T01:14:00.001+08:00

Hello to all from Perth, Australia (and beyond if you like)

Sage Computing Services have just scheduled two courses for November:

Oracle Apex 4 Workshop => 9-11th November
PL/SQL Workshop => 21-23 November

Both will be held in the Perth CBD, please e-mail us if you're interested in further details.

Cheers!

Scott

ps - we don't often put on PL/SQL training here in Perth - get in while you can!

He started a fruit company...

2011-10-06T11:59:00.000+08:00

Sometimes I find it hard to think of some people without thinking of references to pop culture.

Edit: This post from Brian Dunning is a timely & worthy read regarding treatment of health issues with modern medicine vs naturopathic woo

There are people in this modern world live their lives as best they can - grow up, get a job, get married, work hard, retire when they can. No doubt making many mistakes along the way, hopefully learning from each and every one.

Other people also live their lives the best the can, making similar mistakes, but for whatever reason taking hold of different opportunities, then start affecting the lives of many others - either directly or indirectly.

Two sets of people, different paths, same result. We all have an expiry date. Some sooner than others, some know when the end is near, others find it unexpectedly - some don't even see it coming.

One person might end their life with billions of dollars in their pocket, the other may have saved enough for a dream holiday one day once they retire.

Either way, I just hope both of them have enjoyed their life, perhaps finding a profession they love. One where they can go to work every day and do it with enthusiasm.

And if they are lucky enough to finish up with mega-bucks in the pocket, I hope they eased off the hard work, regularly spent time with the people they love, and doing the things they enjoy.

Because at the end of the day, there are some people out their with the sole purpose of seeking wealth - but to what end? If you die with a million dollars in your pocket, but haven't found happiness or contentedness - has it been worth it? Did you "win" because you finished with more money than the next person, or did you enjoy your life because of what you did every day, because of the people you touched?

Idle thoughts.

My condolences to the family & friends of Steve Jobs.

Upgrading Apex Listener on Glassfish

2011-10-06T10:10:00.000+08:00

I can't believe it was July when I blogged about a problem with my Glassfish server.

Since then Oracle has release Apex Listener 1.1.3 (September 2011)

I thought I'd finally upgrade, and I was surprised how simple it was.

If you're using Glassfish already, check out the notes for upgrading Apex listener with Glassfish. The details are super simple and fit on one screen.

First I went to my Glassfish Administration module at http://localhost:4848
It took a while to respond, and so did logging in - the rest of the process for upgrading the listener didn't even take as long.

Clicking on the Application node took me here

Then I disabled my Apex deployment

Clicking on deploy enabled me to select the WAR file from my Apex Listener download, which I extracted to C:\app\Scott\apex_listener_113\apex.war

And it was enabled straight away

I opened my Apex login URL (without the trailing slash, which was the bug I wanted to avoid) and presto, all was good!

Next I need to upgrade my local instance to 4.1...

While in the Glassfish Administration console, I noticed there were 47 updates available to my Glassfish server.

So I attempted to upgrade them all, but received the following lovely error:
manifest of pkg://release.javaeesdk.sun.com/glassfish-bundled-jdk@1.6.0.26,0-3.1:20110525T211958Z has duplicates: file: path=jdk/register.html & file: path=jdk/register.html

So I decided to call it a night - if it ain't broke...

On a final note, if you would like to use Glassfish as your proxy between your web browser as the Application Express engine, check out the installation notes. This would be instead of using a dedicated HTTP server, which might be overkill for your laptop; and the basic XDB HTTP server embedded in the database - Embedded PL/SQL Gateway (EPG)

Enjoy.

Scott

Today's post is brought to you by the letter 'C'

2011-10-04T07:07:00.001+08:00

I may not be at OOW11, but I've been listening to what's going on - one thing I did notice was the announcement of one of Oracle's latest products: Oracle Enterprise Manager 12c

I was joking with someone just the other day about what letter they were going to use for Oracle 12, and suggested perhaps C is for Cloud.

Careful what you wish for... this statement accompanied the press release:

Oracle Enterprise Manager 12c is the first product in Oracle’s portfolio numbered “12c” where the “c” stands for Oracle’s significant investments in delivering cloud-ready products

This of course follows
8i - Internet
10g - Grid

It already makes me wonder - what is going to be the next big thing in 5 years time. One suggestion:
"M" - Mobile. Yes, mobile technology is ubiquitous now - and that's what cloud technology is all about, but what's Oracle going to do with it? Whack some specially designed database on your latest octo-core, 8GB RAM, post-SSD mobile device?

People like Tim Hall will have you thinking Oracle will come out with something like 13OGC, or some cleverly branded acronym/mnemonic...

What pace our technological world grows in, huh?!

Scott

Apex Tutorial: Adding a Read Only Option

2011-09-22T17:11:00.000+08:00

~~Last week~~ Recently I published a tutorial walking through setting up a form and report on the same Apex page. This one done using out of the box functionality, without the use of dynamic actions. Hence this is aimed at those learning the Oracle Application Express development tool.

We can take this a step further and extend this functionality to open the record in read-only mode first.

1) Modify report link to pass P6_MODE = R, instead of E
Now we have three "modes" View (which doesn't display the HTML region), Read only & Edit.

2) Modify all form items to apply read only condition to P6_MODE = R
Now when you click the link for an employee record, the form initially opens in read-only mode.

3) Modify HMTL region condition to be :P6_MODE IN ('E','R')
We need this region to display in edit and read-only mode.

4) Extend Delete/Save buttons conditions to include :P6_MODE = 'E'
ie => :P6_EMPNO IS NOT NULL AND :P6_MODE = 'E'
We don't necessarily want these buttons being displayed in read only mode.

5) Create Edit button in the HTML region
You could add an authorisation condition on this button to make editing records available only to privileged users.

* condition :P6_MODE = 'R'

* redirect to page 6

* passing parameter P6_MODE = E

The problem now if if you have, for example, quick picks on any of your items - these will still be displayed as the read only option only adds the class="display_only" tag to the item definition:

6) So to clean this up, we need to make a copy of item P6_JOB

Use nearby sequence number to create the copy in the same position on the page.

My job field sequence was 30, so I chose 35

7) Remove the quick picks option from copied item P6_JOB_DSP

These are the options we don't want displayed in read-only mode. The user can't save the change, but it looks untidy.

8) Add display condition on P6_JOB_DSP as :P6_MODE = 'R', retaining the existing read only condition.

Alternatively we could set the item type to display only, however we still only want it appearing in read-only mode.

9) Add display condition to P6_JOB as :P6_MODE = 'E'

The original item only needs to appear in edit mode.

We now have a functioning read-only mode option, with an Edit button available to re-open the page in edit mode.

The item list in the page builder will now look more like this:

I hope that further helps your understanding of some of the concepts available to the Application Express environment.

Scott.

Apex Tutorial - Form & Report sharing the same page

2011-09-21T21:38:00.000+08:00

During some training recently I walked through the "Form on a Table with Report" wizard to create two pages - one report listing records from a table, which then linked to a form page allowing edits on that table.

I was asked how you could amalgamate the two pages into one. Here is a tutorial that completes this exercise from scratch.
Note: if you have existing pages already defined, I would recommend you copy the report region onto your Form page, and modify the relevant attributes - then remove your report page.

1) First, create a form on page on a table - I use the sample EMP table

* When prompted during the create form wizard, set branch for apply/cancel to the same page you're creating. Note in my case it's page 6.

Now we have a basic form page, where most of the hard work has been done by the wizard, such as defining page processes, branches and item definitions.

You form will look something like this to start with:

2) Now create a report region listing employees from EMP

This will show a simple report underneath your form:

3) Create item P6_MODE
This will be the catalyst for showing/hiding certain components on the page

4) Create a button called NEW on the Employee List report region
This will allow you to create a new employee record

Action details:

* redirect to 6
* set parameter P6_MODE = E
* clear cache => 6
* condition => COALESCE(:P6_MODE,'V') = 'V'

5) Modify the Cancel button action to set P6_MODE = V
This ensures your page returns to status quo when the cancel button is pressed

6) Modify the branch defined by the create form wizard and pass parameter P6_MODE = V
Whenever you submit your page, it will now return it to status quo

7) Add condition on HTML region that contains your form items to check :P6_MODE = 'E'
Now your form region will only appear when in "Edit mode", such as when the New button is pressed or the user edits an existing record (next step)

8) Modify EMPNO column in the report to become a link
This will allow your user to edit an existing employee record.
While in the report attributes page, I set the column headings to custom and cleared the heading for EMPNO - just to tidy up the display a touch.

* redirect to 6
* set parameters P6_EMPNO = #EMPNO# and P6_MODE = E

You should now have report page that displays the form region when the New button is pressed, or when you click to edit an existing record. The form region will disappear again once you Apply/Delete/Cancel.

Above is "status quo"; below while editing an existing record

I hope that helps, and I also hope it made sense. I made certain presumptions about you finding the relevant settings - otherwise there would be a real flood of screenshots!

Here is a screen grab of the page builder after all the modifications have been made (the row fetch page render process is off-screen, and shared components section not displayed)

Scott

Short Oracle Forms Survey (Whitehorses)

2011-09-21T19:29:00.000+08:00

Still using Oracle Forms?

Why not participate in this quick Forms survey at Whitehorses.

It has 9 short questions and only takes a couple of minutes.

Get it done ;-)

Scott

Perth 2011 conference program available

2011-09-13T21:34:00.001+08:00

Yep, this year's AUSOUG conference program is now available.

It was quite a challenge putting it together, and I thank those involved in helping out. It's not yet final, but gives a good indication of how strong it is this year. I count 3 Oracle Aces & 9 Ace Directors - plus a few quality speakers not (yet) recognised by that particular program.

The early bird special finishes at the end of September, so why not register early to see speakers such as:

Local

Connor McDonald
Penny Cookson
Chris Muir
Mark Lancaster
Yury Velikanov
Gavin Soorma

International

Graham Wood
Francisco Munoz Alvarez
Debra Lilley
Craig Shallahamer
Tim Hall
Ronald Bradford

Plus me, of course

All covering topics ranging through Fusion Middleware, Apex, ADF, EBS, DBA interests and even MySQL & Java.

Burswood is a fantastic venue, hope to see you there.

Scott

Oracle in pop culture

2011-09-09T12:01:00.000+08:00

Twice this week I noticed Oracle mentioned in a web comic.

The first was Dilbert joking about the patent infringements amongst all the major companies at the moment - of which you could probably find a zillion info-graphics looking like this these days. I think Scott Adams had a flaw in the middle frame however - there is no way Google, Apple, Microsoft and Oracle would join forces. Oracle & Microsoft alone are justification for that - evidence in that the Windows version of SQL*Plus is no longer available from 11g, and SQL Developer is as portable as my Android phone.

What I did enjoy however was this from BonkersWorld.

Having just come from an Oracle conference, the comparison of more power for less cost seems spot on! Mind you, the other vendors seem well represented also. Looking through the archives, I found another suggesting Oracle will just sue you if necessary - I think perhaps they might buy you out!

Nothing quite so simple as this though, comparing computer updates for Apple, Windows & Mac.

My question for you is - have you seen Oracle mentioned in a web-comic or something similar and you think it's worth a giggle? Please comment below.

Scott.

Apex performance presentation

2011-09-08T21:35:00.000+08:00

At the Insync conference in Sydney I experimented with a different presentation technique.

I've forgotten already where I stumbled across it - might have even been somewhere on twitter - but it's called Prezi. I did have a fear that I would instill motion sickness onto my audience, but after some initial feedback with a prototype I decided to go for it.

My topic was Oracle Apex Performance, and I originally presented it as a powerpoint slide deck at last year's AUSOUG conference in Perth (Nov 3-4 2011, btw).

So I updated and converted it to the Prezi format, which can be found here:
http://prezi.com/gfmw8q_gstcl/n-methods-to-improve-apex-performance/

A link to the PDF version of my powerpoint display can be found here. Yes, in writing that it does seem a little strange, I'm not sure why I didn't publish the PPTx version - not that there would be much difference.

I certainly learned a few things from the experience - things that worked and things that didn't. I have plenty of visual ideas for the topic I'm doing in November, to try and further embrace the prezi style instead of thinking "slides".

I don't think the technique will work for everyone. Connor certainly has a unique style, similar to Larry Lessig and further pioneered in IT by Dick Hardt. A great write up can be found here. If you've never seen Connor present, you must make it happen. He makes powerpoint his own and will rip through a zillion slides in less than one hour - and you'll come out very much entertained!

I don't think Connor could adapt his slick style to the prezi format, and I think many technical presentations would be difficult to translate. I found when converting my original presentation I had trouble "forgetting" the powerpoint way of thinking. I think when I do my next one, since it will be fresh I'll be able to plan the layout a little better and provide something more fresh & creative. I found myself jotting ideas down as I listened to people speak at Insync. It will be on Apex 4.1 security.

If you come along to our Perth conference you can experience it first hand! ;-)

Now, to start writing it...

Scott

Off topic: Friday fact

2011-09-02T14:25:00.001+08:00

Being the fact it's Friday, I'm home sick, it's related to my interests and that this picture moved me - I thought I'd share it.

I'm a regular reader of Phil Plait's Bad Astronomy blog, and recently he posted this image from NASA's Juno spacecraft heading to Jupiter.

It's a picture of Earth and our moon from almost 10 million kilometers away. Now just for a moment, contemplate the sheer scale of what's being mentioned there - a tenth of the distance from Earth to the Sun, and this is all we see.

It also helps highlight just how far away the moon really is, and it's not even at an angle which reflects the true distance spanning the two.

As in Phil's blog, this reminded me of part of what I think is my favourite literary piece ever, from Carl Sagan's Pale Blue Dot.

"The earth is a very small stage in a vast cosmic arena. Think of the rivers of blood spilled by all those generals and emperors, so that in glory and triumph they can become the momentary masters of a fraction of a dot. Our planet is a lonely speck in the great enveloping cosmic dark. There is perhaps no better demonstration of the folly of human conceits than this distant image of our tiny world. To me, it underscores our responsibility to deal more kindly with one another, and to preserve and cherish the pale blue dot. The only home we've ever known"

He is of course referring to a different image, taken from 6 billion kilometers away - but it works just the same.

Have a great weekend :-)

Scott.

Free Oracle 11.2 database just released

2011-09-02T10:09:00.000+08:00

Kris Rice has just announced the release of Oracle Express Edition 11.2.

This is a free product you can download, install & learn. A thorough description of the exact licencing can be found here.

It's very easy to install, just like installing any other software on your computer. I created a document with some step-by-steps to help get people learning started - including logging in with SQL Developer to establish some users, instead of using SYS.

Go, install, play, learn.

Scott

Insync 2011 Day 3

2011-08-24T21:03:00.000+08:00

First you may be thinking - hang on, there was only 2 days in the Insync conference, wasn't there?

Just like a PL/SQL boolean, there is true, false, and unknown to that statement.
Ooh, did I really go there?

True - there was two days of scheduled presentations.

False - like any conference, there are often fringe events scheduled before or after the main event.

Unknown - for those unfortunate enough not to have heard of the event, in the Sydney Convention & Exhibition Centre there was an extra day-long session entitled "A Day of Real World Performance". More information can be found here.

It was co-presented by

Tom Kyte - author of AskTom - directing the core of the discussion with slides in a centre screen, providing a developer perspective.
Graham Wood - Oracle Database Performance Architect - dishing out Enterprise Manager output as an Exadata box is manipulated
Andrew Holdsworth - Head of Oracle's Real World Performance team.

It was less of your usual seminar style format, more of a conversational piece - which made it much easier to concentrate after two days of conference presentations. This style also made it more engaging. There was a general script, but Graham mentioned to me at one point that after nine roadshows, none ever really come out the same - I'm not surprised!

They kicked off by loading a terabtye of data into a remote Oracle Exadata box, then used that as a platform to discuss performance issues from compression to statement parsing to index use - but nothing like you've ever really seen before. Andrew kept tweaking settings in the exadata machine to show how they can affect performance - accompanied by a screen full of numbers whaling up and down; Graham showed pretty graphs that illustrated what was going on post-change; and Tom get adding his pearls of wisdom with his easy listening voice.

Mark Lancaster also described the event in his blog and I must agree - if this roadshow comes to a town near you, it is a must see. If you can't, try out the videos. They don't do it any justice, but apparently they have been used to sway decisions by the powers that be in the correct direction.

A little tip - don't book flights for conferences without checking/looking out for these events - I know a few people who missed out because they booked flights too early. Mind you, I also know there are a few people who flew up from Melbourne specifically for this day.

And to cap it off - Tony, Barbara & I finished the day with a beer overlooking Darling Harbour in a wonderful sunny Sydney afternoon.

... but then we had to head to the airport to go home...

Scott

Insync 2011 Day 2

2011-08-21T09:33:00.001+08:00

My tip for surviving an interstate conference - sleep in the second day until as late as possible.

I walked in with time to grab a cup of tea and walk up to the first session. I chose Alex Peattie talking about an introduction to SOA for application integration to see where Oracle are pitching these days for their grand world of SOA. It was fairly entry level, but a thorough overview - a nice way to warm up.

It certainly would have been tough to warm up with Shan Nawaz talking about the top ten 11g features that aren't your usual top ten. It was a good idea for a presentation as one does get bored with hearing about the same glossy features all the time. For those curious, here are my interpretations of my quick scratches on the day

DBMS_PARALLEL_EXECUTE
DBMS_COMPARISON
Reproduce SQL test case (DBMS_SQL_DIAG)
Advanced compression (costed option)
Diagnose/Resolve failure with DRA
Multi-section backups
Database resident connection pool
Server consolidation
SQL*Plus error logging (sperrorlog)
Preprocessor

And yes, some of those are very cool.

The post-morning tea slot saw me torn again between Francisco Munoz on Oracle security tips and Marcelle Kratochvil convincing us to store unstructured data in the database. I ended up in Marcelle's, and while I can't compare to Francisco's session (Barbara thought it was excellent), I think Marcelle's was up there for the most engaging and thought provoking of the conference. It reminded me of Guy Harrison in Perth last year - his was less Oracle and more commentary on the direction of the computing world, but Marcelle really hit the nail on the head for the issues of tomorrow. Our technology isn't there yet for searching videos/images, protecting copyright etc, but Marcelle certainly convinced me by not putting this information into our database, we aren't exactly heading in the right direction!

Before lunch, again two slots I wanted to see. I ended up with Connor McDonald talking statistics, which I will say is my favourite from his catalogue. I saw it previously in Perth, but was more than happy to be entertained once again. If you've never seen one of his presentations - it doesn't matter what the topic - you must see him at least once.

After lunch, torn again - sense a trend? I almost went to see an Australia Post case study on SOA, which apparently was packed - but I thought I'd learn a thing or two about Exadata from Tony Jambu, and get myself a mintie. Tony assessed the facts and myths to see if Oracle was pulling the wool over our eyes or if they really have the beez neez of hardware/software integration - turns out Oracle really does have the right stuff.

And we all saw some magic that involved Guy Harrison wondering if he was going to walk out of that session with a cup of water drained over his head. Great work, Tony.

After three sessions of indecision, I found myself struggling to pick something in the penultimate session. I ended up seeing Clancy Bufton introduce flashback queries and total recall. Not a new topic, but one I'm not completely familiar with.

Now for the biggest scheduling issue of the conference - we had Mark Lancaster talking advanced Apex 4 UI; Tim Hall on edition-based redefiition; and Tom Kyte on SQL techniques.

I went the big name, which is a shame because it turns out I had seen this one before, and was very happy with my use of his suggested better practice. I was however glad to see and hear the younger generation (did I really say that?) talk about some of his suggestions and seek clarification with Tom afterwards. To me that indicates some success and progress within a conference.

Tom certainly drew the crowd as Marc and Tim suggested afterwards some smaller than expected numbers. But that's ok, those who were there got an up close & personal, interactive discussion with them both.

So that concluded the conference proper - however I still had the evening and an extra day to survive. The evening made it easy. After a quick revival back at my room, 14 of us headed out for dinner. We tried once place only to find we wouldn't fit, but plan B was superb. We headed to South Styne - a floating restaurant in Darling Harbour. I enjoyed perfectly cooked kangaroo fillet with lovely spices and mash, chased down with a few king browns and good company. Thanks people, and I remember someone taking a group photo so no doubt I'll stumble across that in the near future - hopefully I can post it up.

Stay tuned, I have some more thoughts on this week to come.

Insync 2011 Day 1

2011-08-21T08:30:00.000+08:00

Yeah, these posts are a little late, but boy - what a busy week it's been!

After a delayed flight from Perth (isn't that the norm?) I spent me late hours on Monday night tidying up my presentation. Unfortunately that lead to me staying up waaay past my bedtime, but nothing a fresh morning and a cup of tea wouldn't fix...

That, and a pretty sizzling conference intro by Dan Thurman! The introduction from Martin Power and Ian White was relatively brief, then John Bushell introduced Dan and advised us to sit back and enjoy. When Dan came out with his strut, Bill Paxton sounding accent and unwavering zeal, I think most of the audience was wondering what sort of cheesy slop were we in for.

It was however a very thought provoking display by a man who knew exactly who he was and how he was probably being judged. He tied in his thoughts and messages with the IT world quite well, no doubt with as much practice as he spends on his juggling and unicycling. Kudos to the user groups involved in bringing him over.

To the real content, however:
Tim Hall showed a decent group a new hidden way of cheaply cloning your database. The content was somewhat outside my area of expertise, but I enjoyed it regardless. I enjoy Tim's presentations and I'm glad he made it back to Australia.

Damien Bootsma from Embarcadero followed morning tea with a reminder to all about the best practices of capturing meta data for productive database development. That was pretty much his title, and while some of his messages may not have been new material for many, I'm never opposed to drilling into people the better ways of designing and managing your database.

Which is exactly what Tom Kyte did in his "Efficient PL/SQL" presentation before lunch. I heard a few people commenting afterwards that there wasn't the sizzle and awe they expected from and Oracle celebrity, but I thought Tom's message was loud and clear, and always needed. So many times do I see people coding without contemplating the messages Tom was delivering. I had seen a similar presentation from him before, but I didn't mind seeing it again and I hoped some of the newer Oracle people in the crowd took many notes!

After lunch I was torn between a database security talk by Victorian AUSOUG president Barbara Rabinowicz and 10 things I possibly didn't know about Oracle indexes by Canberra ACE Richard Foote. Tony Jambu somehow leaned me towards seeing Richard's show - which I must say was really interesting. A little esoteric, but I learned a few things to think about in future. Having met Barbara during the welcome reception later, I was a little disappointed I didn't see her security talk - but isn't that the peril of having many streams at a conference!

There was one more session before mine, and I ducked my head into Angus MacDonald from Oracle talking about what's next. I couldn't find the abstract, and it was quite hardware centric so I ducked back out to run through my "slides" before I was up.

I'll suspend commenting on my own presentation until another post - I'd like to go into some detail on things and not let it get lost amongst this commentary. The turnout was unfortunately a little low, I'm curious where all the people were - although Craig Shallahamer was on at the same time...

Afterwards I steeled myself for the final keynote of the day on cloud computing strategies by Roland Slee and Doug Hughes. I hadn't seen Doug speak before, and I liked his attitude. Let me suggest it was one of the least cliche Oracle keynotes I've attended ;-) I look forward to him coming to Perth soon.

As usual, the welcome reception was a welcome end to the first day. I found the right position to get quick dibs on the beer trays, although the food was a little exotic for my simple tastes. I formally met Barbara who was a delight to communicate with and get to know for the remainder of my time in Sydney. I also caught up with other interstate fellows such as Tony Scholefield & Tony Jambu - and we ended up deciding to meet up for dinner with some Oracle specialists who had earlier snuck away for a Sydney Meetup, which I meant to attend but got lost in the mayhem.

So we had a nice dinner with a group of very skilled technologists. I enjoyed some spanish cuisine sitting nearby Connor McDonald, Marcelle Kratochvil & Barbara. However Tony, Barbara and I thought we'd nick away afterwards to the IBM/RedRock party down the road. It had quietened by the time we got there, but it was a good opportunity to wind down after a very long day. Yep, that was only day one.

Australian Conference Presentations?

2011-08-10T12:59:00.001+08:00

Attention all Oracle technologists interested in presenting at an Australian conference in 2011.

Today is officially the final day for abstract submissions for the Perth conference in November.

Without letting too much out of the bag, it looks like we'll have a ripping line-up this year. Get your abstract ASAP in so we can consider you for selection!

Anyone interested in simply attending, the earlier you register the cheaper it will be. It sounds cliché, but this will hopefully be one of the best we've ever had.

Next week is the InSync11 Conference in Sydney.

Of course it's a little late to get your submissions in for that, but I'll be there presenting on performance in Application Express. I'll be moving away from (death by) powerpoint and trying something a little different this time. If that sounds interesting, I'm currently slotted in the final session for day one.

I'll also be attending the bonus Thursday session involving Tom Kyte. Hope to see you there!

update - I had flagged this post as something to read, thanks to Chris Muir's twitter feed - should have read it before posting this entry. Well worth a read of you're still considering presenting:
http://www.pythian.com/news/25105/why-you-should-submit-a-paper-for-an-oracle-user-group-event/

ScottWe

Subscribing Apex Navigation Bar Entries

2011-07-28T15:17:00.002+08:00

Here is a little ditty I found regarding Navigation Bar entries in Apex and had a minute to investigate how/why, so I thought I'd share.

When you attempt to set the master navigation bar entry in your application, you need to use a search dialog to select the relevant navigation bar entry from a list of the applications within your workspace.

Unfortunately, the list of entries is sometimes without a descriptive label.

So I thought I'd have a look to see what attribute is being displayed in the dialog.

It turns out the dialog uses the Image ALT text, not the entry label. So when you create/edit your navigation bar entries, I suggest make it a habit to set this attribute also - it may make your life easier in future. Particularly I guess if you ultimately move towards a different template for your navigation bar entries.

Even the context help showing existing navigation bar entries uses the ALT text as its descriptive term.

This may change in future, but certainly the behaviour of Apex 4.0.2.00.06

Scott.

Glassfish - url is not allowed

2011-07-20T16:14:00.026+08:00

On my laptop I use Glassfish as my server platform for the Apex Listener.

A common problem I see (mainly from trainees trying to plug in my address) is the following error.
HTTP Status 404 - Requested url http://localhost:8080/apex40 is not allowed.

Now this isn't really a problem for me day-to-day because I have a nice bookmark that takes me to the correct location. But next time I try to set-up Glassfish, or I'm having some troubleshooting problems, I'll forget this issue.

It turns out, all I'm missing is a trailing slash on my address. It should be
http://localhost:8080/apex40/

I don't know if this is solved by some setting - I'm certainly not a web server expert. If anyone has any comments I'd be glad to listen.

... hold the phone!

I just thought I'd have a read about the changes in Release 1.1.2, and I notice this fix:

Fixed a problem with an IllegalStateException being thrown when the path /apex (instead of /apex/) was accessed on the standalone Listener

I was adding this entry so I might also remember in future, and let's just say I passively found the solution, instead of actively looking for a potential one.

I remember seeing the announcement for this release, I figured I wouldn't worry about ~~going through pain of~~ upgrading since it's just my laptop... now I'm downloading as I post... (well I was, I scheduled this post a while ago to stop the bombardment of the start of July - but now I've created a paradox to confuse time travelers)

Maybe I'll remember to record the upgrade process...

ScottWe.

Modifying your Apex login page

2011-07-13T12:12:00.003+08:00

If you would like your Login (101) screen looking a little more exciting, add a picture to paint a thousand words.

Today I added a little polish to one of our sample applications, and I thought I'd share the relatively simple process.

As with all things Oracle, there are many ways to skin this... er, vegetable. The simplest is to edit the region properties for the container holding the username/password fields.

Here I added a region image definition of
#APP_IMAGES#sage_logo.gif
which referred to an image I've loaded into the Apex repository.

On a side note, it may be a good idea to parameterise the image location, but that's another story.

Alternatively, I could have added a Display Image item; modified the region source; added some pre-element text... I could go on, but this solution placed the image in a place that suited my imagination exactly.

Part of this decision was not fighting the product. Apex isn't always great at complying with your WYSIWYG thoughts, sometimes it's not worth spending 80% of the effort on manipulating layout.

I also thought the default width for the form region was excessively wide, so I thought I'd investigate where to change that. I wasn't exactly sure what this theme did, so I needed to look underneath the hood.

I'm a regular Chrome user, so out of the box I could right-click on something in my page and select "Inspect Element."
I happened to right-click in the table that houses my items, so I kept looking at the parent divs until I saw the width: 450px definition. Next to that is a link that names the CSS file that sets that attribute, so I opened that file; modified the value from 640 to 450, and refreshed my page to a nice ratio.

A simple task, hope it helps.

Scott

Look ma, it's me!

2011-07-06T12:50:00.000+08:00

My mum would be proud. My wife, well, she's embarrassed.

While standing on the shoulders of those more skilled than I, I managed to eke myself into a book.

Not just any book - but a book written by Oracle Apex Experts where the proceeds to head towards families of those experts who are no longer with us - Scott Spadafore and Carl Backstrom.

It was great fun being involved, if by fun you mean I learnt an awful lot while making constructive criticism on other people's work - I hope they didn't mind ;-)

Buy the book.

My copy arrived recently. It really is one of the best out there. Kudos to all those involved:

John Scott, Dietmar Aust, Martin D'Souza, Doug Gault, Dimitri Gielis, Roel Hartman, Michael Hichwa, Sharon Kennedy, Denes Kubicek, Raj Mattamal, Dan McGhan, Francis Mignault, Anton Nielson, and the team at APress.

More ACEs than a pack of cards...

JDeveloper ADF - The Real Business Experience (plug)

2011-07-06T06:33:00.003+08:00

Yesterday I sent out a newsletter from Sage Computing Services detailing a free half-day seminar that we're running in conjunction with the Australian Oracle User Group.

Presenters:
Chris Muir - Oracle ACE Director - recognised internationally for his expertise with JDeveloper.
Penny Cookson - Oracle ACE - Perth Oracle celebrity and Oracle Magazine award winner.

Date: Tuesday 26th July 8:30 - 13:15 (double doses of coffee plus lunch provided)

Location: The Great Southern Room, State Library of Western Australia
http://goo.gl/78zHs

RSVP: Penny@sagecomputing.com.au by 19th July

Cost: Free!

Check out the newsletter for further details on agenda.

Sage was recently mentioned in an Oracle case study for our work at the Department of Treasury & Finance.

I'll hopefully be there to learn what I can about JDeveloper, but they won't convert me! (not yet anyway)

Learning SQL?

2011-07-05T22:24:00.000+08:00

Sometimes I hit the "Explore" link in Google Reader, I encounter some interesting an amusing things - kinda like using iTunes Genius but for web pages that suit your RSS interests.

I stumbled across this blog listing free SQL books - as he says, not all of them Oracle, but worth a look.
http://newappsdba.blogspot.com/2011/06/free-sql-e-books.html

I thought the OracleXE tutorial was well constructed - great for beginners trying to tinker with a locally installed Oracle database.

Not too long ago I created a small document detailing the installation process and basic configuration for OracleXE 11g Beta on a Windows box, which can be found here. I just hadn't got around to publishing it in any form.

If I can convince my laptop to open an EPUB file, I might even be able to comment on the SQL Performance Explained e-book. (nope, while writing this I tried a few avenues, but my patience has been lost)

Scott

Integrating Oracle Apex with EBS

2011-07-04T17:20:00.000+08:00

Recently I was e-mailed a white paper from David Peake suggesting how to integrate Oracle Application Express with E-Business Suite.

It was an interesting read, so I googled the partial document name to find what else has been said about it, and to find the link for this post
url:apex-ebs-extension

David Peake announced the white paper, of course - being the author.
Steven Chan also announced it the paper.

It's been a fair while since I had to communicate with EBS. The coding examples took me back a few years, but one of the examples made me think about plug-in changes coming in Apex 4.1 - I wonder if the authorization methods will be modified to use an authorization plug-in?

David did suggest they will modify the document in future with the latest improvements. Time will tell...

Yet another sign that Oracle is fully committed to Apex as another primary option for development.

Scott.

Useful Oracle Apex URLs

2011-07-04T12:42:00.000+08:00

Today one of my tasks was to compile a list of useful Oracle Apex URLs to send out to some people.

I've managed my bookmarks fairly well, and kept track of good links I've encountered over time. This list is certainly not exhaustive, and I've no doubt missed some key links, but if you're looking to explore - you might find something new or useful below.

(Last updated: 2011-09-21)

-- Apex documentation

http://www.oracle.com/technetwork/developer-tools/apex/documentation/index.html

-- Apex Statement of Direction
http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-sod-087560.html

-- Apex Early Adopter website (recently used for 4.1)

http://tryapexnow.com/

-- Dynamic actions sample application

http://anthonyrayner.blogspot.com/2010/07/apex-40-new-dynamic-action-sample.html

-- Set item on return/validation

http://www.itworkedyesterday.com/blog/2010/2/23/apex_util-ready-set-json.html

http://rutgerderuiter.blogspot.com/2008/10/update-multiple-items-from-lov-field.html

-- Apex splitter

http://oracleinsights.blogspot.com/2009/12/quickest-way-to-delete-unused-templates.html

-- On remembering URL parameter sequence

http://triangle-circle-square.blogspot.com/2010/08/all-programmers-should-read.html

-- Template substitution strings

http://oraclequirks.blogspot.com/2007/07/apex-substitution-strings-cross.html

-- Plug-ins

http://skillbuilders.com/oracle-apex/Application-Express-APEX-Consulting-Training.cfm?tab=plug-ins

http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-plug-ins-182042.html

http://www.apex-plugin.com/

-- Online sample applications

http://www.oracle.com/technetwork/developer-tools/apex/application-express/packaged-apps-090453.html

http://apex.oracle.com/pls/otn/f?p=11933:5

http://apex.oracle.com/pls/otn/f?p=31517:1

http://apex.oracle.com/pls/otn/f?p=11620:5

http://actionet.homelinux.net/htmldb/f?p=100:59

-- Apex blog aggregator

http://apexblogs.info/pls/apex/f?p=APEXBLOGS:HOME:0:

-- Integrate forms with Apex

http://roelhartman.blogspot.com/2008/11/integrate-oracle-forms-with-apex.html

-- Error page replacement (more flexible in 4.1)

http://triangle-circle-square.blogspot.com/2010/09/jquery-error-page-replacement.html

-- Apex themes

http://www.apex-themes.com/

-- I highly recommend the following books

http://www.apress.com/9781430235125 (released June 11)

http://www.amazon.com/Oracle-Application-Express-Experts-Voice/dp/159059827X (Amazon best seller)

-- Prettying Apex URLs

http://ora-00001.blogspot.com/2009/07/creating-rest-web-service-with-plsql.html

http://krisrice.blogspot.com/2007/02/better-apex-urls.html

Scott

A mash of Oracle related thoughts

2011-06-20T22:37:00.000+08:00

It's been almost a month since my last confession. I have plenty I want to say!

Australian Oracle User Group
As usual, there is various activity going on around the country.

July 14 2011 there is a one day Northern Region Conference at Jupiters Townsville. Kudos to Mark Lancaster et al for that one. It's a shame I can't be there.

August 16-17 2011 is this year's InSync conference. My paper has been accepted so I look forward to seeing & meeting old & new members of the Oracle community from around Australia and the world. This will be the largest Oracle event I've been to so far.

August 18 2011 there will be a Day or Real World Performance with Tom Kyte, Andrew Holdsworth & Graham Norwood. This has recently been announced and looks very attractive. I'll be looking out for more details on this - a similar event was recently at UK-OUG.

November 3-4 2011 will be my hometown's conference - Oracle with 20:20 Foresight, of which this year I have taken the reigns from Penny Cookson as the Program Chair. It will be an interesting and challenging task, and I look forward to what should be an even better event than last year. The call for papers is now open, so get your submissions in by August 10th.

In between all these events will be the regular branch meetings where you can catch a local presenter talking about anything Oracle. Aussie Oracle technologists - get in there!

And if you're not from Australia, I hope this isn't the only sort of plane that will get you here (think volcanic ash...)

So when you sit down to write or update your presentation, keep an eye out for tips such as these to create something outside the square.

Keep Yourself in the Loop
Oh so tempting to throw a PL/SQL pun in there, but I'll leave this the exception.
Steven Feuerstein and his team have been doing such a great job moving forward with the PL/SQL Challenge website, it's now in version 2.0.
In addition to a daily PL/SQL quiz, Steven has now launched a weekly SQL quiz and a weekly Apex quiz.

Now you don't have to play competitively or publicly rank yourself amongst the few thousand active players - but it is a great place to spend 5-10 minutes every day testing yourself on your general knowledge of Oracle technology. When I was first learning Oracle I spent a fair bit of time at asktom.oracle.com, this is another location you should bookmark and visit regularly. And on top of all this, Steven has given away almost US$30k in prizes - and it's not just for the winners. Granted, US$30K just isn't the same as AUD$30k these days, but it's worth the effort ;-)

And more kudos to Aussies Gary Myers and Jeff Kemp for ranking highly in the recent quarterly playoffs.

Oracle Training
What has been keeping my working life busy recently - in between the regular consulting - has been the influx of training request around the country. I only hope that planes will be flying regularly once the volcanic ash has settled down, otherwise I might have some disappointed customers!

It's end of financial year time, and it's great that so many companies out there want to keep their team skilled. I've been keeping our Apex notes up to date with all things Apex 4.0; our own Oracle Ace Director Chris Muir has been hitting it hard with numerous JDeveloper ADF workshops; plus we have our regular SQL training to keep your queries sharp, amongst our other courses.

If you're on the lookout for exceptional Oracle training, hit up Sage's website.

My Blog Layout
While nothing has changed yet, I plan to give this site a little revamp and bring it into 2011. I've learnt a few things along the blogging path, so keep an eye out for additions such as Google +1. It's such an interesting concept, and I'll be keeping an eye out to see where it goes down the rabbit hole. I read an interesting analysis at Phandroid, and webmasters can learn more about it at Google.
I also want to make it a little less clunky & boring and perhaps show that I have some of my sister's graphic design genes.

I'm even tempted to go Apex.

Around the Oracle Community
There have been a number of great posts by Oracle bloggers recently that I'd like to share if you haven't seen them already, or in case you missed them.

Eddie Awad shared a short video by Tom Kyte talking about Oracle. It's good to see the occasional video instead of faceless text - it's just a shame our Australian networks just don't make it easy to see this content smoothly on our smartphones.
Speaking of mobiles, Peter Raganitsch has been talking about theming on Apex for mobile devices.
Apex 4.1 is moving forward, and one of the features I'm looking forward to working with is the Error Handling Improvements. If you haven't upgraded from Apex 3.x, why not?!
Plug-ins is one area that I really need to catch up on, experts like Dan McGhan are doing all sorts of crazy work with Apex Plug-ins, demonstrating Apex as the flexible tool it is.
And if you haven't heard or have forgotten, Oracle XE 11g has been released in beta. The world keeps on spinning

An Apex Book Everyone Must Have

And I simply can't go by without mentioning the latest Apress book Expert Oracle Application Express.

I had the wonderful opportunity of being a technical reviewer for this book, and it's simply stunning. You might see my ugly mug somewhere in the first few pages.

Not only that, as you've probably heard, proceeds of this book help support the families of Carl Backstom and Scott Spadafore.

Keep an eye out for other Apress books that are in the works, too. I have a feeling there are a few other good ones on the way ;-)

Now, back to the business of real(ish) work. I should be back to more regular postings from July.

ScottWe.

A simple way to learn more about PL/SQL

2011-05-23T10:25:00.000+08:00

Attention PL/SQL developers - Steven Feuerstein's PL/SQL Challenge website has just been 2.0'd

If you haven't given it a go, now is the time for a fresh crack. It's not just a daily quiz, but a place to learn and keep up to date with Oracle technology - not necessarily with new features, but techniques and capabilities you may not have been aware or, nor had the exposure to.

That and everyone has a chance to win something.

Kudos to Steven and his team of merry men.

Scott.

Anonymous PL/SQL blocks

2011-05-19T12:12:00.001+08:00

Some of you may be familiar with the ability to administer Apex applications via SQL Developer, for instance I could modify the alias of my application thusly:

It opens a popup which allows me to write my new alias.
As with the other facilities of SQL Developer, you can opt to view the relevant SQL for this change.

All Oracle is really doing here is calling the relevant API, but what I also noticed the first time I used it was the autonomous transaction pragma it applied to the anonymous block.

declare
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  wwv_flow_api.set_security_group_id(p_security_group_id=>100001);
  wwv_flow_api.set_application_alias (p_flow_id=>101,p_alias=>'TIM_0101_b');
  commit;
end;
/

In the past I've quite happily applied this pragma for a procedure defined within a package, but this demonstrates we can also define independent transactions within these anonymous PL/SQL blocks, in addition to certain triggers.

Something I added to the memory bank when I first stumbled on it.

A final note - you can also kinda "name" your anonymous block to assist your documentation process, ie - there is no reason why you can't finish with:
END my_anonymous_block;

ScottWe

An random insight into computing history

2011-05-16T20:00:00.001+08:00

In the days of reaching the moon, we amazed ourselves with the mathematical capability we empowered our machines with.

Apollo's 32kg guidance computer was basically 2Mhz and it's memory measured in kilobytes, and gave Neil Armstrong a cryptic memory error just seconds from landing on the moon.

It used to be said that the apollo spacecraft technology is blitzed by "today's" vehicles. Well, today's vehicles are way ahead now, and we can find electronics that exceed Apollo computing power everywhere. This article compares the guidance computer to a 2009 smart phone. I tried to find the exact specifications for something less conspicuous like anti-lock brakes but alas.

Where am I going with this? Recently I was cleaning out the extended garage and found an old computer I was yet to dispose of. This was partially due to nostolgia, and another part of me thought I might be able to find some use for it - well, I did find a use - amusement.

As I pulled it apart, I thought I'd take a few photos and document the occasion. It was dated 1999 so I imagine this is what got me through university. I know 1999 isn't exactly looking through a wondrous time portal, but it was enough to keep me amused for a while - amusement which started with the 5 1/4 inch disk drive!
Looking in the guts I'm surprised at the lack of a modem, unless I moved that 56k beast to a different pc. Another look at the front shows it is a bit of a mutt, with cd drive upgraded to dvd & pasty old disk drive installed to pilfer data off old disks.

The computer belonged to the era where hard disk space was doubling and this affected what (and the amount of) software you could install. Today, space requirements for software such as your operating system is simply absorbed into the mass amount of storage available. I could tell you DOS6 fitted on a handful of 3.5" disks, I can't tell you how much Windows 7 now consumes. Yes, my first computer was an XT using DOS 3ish.

Here is a fuzzy look at the mutilated insides, the close-up shows the original 1GB HD, and the 8GB upgrade, with the Pentium II core and 64mb RAM.

The back of the PC shows the March 1999 build date, with pride by PLE Computers - our local mainstay.

I just noticed the whopping 2 USB ports, wasn't much need for them back then, because at this time for data transfer we all used ZIP drives.

A nice 100/250mb of data in the one iomega disk, great for holding (and potentially losing) all my university work. I was prudent with my backing up - apart from theft and stupidity, with excessive use we also had to avoid the click of death, which (why should I be surprised) you can listen to on Wikipedia.

And on a side thought, one of the podcasts I listen to talked about how much data capacity exists around the world (if that's the right phrase to use) - a result of some study. A secondary finding to that was much of the world's data is redundant. Isn't that a fascinating conclusion, but when you think about it, it becomes completely obvious. We are all consumers of data, downloading what companies around the world have to offer. We also share information - music, videos, photos - all of which could easily be replicated on multiple computers, backup disks, internet servers. I'd like to know how much purely unique data might exist out there? Perhaps a mass amount - I wonder how much the LHC would produce in a few minutes?

Random mentations of an Oracle developer...

Off topic - excellent computing award

2011-05-04T04:44:00.007+08:00

I have been cleaning out the garage and I found a box full of stuff my mum kept from school.

Turns out back when in primary (grade) school when I was twelve (the days when I used already dating logo and what I'm sure was this mathematical adventure), I was given a slight insight into what was to become of my career - an A5 sized certificate.

Have I reached excellence? Well, that's a subjective term (with a good blog entry I just can't find)... but for today, may the fourth be with you while enjoying this very timely parody worth watching.

ScottWe

Oracle Training Australia (plug)

2011-05-03T11:07:00.000+08:00

As it so happens, June is going to be a busy month training for us in Perth, Australia.

For 5 days from the 13th June, ACE Director Chris Muir will be conducting a JDeveloper Workshop in Perth. If you're an Oracle Forms developer who wants to migrate your skill set to the 21st century, this is your course.

Alternatively, you can immerse yourself in the world of Oracle Application Express. From 15th June for 3 days, I will take you on an Application Express workshop - no experience necessary.

To supplement that, we are also holding an Advanced Application Express & New Features workshop for Apex 4.0 on the 28-30th June 2011 in West Perth.

Feel free to contact that Sage team for more information.

If you're somewhere in Australia other than Perth, and you can't make it across in June - arrange a time where we can travel to you to train your team. SQL, JDeveloper, Apex, Forms, DB Tuning... name your poison. We have competitive rates, extensive training notes, and I'll almost guarantee you'll have a good time.

Otherwise, we hope to see you at the next Oracle event.

Scott

A data mining experience

2011-04-14T18:20:00.003+08:00

Hands up who went to Spain in August '08? Well, I did with some friends after my mate's wedding. Last night while talking with the said mate, the topic of earthquakes came up somehow via me mentioning Aaron Rolston.

For a little context, I've visited this site a few times recently to see just how many quakes there are around the world every day - turns out a few dozen. Activity around Japan is just phenomenal.

http://earthquake.usgs.gov/earthquakes/recenteqsww/Quakes/quakes_all.html

So back in Spain - one morning over breakfast, I remember a friend telling us about a news report about a nearby earthquake that occurred the night before while we were playing poker. He mentioned something at the time, not many of us noticed anything - might have been the San Miguel...

So I thought I'd look to see if I could track it down

http://earthquake.usgs.gov/earthquakes/eqarchives/epic/

This is a five day inclusive search around the world in August 2008 - I repeat, only 5 days worth:

So to find those just in Spain, I restricted the region in the search criteria.

The two in highlighted red in the report below are the ones that we were within possibly 3 kms of it, if I remember our exact location correctly. Two evening quakes just over an hour apart, 7km deep.

                    U.  S.  G E O L O G I C A L  S U R V E Y
                     E A R T H Q U A K E  D A T A  B A S E

 FILE CREATED:  Wed Apr 13 15:52:46 2011
 Geographic Grid Search   Earthquakes=         8
 Latitude:   50.000N  -   30.000N
 Longitude:    10.000E  -    20.000W
 Catalog Used: PDE
 Date Range:   2008/08/04   to    2008/08/08
 Data Selection: Historical & Preliminary Data


 CAT    YEAR  MO DA  ORIG TIME   LAT    LONG  DEP  MAGNITUDE  IEM DTSVNWG DIST
                                                              NFO          km
                                                              TF 
 PDE    2008  08 04 060910.21  42.10   -6.82  17  2.8 LgMDD   ... .......      
 PDE    2008  08 05 152121.24  37.00    3.42   0  4.0 UKMDD   ... .......      
 PDE    2008  08 05 224121.93  38.05   -0.78   7  3.5 MLSTR   3F. .......      
 PDE    2008  08 05 234123.44  38.07   -0.78   7  2.7 LgMDD   3F. .......

 PDE    2008  08 07 090314.60  42.94   -1.33   2  2.5 MLLDG   ... .......      
 PDE    2008  08 07 103215.30  41.98    2.56   5  2.6 MLSTR   ... .......      
 PDE    2008  08 08 094711.30  42.36    3.45  10  2.5 LgMDD   ... .......      
 PDE    2008  08 08 225611.15  35.58   -4.70  65  2.8 UKMDD   ... .......

I had a look on google maps for the location, I think we stayed in Los Montesinos, Torrevieja - very close!

The internet is full of amazing information - sometimes it's just a matter of tapping into it, sometimes it's a matter of coming up with the idea.

ScottWe

Observations in pattern matching

2011-04-12T22:06:00.000+08:00

Sometimes while driving the suburbs, I'll see a line of cars the stands out for the most benign reason - each boldly coloured - red - blue - yellow - green.

The first time I became aware of how much it stood out for me, I've kept an eye out for it again - a simple alignment of colours. Look around, often you see is really nothing exciting white, white, grey, red, white, white... more variety in the models. So the bright sequence is an outlier that appears very low on my bell curve.

However, I don't really do cars, but when using computers, sometimes I see strange alignments in behaviour. As a developer, I see issues in code behaviour, and sometimes just the way it looks. It's same same but different for testing colour-blindness with the Ishihara color test; and it would be fascinating to hear something with synaesthesia describing the stroop effect, and how the could also be used to find spies!

These days, many people are becoming proficient in using Google. And the engine learns from the use of everyone. People get familiar, particularly with their regular style of searching. I'm reading a fascinating book right now called Click: What Millions of People Are Doing Online and Why it Matters. It's phenomenal the amount of information to be mined & utilised.

That was a tired ramble, this is what I saw that spurred it on

Just like the four words of Isaiah Mustafa "silver fish hand catch", "computers marijuana guns shareholding" were part of four terms I never thought I'd see together in the top four results.

And onya Google for honouring the first human space flight - pairing nicely with another book I've started by Buzz Aldrin.

Over.

ScottWe

Of course the Australian Oracle Community is alive

2011-04-01T00:12:00.000+08:00

It's just sometimes not everyone hear's about it.

Last Monday I was at the South Australian AUSOUG Conference in Adelaide. This was my first real visit to Adelaide and I was also keen to greet those I've met at previous conferences around Australia, and meet those I haven't had opportunity to see.

The day ended up being quite interesting and fulfilling. SA President Vito Rinaldi mentioned they ended up with around 80 delegates, which he said wasn't bad for this particular event.

As usual, I wasn't overly enthused for the keynote address by Roland Slee, but he had some interesting things to add regarding Oracle in Australia. One interesting point I happened to note down was the notion "economies of scale." It's been a major driving factor of our civilisation for well over one hundred years, but it still doesn't really apply to software development. He took this notion to spruik the power of Oracle Exadata - I was suitably impressed.

I made another great note while in that seminar - an idea for a basic Apex application - don't they come at the strangest times?!

I then had to bust out my first presentation - on Apex Performance. I got some good feedback which was a good offset to the guy I saw at the back of the room nodding off straight after the coffee break. One particular fellow said it was great jot down so many little thoughts on how to generally improve their applications which were basically borne from a self-taught environment.

Next on the agenda was a case study on Agile development by Kevin Wohling. I learned a lot from this one and I thought his analogy of building a house was apt -would you define where to hammer every nail up front? Simply don't over engineer your solutions.
Some good practices were mentioned that I think are regardless of the life-cycle approach - constant communication with users & shield your development team from distractions.

After lunch Guy Harrison's talk on High Performance PL/SQL was his usual high quality. I think info-graphics are great, and his food pyramid for PL/SQL was on the money. From data type usage up the top, down through code structure, data handling and the all important SQL. Again, I noted down another idea - a feature request for the PL/SQL development team regarding bulk binding.

I almost went to Avi Miller's Virtualization and Cloud Computing seminar, but I thought it may have been a little beyond what I was after so I attended Connor's 11g Features for Developers instead. I've seen a variation before, but I'm glad I went as I picked up a few features that I had to note down for looking into further later. I sure wasn't going to soak it all up at the glorious pace that he runs! The most impressive I thought was the ability to manage Editions using TNS services - very cool.
I also noted what he mentioned regarding bulk operations so I didn't repeat the information in my second session.

So my second session was on Bulk Binds, a revision of a presentation I did a few years ago in a WA branch meeting. It was good to leverage what Guy & Connor already glossed over in their presentations and allow the delegates to see a deeper view of what can be done and what should be considered.
The pace was fast, and I'm really thinking the 45 minute timeslots we have here in Australia simply isn't sufficient, particularly when we invite international guests to deliver their 1 hour sessions.

Speaking of international guests, I attended Craig Shallahamer's second session on Evaluating Alternative Performance Solutions. I thought it may have a little outside my league - I was right. I've been happy with the level of mathematics I've seen since high school, and that was a bit much for an afternoon! However he made up for it with his zeal. It was entertaining, and it was a pleasure to catch up with him for drinks afterwards, and ultimately share interesting dinner conversations with him & Tony Scholefield at the Richmond.

Craig has already submitted abstracts for Australia's next event - Insync11 at the Sydney Convention Centre, August 2011. I will be submitting some of my own, so I hope to meet him again in a few months.

Back home in Perth, Sage Computing have some news of our own. We were recently mentioned in an Oracle Customer Snapshot for our work at Department of Treasury and Finance. I can't take any credit for this particular project, but well done Penny, Ray, Eddie & Chris. They all bring aboard specific talents and it sounds like a engaging project - converting a large Oracle Forms application to JDeveloper.

Speaking of, this will be my final segue in this post - Sage have set dates for our next JDeveloper Workshop here in Perth - 13-17th of June 2011 - run by Ace Director Chris Muir.
We will also be scheduling an Apex4.0 course in May or June, which will be an advanced look at Apex4 with special attention to new features - more to come soon.

Phew - I'm glad I'm off to Bali tonight ;-)

SQL Developer 3.0 is out there

2011-03-30T07:33:00.000+08:00

One of the first Oracle related news I noticed this morning was SQL Developer 3.0 has just been released.

Make sure you check out the release notes, while I've downloaded it I think I won't have time to take it out of the box for a little while - so I'll have to be content with the manual :-(

Congratulations to the development team for reaching this significant milestone.

Off topic: 'A' Week

2011-03-22T18:08:00.000+08:00

Some days I decide that since this is my blog, I'll say whatever I like, even if it's not about work! This is my major outlet to the world that is.
Heck, Tim does it all the time ;-)

This week, besides being my birthday week, is 'A' Week. It basically means tell others you’re an atheist and proud of it week. Start some discussions with people about your atheism.

A Week is not about being disrespectful to religion or people who have religious views, it’s about quietly showing that there are more people than may be realised who are ‘Good without God’ and who don’t need religion to influence their lives. Link

Recently I saw Chris asking the question, I was very curious to see who responded. Around the world responses differ when it comes to talk about people's beliefs. This week is about helping break down those barriers as some people even feel bullied for a variety of unfortunate reasons. Of course, at times around the world it goes both ways, so the week is also about understanding that it's not opposing sides of the fence, but a way of life unto it's own.

Many, myself included, would like a world of religious freedom - people can believe anything they want, as long is it does not harm or annoy others. And this goes for many more things beyond religion.

This has been said by others in a much more eloquent manner, but I enjoy the wonders of the universe without looking for something imaginary.

Scott

Date format tolerance

2011-03-15T08:49:00.007+08:00

This is just a little ditty to remind me which date format mask is more tolerant.

SQL> select to_date('21-03-2011','dd-mon-yyyy') from dual;
select to_date('21-03-2011','dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date('21-mar-2011','dd-mm-yyyy') birthday from dual;

BIRTHDAY
-------------------
21-03-2011 00:00:00

1 row selected.

Note that it accepts 'MAR' when using the MM format, but not numerics when using the MON format.

You may like to consider this when defining an application wide default format.

Scott

Apex in the Amazon Cloud

2011-03-08T16:20:00.002+08:00

Hello Oracle Community,

I'm currently researching Oracle Apex on an Amazon EC2 instance, and at the moment good content out there seems a little thin - or quickly dated.

Either that or my google kung fu has weakened :-)

The basics I have covered -

Justin Lokitz has a comprehensive article on setting up Oracle on the cloud;
There is also a good Oracle white paper explaining the setup process;
Bill Hodak from Oracle has defined a perfect machine image;
This is probably the best dialogue I've seen on sending e-mails. I have seen others, but exact solution is unclear and possibly out-of-date;
And Kris has mentioned SEO considerations in regard to Apex generated URLs. There are other decent discussions, but one pertains to EPG setups, and I'm sure things have come along a little more since 3.1.2

My particular interests are -

Sending e-mails from EC2 using UTL_MAIL, preferably with a designated domain (not Amazon)
Prettying up the Apex URLs, and/or other issues to consider with page ranking

No doubt once I start getting my hands dirty, some of theses answers may become more apparent, or I'll have a better idea of how/where to look - but for now if anyone has any notes, tips, experiences, URL suggestions, answer to the life, universe and everything - I would appreciate it!

ScottWe

ps - if you just wanted pictures of pretty clouds, try these - simply stunning.

Update: I've also created an OTN post on this, stay tuned

Adelaide One-Day AUSOUG Conference 2011

2011-03-04T15:56:00.001+08:00

The South Australian branch of AUSOUG are holding a one-day conference on Monday March 28th at the Mercure Grosvenor Hotel in Adelaide.

I will be presenting there, along with fellow Perth-ite Connor McDonald.

I will be looking forward to seeing some people I haven't seen for quite some time, and meeting some others I see on the schedule I have not yet had the opportunity to meet, such as Craig Shallahamer.

Further details can be found at AUSOUG.

ScottWe

Apex Listener Issues

2011-03-03T11:08:00.000+08:00

The configuration for my Apex 4.0 environment on my laptop is basically as you see on the front page for the Apex Listener.

I have Google Chrome on the left; my J2EE Container is Glassfish; and I have Oracle 11gR2 on the right, with Apex4.0 installed.

Sometimes I hate to admit it, but I don't do "networking". My Achilles heal is anything starting with "serv".

So when I rebooted my laptop recently and my browser said I couldn't connect to Apex, I thought - "I wonder what part of that horrible black box in the middle has broken?"

System Unavailable? I don't think so, Mr Laptop.

It wasn't really that bad. I pretended it was a development issue and started eliminating factors.

My database was up
I was about to get into the GlassFish Server Administration console ok (http://localhost:4848/), there didn't seem to be anything out of place.
I restarted the server, the TNS listener, the Apex Listener
I read through some documentation to look for troubleshooting ideas
my apex-config.xml file was present and ok

Then I found myself looking at the username/password combination for APEX_PUBLIC_USER and thought "I wonder if that password has expired..."

Sure enough, all I needed to do was reset the password.

ALTER USER apex_public_user IDENTIFIED BY my_awesome_password;

Problem solved.

I mention this because it could have quite easily bugged me for an hour, and I like to save some people pain. I know there are some out there learning Apex and their deployment is balancing on guesswork from (barely) following Oracle's installation instructions.

In other news, I find myself reading through Oracle's Apex Listener front page, and I don't notice the big red

APEX Listener Release 1.1 is now available

So Apex developers, the Apex Listener Release 1.1 is now available ;-)

ScottWe

I quizzed an Oracle community

2011-03-01T22:51:00.000+08:00

Once was a time when I read a few questions every morning at AskTom. It only took a few minutes but the amount of hints, tips, tricks and general best practice guidelines I learned became so valuable as my experience grew.

I still find it a valuable resource, but these days as a seasoned developer, I found myself a solution with a different style of learning - keeps my mind active and I learn the odd thing occasionally, other times it helps affirm my current understanding of Oracle behaviour.

Steven Feuerstein and his friends have created a (business) daily PL/SQL Challenge. You may have heard about it and wondered if you should give it a go - for most probably a variety of reasons.
Well, you don't even have to compete for the frequent prizes - you can have a private profile; or you may see people providing a link to their public profiles on their blog; or as a resume item.

The main thing is the occasional "test your knowledge" - typically a little snippet of code to have you thinking for a couple of minutes.

Steven has provided players an opportunity to submit their own quiz questions, so if you're curious of the sort of questions that get asked, here's mine from 28th February 2011 - it's a lesson on Short Circuit Evaluation:

I create and populate a table as follows:

CREATE TABLE plch_parts
(
   partnum    INTEGER
 , partname   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_parts
        VALUES (1, 'Mouse');

   INSERT INTO plch_parts
        VALUES (100, 'Keyboard');
   COMMIT;
END;
/

Then I define the following function:

CREATE OR REPLACE FUNCTION part_exists (pn_partnum  IN  plch_parts.partnum%TYPE)
RETURN BOOLEAN IS
  ln_partname  plch_parts.partname%TYPE;
BEGIN
  SELECT partname
  INTO   ln_partname
  FROM   plch_parts
  WHERE  partnum = pn_partnum;

  DBMS_OUTPUT.PUT_LINE(ln_partname);

  RETURN TRUE;
EXCEPTION WHEN NO_DATA_FOUND THEN
  RETURN FALSE;
END part_exists;
/

Which of the choices, when used in place of the /*IF CONDITION*/ comment in the following block,
will result the subsequent output being displayed on the screen after the block is executed?

BEGIN
  /*IF CONDITION*/
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

Mouse
Keyboard
TRUE

A	IF part_exists(1) OR part_exists(100) THEN
B	IF NVL(part_exists(1) ,part_exists(100)) THEN
C	IF COALESCE(part_exists(1) ,part_exists(100)) THEN
D	IF part_exists(1) AND part_exists(100) THEN
E	IF CASE WHEN part_exists(1) OR part_exists(100) THEN TRUE END THEN

The rules are well written, the idea is well presented (unlike my hand written html table representation here), and the user feedback is fantastic. Steven also has an associated blog for constructive discussions on the occasional contentious question, or question of general interest or quirkiness.

My question was well received by some friends, and Steven now provides users with simple, key survey results from each question - and I was happy with mine.

The answer is B & D - "NVL" & "AND"
I provided the following verification code to assist explanation of the answer:

BEGIN
  -- As soon as the first expression returns true, PL/SQL no longer needs to evaluate the second. This is perfect for situations where you can put more efficient calculations first.
  IF part_exists(1)
  OR part_exists(100) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- Ideally this would behave as per COALESCE, but unfortunately NVL does not support short circuit evaluation.
  IF NVL(part_exists(1)
        ,part_exists(100)) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- COALESCE is ANSI-defined shorthand for similarly written CASE functions, thereby supporting short circuit evaluation. COALESCE is generally preferred to NVL.
  IF COALESCE(part_exists(1)
             ,part_exists(100)) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- Since this uses the AND operator, both expressions need to be evaluated to determine if the entire boolean equation is true. Oracle would use short-circuit evaluation if the situation permits, for instance, if the first expression returned false the second would not be evaluated.
  IF part_exists(1)
  AND part_exists(100) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- The Oracle database will use short circuit evaluation for CASE expressions.
  IF CASE WHEN part_exists(1)
  OR part_exists(100) THEN TRUE END THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

There are currently over 1000 regular players, but only about two dozen regulars are registered as Australian - I've seen more than that at our AUSOUG branch meetings! Come on Aussies - "represent!"

It's a great learning & affirming tool - I'll be on the lookout for inspiration for submitting more questions. :-)

ScottWe

ps - no, you can't cut & paste the code while a question is active

Advert: SAGE Computing Services Training - 2011

2011-03-01T21:30:00.000+08:00

This April SAGE Computing Services are holding a training event that may interest beginners, business users, testers & developers alike.

April 18th-21st 2011 - Oracle SQL 10/11g Workshop - Perth
The course is designed to provide the student with a basis for developing systems using the Oracle database. The SQL language is covered from simple to complex constructs. Guidelines are provided on writing SQL for optimum performance and ease of maintenance.

Stay tuned as we are also looking to schedule a JDeveloper Workshop in the next few months, and are happy to hear about any requests for Application Express 3.x or 4.0 training.

We also arrange on-site training on-demand around Australia.

Hit up our website for further training enquiries and follow through to the contact us details page. We look forward to hearing from you soon.

Android Me

2011-02-26T12:12:00.000+08:00

I have an Android phone, quite happy with the software after moving away from an iPhone, but I think I will forever want a faster chip on the hardware.

I like browsing through the market, looking around at what people around the world have created - a bit like the enjoyment some people get out of shopping, but in the comfort of my own lounge chair.

I came across Androidify, and in about two minutes came up with this apt creation:

Now, for those who've met me in person, does it fit the bill? :-)

ScottWe

Review: Oracle Apex 4.0 Cookbook

2011-02-25T15:47:00.000+08:00

I think Apex would be a hard topic to write book on, but I think the analogy Oracle APEX 4.0 Cookbook authors Marcel van der Plas and Michel van Zoest utilise a good, ahem... recipe. And it’s naked chef cooking, not Iron Chef, MasterChef nor is it quite "My First Cookbook."

I think the by-line on the cover is most apt – Quick answers to common problems. The other is "Over 80 great recipes to develop and deploy fast, secure, and modern web applications with Oracle Application Express 4.0"

Overall, I think the book would be great for numerous audiences and I think the "who this book is for" section is spot on. In particular, newer developers to the Apex environment would benefit from the easy to read, piecemeal approach this book has to offer. When I first started reading, it came to mind what a great reference book this would be for a university. Don’t get me wrong, advanced developers might also learn a thing or two. Those conversant with Apex 3.2 would also find this useful, as it covers many Apex 4.0 topics.

Straight onto the pan was a bio of and message from the author. I would imagine some people don’t put too much thought into these few pages, but I think it helps put things in context. It also recognises theses authors who put a lot of work into producing the content for these books. Good on the reviewers, too – Dimitri Gielis and Surachart Opun.

Back to the unique nature of the book - a collection of recipes. I think it’s a unique idea and works well in the Apex environment where you might need a lot of screenshots to illustrate the technique and goal for a given situation. Alternatively, it could be a boring book with a lot of prose and bolded words that distract you from what you’re reading – I think that’s what put me off Java long ago when I had a bad uni textbook.

It takes a basic idea or and describes the concept in a paragraph or two. What are you cooking?

Then they take you though the "Getting ready" process. This could be something small, like having certain settings set or having a script on hand. Find your ingredients!

The "How to do it" section is obviously the process to get the job done. Screenshots are used conservatively, either showing the starting point or end result, or some key illustration. Step-by-step instructions are also utilised, for instance when leading you through a wizard, which really is what Apex is all about when you start off. The problem with this is if you miss a step, start in the wrong spot, can’t find the button they’re referring to, or version patches since the book release has changed things in such a way that may confuse the reader. I don’t think any of this can really be avoided, hence the appeal to the recipe style book. Method to cook.

There is a "How it works" section that follows the recipe. You don’t get this in your usual food cookbooks and I think it’s a great tool to convey to the user another way of thinking about what they’ve just done. Do you ever listen to somebody explain how to do something and think "why" or "how" ? I know I do, and that’s probably why I wanted just a little bit more out of this section of the book. My advice would be to take what you’ve learned, then go out and look for Oracle-by-examples or detailed blog entries describing the relevant task. Why you can't burn dihydrogen monoxide.

That being said, some recipes include a "There’s more" and a "See also" section. This really applies to all recipes as they don’t go into ridiculous amounts of detail, and they describe the great potential Oracle Apex has in crafting applications – this isn’t War & Peace.

Sometimes the format shows just how much of a winner it is. I find the topic of multi-language applications a little dry, but the recipe format really works well to break things down and make dry topics more readable. There were some good highlights too - the APIs chapter had the best explanation for how checkboxes work I've seen for a while. I also found the chapter on extending Apex wide ranging. It touched on topics that people will want to explore, and uses examples that people may want. Even calling Apex from Oracle Forms is briefly covered.

While it's not the final solution for most things, our family considers recipe books a starting point for ideas. I could go step-by-step and create something that looks nothing like the picture (my gardening brown thumb carries through to the kitchen), but the Apex 4.0 Cookbook doesn't convey those delusions. It tells it like it is, and it's up to the reader to take these ideas further and create something masterful.

Well done to Marcel and Michel, and to the team at Packt Publishing.

You can find another review by Christian Rokitta here, and peruse through the book's contents here.

Go start baking and serve out the Oracle community some delectable dishes!

PL/SQL quickie returning results

2011-02-18T10:26:00.001+08:00

Oh how I love a (good) pun.

In depth blog posts are great, but sometimes little quick examples are all we feel like consuming.

Those out there new(ish) to PL/SQL, familiarise yourselves with the RETURNING clause, it can provide a useful efficiency. See this example here which shows the potential - grab extra information while updating a table instead of requiring another SELECT. You can also use it in conjunction to BULK COLLECT.


CREATE TABLE sw_temp (a NUMBER ,b NUMBER);

DECLARE
  l_a  sw_temp.a%TYPE;
  l_b  sw_temp.b%TYPE;
BEGIN
  INSERT INTO sw_temp VALUES (1, 1);

  UPDATE sw_temp 
  SET a = 2
  RETURNING a, b
  INTO l_a, l_b;
  
  dbms_output.put_line('a:'||l_a);
  dbms_output.put_line('b:'||l_b);
END quickie;
/

a:2
b:1

PL/SQL procedure successfully completed.

DROP TABLE sw_temp;

ScottWe

Off Topic: a historical birthday

2011-02-14T07:05:00.000+08:00

It's official, it was 2 days ago - well, in some places of the world it's still yesterday, but at least you don't get the same posts all on one day. I'm late because I was enjoying my weekend!

12th February was the birthday of a historical scientific figure. If you can work out who authored the provided image (source), I think you deserve to click on this link for a little giggle - but only if you figure it out ;-)

Here is a modern day version of the diagram.

Happy days.

Validating e-mail in PL/SQL

2011-02-11T08:17:00.001+08:00

This seems like a frequent request across all languages. I google search can give you all sorts of options for validating a singular e-mail address.

For instance, here's one option using a regular expression:

FUNCTION val_email
  (p_email  IN  VARCHAR2)
  RETURN BOOLEAN IS
BEGIN
  RETURN REGEXP_SUBSTR (p_email, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}') IS NOT NULL OR p_email IS NULL;
END val_email;

Couple this with the common need/want to enter multiple addresses in the one field, I wondered how best to action that requirement - I came up with this:

FUNCTION val_email_string
  (p_email      IN  VARCHAR2
  ,p_separator  IN  VARCHAR2 DEFAULT ',')
  RETURN VARCHAR2 IS
  lt_emails  apex_application_global.vc_arr2;
  lb_valid   BOOLEAN DEFAULT TRUE;
  ln_error   PLS_INTEGER;
BEGIN
  -- split string into separate addresses
  lt_emails := apex_util.string_to_table
                (p_string    => REPLACE(p_email,' ')
                ,p_separator => p_separator);

  << email_val >>
  FOR i IN 1..lt_emails.COUNT LOOP
    lb_valid := val_email(lt_emails(i));
    -- If error occurs, record the problem number and don't bother continuing
    ln_error := i;
    EXIT WHEN NOT lb_valid;
  END LOOP email_val;
  IF NOT lb_valid THEN
    -- return the message with the first problem address
    RETURN 'At least one e-mail not valid: '||lt_emails(ln_error);
  ELSE
    -- no errors occurred
    RETURN NULL;
  END IF;
END val_email_string;

Any suggested improvements?

It was designed for Apex validation with type "Function Returning Error Text"

Is Apex heading in the right direction?

2011-02-04T12:15:00.000+08:00

Ever since reading the Oracle Application Express Statement of Direction for 4.1, I've had it in mind to share some thoughts on this forum. So why not now when the water's settled, it's a new year after all and apparently we can look forward to 4.1 goodness in 2011.

Development for Mobile Applications - any product, software, company, fish in the ocean with a vision of the future should be considering mobile applications. The word "applications" can have multiple contexts here, but I must say even for websites - it really grinds my gears when I encounter what I'm sure is a new website that doesn't appear nicely on my mobile phone browser. Good on Oracle for bearing this in mind.
Charting - "without using Flash", obviously the iPhone is being considered here, but considering the CPU drain with Flash I think it's great that other charting options are being developed.
Error Handling - I'm forever updating the (ugly!) Error Page Template with some sexy scripting here, so I'm glad that the Apex team are starting to tidy some of the looser ends of the product.
Interactive Reporting - Not so sure about multiple reports per page, unless perhaps they are little ones on a very dynamic page; but supporting newer database features like pivot queries is great to see.
Tabular forms - Multiple on one page? I avoid them like the plague myself, but more functionality with validation would be good - wasn't that impressed with the 4.0 improvements.
Mater-Detail-Detail - makes sense.
Dynamic Actions - never has a declarative feature offered so much potential and provided flexible options, I'm glad this feature is being developed further.
Plug-Ins - The community has really embraced plug-ins, you'd be mad not to extend it.
Use of ROWID - this has been on my wishlist for a while, it irks me this facility wasn't in a much earlier release.
Modal Dialogue - I can only imagine this is a JQuery dialogue, something I advocate and have used a number of times to help ensure the end-user takes notice of pop-up messages, instead of blindly clicking.
Websheets - do people really use this?! Please tell me the initial Apex4 release wasn't due to dusting off websheets...
Data Upload - if this is what I think it is, I can see many applications for it and I think it will help developers the world over from reinventing another wheel. Perhaps an adjustment of the Data Load facility in Apex utilities?
Accessibility - I'm finding more clients ask that this is considered in the requirements, good on you, Oracle.
Numerous functional and performance improvements - I'm sure in this random bucket of goodies there will be the odd pearl. I remember re-writing our Oracle Apex training application from 3.x to 4.0, and it really was the little things that made me happy - unnamed improvements that made my task easier.

As for wishlist features, I'm the sort of bloke that grumbles at Apex every now and then, but do you think I can collate my whims on demand? Hardly...

Column reordering - one of the most frustrating but regular tasks in development, aiming for that little arrow to re-order fields. Perhaps a drag and drop mechanism?
Handling CSVs - depending on what that data upload improvement entails, I think it would be great to offer a black box that facilitates uploading a CSV file into an Apex collection
Validation that communicates - it would be wonderful to construct validation dependencies, enough said.
ROWID - I'll mention it down here too, handing DML by ROWID in lieu of primary keys is overdue.

And to top it all off, if Apex 4.1 is shipped with Oracle 11g XE, that would be super.

So is Oracle Apex heading in the right direction? You bet. All these focuses show that the Oracle team really are listening to the heartbeat of the development community - and of the web world at large, in my humble opinion anyway.

Scott

Apex Object Synonyms

2011-02-03T17:02:00.000+08:00

It's been almost a month since my last post - it's been January and I'm going to leave it at that.

I thought I'd continue 2011 with a little snippet I wrote to save some of my frustrations.

Occasionally I find myself reading the package spec for an Oracle Apex package, and I'd like to know the synonym name I can refer to it with - or I'm aware of a synonym name and I'd like to pull up the package specification for a look-see.

Either way, I always find myself trying to remember what to replace with what, seeing now it's usually just WWV_FLOW <-> APEX, but sometimes it's APEX <-> HTMLDB, and sometimes it's something else...

So I wrote a little query to help myself do the mapping between synonym name and package name, maybe you'll take it on board:

SELECT owner, synonym_name, table_name
FROM   dba_synonyms
WHERE  synonym_name LIKE 'APEX/_%' ESCAPE '/'
AND    owner LIKE 'APEX/_%' ESCAPE '/'
ORDER BY synonym_name
/

OWNER        SYNONYM_NAME                   TABLE_NAME
------------ ------------------------------ ------------------------------
APEX_030200  APEX_ACTIVITY_LOG              WWV_FLOW_USER_ACTIVITY_LOG
APEX_030200  APEX_APPLICATION               WWV_FLOW
APEX_030200  APEX_APPLICATION_FILES         WWV_FLOW_FILES
APEX_030200  APEX_APPLICATION_GLOBAL        WWV_FLOW_GLOBAL
APEX_030200  APEX_COLLECTION                WWV_FLOW_COLLECTION
APEX_030200  APEX_COLLECTIONS               WWV_FLOW_COLLECTIONS
APEX_030200  APEX_CUSTOM_AUTH               HTMLDB_CUSTOM_AUTH
APEX_030200  APEX_INSTANCE_ADMIN            WWV_FLOW_INSTANCE_ADMIN
APEX_030200  APEX_ITEM                      HTMLDB_ITEM
APEX_030200  APEX_LANG                      HTMLDB_LANG
APEX_030200  APEX_LDAP                      WWV_FLOW_LDAP
APEX_030200  APEX_LOGIN                     HTMLDB_LOGIN
APEX_030200  APEX_MAIL                      WWV_FLOW_MAIL
APEX_030200  APEX_MAIL_ATTACHMENTS          WWV_FLOW_USER_MAIL_ATTACHMENTS
APEX_030200  APEX_MAIL_LOG                  WWV_FLOW_USER_MAIL_LOG
APEX_030200  APEX_MAIL_QUEUE                WWV_FLOW_USER_MAIL_QUEUE
APEX_030200  APEX_PLSQL_JOB                 WWV_FLOW_PLSQL_JOB
APEX_030200  APEX_PLSQL_JOBS                WWV_FLOW_PLSQL_JOBS
APEX_030200  APEX_SITE_ADMIN_PRIVS          HTMLDB_SITE_ADMIN_PRIVS
APEX_030200  APEX_UI_DEFAULT                WWV_FLOW_HINT
APEX_030200  APEX_USER_ACCESS_LOG           WWV_FLOW_USER_ACCESS_LOG
APEX_030200  APEX_UTIL                      HTMLDB_UTIL

22 rows selected.

Yes, unfortunately at the moment I'm doing most of my work in Apex3.x - I feel so dated!

Adding tooltips to your Apex report

2011-01-04T11:04:00.000+08:00

There are a number of ways to publish tooltips in your application, many of them much more verbose than they need to be.

Some of them implement fairly fancy JavaScript and CSS, but there is a simple way to plug in the default tooltip behaviour into your Apex reports.

When you edit your report column attributes, add the following code into the HTML expression.

<span title="#MY_TOOLTIP_COLUMN#">#MY_COLUMN#</span>

These attributes typically have decent help in regard to the sort of coding examples you can apply.

So this setup places the data from the selected column my_tooltip_column, and displays the value in my_column as you'd normally expect.

And is the display from Google Chrome:

Simple but effective - and flexible.

Basic Apex scripting - show/hide & labels

2010-12-29T16:07:00.000+08:00

Sometimes the scripting requirements for an Oracle Apex page are fairly simple, yet determining the required modifications can be frustrating, particularly for a beginner in either Apex or HTML behaviours.

Recently I had the requirement of showing or hiding both the label and input item, depending on the selection within a select list.

So with this example, let's say my requirement is to hide the "Other Description" label and input item if "My select list" equals "- Select me -"
Otherwise, display the items and as an added bonus - set the label to the value of the selected list item.

First I need to modify the label so I have the ability to reference it in my JavaScript. So I edit my P1_OTHER_DESCRIPTION field and extend the HTML Table Cell Attributes in the Label section (not the Element section).

Here I added id="label_other"

Now I can define a script to do my work. It's highly likely this script will be used solely on this page so there's no real harm in adding it directly to the page attributes - unless you have a dedicated JavaScript file you add your scripting code.

So this would be added to the page footer text:

<script language="JavaScript" type="text/javascript">
<!--
function toggleOtherDesc(){
  $f_Hide_On_Value_Item('P1_MY_SELECTLIST',['P1_OTHER_DESCRIPTION','LABEL_OTHER'],'hide_desc');

  //I could also change my actual label, for instance to the value of my select list input item
  document.getElementById('LABEL_OTHER').innerText = $v('P1_MY_SELECTLIST'); 
}
toggleOtherDesc();
//-->
</script>

Some points to note here

$f_Hide_On_Value_Item is a pre-defined function you can call to hide a list of items based on the value of another. Its behaviour is documented online here. In this case I listed both the label I created an id for plus, the item name - surrounded by square brackets as to define an array.
'hide_desc' is the value I've set in this case when the input description is '- Select me'
.innerText allows me to set the descriptive label, as long as I supply the id I defined for the label, not the item name you see in the Apex builder.
$v will return the value in the field, as it would be posted. This is also documented in the JavaScript API reference page.
The highlighted line 9 means the function will be called when you open the page, in this case after it's rendered. That way the fields will be shown/hidden depending on the set value in the select list.

Lastly, I can modify the P1_MY_SELECTLIST item to call the JavaScript each time the selection changes.

So I add onChange="javascript:toggleOtherDesc();" to the HTML Form Element Attributes in the Element section.

So the final product will change the label to the selected value in the select list, or hide the label and input field if '- Select me -' is chosen.

There you have it!

Holiday season cometh, want to decorate your laptop?

2010-12-23T19:09:00.000+08:00

It's almost a long weekend. Awesome!

Firstly, I'd like to offer a brief sentiment: some people around the world use this time to celebrate Christmas. I find it a somewhat uncomfortable time because I am one of few in my country, certainly in my circle of friends and family that do not. For those curious, recently someone else found almost the perfect words that fit my noggin. I guess I find solace in the fact that at least a third of the world's population don't celebrate it either.

However... for those of you that do, I have a great (albeit late) gift idea for you! They are called Laptop wraps, and here is a funky design example:

For those with small to large businesses, you may even consider it a great tax write-off while promoting your business. During this year's conference, we certainly had many people interested in having one designed to suit their brand. As I was going through the airport scanners recently, it even caught the favourable and approving eye of some of the security staff. Here is my laptop -

So if you want one (or many), my sister, Vicki, is in the process of populating her website full of various ideas along the same concept. It doesn't matter where in the world you live, you can order one of these; and/or ask her to help design one to suit your business or taste. So give it a visit: ArtWraps.com.au

They're pretty easy to apply, I used a ruler, some scissors, credit card, and a screen-cloth (which you can see in the photo)

I think you'll agree the final product looks a lot better than advertising for the company who built your chosen laptop. And for those consultants out there, what better way to promote your brand. I'm getting comments everywhere asking about it (and yes, sis, I'm letting them know about you ;-)

Maybe if you're a complete Oracle/Java fanboi, you could borrow a design from here ;-)

As a little extra plug, she has another website promoting her graphic design business, she's been doing all sorts of design activities and sign-writing for years - www.grovewesley.com

Anyway, I hope you all enjoy whatever it is you choose to do this time of year, and make sure you have a relaxing time doing it. Being around 35C here in the southern hemisphere, I won't be far from the beach or a pool.

Happy holidays!

Modifying Apex Tab behaviour

2010-12-22T19:25:00.003+08:00

Today I was having a minor battle with tabs in Oracle Apex (3.2).

Sometimes you don't necessarily want it to submit the page when you click on a tab, so I was having a think about options to override the functionality.

I found an interesting discussion here on OTN, but I came up with something else involving On Demand processes. I'm still deciding whether it's an overkill for my situation, but I thought I'd post it as it may help someone else one day.

The solution involved :

Current tab page template
Script on page zero
On-demand page process

My original "Current Tab" in my page template looked like this:

<td class="t9tabCurrent">
<a href="#TAB_LINK#" class="t9tabCurrent">#TAB_LABEL#</a>
#TAB_INLINE_EDIT#
</td>

I modified it to:

<td class="t9tabCurrent">
<a href=javascript:tabNav("#TAB_LINK#") class="t9tabCurrent">#TAB_LABEL#</a>
#TAB_INLINE_EDIT#
</td>

This calls a script I defined on page zero, passing through the original #TAB_LINK# code.

<script language="JavaScript">
<!--
function tabNav(p_orig)
{
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=tab_target',$v('pFlowStepId'));
    get.addParam('x01',p_orig);
    vReturn = get.get();
    window.location = vReturn;
}
-->
</script>

The script will catch any click on the tab set. It takes the #TAB_LINK# substitution value and sends it to the on-demand process for processing. Then it re-directs the page to the resulting vReturn value.

My on demand process just encapsulates the code, so it's a call to a package I have defined. The g_x01 variable is the value I added in the AJAX call :
htp.p(my_pkg.tab_target(apex_application.g_x01));
This function definition simply replicates existing behaviour, but without the page submit.

FUNCTION tab_target
  (p_source  VARCHAR2)
  RETURN VARCHAR2 IS
-- Replicate tab functionality, but not as a submit
  lc_target  VARCHAR2(200);
  lc_delim   VARCHAR2(1) := q'[']';
BEGIN
  SELECT 'f?p='||v('APP_ID')||':'||tab_page||':'||v('SESSION')||'::'||v('DEBUG')||':' -- essentially existing behaviour
  INTO  lc_target
  FROM  apex_application_tabs
  WHERE application_id = v('APP_ID')
  AND   tab_name = SUBSTR(p_source -- obtains T_HOME from -- javascript:doSubmit('T_HOME')
                         ,INSTR(p_source,lc_delim)+1
                         ,INSTR(p_source,lc_delim,1,2)-1-INSTR(p_source,lc_delim));

  RETURN lc_target;
END tab_target;

You could extend this to do whatever you like, depending on which tab has been pressed. I know in the past I've considered wanting to pass parameters on tab press...

Hopefully all the code is displayed ok, I'm having trouble finding an easy way to paste html examples into blogspot. I'm happy for any suggestions on that matter...

So, is anyone still working or reading blogs right now, or am I one of few? ;-)

The bar has been raised

2010-12-02T08:29:00.000+08:00

One day, I'd like to see something like this at an Oracle event.

Sure, it might be a little difficult if it's live, but I'm sure the technology will get there. Perhaps we'll see more of this type of thing once our broadband network gets up to scratch.

On a side note, look out for circles that do wild things - Japan in the 40s (medium red dot), China in the 50s (big red dot), Rwanda in the 90s (little blue dot), Middle East in the late 90s (green dots)... sense a trend?

Apparently Hans Rosling has some good TED talks, I might have to look those up.

Spotted at Pharyngula

My thoughts on presenting

2010-11-30T22:33:00.000+08:00

When it comes to presenting on Oracle topics, I am by no means a professional, nor would I consider myself an experienced presenter like Penny Cookson or Connor McDonald - in fact the delivery he mustered at this year's conference was outstanding.

I currently have seven presentations under my belt with 13 deliveries. I've experimented with a few different styles in an attempt to find what works best for me.

That being said, I'd like to offer a few words & discoveries to those newer at this to me, or even those considering submitting a presentation for your local event, without sounding like this.

#1 - nobody wants you to fail

Given the feedback and support I received as a new presenter, I immediately got the feeling that people wanted me to succeed.
As an attendee to many a presentation at conferences, branch meetings and other events - I'm sure I share the feeling amongst others that you will the person behind the lectern to succeed regardless of troubles faced.
As a volunteer at AUSOUG conferences, I've overheard conversations and witnessed feedback provided even when a demonstration blue screens at the critical moment - people are on your side.

We are all human, we want our fellow species to succeed in that position that many fear over death.

#2 - write a presentation

It sounds a little silly, but keep an eye out for an idea then start writing one. Do it from the top down - get yourself three main objectives, an agenda, a list of issues to cover, some slide headings... it flows. You don't have to commit yourself to a particular date - although that does help some people. However, once you have one in the bag, more ideas come from a variety of sources.

After years of ~~looking~~ procrastinating, now I've always got a handful of topic ideas that I'd love the time to investigate and write. If you're struggling to think of anything, look through past conference programs or have a look here - Tom Kyte started a discussion on it.

Stick with topics that interest you, because ultimately you will know these topics very well and that knowledge will enhance your career, either through product knowledge for your job or prospective clients witnessing your skills. Once again, I think Seth pretty much nails it.

Once you have it in the back of your mind, it's amazing where topic ideas come from.

#3 - be prepared

I'm not sure I can emphasize enough the importance of this one. I think Connor did the same when he was providing advice before my first session. I said above that people are ultimately there for the content - and if that isn't up to scratch, what do you have left? Your charm and charisma? We does that get car salesmen?

So what does it mean to be prepared? Ultimately, I think it means you must know your content. You must be able to rationalise a question and construct a response based on your understanding of the fundamentals, not just regurgitate a crammed fact. We all learnt from this mistake in high school. Sometimes with bleeding edge content you won't know information, or if the question borders the scope of your presentation. Then you just need the confidence to say "I don't know", then carry on further to say it would be good to investigate, or offer a way to potentially test the issue. That comes from understanding.

Preparation should also relate to any demonstrations you may have. Rule 1 - always have representations of your demo in slide form, you never know what's going to happen on the day. If you repeat the presentation one day, it will also be a nice reminder of how things were meant to happen. You can also use it to post your presentation on the net.

It's also a good idea to ensure your demonstrations are repeatable. Just like your polished production-level scripts, it's a good idea to have a test case that will clean itself up and be able to execute it more than once. People may like to see that example shown again. You don't want flaky demonstrations because it's difficult to think on the fly if something goes wrong.

Have a trial run at home. Present your slides to your wall/pet/partner/colleague - whatever works. Do it in front of a mirror to see your mannerisms and see if you move too much. Make sure you're speaking fluently, know what words you want to use. Make sure the slides flow and make sense together. Be consistent and make most of the clicking change the actual slides and limit internal events, you may forget they exist.

Connor gave me a rule of thumb and suggested you know the content of the next 2-3 slides. I noticed in Chris Muir's recent effort he had little grey text in the bottom corner to prompt what the next slide was about.

At the end of the day, the audience will know the difference between someone who has put the effort in and understands their content vs a fraud who is stumbling their way over plagiarised content.

#4 - simplify content

What do you want to see in a presentation? Nitty gritty detail that will one day serve as a reference item, or a bunch of ideas that you can use to add to your own repository of creativity?

I have been trying to limit the amount of code in my developer-based presentations. Nobody coming out of the seminar will remember code detail, but they'll remember the key ideas or words that they can look up during their own research that they will have to do regardless so they can apply it to their scenario.

My latest presentation was a bunch of simple concepts with perhaps the odd coding example that provides a starting point to a solution. It would be difficult to create and convey a bunch of case studies to attendees if you're trying to offer a decent amount of information without causing confusion.

Limit ideas/concepts per slide.

#5 - beware scope creep

After a while I noticed that 40 minutes to get your presentation out turns out to be a short amount of time. When I was in school I would think that even 20 minutes for a report felt like an eternity. I now need to ensure I stay focused on certain goals and not introduce too much semi-relevant information.

Phil Winstanley concisely covers some key tips in his 24 slides on good presenting. Slide 18 on structuring your presentation shows what I agree is key to avoiding scope creep.

- Key message

- Define the problem

- Benefits of solving the problem

- Proposed solution

- Reinforce key message

- Conclude and close

Information can be quite evenly shared over these points as well - it doesn't need to be all about the proposed solution. Everyone also needs to understand how to identify the problem and what the benefits would be if we tackled it.

Having a key message at the start helps put everything into perspective. Provide the big picture before attempting to describe the detail. Think about how you may wish this information presented to yourself. Sometimes it makes me feel better knowing that I've given the audience key objectives to keep pondering.

Think different. Your key message doesn't need to be displayed as an agenda slide.

#6 - get a hand-held presenter

This is the one I have, but companies like Logitech are always releasing snazzy new ones. Having a presenter will give you the flexibility to walk away from your laptop (or other device that does the job these days). I think the presenting style demonstrated by Dick Hardt was damaged by being chained to the laptop.
Beyond the obvious advantage of having a laser pointer, these devices allow you to step forward & back through the slides when you are ready. Mine also has a setting that allows me to set a timer, and it will vibrate five minutes before the end - a good cue for winding up.
Just limit the movement of the laser dot - just imagine what your pet would do with it zipping around the screen everywhere!

Having freedom to walk away from the laptop may help settle your nerves.

#7 - identify 2-4 people around the room to alternate your attention

I find that if my eyes focus on just a handful of people as I speak and wander about, I limit the amount of distractions I face and stop myself from getting dizzy. I also find these people a gauge on how the talk is going - whether I need to up the pace or concentrate on certain slides.

Don't make it a hard & fast rule that it will always be people sitting in the four corners - you won't always have people sitting in these locations and you kinda want to avoid the habitual sleeper. I also tend to avoid those of a particular authority as they can add undesired & unwarranted pressure.

I also don't agree with trying to imagine people naked - I'm sure that's just an old wive's tale. I prefer to think I'm just talking amongst friends.

This will ultimately limit your movements and allow people to focus more on what you have to show & say.

#8 - body language

Your body speaks a thousand words. It is often said that if aliens came to observe us, they would think the majority of our communication is non-auditory. I think this site explains my thoughts well. I let my enthusiasm for my topic (as nerdy as some people say it makes me) steer the ship when it comes to my movements, and that also seems to assist audience enjoyment and satisfaction - if I'm coming across happy, it's hard to ignore that contagion. Trouble is, with that I always forget to pause - I'm still learning the best time to throw pauses in. I've seen good presenters do it at the perfect moments, I'm still working on that skill.

Speak with the confidence that you believe what you're saying.

#9 - they are there for the content

At the end of the day, if your slides aren't projecting well; if you have an accent foreign to the majority; or your demo just isn't playing ball - people want quality verbal/visual content. If at least one of these succeeds, you've probably made your mark.

The rest will probably come with practice and experience.

No doubt over the months & years these thoughts will change and evolve into something else, but it's where I stand right now.

At the end of the day one may ask, why present? For starters, as an example, it helped provide me the opportunity to work for Penny in a job that suits me perfectly. You never know the opportunities it might present. You also gain thorough knowledge of your chosen topic - a skill that won't go astray in your day-to-day job. So there's two-three good examples straight off the bat.

I hope I've provided at least something to think about, so you can go away an adapt to your own style and scenario. No doubt some of the suggestions here may not fit the type of information you need to get across. For me they've worked for semi-technical Oracle developer information.

I'll leave this with another site I might start sinking my teeth into:
http://speakingaboutpresenting.com/

ScottWe

Ubuntu, here I come...

2010-11-30T08:21:00.000+08:00

For many many years, I've promised myself I would learn a flavour of Linux. After suffering some more Windows pain yesterday, a few contacts suggested Ubuntu, which has been on my short-list for a while.

I have my old laptop which has been a bit of a trusty work-horse (except for the frequent blue-screens), so it's now dual-booting into Ubuntu 10.10 - in fact I'm writing this post via Ubuntu!

First impressions - wow. While I can see the learning curve will be a little steep at times, it seems that releases such as Ubuntu have reached a level of maturity that will hopefully see a larger uptake from professionals around the world - particularly (I suspect) from University students whose brain will be like a sponge.

I may liken it a little to all the benefits of the simplicity of an iPhone, with the flexibility of an Android - let's hope that stays true.

I'm in the process of attempting to install Oracle 11gR2 with the following instructions (thanks Pythian). I've come across my first of many hurdles...

In the meantime, I'm downloading the Debian package for OracleXE, see if that's a little easier for my baby teeth to bite into.

I've registered for the Ubuntu forums, and will always welcome further advice.

ScottWe

SAGE 2010 AUSOUG Conference presentations available

2010-11-29T21:14:00.000+08:00

To all those that requested or are interested in our presentations from the AUSOUG conference, all five are now available on the SAGE website - click on the New! 2010 Presentations link.

As for all other presentations, the AUSOUG committee are in the process of chasing down those we did not obtain and with any luck, should be available on the AUSOUG website before the next conference.

You may even find some on the websites of the respective presenters. Big names such as Steven Feuerstein and Tom Kyte always have their's available on-line. However for presentations such as Guy Harrison's keynote & Connor McDonald's 350+ slide masterpiece - you're never going to get the same value reading it as being there - their delivery was impeccable.

Truth be told, the WA state committee has gathered fresh and keen members this year, and we are pushing hard to make dramatic improvements to the design and functionality of the site. During the AGM, it was mentioned that around March 2011 should see the fruition of this change.

On top of website changes, we have received good feedback from the conference this year and we have plenty of changes and improvements in mind for next year to make it more exciting. I look forward to how many of these improvements we can fit in, and 2012 should be even better!

One of these changes is to ensure we get better attendance for the AGM, since we would like to better serve and provide for our members. This means we will try to schedule it so it's not the same time the drinks & nibblies come out ;-)

If you have something to contribute, or a super idea to suggest - you don't even have to until then, contact the committee today.

This should be my last user group related post this year, back to normal Oracle blogging now. If I don't see you at the end of year function (8th Dec @ Rosemount Bowl), I may see you at our first event next year which will be a breakfast in February, if I'm not mistaken.

ScottWe

Sage AUSOUG Conference photo

2010-11-23T06:31:00.000+08:00

Our entire team in our snazzy new tops on day one of the conference.

Shame our camera man had an unsteady hand on a smart phone...

From left to right: Chris Muir, Kylie Payne, Scott Wesley (me), Kate Marshall, Penny Cookson (our illustrious leader), Branka Njegich, Eddie Harris, Ray Tindall.

Advert: SAGE Computing Services Training - Apex 4.0 Workshops

2010-11-18T20:04:00.001+08:00

SAGE Computing Services are featuring two training workshops early next month, not long after the AUSOUG conference.

December 6th - Oracle Application Express 4.0 New Features Workshop
This course is designed to familiarize current Application Express developers with the exciting new features in Apex 4.0. Topics will include the changes in the interface, new client events, tabular form enhancements, web sheets and team development.

December 8th - 10th - Application Express 4.0 Workshop
If you're looking to get involved with Apex, this course provide you with the skills and knowledge required to develop a complete application with the latest release of Oracle’s Application Express 4.0 product. You will develop web interfaces including forms, reports and charts, add validation and customise the formatting to create a small application.

Visit our training web page for more information about our extensive course catalogue. Follow through to the contact us details page. We look forward to hearing from you soon.

Scott Wesley & the team at SAGE Computing Services.

I'm sure Penny, Eddie, Kate, Chris, Branka, Kylie & Ray would like you all to enjoy summer (or winter/wet/dry), and have a great new year!

Final days before the 2010 AUSOUG conference

2010-11-18T18:14:00.001+08:00

This blog has been a little neglected as I've been a busy little bee recently, mainly fretting about getting my presentation completed for next week's Australian Oracle User Group Conference - AUSOUG 2020. I have plenty of ideas lined up for post topics, so I should be more active come the next few weeks.

As for the conference, SAGE Computing Services (my employer) will have an exhibition booth, so why don't you come by and say hello to our team. We will have a few things to give away plus some prizes to win - but make sure you bring your thinking caps because we're going to make you work for them!

I'll be joining the rest of the user group committee in Burswood on Sunday night just to make sure everything is good to go - including perhaps that I did the right thing setting up for a wide-screen projector. We should have a good turn out, although humbling in comparison to some of the overseas gigs, I hear.

Some of us are scheduled to present:

Penny Cookson - Meet the CBO in Version 11g
- 11:30am 22nd Nov 2010
Scott Wesley - 'n' methods to improve Apex performance
- 3:15pm 22nd Nov 2010
Ray Tindall - Active Directory Integration - AD, WLS & ADF in Harmony
- 9:45pm 23rd Nov 2010
Chris Muir - A change is as good as a REST: JDeveloper 11g's REST web services
- 11:00am 23rd Nov 2010
Penny Cookson - A Path to the Future for Dinosaur Nerds: JDeveloper ADF - A Mind Map for Forms Developers
- 3:45pm 23rd Nov 2010

The conference program can be subject to change, so check on the day to ensure you don't miss out!

So unless the schedule's changed on me, I'll probably be at the following sessions:

Steven Feuerstein - Golden Rules for Developers
Penny Cookson - Meet the CBO in Version 11g
Howard Ong - Make your legacy application live forever
Scott Wesley (won't want to miss this one!) - Apex Performance
Jeff Kemp - Oracle Apex Themes and Templates

(but sorry Jeff, I may find myself at Mogens Nørgaard - Oracle Licencing)

Damien Bootsma - Database performance made easy
Tom Kyte - The Best Way
Stuart Long Keynote - Cloud Services Automation (CSA)
Guy Harrison Keynote - Technology trends impacting Oracle professionals
Connor McDonald - Managing optimiser statistics
Frank Bommarito - Outlines, Profiles, SQL Plan Baselines

Yes, my selection is very much weighted towards Oracle performance this year. Nothing wrong with having a theme...

Please do come by our booth if you are also after training information, we train around Australia all year round and cover a wide range of Oracle topics. We can also customise the content to suit your organisation - talk to us to find out more. If you can make it to the conference, contact us on-line.

For you conference goers, if I don't see you anywhere amongst all of that, no doubt we'll all be with a beer at the welcome reception from 5pm (oh, I'll be at the AUSOUG AGM first - save me some beer)

Speakers, committee members, exhibitors et al. - listen out for details regarding dinner on Tuesday night, it's always a great night. I may be there... but it's my & Tracey's first wedding anniversary that day ;-)

ScottWe

Quoting inside literal strings

2010-11-05T09:51:00.000+08:00

Well some of you may have noticed my slight boo-boo yesterday. I had made some notes on a blog post I planned to finish later, and accidentally published instead of just saving to continue later - so here's my second attempt ;-)

What I found funny, was the post was about exactly what Steven Feuerstein implemented for yesterdays (4/11/2010) PL/SQL Challenge - embedding quotes in literals.

I was reminded of it recently while searching for something - I can't remember what for, but I came across this forum entry: http://database.itags.org/oracle/81577/

I used what I thought an interesting method for adding single quotes to strings - interesting I suppose because I've never used this method before:
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=' || chr(39)||'DD.MM.YYYY'||chr(39);

The single row function CHR() will return the character equivalent to the numeric passed, in my case the ASCII character for single quotes. I the past I've mainly used CHR to provide my line feeds and carriage returns. (chr(10 & chr(13) respectively)

The author used this method because inserting a single quote in a normal string means you need to escape it, and this sometimes looks ugly or difficult to interpret:
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT='''DD.MM.YYYY''';

Oracle 10g introduced a method often called q-quote notation, further information can be found in the SQL Reference documentation here.

This format basically allows you to type the string as you would normally expect to see it, so the following example would display everything between the brackets [ ]
q'[ this isn't my string, is it? ]'

Which would mean our previous example would look like:
EXECUTE IMMEDIATE q'[ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY']';

Of course, as the documentation describes, you don't need to use those particular brackets. For a while I thought the brackets had to be curly { }, which was frustrating because it became annoying to type. Until recently, I wasn't aware you could go beyond any form of brackets and use any character - as long as it matched up. Lucky, considering yesterday's PL/SQL Challenge did just that!

I found it uncanny, since the list of examples I had lined up for this post was very similar to Steven's list - these will all produce the same result - Hello Scott's computer:

select 'Hello Scott''s computer' hello from dual; 

select 'Hello Scott'||chr(39)||'s computer' hello from dual; 

select q'[Hello Scott's computer]' hello from dual;
select q'{Hello Scott's computer}' hello from dual;
select q'<Hello Scott's computer>' hello from dual;
select q'(Hello Scott's computer)' hello from dual;
select q'"Hello Scott's computer"' hello from dual;
select q'sHello Scott's computers' hello from dual;

So I would suggest choose a combination of brackets that makes sense to you - probably not the last example since it's not that intuitive.

Someone asked me what the point of this particular notation was. I've found it very handy in the past for a number of reasons, but it probably still depends on what your brain is used to seeing (I'm still quite happy using DECODE over CASE, for instance)

For example, when it comes to replacing quotes with double quotes, an exercise I did for something long ago, I can either write it as this complicated list of quotes:
with mine as (select q'[Hello Scott's computer]' hello from dual)
select replace(hello, '''', '"') from mine;

Or make it a little more elegant. This is still probably a weak example, but I've seen a horrible list of quotes in my time that took a while for me to count and realise what was doing what.

with mine as (select q'[Hello Scott's computer]' hello from dual)
select replace(hello, q'[']', '"') from mine;

And then there is the case of large dynamic SQL. It truly is horrible to have to apply two single quotes every time a quote is required, particularly when you're conditionally concatenating certain clauses; and want to just copy and paste the query directly in your GUI to test without replacing all the quotes again.
Here is a basic example of how a small bit of dynamic SQL could become much more readable with the q-quote notation:

declare
  my_string varchar2(100);
begin
  execute immediate q'(select dummy from dual where dummy = 'X')'
  into my_string;
end;
/

I think this PL/SQL challenge has turned out to be a great boon for Oracle developers worldwide. You may be quite a proficient PL/SQL programmer, but not everyone has had the opportunity for exposure to all facets of the database, and people are reporting how wonderful it is to pick up these tips and tricks - in the arsenal ready for future use.

Attention Students! (AUSOUG plug)

2010-11-01T09:03:00.001+08:00

Are you a student interested in where the Oracle industry is heading? Are you keen on meeting key figures in the Perth & Australian community? Do you want to be a few steps ahead of your fellow graduates?

These are the questions I remember thinking about as I was studying. There were a number of options out there, and sometimes it's hard to sift through the options to find those of value.

I think the Australian Oracle User Group (AUSOUG) is one of the valuable options, and this year the committee has organised an even better value deal.

The 2010 AUSOUG Perth Conference is just around the corner, and we've brokered a deal to benefit the grass roots of our industry - students!

For $150 tax deductible dollars, you receive membership to the user group AND registration for the two day conference.

If you happen to be a member already, it's only $100. Membership is normally $65, and student conference registration is normally $320.

I'll let the user group brochure spruik it's benefits, and you can find a link to the conference brochure at the SAGE website - it's worth it, even just listening to the great presentations to be found in the program.

If you're doing a degree like Computer Science or Software Engineering, students of Edith Cowan University, Murdoch University, University of WA, Central Institute of Technology (TAFE), (and any others beyond the major players) contact membership@ausoug.org.au for enquires, or contact Ailsa, our WA President.

ScottWe.

SAGE Computing Services welcomes Kylie Payne

2010-10-18T22:25:00.000+08:00

While I was in wonderful Kong Kong, the mugshot arrived and I now have the content to announce Kylie Payne has arrived on our SAGE team:

http://sagecomputing.com.au/about_sage_computing_services.html#kylie

I've already had the opportunity to work with Kylie on a few things and I enjoy her great attitude. We have a similar background - working on a few data integration projects; and now we look forward to the growing world of Oracle Application Express.

To meet Kylie come by our booth at November's AUSOUG conference.

See you there!

ScottWe