Advert: Queensland User Group

This is really late notice and really only applicable to Brisbane residents - but I'll be in Brisbane over the next few days and the QLD Oracle User Group has lined me up to talk PL/SQL.

I'll be rabbiting on about Conditional Compilation at Oracle House at 5:30pm, Tuesday 9th March 2010.

Please contact Mark Lancaster if you're interested.

Escaping wildcard searches

What if the character you want to search for is one of Oracle's wildcards?

Today I wanted a list of all Apex schema related synonyms, but I wanted to excluded any APEXLIB stuff.

-- Find actual object names for apex objects
select synonym_name, table_name
from dba_synonyms
where synonym_name like 'APEX/_%' escape '/'
and table_owner like 'APEX/_%' escape '/'
and owner like 'APEX/_%' escape '/'
order by synonym_name;

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
APEX_APPLICATION               WWV_FLOW
APEX_APPLICATION_FILES         WWV_FLOW_FILES
APEX_APPLICATION_GLOBAL        WWV_FLOW_GLOBAL
APEX_COLLECTION                WWV_FLOW_COLLECTION
APEX_COLLECTIONS               WWV_FLOW_COLLECTIONS
APEX_CUSTOM_AUTH               HTMLDB_CUSTOM_AUTH
APEX_INSTANCE_ADMIN            WWV_FLOW_INSTANCE_ADMIN
APEX_UTIL                      HTMLDB_UTIL
...

I know in times passed I've also wanted to search for all database objects that contain _ROLE_

select * from all_objects where object_name like '%\_ROLE\_%' escape '\';

Obviously it need not be a forward or back slash. If you don't want to confuse yourself when you also have '\' characters in your search term. A tilde is a favourite of mine.

select directory_path from all_directories where directory_path like '%\sales~_%' escape '~';

DIRECTORY_PATH
------------------------------------------
E:\oracle\sw10g\demo\schema\sales_history\

In fact, if you don't succeed the escape character with Oracle's wildcards (% or _), then you'll receive the following error:
ORA-01424: missing or illegal character following the escape character

Documentation on this feature can be found here.

Another Apex 4.0 feature for Forms Developers

Late last year I wrote a post about the new Tree View in Apex 4.0, and how it reminded me of the object navigator in Forms.

After reading Patrick's latest entry regarding Cascading LOVs in Apex 4.0 EA2, it reminded me of one of the things I used to do quite regularly in Forms - repopulate one Select List based on the selection of another, during when-list-changed.

Oracle writes in their Application Tools Statement of Direction that (in context of migration tools)
...
Instead, Oracle’s strategy is to provide a Java EE development environment exposing similar concepts as Forms and Reports, giving to Forms and Reports developers the opportunity to become productive in a new, but familiar environment

I think this cascading LOV is another example of that.

I defined my first Select List as an LOV with the following select:
select name d, org_id r
from   organisations
where  parent_org_id is null
order by 1

Then in the definition for the second Select List, I designate the Cascading LOV Parent Item, and refer to that item value within my second LOV definition.

Pretty simple. Even easier than Forms. The less AJAX I need to write, the happier I am.

Why I use Google Chrome for Oracle APEX Development

I keep it no secret I'm a fan of Google products. Funny considering I remember I used to go out of my way to use other search engines when I was at uni, like Altavista.

Recently I read a great an eye catching article 20 Instant Upgrades to Make Chrome Better than Firefox. I've been using Chrome a fair bit recently - it certainly loads faster than Firefox, even though I've been advised that's because of all the Firefox add-ins I've applied. In some ways I beg to differ - I have a VMware instance with only Firebug added, and Chrome still anecdotally performs better.

As a database developer when it comes to Application Express, Chrome seems snappier and more responsive. I thought I'd mention a few things about this article that stood out for me when it comes to APEX.

Searching

I like to provide people options to search through Oracle Documentation, and here is another new trick.

Under the search options you can add a search engine.

An example URL might be:
http://www.oracle.com/pls/db102/search?remark=quick_search&tab_id=&format=ranked&word=%s

Then when searching within the "Omnibar", you can type "oracle", press tab, and all search results will be pertinent to Oracle 10gR2.

Alternatively you could use something like the site: search keyword in Google Search to form your URL.

Stats for Nerds

I can't imagine our APEX applications will consume too many resources, but you can certainly use this nifty feature to find out what other tabs may be devouring valuable CPU/memory on your hardware.

Shift-Escape brings up this little task manager, and the "Stats for Nerds" link give you even more information.


Firebug for Chrome

Don't even need to worry about installing an add-on. Just right click on your page and select "Inspect Element". A wealth of options will now appear before you, allowing you to edit your web-pages on the fly - a necessity for many APEX developers.

