For those interested, here is a prezi-style presentation on why I think you should be upgrading your APEX environment to at least 4.x.
Hopefully you understand the messages without listening to my babble.
I presented this to our local Perth user group as part of a double header with Mark Randell discussing (on a similar theme) why you should upgrade your database to 11gR2.
I'm really liking the Prezi tool, and I think I have the hang of designing them instead of powerpoint slides.
I think the best feature is you can concentrate on the layout, which will help you memorise the flow - which I defined very late in the scheme of things.
I always had an idea of the general flow - from inception of the idea, and the first segment (upto the tree rings) I did first; then I filled in the rest once I was happy with the content/placement around the tree; deciding on how to end it later on.
And I had a few ideas for a new one in the shower this morning :-)
Scott
Thursday 28 March 2013
Tuesday 26 March 2013
APEX Gauge Chart example
Wednesday 20 March 2013
Playing with dates, again
When creating LOVs for APEX I sometimes debate to myself whether to make a static or dynamic LOV.
I had one scenario where having some SQL was handy, so I started with this
Anyone have thoughts on benefits of using dynamic vs static LOVs in APEX?
I had one scenario where having some SQL was handy, so I started with this
SELECT TO_CHAR(NEXT_DAY(sysdate, 'MON')+ROWNUM-1,'DY') FROM dual CONNECT BY LEVEL <= 7;It's possible to then place this as an inline view within a subquery factoring clause, to use fancy terminology. This makes it easy to share column data.
WITH data AS (SELECT NEXT_DAY(sysdate, 'MON')+ROWNUM-1 dt FROM dual CONNECT BY LEVEL <= 7) SELECT TO_CHAR(dt,'DY'), TO_CHAR(dt,'Day') FROM data; TO_CHAR(DT,'DY') TO_CHAR(DT,'DAY') ---------------- ----------------- MON Monday TUE Tuesday WED Wednesday THU Thursday FRI Friday SAT Saturday SUN Sunday 7 rows selectedSimple, but effective.
Anyone have thoughts on benefits of using dynamic vs static LOVs in APEX?
Monday 18 March 2013
User friendly APEX date items
Back in my Oracle Forms days, we had a library function associated with our date fields that accepted a value of "t", which then returned today's date.
We had further variations on this, but I thought I'd see how I'd go at implementing this in the APEX environment.
Update - included
http://stackoverflow.com/questions/8437125/jquery-invoke-change-without-user-action-but-by-val-change
First, well, second after creating some date fields on my page - I defined a dynamic action "t in date"
You only require a true action, executing some JavaScript
In my case I used a function to return a date formatted nicely for my Oracle environment - more details below.
Don't fire on page load, and set "Selection Type" to "Triggering Element"
I must thank Tobias in the OTN forums for to return date function, but I've extended it a little to suit my tastes.
I also added a parameter so I could define another DA that accepts "y" for yesterday - and adjust my call to
What do you think? The only problem I've found is if you tab quick enough after typing "t", the trigger does not fire.
Oh, and IE8 seems to have a problem with the date constructor - but I've all but lost my patience pandering to IE.
An example can be found here:
http://apex.oracle.com/pls/apex/f?p=SWESLEY_FORUM:6:0::NO::P6_MODE:E
Scott
We had further variations on this, but I thought I'd see how I'd go at implementing this in the APEX environment.
Update - included
.change()
to invoke triggerhttp://stackoverflow.com/questions/8437125/jquery-invoke-change-without-user-action-but-by-val-change
First, well, second after creating some date fields on my page - I defined a dynamic action "t in date"
Event: Key release
Selection type: jQuery Selector
jQuery Selector: .hasDatepicker -- this is a class automatically assigned to my dates, found simply with right-click -> Inspect element in Chrome
Condition: equal to
Value: t
Dynamic Action definition |
$(this.triggeringElement).val(return_date('-')).change();
In my case I used a function to return a date formatted nicely for my Oracle environment - more details below.
Don't fire on page load, and set "Selection Type" to "Triggering Element"
JavaScript action |
I also added a parameter so I could define another DA that accepts "y" for yesterday - and adjust my call to
return_date('-',-1)
function return_date(p_delimiter, p_offset) { /* with help from https://forums.oracle.com/forums/thread.jspa?threadID=2186734 http://stackoverflow.com/questions/894860/set-a-default-parameter-value-for-a-javascript-function */ /* Default delimiter to . */ p_delimiter = typeof p_delimiter !== 'undefined' ? p_delimiter : '.'; p_offset = typeof p_offset !== 'undefined' ? p_offset : 0; /* Create date object */ var myDate = new Date(Date.now()); myDate.setDate(myDate.getDate()+p_offset); /* Create output string DD.MM.YYYY */ /* Day */ var myStr = (myDate.getDate() < 10 ? "0" + myDate.getDate().toString() : myDate.getDate().toString()) + p_delimiter; /* Month */ myStr = myStr + (myDate.getMonth()+1 < 10 ? "0" + (myDate.getMonth()+1).toString() : (myDate.getMonth()+1).toString()) + p_delimiter; /* Year */ myStr = myStr + myDate.getFullYear().toString(); /* Set value */ return myStr; }Note how much more difficult it seems to default parameters in JavaScript compared to PL/SQL.
What do you think? The only problem I've found is if you tab quick enough after typing "t", the trigger does not fire.
Oh, and IE8 seems to have a problem with the date constructor - but I've all but lost my patience pandering to IE.
An example can be found here:
http://apex.oracle.com/pls/apex/f?p=SWESLEY_FORUM:6:0::NO::P6_MODE:E
Scott
Wednesday 13 March 2013
Reset sequence values to align with table
Migrating data between environments sometimes requires the need to update the sequence next value.
I can't remember where I adopted this code, but I've had it for a while now and I've improved it a little.
So you can reset a sequence manually using
Take it a little further and you can make it dependent on the current value in your table.
Hopefully it might be useful for you one day.
Scott
update 2 - Also explore identity columns in 12c.
update 1 - I saw this was blogged about recently, but this post was already scheduled - so information in numbers!
I can't remember where I adopted this code, but I've had it for a while now and I've improved it a little.
create or replace procedure reset_seq (p_seq_name IN VARCHAR2 ,p_new_value IN NUMBER DEFAULT NULL ) IS l_val number; begin execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val; l_val := -l_val+COALESCE(p_new_value,0); --debug( 'alter sequence ' || p_seq_name || ' increment by ' || l_val ||' minvalue 0'); execute immediate 'alter sequence ' || p_seq_name || ' increment by ' || l_val || ' minvalue 0'; execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val; execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0'; end; /
So you can reset a sequence manually using
exec reset_seq('my_seq', 117)
Take it a little further and you can make it dependent on the current value in your table.
declare l_id number; begin select max(my_id)+1 -- replace with relevant pk column into l_id from my_table; -- just replace with relevant table reset_seq('my_seq', l_id); -- replace relevant sequence name end; /To take this even further to treat an entire schema, you could do something like this.
declare v_id number; begin FOR r_rec IN ( select table_name -- mapping sequence to table caught me out at first, but luckily we had a good standard in our table comment descriptions. ,(select substr(comments,14,4) from all_tab_comments c where c.table_name = t.table_name and t.owner=c.owner) seq from all_tables t where owner = 'SAGE' order by table_name ) LOOP execute immediate 'select max(id)+1 from '||r_rec.table_name into v_id; dbms_output.put_line(r_rec.table_name||' id:'||v_id||'; seq:'||r_rec.seq||'_id_seq'); reset_seq(r_rec.seq||'_id_seq', v_id); end loop; end; /
Hopefully it might be useful for you one day.
Scott
update 2 - Also explore identity columns in 12c.
update 1 - I saw this was blogged about recently, but this post was already scheduled - so information in numbers!
Resetting Sequences dlvr.it/32j88h
— Prasanna Peshkar (@PrasannaPeshkar) March 6, 2013
Monday 11 March 2013
Perth AUSOUG Breakfast March 27
I'd like to encourage all Perth AUSOUG members to come join us for breakfast at the West Perth Oracle offices on Wednesday March 27.
If you missed Connor McDonald in February you can make up for it with a double header with a focus on "why upgrade?"
11 reasons to standardise on Database 11gR2
Mark Randell - Oracle
With the announcement of Database 12c at OpenWorld 2012, many people are holding back upgrading their database environments until they evaluate the latest Oracle Database release. However, with 12c introducing major architectural changes, 11gR2 will continue to be recommended as the version with the ideal combination of features, performance and stability. Now is a perfect opportunity to upgrade legacy deployments of Oracle Database to 11gR2, and benefit from the performance improvements and new core features/options, while also providing a newer base to transition to 12c in future. This session will cover 11 reasons why you should (and how you can) upgrade to 11gR2.
Reasons why you should upgrade your APEX environment - Discover how APEX 4.x makes your life easier
Scott Wesley - SAGE Computing Services
Unless you truly pick things apart, version numbers don't proffer much to explain the differences between product releases.
Over time with Application Express I have taken note of the differences, and I might suggest you'd barely call APEX 4.2 even the same species as 3.2 - it has evolved fantastically!
For instance, we've seen the introduction of Dynamic Actions, Plug-ins, support for mobile, improved security - all of which lead to improved productivity, more user friendly applications and ongoing support.
We'll explore the growing tree that is Application express with a relatively unique presentation style -and we'll will climb it all the way to the canopy, exposing what's ahead with APEX 5.
DBAs will gain awareness, Development Managers will get ideas, Business Analysts will see options, and Developers can get some details.
RSVP using this link or e-mail Kevin Payne.
If you missed Connor McDonald in February you can make up for it with a double header with a focus on "why upgrade?"
11 reasons to standardise on Database 11gR2
Mark Randell - Oracle
With the announcement of Database 12c at OpenWorld 2012, many people are holding back upgrading their database environments until they evaluate the latest Oracle Database release. However, with 12c introducing major architectural changes, 11gR2 will continue to be recommended as the version with the ideal combination of features, performance and stability. Now is a perfect opportunity to upgrade legacy deployments of Oracle Database to 11gR2, and benefit from the performance improvements and new core features/options, while also providing a newer base to transition to 12c in future. This session will cover 11 reasons why you should (and how you can) upgrade to 11gR2.
Reasons why you should upgrade your APEX environment - Discover how APEX 4.x makes your life easier
Scott Wesley - SAGE Computing Services
Unless you truly pick things apart, version numbers don't proffer much to explain the differences between product releases.
Over time with Application Express I have taken note of the differences, and I might suggest you'd barely call APEX 4.2 even the same species as 3.2 - it has evolved fantastically!
For instance, we've seen the introduction of Dynamic Actions, Plug-ins, support for mobile, improved security - all of which lead to improved productivity, more user friendly applications and ongoing support.
We'll explore the growing tree that is Application express with a relatively unique presentation style -and we'll will climb it all the way to the canopy, exposing what's ahead with APEX 5.
DBAs will gain awareness, Development Managers will get ideas, Business Analysts will see options, and Developers can get some details.
RSVP using this link or e-mail Kevin Payne.
Wednesday 6 March 2013
APEX Listener 2.0.1 available
I've been using my local APEX install infrequently recently, but I have encountered occasional issues that I'm sure are attributed the the APEX Listener stand-alone.
Tonight I tried to import some plug-ins but received "Internal server error 500". I found a related OTN forum post with a recent comment saying it's solved in 2.0.1 - I'm pretty sure I didn't have that version.
So I just downloaded the latest APEX Listener and had immediate (successful) results.
Since I run the listener stand-alone, so all I did was unpacked the file to C:\apex_listener2.0.1, changed my "start apex listener.bat" to
and restarted my listener. I didn't even have to log back into APEX ;-)
For those listening (no pun intended) on twitter, follow @krisrice for relevant announcements.
Happy upgrading!
Tonight I tried to import some plug-ins but received "Internal server error 500". I found a related OTN forum post with a recent comment saying it's solved in 2.0.1 - I'm pretty sure I didn't have that version.
So I just downloaded the latest APEX Listener and had immediate (successful) results.
Since I run the listener stand-alone, so all I did was unpacked the file to C:\apex_listener2.0.1, changed my "start apex listener.bat" to
cd c:\apex_listener2.0.1
java -jar apex.war standalone --apex-images \apex4.2\images
and restarted my listener. I didn't even have to log back into APEX ;-)
For those listening (no pun intended) on twitter, follow @krisrice for relevant announcements.
Happy upgrading!
Subscribe to:
Posts (Atom)