Full outer join example

Recently I came across a scenario where I thought "say, a full outer join might hit the spot here"

It was a simple problem - we need to check that an old mapping table mapped all the old values to new values.

I figured doing a full outer join would assist making sure all values were accounted for.

Below I've cut down the actual output, but it highlights to me that the Yilgarn & Avon-Mortlock districts are not mapped to any new records, and there are number of new records that don't have a presence in the old tables.

SQL> l
  1  select distinct
  2    o.mapped_val
  3   ,o.old_district
  4   ,d.dst_name
  5   ,d.rgn_name
  6  from old_mapping_table o
  7  full outer join new_region_list d
  8  on d.dst_id = o.mapped_val
 
MAPPED_VAL        OLD_DISTRICT         DST_NAME             RGN_NAME
----------------- -------------------- -------------------- --------------------
               92 AVON-MORTLOCK
               93 YILGARN
               16 ALBANY               ALBANY               SOUTH COAST
               17 ESPERANCE            ESPERANCE            SOUTH COAST
               69 GERALDTON            GERALDTON            MIDWEST
               94 GREAT SOUTHERN       GREAT SOUTHERN       WHEATBELT
               14 KALGOORLIE           KALGOORLIE           GOLDFIELDS
               71 PERTH HILLS          PERTH HILLS          SWAN
               73 SHARK BAY            SHARK BAY            MIDWEST
                                       BUNBURY              SOUTH WEST
                                       GASCOYNE             MIDWEST
                                       GOLDFIELDS           GOLDFIELDS

Nothing more, just thought I'd share.

Off Topic - twttr

I've done it now, do you need to buy a vowel?

I'm not yet sure what my use of this will be like, but you can be assured there will be many personal posts as I think about issues that may be found at sites such as the JREF. So perhaps use that as a guide if you may be interested in following (I have set my profile to private at this stage)

No doubt if I my Oracle related network grows, my use may change. You never know, we may all even learn a few things along the way.

swesley_perth

West Aussies - have a great, sunny, grand final long weekend!

Another freebie from Oracle

On the back of my recent discussions on SQL Developer, at the start of the week Mike Hichwa announced on his blog that SQL Developer Data Modeler is now free!

This announcement was also confirmed by Sue Harper on OTN.

How there is no excuse not to model your applications :-)

More information on this product is available here.

note - us Aussies and probably a whole bunch of other countries spell it "Modeller" - good thing Google Search doesn't care.

JQuery Error Page Replacement

Yesterday I commented on using JQuery to replace the standard JavaScript dialog boxes.

Another simple solution to pretty-up your application is to replace the standard error display.

I'm not so much referring to making the messages user friendly, Martin Giffy D'Souza covered that very well recently, and you may have also seen Roel's example in the past.

So to really summarise their posts, define an On-Demand Application process called GetErrorMessage.

BEGIN
  -- this is probably the simplest example available - limited by imagination.
  htp.p('error title'||'#'||wwv_flow.g_x01);
END;

The following needs to be in the Error Page Template section of your page template.

<script type="text/javascript">
$(document).ready(function() {
  raiseErrorHandler();
}
);
</script>

And most of this you may choose to leave alone, except for the highlighted lines. I place it in my application's JavaScript file:

function raiseErrorHandler(){
  vError = $(".ErrorPageMessage");
  vError.hide();
   var get = new htmldb_Get( null, $v('pFlowId')
                ,'APPLICATION_PROCESS=GetErrorMessage'
                ,$v('pFlowStepId'));
   get.addParam( 'x01', vError.html());
   gReturn = get.get();
   get = null;
   var errArray = gReturn.split("#",2);
   showError( vError, errArray[0], errArray[1]);
}


function showError(pThis, pTitle, pText){
 vText = '<div id="alert" title="'+pTitle+'">'
          +'<img src="/i/alert_error.gif">'+pText+'</div>';
  $(pThis).append(vText);
  $("#alert").dialog({
               bgiframe: true,
               modal: true,
               minHeight : 200,
               width : 600,
               closeOnEscape : false,
               close : function(){window.history.go(-1)},
               buttons: {
                   Ok: function() {
                     $(this).dialog('close');
                     $("#alert").remove();
                     window.history.go(-1);
              }}
   });
}

Then bang, you have a mildly more beautified application.

