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
Friday 27 January 2012
Wednesday 25 January 2012
Compound primary keys in Application Express
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.
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:
And if you attempt to create a new record, you'll receive this complaint depending on the column that is excluded from the screen:
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.
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
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?!)
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:
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:
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.
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.
Monday 23 January 2012
Mr Amazing, is gone...
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
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
Friday 20 January 2012
2012 - Preparation meets opportunity
"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...
And no, the world will not end this year.
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.
- Read 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.
Wednesday 18 January 2012
DB minimum version with APEX
(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.
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
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
Friday 13 January 2012
2011 Blog Review
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)
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.
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!
Photo by Robbert van der Steeg - check this one out!
Wednesday 11 January 2012
APEX performance issues with v()
One of the checks in the APEX Advisor is to determine if any instances of the
This confirms one of the first APEX best practices I remember hearing, that you should use bind variables instead of
It makes sense, since
Patrick Wolf talks more about performance with determinism here. Martin Giffy D'Souza summaries APEX variables nicely here.
In the comment thread Patrick also suggests the wonderful scalar subquery solution that works on all db versions
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
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
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
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
Some tests by themselves
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
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:
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. Martin Giffy D'Souza summaries APEX variables nicely 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 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.
- 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...
Monday 9 January 2012
First events of 2012
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
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 6 January 2012
Friday Fun: The SAGE Green Pig
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 tumblr 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
Wednesday 4 January 2012
My APEX 4.2 wish list
UPDATE: Where to post your APEX feature requests.
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:
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.
- 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
- Andy (2012/1/31) - (essentially) SQL based lists
Update: I gave up - replies went on for 6 months, 264 posts!
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
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
Subscribe to:
Posts (Atom)