Case sensitivities and making JavaScript more like PL/SQL

Yesterday the Anti-Kyte (a.k.a. Mike) published a great post on coding styles. I started to post a comment but ended up rambling to I thought I'd put my thoughts here.

I can't be one to comment on tangential intros (just look at any of my presentations), but he almost lost me when I thought the entire post was going to be soccer jokes I didn't get but switched in time to three controversial topics:

1. Uppercase keywords
2. Camel case
3. ANSI joins

To add my 5 cents - I still tend to uppercase keywords - partially out of habit and sometimes standards are a client site. I still like the concept of the brain finding lower case easier to interpret than upper case - now with extra help thanks to syntax highlighters. I don't mind all lower case, though I feel a little naughty when doing so. I know Connor does it all the time, and it looks raw & simple - which is not a bad thing.

He scared me a little when he started to provide PL/SQL examples using camel case - I can't stand camel case.
My identifier naming conventions in JavaScript tend to match up with my PL/SQL preferences, though sometimes I find it to be a hybrid in occasions where the name might harmonise with other jQuery functions.

function p42_show_info (t) {
  theRow = $(t).closest('tr').addClass('highlight');
  l_order = theRow.children('td[headers=ORDER_NO]').text();
...

I should probably stick with one way or the other since bugs thanks to tolerated case sensitivity are the worst kind of bugs - parasites, if you will.

I have no problems using ANSI syntax when used explicitly (no natural joins), but I tend to only use them for outer joins and when readability would benefit. There are scenarios where ANSI outer joins are the best solution to a query.

Scott

APEX Printer Friendliness using CSS Media Queries

In the projects I've been involved with I've rarely seen it used, but there is a ninth argument in the f?p= syntax that toggles Printer Friendly mode.

Side note - being the space geek I am this reminded me of the hubhub over Pluto being the ninth planet.

The APEX documentation on Printer Friendly mode states

When referenced, the Application Express engine does not display tabs or navigation bars, and all items are displayed as text and not as form elements.

You also need to take care with the construction of your theme's Printer Friendly page template to help make this happen. This template is included in the theme by default and should have the namely template class.

However, there is another method you may like to consider to prepare your pages for printing - without needing to re-open the page - CSS Media Queries.

Here I have a print button on a demo page which invokes the browser's printer dialog box using window.print();

Basic report with Print button

Trouble is all my menus and sidebars are included in the print preview.

Printer preview including expanded menu / sidebar

CSS Media Queries aren't just for making your website respond to the gamit of screen sizes that exist, but also allow you to interrogate the media type. We can use this to identify components on our page that should not be displayed when printed.

@media print {
  #navwrap, #blog_posts, #p31_detail, #p31_notes, .uButton
 ,.noPrint
    {display:none;}
}

You could either identify the page components in the CSS media query - here I've identified my menu bar, side bars, second region at the bottom of the page and any buttons.

Alternatively you could apply a class to all components you don't want printed - eg: .noPrint.

The HTML shown in the print preview is now much cleaner when I click the printer button.

This HTML document is ready to kill a tree

Of course if you have various form elements you may still want to consider a combination of media queries and the use of the Printer Friendly mode. While looking for documentation links I also stumbled across this example by Andrew Tulley.

Run the demo to see it in action. Try view the print dialog before pressing the 'Apply Media Query' button

There is a more comprehensive breakdown on how to action this for UT here by Sandro
https://www.sotful.com/sandroferreira-blog/quickest-print-friendly-in-oracle-apex

References

CSS Media Queries
W3Schools Media Type
APEX Documentation - Printer Friendly mode
Grassroots Oracle - f?p syntax mnemonic
Andy's Blog - hybrid example
Smashing Magazine - tips and tricks for print style sheets

Order APEX column based on hidden data

An occasional question in the forums relates to issues ordering a particular column. It's one of those things that will probably keep coming up, so it's worth having another reference out here on the web.

The basic example stems from the need to order data that might contain characters.

with data as
  (select '1'  vc from dual union all
   select '11' vc from dual union all
   select '2'  vc from dual union all
   select 'a'  vc from dual)
select vc
from data
order by vc;

VC

--
1
11
2
a

Most of the time people would want this ordered numerically as 1,2,11, then characters before or after the numerics. There are a few SQL solutions, but we need something for APEX so when you click on the column heading - it orders as you expect.

Other sample use-cases might also be found

• in ealier versions of APEX where LOV sourced column order by return value, not display value
• any place where data is formatted in some way
• any occasion where you want to order data conditionally

So another example is the blob file size supplied format mask mentioned in last week's post. Here data was ordered by data values - not logically from our human perspective.
1KB
1MB
2KB

One solution offered here by Arie Geller is to prefix the column value with a hidden column
'<input type="hidden" value="'||(estimate-actual)||'" />'||abs(estimate - actual) AS variance
I found another example from Denes Kubicek back in 2008.

The solution can be even simpler & lightweight than that - use HTML comments as a prefix.

And typically we'll need to left-pad our character data to the same length - this ensures ordering compares apples to apples.

I've applied this prefix on my blob file size format mask example, to allow ordering on the column I said wouldn't order so well. The prefix is essentially the untrimmed output of filesize_mask(), with the leading spaces replaced with zeroes - and the suffix removed.

'<!--'||LPAD(dbms_lob.getlength(blob_content),10,'0')||'-->' 

||TRIM(apex_util.filesize_mask(dbms_lob.getlength(blob_content)))

Both solutions require the column to be displayed as "Standard Report Column". So if you are using text data it's best to also escape your content in the query using apex_escape.html()

Run the demo to see it in action. Try ordering using "Size Mask" vs "Bad Order" and see where +1MB files go

The file names are scrambled using much simplified scrambling technique demonstrated here by Joe Lipman.
DBMS_RANDOM.STRING('a',length(filename))
My full SQL can be seen at the bottom of the demo page.

-- Update: Be sure to read the comments on the post regarding Patrick Wolf's elegant solution of just putting the display version of the column in the column's HTML Expression.

Returning BLOB file size

Occasionally I'll want some form of report noting file sizes of blobs in a database.

The solution is relatively simple, and I thought I'd write it up here for a place to copy syntax each time.

APEX users also have a handy table to verify this against (apex_application_files). Well, a synonym/view that ultimately maps to the core table wwv_flow_file_objects$.

It contains a doc_size column, which is no doubt evaluated at some point during upload of the file. For blobs in your own table you can use the supplied package DBMS_LOB to get the length of the file in bytes.

Multiply that by 3 orders of magnitude and you have it roughly in kilobytes - a digestable figure for most files dealt with these days (calc_size).
Add some pretty formatting and you have how Windows Explorer shows file sizes (calc_fmt).

Inside an APEX report you might consider the format mask "999G999G999G999G990" on 'calc_size' - without any trailing suffix so you can still order the results.

select id, filename
  ,doc_size
  ,round(dbms_lob.getlength(blob_content)/1000,2) calc_size 
  ,to_char(dbms_lob.getlength(blob_content)/1000,'999,999')||' kb' calc_fmt
  ,apex_util.filesize_mask(dbms_lob.getlength(blob_content)) size_mask
from apex_application_files
order by doc_size

/* UPDATE 2014-07-11 */
Carsten (@czarski) pointed out on twitter that an API that's available that returns a rounded off relative size with the appropriate trailing KB/MB/GB/TB.

It's not shown in the current 4.2 documentation - but I think it's probably just been missed rather than it not being supported. The APEX_UTIL package is pretty large should probably be broken up so little nuggets like this don't get missed.

He also mentioned a "FILESIZE" format mask, which isn't shown in the format popup (you just type it in) and also behaves differently to the API. Values under 1MB returned just numbers to 3 decimal places, and a 10MB file was labeled as 10KB - this was on 4.2.0.0

The only downside to the API formatting is as per calc_fmt, the ordering is impaired - by ordering on this column you will get results like:
1KB
1MB
2KB
***/

So in my sample results run in APEX SQL Workshop I can see tiny files to progressively larger files - up to an APEX application export of about 1.6 mb & a PDF over 3mb.

Filenames redacted to protect the guilty

Note, this view can't be queried from SQL Developer (or your tool of choice) since the current workspace is part of the view. Martin has an interesting post to get around this sort of thing by defining an APEX session using PL/SQL.

Scott

Thursday Thought - History & Creativity

My sister sent me this video not long after my daughter was born earlier this year - I finally watched it while cleaning up e-mails and I'm glad I did - I highly recommend you all take 2 minutes aside and just let these images of history wash through your brain.



If you think the first half had an air of familiarity, you might be thinking of this video from Carl Sagan - I certainly was.

Now consider this was a project by a 17 year old at high school. This is the kind of creativity technology kids are creating at school now - awesome.

Note in the about section of the video he states he doesn't own rights to the images or song - if you think laws can sometimes strangle creativity then you might be interested in this TED talk by Larry Lessig.

If you like the video, and like the concept of looking back through time as life on this planet evolved - there is a great segment in one of my favourite fiction books that explores this concept - The Light of Other Days, by two science fiction greats Arthur C Clarke & Stephen Baxter.

Scott.

Two milestones, one offer

I stumbled across a blogging milestone while fact checking what I was going to open this post with - for me this month represents 5 years of blogging.

In that time I've learnt heaps, met new people, changed blog name, visited other cities to speak, and dived deep into APEX after a long time in Oracle Forms, published a video series & was recognised as an Oracle ACE.

Turns out Packt Publishing are celebrating an even bigger milestone - 10 years of publishing. To celebrate they are offering all their eBooks & Videos for $10 each.

Hurry along - you've got until the 5th of July to get my video series "Oracle APEX Techniques" for a steal.

I'm keen on publishing again - I have a topic in mind, it's just a matter of time!

All I can offer for now in celebration of my milestone is to continue to share my passion for Oracle & science ;-)

Scott