Reasons you should upgrade your APEX environment

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

APEX Gauge Chart example

How ready is tomorrow's presentation?

...never to reach 100%

Create Chart page
Gauge - dial %
Look 5
Series source: select 95, 100 from dual

Edit Chart
Gauge pointer - bar
Uncheck Values
Major/minor interval 10/5

Scott

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

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 selected 

Simple, but effective.

Anyone have thoughts on benefits of using dynamic vs static LOVs in APEX?

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 .change() to invoke trigger
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"
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

You only require a true action, executing some JavaScript
$(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 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 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

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.

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

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.

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
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!