Saturday 26 February 2011

Android Me

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

Friday 25 February 2011

Review: Oracle Apex 4.0 Cookbook



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!

Friday 18 February 2011

PL/SQL quickie returning results

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

Monday 14 February 2011

Off Topic: a historical birthday

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.

Friday 11 February 2011

Validating e-mail in PL/SQL

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"

Friday 4 February 2011

Is Apex heading in the right direction?

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.
  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. Mater-Detail-Detail - makes sense.
  7. Dynamic Actions - never has a declarative feature offered so much potential and provided flexible options, I'm glad this feature is being developed further.
  8. Plug-Ins - The community has really embraced plug-ins, you'd be mad not to extend it.
  9. Use of ROWID - this has been on my wishlist for a while, it irks me this facility wasn't in a much earlier release.
  10. 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.
  11. Websheets - do people really use this?! Please tell me the initial Apex4 release wasn't due to dusting off websheets...
  12. 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?
  13. Accessibility - I'm finding more clients ask that this is considered in the requirements, good on you, Oracle.
  14. 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

Thursday 3 February 2011

APEX Object Synonyms

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!