I scraped this example off the application I'm currently working on. We have not yet filtered the errors to display something more user-friendly - but you can read more Martin's post about that.

Basically instead of navigating to the default error page, you get this pop-up instead.

ScottWe

SQL Developer gripes - Part II

Last week I commented on some issues I have with SQL Developer, from which a number of people commented and e-mailed me about.

Stew in particular commented on his usage of PL/SQL Developer (aka PSD) and asked the question:

  why would you move away from using PL/SQL Developer?

So I thought it would be polite to respond with a fresh post.

I think the best way for me to respond to that question is to highlight the most pertinent part of that statement - "move away"

To add some more context:

• I learned Oracle using SQL*Plus. I like using it, I still do for some tasks.
• I started using PSD when we (myself and the fellow that sent the original e-mail that spurred this discussion) pushed the company we were working for to supply a GUI tool. We evaluated a few, and PSD fitted the bill. It served me well, and the company managed the licences.
• In mid 2005 I started with another company doing mainly Oracle Forms programming - so I was essentially forced to stop using PSD as I no longer had a licence, and I didn't really need a GUI at the new job.
• When SQL Developer was first released, I did download and use it occasionally, but since I learned Oracle using SQL*Plus and a fancy text editor, I managed to retain productivity. I have a suit of scripts that displays the same information I get out of the GUI tool and have them ready to fire in a nice output in SQL*Plus.
• Since joining Sage Computing Services, I've relied on SQL Developer even more-so for some positives I'll explain later.

So in essence I was forced to move away from PL/SQL developer mainly because of job changes. Had I still been working there, I may still be happily using it - unless of course they've done the cheap and gone to SQL Developer. (nope, I asked the question - they still use PSD)

If I had use of a PSD licence, I don't see any real reason why you'd purposely shift to SQL Developer, unless you are a real Oracle love-bird and want to use only their products.

However, for the position I currently find myself in, SQL Developer is great  (over others) because:

1. It's free - Licensing for PL/SQL Developer starts at US$180+60. I had trouble finding an exact figure for Toad, but "six times the price" seems like a common phrase.
2. Easy to "install" - if I find myself on a client's PC, I can drop the SQL Developer folder in with no admin rights, open it up and use it. There are other similar advantages your typical consultant might cite.
3. Forget TNSNAMES.ora - that little file has bugged me for a long time. Not having to worry about it has made my time easier and I can connect to any database I have the host, port & SID for - without having to worry about multiple copies of tnsnames.
4. It's evolving - Oracle haven't dropped a grenade and ran - they are continually updating, refining & extending SQL Developer. Good things come to those who wait. In fact, I heard a little whisper that improvements to the JRE will help improve the saskatchewan footprint over time. 
5. Did I mention it's free?

As for combining it's use with SQL*Plus

1. it's thin - half a second and you have a fresh connection to the DB
2. preparation is key - I have a login script that suits me nicely (for Windows versions prior to 11g) and a bunch of scripts that I can fire on demand to get the same info the GUI provides:
   SQL> @constraints my_table
   I even have scripts called "source" and "pksource" which quickly give me a nice view of compiled objects.
3. it works - without fail, every time.  

Obviously this is all my opinion subject to my personal working scenario. 

If anyone's interested, there are detailed reviews to other software tools for Developers and DBAs here at OraFAQ. There is a more succinct and colloquial comparison written at OTN here. And here is an old but interesting & relevant article (2007).

Oh, and Kris - if you're still listening - handling serveroutput was ok a few versions ago, but very frustrating in 2.x

It would be simply super if output to calls to dbms_output.put_line() became more predictable and was controlled by an icon/shortcut key/menu item. :-)

Using JQuery dialog boxes

I was having a discussion with a client recently about how many users ignore any pop-up messages that may appear. I've seen it many times, particularly in Forms applications, where the user just dismisses any pop-up before reading it while I stand behind them thinking "I'm pretty sure that message was important!"

I think the same can be said for JavaScript messages, which is why I'm really liking the JQuery dialog box. Instead of just being a message, you can use it to dim the entire browser canvas and the dialog box appears quite imposing. Check out the demo here.

I've been using JQuery for specific components, such as the Datepicker in Apex 3.x, so since my library is already included in my application, it's not much to replace the JavaScript messages. When you construct your library, ensure you include the Dialog widget.

