Using LDAP to authenticate your APEX users

It can be fairly simple to configure your APEX application to authenticate against your Active Directory server - this means your users can use the same username/password as the use to log onto their desktop machine - a big plus.

Declarative definition can be as simple as heading to Shared Components -> Authentication scheme and selecting 'LDAP Directory' from the scheme gallery.

Settings might be as simple as:
Host : myserver
Port : 389
DN String : MYDOMAIN\%LDAP_USER%

Further examples of the distinguished name string are shown in the item help.

LDAP Authentication definition

I've also seen an interesting use case in a popular APEX book. Here they also enabled authorisation definition (roles based on LDAP groups) - and they did it efficiently with some PL/SQL & materialized views.

There are a number of other examples in the #orclapex blogosphere on this:
http://ruepprich.wordpress.com/2012/11/02/ldap-authentication-with-apex/
http://doloracle.blogspot.com.au/2011/10/how-to-apex-ldap-authentication-for.html
and plenty of support in the OTN forum.

If you're keen to get your hands a little dirtier, there is also a supporting package APEX_LDAP.

Scott

Regular Expressions 101 - REGEXP_COUNT

Not all regular expressions are scary.

As the documentation states, REGEXP_COUNT returns the number of times a pattern occurs in a string.

We can do this to simply count how many times the letter S appears in a string

select job, regexp_count(job,'S') 
from scott.emp;
JOB       REGEXP_COUNT(JOB,'S')  
--------- ---------------------- 
CLERK     0                      
SALESMAN  2                      
SALESMAN  2                      
MANAGER   0                      
SALESMAN  2                      
MANAGER   0                      
MANAGER   0                      
ANALYST   1                      
PRESIDENT 1                      
SALESMAN  2                      
CLERK     0                      
CLERK     0                      
ANALYST   1                      
CLERK     0                      

 14 rows selected 

Today I used this function to detect/count how many carriage returns in a string using CHR(13) instead of 'S', but no doubt you could use the power of regular expressions to do all sorts of things. One such example is validating email address format.

I've also used it in the past to identify dirty data - those with numeric digits when it should be all alphabetical characters (names).

Perth 2013 Insync conference program now available

While many in the Oracle universe are enjoying OOW, here in Perth we are preparing for our leg of the Insync Conference Series.

This year it's on 12-13 November at Pan Pacific Perth Hotel  - a healthy walk or short free bus ride from the Perth CBD.

Long story short - the program is now available. 

Download it now and see why you should come along. It you can only make it for one day, good luck in deciding which day would be best for you!

Consider attending the workshops, they're just like being in training. If you do want to attend any, please let us know - you'll also get the most out of them if you come prepared, so we can help you out with what to download (if relevant).

Scott
2013 Program Chair
Perth Insync

The trick with triggers

Creating triggers, prior to 11g, would default them to an enabled state.

From 11g, we have this in the documentation:

By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.

So when I write my DDL scripts, given this behaviour, I know what I'd prefer:

SQL> create table test_table(a number);
table TEST_TABLE created.
create or replace trigger test_trigger_good before insert on test_table for each row DISABLE
begin
  null -- missing semicolon
end;
/
TRIGGER test_trigger_good compiled
Warning: execution completed with warning

SQL> insert into test_table (a) values (1);

1 rows inserted.

or

SQL> create or replace trigger test_trigger_bad before insert on test_table for each row 
begin
  null -- missing semicolon
end;
/
TRIGGER test_trigger_bad compiled
Warning: execution completed with warning

SQL> insert into test_table (a) values (1);

SQL Error: ORA-04098: trigger 'DEVMGR.TEST_TRIGGER_BAD' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.

Only run this when you know things are cool, ie - the triggers does not have compilation errors.

alter trigger test_trigger_good enable;

Otherwise you get the same problem, since you can enable an invalid trigger.

Tip of the hat to Connor McDonald for suggesting this many moons ago.

APEX information from Montreal

If, like me, you are nowhere near APEXposed in Montreal, but you are interested in what might be coming in APEX5, then you must read this post from Scott Spendolini (USA Scott)
http://spendolini.blogspot.com.au/2013/09/apex-50-impressions.html

Other commentary on APEX5 here.

Scott (Australia Scott)

SQL 101 - Group by ROLLUP

One of the reasons I called renamed this blog grassroots-oracle was to occasionally illustrate some simple examples of functionality not everyone is aware of - or are maybe too scared to give it a go (or research).
Still today I occasionally work with people who aren't familiar with some features I almost take for granted.

Today's little observation was with ROLLUP - a function used in group by queries to provide sub-totals.

I was looking at log frequency by month, and I also needed to consider grand total, so consider:

Rollup folks!

It's one of those features in the data warehousing guide that should possibly be in an area more accessible. While I don't necessarily use it for production code - it's syntax that I often find handy with ad hoc queries while interrogating the database.

select count(*), trunc(view_date,'mm') 
from activity_log 
group by rollup(trunc(view_date,'mm')) 
order by 2 desc nulls last

You'll also want to check out single row functions such as GROUPING.


Go nuts.