Synchronising bookmarks

Xmarks has been a great find for me, and while this feature isn't necessarily APEX specific, I find it very handy in my line of work. For quite a while, Xmarks wasn't available for Chrome, but given a Google account, Chrome will synchronise for you.

Shrink your tabs

While the APEX favicon may not be all the exciting, you can right click on your tabs and pin them to your tab bar, allowing you more room to identify and open other tabs during your day of productive development.

IE Tab Add-on
For those pages that only work properly with Internet Explorer. Sigh.

Split Page View

The article mentions a URL that enables split screen viewing within your browser, which would be great for comparisons within APEX. It's just a shame these browsers don't do this inherently.

Portable Chrome

The article mentions one website, portableapps.com, but I know there's quite a few out there that provide the ability.

Added bonus

Something the article doesn't mention, but I'm sure many APEX developers will appreciate, the ability to resize text areas - out of the Chromium box - no need to worry about the ApexLib extension.

One day Penny noticed this on my laptop and suggested I installed the ApexLib APEX Builder extension - I didn't think I had, and after reading this article the hand slapped the forehead and I realised what was going on.

Chrome has come a long way since its early days. I do appreciate the fact we now have many major browsers affecting the market place and development community, in addition to some minor browsers targeting niche areas - Stainless should be good for those Mac users who'd like to operate multiple tabs with different crudentials - another APEX developer desire.

I know I'll continue to use Chrome for my APEX builder session.

Opinions of the masses

When to hard-code is always a big question when it comes to programming languages. SQL certainly has a few exceptions. PL/SQL - not so much.

So when it comes to hard coding within PL/SQL, I'd say I'd agree with Steven Feuerstein. I agree with most of his standards, and his article on this is concise.

In the other corner, Jeff Kemp raises an excellent point about hard coding in SQL. We worked on a complex project together once where hard coding of certain literals was important - and done correctly. Commenting never goes astray. This example is way out of context so it's hard to illustrate, but if I may:

SELECT something
FROM my_table
WHERE entity_type = 123 /* XYZ type */
AND entity_id = :my_bind_parameter;

Where entity_type contains the value that will never change, and hence Jeff's adage - "do not change this code"

Also note Narendra's comment on Jeff's article - be very careful about the use of cursor_sharing = FORCE in these scenarios.

I just wanted to highlight these two related articles.

Oracle, Data-visualisation and Twitter

Recently Patrick Wolf talked about following Oracle Apex on Twitter. I still haven't decided whether I should take the plunge and open up a Twitter account - I can see the value of keeping the finger on the proverbial pulse of the industry; keeping in touch with industry colleagues that I may only normally see/catch up with at User Group events; and I'm sure the massive flow-on of connections that seems to happen when social media accounts are opened - but perhaps I keep up with all this in a satisfactory manner already, without having to subscribe to another social networking site.

For the time being I still follow some Twitter accounts with my RSS feeder. I also believe it's still a source of valuable information that's yet to be tapped to it's full extent.

In the "Explore" section of Google Reader (I recommend it - a bit like iTunes Genius for your RSS feeds), I encountered a great article illustrating Four Ways of Looking at Twitter. Immediately I saw the potential and relevance to my older post - and I think it's just a matter of finding the right keywords, or the right niche market to tap this massive social pulse.

The first two in particular caught my eye and I thought I'd have a quick play.

Twitter Venn might show some good visuals, but I think I'm yet to find some good search terms.

 

For me, Twitter Spectrum showed immediate potential. Just out of curiosity, I thought I'd see where the relevance lay between Oracle and SQL Server.

Then I wondered if you could use it as more of a marketing strategy to find how people are searching for information, or what particular key words might help your business out in regard to reaching your customers.

Hmm, does this mean Apex Training and blogging go hand in hand?

Data visualisation seems to be a growing industry. Information is Beautiful by David McCandless is a great way to illustrate "a picture paints a thousand words" - as long as the data is precise. His recent post on "When Sea Levels Attack" was criticised for it's inaccuracy. Visualisation sometimes needs a little artistic licence, but obvious mistakes can quickly cast doubt on your information - good thing our databases do a good job at maintaining integrity (as long as we have a good designer on hand).

So much data is now available for us to search, it's just a matter of harnessing it, then translating into a diagram - and even for us database developers is becoming more accessible. I've recently been having a fiddle with the Javascript InfoVis Toolkit. Using a combination of my Oracle database; Application Express; processes; shortcuts; and some basic Javascript knowledge, I've started to produce some pretty clean & nifty pages.

All this really generates more questions than answers, but for me two major questions remain
a) Should I open a Twitter account ?
b) what are some search terms that show some interesting results with these tools ?