The implementation of a JQuery dialog box also lends itself to customising the message content via PL/SQL quite easily.

Let's take the example of replacing the standard Delete question. First, define your message as a hidden DIV"

<div id="DELETE_MSG" title="Delete record"  style="display:none">
<p>
Are you sure you wish to delete this record?
(You may undelete in future)
</p>
</div>

This could quite easily be a PL/SQL canvas, or any other method to dynamically generate HTML of your choosing.

Then you define a normal javascript function that your button would call:

function delete_msg() {

   $('#DELETE_MSG').dialog('open');

}

At the same place you can define the actual dialog call. Review the JQuery documentation for all the implementation options

$(function(){
  // Dialog
  $('#DELETE_MSG').dialog({
    autoOpen: false,
    resizable:false,
    modal:true,
    width: 600,
    buttons: {
      "Delete": function() {
        $(this).dialog("close");
        doSubmit('DELETE');
      },
      "Cancel": function() {
        $(this).dialog("close");
      }
    }
  });
});

The highlighted line doSubmit() is where you essentially change the value of :REQUEST, which you would interrogate after page submission to decide what processes/branches to execute. Alternatively, you could replace it with whatever other JavaScript you wish to action.

The possibilities are endless :-)

 ScottWe

Trying to continue a programming meme

Do you ever find yourself thinking about something and 5 seconds later, you've arrived at a diametrically opposed thought and wonder how you got there?

I did that recently while surfing the net - I think it started when I found myself on a a site suggested by Google Reader's Explore function. It listed 5 most overused expressions on the Internet.

Sometimes I encounter internet memes that I've never heard of - maybe I'm turning into a fuddy-duddy, too esoteric in my usual surfing or just simply ignorant, I don't know; but I didn't realise "die in a fire" was such a fad. I heard it for the first time recently while watching 2 Months 2 Million, when some guy got burned in an on-line poker game and jested back with "hope you die in a grease fire". Charming.

So I googled it, as you do, to find out how much of a hit it is. Oddly, a handful of listings down I came across a programming blog.

Blogger Eric Florenzano was/is trying to start a programming meme (another word I'm almost surprised people haven't heard of).

His basic premise was to ask a simple programming question, and see how many languages the blogosphere tried to solve it with - so here's my contribution:

The Rules:

1. Implement a program that takes in a user's name and their age, and prints hello to them once for every year that they have been alive.
2. Post these rules, the source code for your solution, and the following list (with you included) on your blog.
3. Bonus points if you implement it in a language not yet seen on the following list!

The List (as I found it):

1. [Python] http://www.eflorenzano.com/blog/post/trying-start-programming-meme
2. [Bash] http://aartemenko.com/texts/bash-meme/
3. [C] http://dakrauth.com/media/site/text/hello.c
4. [Java] http://adoleo.com/blog/2008/nov/25/programming-meme/
5. [Python 3] http://mikewatkins.ca/2008/11/25/hello-meme/
6. [Ruby] http://stroky.l.googlepages.com/gem
7. [Ruby] http://im.camronflanders.com/archive/meme/
8. [Lisp] http://justinlilly.com/blog/2008/nov/25/back-on-the-horse/
9. [Lua] http://aartemenko.com/texts/lua-hello-meme/
10. [Functional Python] http://aartemenko.com/texts/python-functional-hello-meme/
11. [Erlang] http://surfacedepth.blogspot.com/2008/11/erics-programming-meme-in-erlang.html
12. [Haskell] http://jasonwalsh.us/meme.html
13. [PHP] http://fitzgeraldsteele.wordpress.com/2008/11/25/memeing-in-php-2/
14. [Javascript] http://www.taylanpince.com/blog/posts/responding-to-a-programming-meme/
15. [Single-File Django] http://www.pocketuniverse.ca/archive/2008/november/27/florenzano-factor/
16. [Oracle SQL] http://triangle-circle-square.blogspot.com/2010/09/trying-to-continue-programming-meme.html

And my solution (with some added flair)

accept my_name CHAR   prompt 'name   [] '
accept age     NUMBER prompt 'age    [] '

select 'Happy '||rownum||substr(to_char(to_date('201001'||(mod(rownum,10)+20),'yyyymmdd'),'ddth'),3)
       ||' birthday, &my_name' salutation
from dual connect by level <= &age
/

SQL> @grease
name   [] Scott Wesley
age    [] 31

