Thursday, 25 August 2016

How to debug stuff in Oracle APEX

Recently a fine young gentleman, who shall remain nameless (let's call him Jerry), asked for some assistance he was getting with an error in Oracle APEX.

He had done all the right things in regard to debugging the problem, but didn't know enough about APEX to know which settings to investigate.

I think developers new to APEX need the occasional post like this to give them an idea on how to start looking into a problem, so I hope you found this knowledge helpful in squishing bugs in future.

The problem.

An associate of Jerry's converted a classic report to an interative report. However, when using the search bar to add a filter, the error "missing expression" was shown instead.

We could be fairly certain it's the filter we added that caused the problem, but that's built by the APEX engine. What can we do but perhaps run the page in debug mode to look for clues.

developer toolbar

Jerry enabled debug mode in the developer bar, which refreshed the page, collecting information about the rendering processes within the page. Clicking on open debug, then drilling into the the recent debug entry, using ctrl-F to find the error and you'll see a result like this.

Click/tap to embiggen

The brackets are there, but there's nothing in the middle, hence "missing expression". Jerry told me about this and wondered how this could come to be?

The Hint

Jerry said they recently expanded the capacity to filter stuff on that report, for my benefit, he says... anyway, that's a lead we can follow through on.

It turns out each column in an Interactive Report is configurable at a fairly granular level, and it turns out all columns had the ability to filter disabled.

Isn't it so great we can manipulate 8 columns at once like this?

If a few columns were set as searchable in this fashion, the debug would report a statement that looked like this.
select EMPNO,
  from EMP
)  r
where ((instr(upper("ENAME"),upper(:APXWS_SEARCH_STRING_1)) > 0
    or instr(upper("SAL"),upper(:APXWS_SEARCH_STRING_1)) > 0
) r where rownum <= to_number(:APXWS_MAX_ROW_CNT)

Note your own PL/SQL can also contribute to this debug log using the apex_debug package.

But, how?

Anyway, how could Jerry know to look there? Unfortunately the debug machine can't do all our work and tell us what to check. I guess knowing exactly where to look is when APEX developers get paid the foldy notes, in knowing what sort of settings to check out first.

Through experience I guess I had a hunch on where to look and how to get there. Adding to that, any errors you may receive with Interactive Report filters, try repeat the filter for just one column instead of 'row text contains'. I find issues are often isolated to one particular column.

I normally start with the related region if I'm not sure where to start, though these settings are mostly oriented to the region framework, regardless of the widget inside the region - report, chart, plugin etc.

People new to the Page Designer often forget the 'Attribute' node under the columns. This is where you find settings specific to the region type you've chosen.

IR search bar controls are fairly granular, and I remembered that IR columns also had some options, and seeing 'Filter' gave it away for me.

Will we always have a job?

Back to the concept that computers can't do it all. I think us software developers have got it good, because we're the ones who need to design the AI to solve problems without humans. We'll be one of the last jobs to go, right?

Thursday, 28 July 2016

Application Item Prefix Usage

I was constructing a fresh app for a presentation idea and I came to think about the prefix people use for Application Items in Oracle APEX.

I've seen a few variations used, and upon asking the question on Twitter (as a poll limited to 4 options), some came back with a few other suggestions.

The final tally based on the replies (we'll see how pasting from Excel goes...)

F_ 15 Inspired by APEX team, no doubt. +1 for me
APP_ 10 Fair call, but could match built-in
G_ 6 Global, clever. One person combines this with A_ for application scope.
{project} 5 To match the prefix on your tables, right?
AI_ 3 Can't deny the provenance
{none} 3 This is dangerous, potential clash with built-ins
A_ 1 Based on item scope.

I remember some chatter recently about a community sourced standards document for APEX being kicked off on StackOverflow. Perhaps mini surveys like this can help shape projects like that, or maybe it fits into the oraopensource activities.

Either way, this sort of discussion is surely healthy for the community, eking out any important considerations in sometimes seemingly trivial matters, or "bikeshedding" is a term I heard recently.

I can't say I've tried creating application items (or substitution strings for that matter) that are the same as a pre-defined substition string, but surely it would be detrimental.

You never know, even the Oracle APEX development team may decide a thing or two from community responses. I certainly know what my next poll topic will be, now the Fire on Page Load default No campaign is over ;p

ps - more 2015 survey results to come. The next question was on instrumentation, so a few juicy points to make there.

Friday, 8 July 2016

What should I know about SQL?

Chris Saxon from the Developer Advocates group asked the following question on Twitter.

My immediate thought regarded features I'd be disappointed to live without. Looking at some other responses I realise that the answer matches Tom Kyte's creed: "it depends".

Here is a collection of responses that I think sums(sql) really well, syntax pun = intended.

What feature should I know, to be productive & efficient?
1. analytics
2. scalar subqueries
2. with clause

What fundamental principles should I understand?
(Stew Ashton)
1. DDL: data design is key to good SQL
3. Bind variables

What frequent mechanics/behaviour should I be consistently aware of?
1. null behaviour
2. date arithmetic
3. data type comparison issues, for lack of better desc

All the while remembering architecture is so important.
(Dani Schnider)
1. SQL is a set-based language
2. Clean data model is important
3. Constraints are not just for fun

and so is performance
(Draft A Center? Or Kyle Hailey posting from wrong account?)
1, Bitmap Indexes
2. Reading Explain Plans
3. SQL Tracing

Why there are often exceptions to rules:
(That Jeff Smith)
4. getting results back doesn't mean your code is right.
5. learning never stops

For the creative out there.
(David Fitzjarrell)
6. There is usually more than one way to write a query.
7. Sometimes you need to think 'different'

And there's always the "comedian".
(Connor McDonald)
select, from, where :-)

That's quite the collection!

Wednesday, 6 July 2016

APEX Survey Results: Editing Tools

Another preference question in my 2015 survey.

Q5: What editing tools do you use for PL/SQL and JavaScript

That Jeff Smith fellow should be pretty happy with the top result, but almost half of the "Other" responses said PL/SQL Developer. I used this many years ago while SQL Developer was still being born. Since SQL Developer was free and portable, it was an easy selection, though I only use if for queries, not PL/SQL development.

A number of editors were suggested in Other, including two takers for Notepad. All I can say is wow, and I hope for the sake of others that tab characters are not being saved in your files.

I've been happily using Textpad almost since I started coding. I'm interested in making the jump to a non OS specific editor like Sublime, or perhaps Atom, but I'm not there yet. Though after watching the Kscope16 deep dive / montage (as a livestream), I want to try soon!

Monday, 4 July 2016


Saddened but not surprised to see COALESCE lagging behind NVL.

Why? Because I think coalesce is a good idea and the modern equivalent of NVL.

I follow @SQLDaily for useful tips. Oracle SQL evangelist Chris Saxon runs the feed.

Sunday, 26 June 2016

Have fun #KScope16

Full and utter credit goes to the gang at CommitStrip, you should check it out.

I thought a few choice replacements would help Joel Kallman and team rip into what I'm sure will be another stellar set of Sunday Symposium in Chicago.

Credit to CommitStrip, text modified from original

We'll be listening around the world, keep your tweet on.

Tuesday, 21 June 2016

Hide nulls in Value Attribute Pairs report

If you have one record where you want to display multiple columns of information, the 'Value Attribute Pairs - column' report template is pretty nifty.

Some of the packaged applications use this within the breadcrumb bar, above a region display selector, and it looks really tidy.

Nulls shown with tilde

Note, I've modified region attribute setting 'Show null values as' to a tilde (~).

But what if I wanted to hide those null values for Mgr & Comm, similar to the 'show nulls' option within single row view of Interactive Reports?

Create an 'after refresh' dynamic action on the region. This is the default when doing so via the Page Designer.
// for each value cell found on the page, determine if contents = ~, then hide row if true
$('dd.t-AVPList-value').each(function() {
  if ($(this).text().indexOf('~') > 0)
Then refresh your page. Done.

Nulls hidden
And if you have another event that refreshes the region, the JavaScript will re-apply and hide any nulls (represented as a tilde - something to find and hide).

The dd.t-AVPList-value is a selector for the Universal Theme. It uses a different class in other themes, so you would have to investigate using Inspect Element browser tool to check.

Right-Click -> Inspect Element

Thinking with my jQuery hat.