Help APEX find it's way

In case you missed it, there was an OTN forum post about asking about the APEX5 release date. As Hilary mentions, it's Oracle's privilege to release it whenever they like.

That being said, there is an APEX Feature Request application.

While new feature will always be cool, I'd also like to see an introspection regarding some facets of the Development Builder.

I've requested a few myself, but for example (to get creative juices flowing)

• conditional on dynamic action true/false actions
• improvements in dynamic action copy
• extent cache infrastructure, including APIs (another similar entry)
• more multi-row editing facilities, similar edit all on page components, and grid edit for pages - but not so buried.
• features aiding release management / version control
  (possibly via extending build options?)
• littler things like adding static ID attribute in component wizards
• compile attribute inline documentation online
• all encompassing comments dictionary view
• small win - delete multiple templates at a time

I think it's also fairly safe to expect a third User Interface for tablets.
All of this on top of what is already mentioned in the APEX Statement of Direction.

For more APEX 5 thoughts, check out

• some recent commentary in Europe from Christian Rokitta 
• desires from Peter Raganitsch
• browser support info from Joel Kallman 
• session reviews from Bart Peeters
• and a post I forgot I did.

Let me know if you've seen any others.

I look forward to the next release, whenever it may come ;-)

Scott

ps - the post also noted 4.2.3 would be 'soon'

Post eastern Insync 2013

It's been over a month since my last confession - mainly due to a lot of interstate travel.

The reason for most of the travel was the AUSOUG Insync Conference Series - on the eastern coast anyway. So in the past month I've been to Sydney (and surrounds), Melbourne (twice) & Brisbane (extra 90 mins to fly home).

The conference series was due to begin in Sydney on the 15th, but unfortunately due to a number of factors (which I won't get into here) it had to be cancelled, along with the Canberra and Adelaide legs. Perth preparations are still going strong, and we look forward to bucking the trend.

This turned out expensive for me because I had promised my wife would come over on frequent flyer points and we'd spent time driving through the blue mountains and other touristy type things. And extra two days of that when the company can't sport for accommodation before I head to the Melbourne gig after the weekend - bummer!

I'll just say Sydney was beautiful and treated us to lovely weather - but we had no success whale watching day.

Melbourne was cold, and the conference numbers were lower than previous. Penny (my boss) was still happy with the turnouts to our talks and networking engagement was reasonably successful.

My presentations went well, and for those coming to Perth and want to join any of the half-day workshops (eg: DB 12c, JDeveloper & APEX) I recommend you bring your laptops and become aware of software needs before hand to make the most of the time. See further comments below.

Some presentations I'm keepto keep an eye out for in Perth in November:

1. Penny Cookson - Redesigning core on-line systems in ADF JDeveloper - if you ever want a case study on a ADF migration project - this is the perfect chance
2. Scott Wesley (me) - APEX in your Hands - another case study on an APEX application preparing for deployment on a tablet. We hope to get our client involved with the intro to this in Perth
3. Chris Muir (et al) - The Future of Oracle Forms: Upgrade. Modernize, or Migrate - if you still use Oracle Forms, come even just to keep yourself informed on where things are at and what's truly available.
4. Mark Lancaster - APEX 4.2 Building Responsive application using Twitter Bootstrap - a good overview of a framework worth incoroporating into your applications - it's not hard.
5. Penny Cookson - New Optimizer Features in Oracle 12c - A technical look at tuning features in 12c, with a funny twist. Uber nerd alert.
6. Kylie Payne - ADF for Newbies - I know there are plenty of you out there. Fresh perspective from an Oracle professional.
7. Connor McDonald - CloneDB - a new one from Connor, let's place bets on the slide count.
8. Jeff Kemp - APEX and row level security - presenting the works of an excellent blog post
9. Phil Robinson - Testing the limits of testing - everybody should be incorporating more testing.

We have a few more to be confirmed from Oracle ACEs.

Workshop tips

1. DB 12c - Martin Power - bring a 64 bit laptop with plenty of gigs free. Download his recommended files first and do your best to get VirtualBox booting beforehand. He brings a thumbdrive with a pre-built image, but it will take a while to copy, initiate, boot.
2. Forms -> ADF - Penny Cookson - Read her setup instructions and try your best to come prepared
3. APEX Plugins - Scott Wesley - the easiest to prepare for - just bring a laptop! You can also do it easily in your own 4.2 environment, just grab a few files beforehand so you can play along.

For those in the east - also good was

1. Guy Harrison - Avoiding death by Big Data - a lighthearted but effective 1000 ft view of how big data is shaping our world. I enjoy his talks, and try to incorporate some of his style in mine. Imitation is the sincerest form flattery, they say...
2. Tim Daniell - Being Productive in IT - a different, thought provoking comparison of the human brain and an Oracle database.
3. Connor McDonald - SQL Tuning 101 - if you missed it last year, too bad - a fast paced, humorous look at SQL basics that everyone will learn from.

SAGE will be exhibiting in Perth and will continue our fine form of booth design, thanks to Branka.

Scott

ps - to all Australian readers - happy footy finals month.