SALUTATION
---------------------------------
Happy 1st birthday, Scott Wesley
Happy 2nd birthday, Scott Wesley
Happy 3rd birthday, Scott Wesley
Happy 4th birthday, Scott Wesley
Happy 5th birthday, Scott Wesley
Happy 6th birthday, Scott Wesley
Happy 7th birthday, Scott Wesley
Happy 8th birthday, Scott Wesley
Happy 9th birthday, Scott Wesley
Happy 10th birthday, Scott Wesley
Happy 11st birthday, Scott Wesley
Happy 12nd birthday, Scott Wesley
Happy 13rd birthday, Scott Wesley
Happy 14th birthday, Scott Wesley
Happy 15th birthday, Scott Wesley
Happy 16th birthday, Scott Wesley
Happy 17th birthday, Scott Wesley
Happy 18th birthday, Scott Wesley
Happy 19th birthday, Scott Wesley
Happy 20th birthday, Scott Wesley
Happy 21st birthday, Scott Wesley
Happy 22nd birthday, Scott Wesley
Happy 23rd birthday, Scott Wesley
Happy 24th birthday, Scott Wesley
Happy 25th birthday, Scott Wesley
Happy 26th birthday, Scott Wesley
Happy 27th birthday, Scott Wesley
Happy 28th birthday, Scott Wesley
Happy 29th birthday, Scott Wesley
Happy 30th birthday, Scott Wesley
Happy 31st birthday, Scott Wesley

31 rows selected.

To get the format in context (st | nd | th), I had to do some manipulation with rownum. I've used the first solution I came to that worked, I'd be interested to know if anyone has any normalised solutions.

ScottWe

SQL Developer gripes

When Project Raptor finally came to fruition as a download in mid 2007 as SQL Developer 1.2, I was excited at the prospect of having a free GUI that I could take with me to various clients, without worrying about licensing.

I was using PL/SQL Developer at the time which was great for the PL/SQL development I was doing. I still use SQL*Plus fairly regularly as a lightweight tool, put as OraFAQ suggests, SQL Developer was intended as a complimentary tool.

I've heard a few comments recently saying how flakey and unreliable SQL Developer is. After further questioning, it appears they are referring to the 1.2 version which I will admit, was obviously an entry-level product. v1.5 was the better stable release before 2.x came out.

I'm currently using the most recent patched release (2.1.1.64), and I'm fairly happy although I still have a few gripes.

What made me think of this was an e-mail from a colleague now working in the UK, here are my extended responses:

I started a new contract this week and have been trying to use SQL Developer again, but it doesn't always do things I expect. They are using some cross database tool called DBArtisan (Sybase background) and I'm not impressed with it. 

It's always good to get feedback on tools from other databases - interesting.

So far there are 2 things I haven't worked out. The first is multi-session/threads, is this possible?

Most of the time I work with one connection for each schema/database that I happen to be working on. This means that any DML is reflected in all views of that information - on the worksheet, the table data view etc.

If you define a new named connection for the same schema, then you have started a separate session that will not see your uncommitted DML.

The second is that there code base is not exactly formatted very nice so I wanted to try auto-format it. I end up with line breaks where i don't expect, not enough white space and it doesn't seem to allow you to set the case on anything other than keywords, rather frustrating.

I've been thinking about this, and I think it just boils down to personal style. An evolving formatting tool couldn't possibly cater for everybody, and I know our styles are similar. I remember the formatter for PL/SQL Developer & Toad being very effective - but they're more mature products. That being said, I don't really use auto-formatters. In fact, I still do most of my PL/SQL development using TextPad. I use SQL Developer mainly for ad hoc SQL queries and looking at table definitions.

Any advice you can give me on how I might achieve this in SQL Developer. I will admit that what it does with statements when you are grouping by adding to the group by clause is pretty cool, it surprised me today when it appeared in something I was writing

This is what I told him about that one: As for completion insight - I have all those options turned off, first preferences I change on a new pc, that and line numbers in the gutter & NLS date parameter to include century.

There are a couple of other comments I let fly in my reply, things I felt compelled to address.

Compiling PL/SQL

Error reporting is a real pain in the behind. If you're just tweaking things, yeah, it works - but iterative development, I still compile my PL/SQL in SQL*Plus - my edits direct from Textpad. The error reporting is much clearer.
Unless I'm using it wrong, I don't find compiling code in SQL Developer helpful at all.

It's JRE based

It just makes things slower, introduces lag which grinds my gears. Now I may be pinning the tail on the wrong donkey, but all the other non JRE products I use don't have these issues. That, and it consistently leaves a 500mb footprint in my laptop's memory. However I know this will never change - tweaks in its guts may improve efficiency, but it will always be JRE based. I will say a clear advantage here is you don't need to "install" it, copy the files onto your OS and you're ready to go. Super!

Best I conclude my rant for now. Did I miss anything?

ScottWe

ps - commendations to Sue Harper, Kris Rice and the rest of the SQL Developer team - the product has come a long way in the past half-decade. People always have something to complain about :-)

APEX IR CSS tweaks

Recently I had a request to top align all the data within an APEX 3.2 interactive report.

There were some data in the report that made other information appear out of place. Consider the sample data below on demo_customers table. The address field is subtly pushing the other fields down. Combine this with an Edit record image and it starts to look silly.

Normally you can hop into the templates of your application and throw in the occasional tweak to make these adjustments, but I found nothing for an IR.

After a quick search, I found the solution here by Matt Nolan - who ironically enough commented on my blog recently.

My task wasn't as in depth, so I simply used the vertical align style and inserted it in my application's custom CSS file. Alternatively, I could have inserted it into my page footer, for instance.

<style type="text/css"> 
  table.apexir_WORKSHEET_DATA td {
    vertical-align:text-top; 
} </style> 

This adjusted my report to appear just how my customer wanted, and because I put it in my application's CSS file, it applied to all interactive reports.

ScottWe.

Apex Post Calculation Computations

A warning - perhaps my musings are different as I wrote this sitting in Melbourne airport, my mind exhausted after a few days of conducting training with not much sleep in the hotel bed.

Once upon a time I was a regular Oracle Forms programmer (and sometimes still current). These days I spend most of my time with Application Express. This makes me happy as I did enjoy mod_plsql - an ancestor (of sorts) of Apex.

Occasionally I notice some parallels between the two, even more occasionally I get around to writing an entry for the world to see - a strange urge for some but it seems that people read even more mundane topics.

There are many attributes available within the Apex environment. By attributes I mean little boxes in the various wizards ready for my to type something in. Sometimes it seems overwhelming. Then I remind myself how flooded with settings the Forms environment must seem. Of course I snap myself back to normal when I think about what I've seen of JDeveloper.

Have you ever wondered what some of these settings do?

Recently I was creating a copy of a data entry form within Apex so I could present a cut-down / read-only version of the page. There were some fields that instead of being Select Lists, I needed to display their descriptive value - not the return value that is stored in the column.

There are a number of solutions to this problem, as with most problems. One solution I came to involved utilising the "Post Calculation Computation" attribute of the item. This means that after I source the item from the database column, I can transform it's value into something else. The obvious solution here would be to pass the value to a function that determines the descriptive form of the value - from some sort of reference code table.
my_pkg.get_label(:P1_VALUE)

I mentioned forms programming before, right? Immediately I thought of post-query triggers and the pros and cons behind various coding techniques in these triggers. First and foremost was the very same practice of taking a value and converting it to a description. This was an expensive task as not only did it require an extra hit on the database, you needed another round trip from Forms runtime to the application server. The better solution was to incorporate the request within the query - perhaps via a key-preserved view.

The same rings true within Application express. Sure, we don't have another round trip between servers since all the action is happening on the database; however it still requires another select statement to be executed. For a dinky (a Aussie/British colloquialism meaning small and insignificant) little Apex page, what's an extra hit on the ever powerful Oracle database? Perhaps try see what happens when scaling your request to thousands of users.

So perhaps some of our old habits can carry on to this modern day programming tool? I'm certainly not saying this post calculation attribute is not useful. I have another field populated via a list manager with a popup lov. This means the values are separated by a colon. In my application, this field holds a list of e-mail addresses. When I want to present this list to the user in a pretty format, I can use this attribute to convert it to something suitable for a HTML page:
REPLACE(:P1_EMAIL_LIST, ':', '<br>')

Of course if you wish to do this, you may need to ensure your item type does not convert special characters.

It seems my plane is about to call for boarding, so I'll save you all from further ramblings... for now. Enjoy your weekend.