Wednesday, 4 March 2015

LISTAGG to a CLOB, avoid 4000 chr limit

Thanks to twitter I found this post by Carsten Czarski on LISTAGG and CLOBS that helped my with the 4000 character limit with LISTAGG(), found when building JSON strings.
ORA-01489: result of string concatenation is too long

I follow a few bi-language blogs but I do wonder if English speakers may find this post when googling the issue. For me it's on page 1 when googling "listagg clob", but I knew what keyword to search after the fact.

A little tip if you do find it - don't attempt to copy the code from the translated version of the page. It's amazing how many syntax issues were introduced by the translator, and logical issues that I wouldn't have noticed had I not performed a diff after things went wrong.

For instance, the "aggregate" keyword here vanished:
CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob
PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;
/

Thank's Carsten for a useful solution using custom aggregate functions, an element of PL/SQL I'm yet to master.

It's certainly faster than the XML solution, here is the throughput difference from 100 iterations of each.
-- listagg_clob
3.34 secs 
.0334 secs per iteration
-- xmlagg
18.75 secs 
.1875 secs per iteration

The simplest solution, however, would be to set up an RESTful service using ORDS.

Tuesday, 24 February 2015

Introducing the Official LinkedIn APEX Group

Once upon a time I had a laugh on facebook when someone posted an image showing multiple groups dedicated to stopping duplicate groups, oh the irony.

Recently I've been trying to share my blog posts via LinkedIn but I'm always unsure which of the seven APEX groups I'm a member of I should post to.


It turns out only the SIG groups are owned by heavyweights, namely Product Manager David Peake and evangalist Dimitri Gielis.

I asked the question twitter ...
... and once again Joel Kallman comes through and creates the 'official' uber group for APEX developers.

https://www.linkedin.com/groups?gid=8263065

Joel includes this in describing the purpose
People should freely promote anything they wish here - jobs, trainings, new products, new blog posts, whatever you wish. But it has to be related to Oracle Application Express or the community. If not, it will be deleted
I've been nominated as a moderator, something I've never done before but we'll see how it goes. However if you need help with APEX programming issues, please utilise the OTN APEX forumStackoverflow is also handy for jQuery/CSS questions, though I'd still recommend OTN first to keep question in context.

The group is still in it's infancy, but the plan is to make it a quality feed for APEX developers, perhaps those who aren't twitter users. Though if not, perhaps you should.

If you're an APEX developer looking for another source of information, feel free to join and contribute.

As Joel likes to say... #letswreckthistogether!


Monday, 23 February 2015

APEX Community Sample Applications

There are a bunch of sample APEX applications out there built by developers (typically with blogs) that are darn useful, but so far I haven't seen a useful central repository.

So I've started one here with a very basic form/report:
https://apex.oracle.com/pls/apex/f?p=73000:APEX_SAMPLES

At the moment it's just a list of those I happened to have bookmarked over time, in one place that others could use and perhaps contribute to.

Depending on feedback I have ideas to expand this concept, including but not limited to:

  • Better tagging, searching
  • Community ratings
  • Include and flag sites that happen to use APEX, for example apex.expert
  • Add notation for websheets, I'm sure some are out there...
  • Improving UI to use dialog

This would have been useful recently when a twitter friend asked me if I knew any good samples out there using the mobile user interface.

Scott

Sunday, 22 February 2015

2014 Annual SQL Championship

Steven Feuerstein runs a great site at PL/SQL Challenge that is just another way for developers to stay up to date with their knowledge of PL/SQL, SQL and database design with a bit of fun.

PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.

Unfortunately do to a timing issue, I missed the Database Design championship. I'll have to try again next time - hopefully with more competition from the wider community, <hint> since it's a worthy cause to participate. </hint>

Steven recently announced the rankings for the 2014 SQL championship. I managed 9th from 40 competitors, which is pretty cool. With a little less haste I should have got a few more correct but that's the game you play ;p

To perhaps pique your interest, the question topics were

  1. String aggregation techniques
  2. Temporal Validity (12c date queries)
  3. Date datatype behaviours
  4. DML in nested tables
  5. MODEL queries.

Yup, it got hard quick, but they don't call it an annual championship for nothing, right?


Twitter as a work tool

Scott Spendolini conducted a little experiment last week, then Jeff Smith & Kellyn Pot'Vin whipped up a little slideshow on the topic.

I thought I'd chime in after some recent experiences were helped along by the use of Twitter, a tool it seems many are underutilising.

Why?

Exhibit A

I had an afternoon of generating JSON with SQL using LISTAGG and I kept facing this issue of being limited by 4000 characters. Knowing I occasionally get feedback from comments on Twitter about SQL, I thought I'd post something passive

Moritz Klein ended up replying, pointing me to a blog post in german by Carsten Czarski that I may have ended up missing with a google search. Utilising that post will be another story, but the fact is within an hour I received a useful response to a simple tweet.

Exhibit B

Trevor: Can we advertise your APEX demo as using APEX 5?
Me: Don't know, I'll ask the product manager
<goes on twitter>
Reply from Joel Kallman:
"I'll ask the Oracle APEX product manager" - can you imagine saying that in 2005? Australia is almost the antipode to the United States...

Exhibit C

All the examples Tobias Arnhold provides here regarding SQL Developer tips. I know there is plenty I don't know about SQL Developer, I scratch the surface as far as what it can really do - yet here is a great way to learn little snippets at a time.

Initial Trepidation

I understand people's hesitation to join yet another social media outlet, I had the inital trepidation considering I only got talked into joining Facebook to play some online poker.

Twitter usage has some distinct levels, my first post about twitter suggested just following certain people by adding them to your RSS feed (long live google reader). You can also secure all your tweets to only those you let follow you, which I did for starters while I tested the waters.

Then I came to realise how useful twitter can be even as just another resource to find information, but I also realised it had a little more potential than that.

Twitter as a Tool

You don't even need to register a twitter account to extract information - it's a public feed. Visiting this URL alone will provide you all the recent posts about APEX
https://twitter.com/search?q=%23orclapex
Visit this URL to peruse tips about SQL Developer at your own leisure.
https://twitter.com/OracleSQLDev

Creating an account will make it easier to use twitter as the apps will help you follow the grain, so to speak. Over time your can build rapport with the community to enable you to harness it further.

As Jeff and Kellyn explained in their slidedeck, you don't need to post statuses to make effective use of twitter, or worry about gaining followers - but it can help.

Critical Mass

In my experience I found once I started contributing more, I gained more followers. I'm sure that's simple internet physics, but I think it helps attain a level of critical mass that turns a status from shouting into a crowded room to something that people will encounter and perhaps give you feedback on (see Exhibit A).

You posts don't need to be about what you ate for breakfast, the deja vu I experienced writing this sentence, or about piece of code you just wrote - though some people do in fact find that interesting.

Thanks to a suggestion from Chris Muir, I started reposting articles I was reading from my RSS feed, often prefixing them with "Reading: ", showing I wasn't necessarily endorsing the article but found it interesting enough to share.

I read a great article once describing the benefits of being a contributor to social media, not just a consumer. In trying to locate it I came across a term for this: prosumer.

Coming back to the critical mass concept, I wonder if that's why some people have trouble working out how to use twitter and "don't get it", particularly if they've been a long time facebook user - it's just a different kettle of fish (aren't English idioms crazy?)

Outcomes

I think I can attribute twitter with a lot of credit when it comes to having my paper accepted at Kscope15. Twitter has helped me build an online network, I've gained rapport with my peers and I've demonstrated who I am beyond what I write in this blog.

Another great advantage is to help find like minded people at conferences. I found Spendolini's comments regarding twitter participation interesting, I've observed the same here in Australia.

A few years ago I attended an APEXposed event in Melbourne. Out of the 50-odd people I found a mere handful tweeting about it. In some respects it was an echo chamber, but you'd be surprised how many people in other countries are interested. I often follow feeds like #apexworld to listen out for special announcements or news.

And I did make a new friend, someone to make banter with during the event, to affirm certain thoughts, to help, to learn from. Imagine if more people did it?

Basic Rules

Before using something for the first time it helps to understand the basic rules, from a programmer's perspective.

First of all it's not case sensitive. Hoo-bloody-ray, it's not related to JavaScript. That being said I find adding the occasional capital helps readability.

Hash tags 

if you've ever encountered instagram, hash tags are an easy way to be a douche and stop people from venturing into twitter. Used effectively they're essentially an index to good information. Take #orclapex, it's the standard hashtag used for all posts relating to APEX. It's an easy way to find information you're after amongst the 500 million tweets per day.

Nobody owns a hash tag and anyone can create one. Sometimes they're done just for humour's sake, in fact I think there is a certain nature to twitter humour that's a little unique, but it could just be me.

@somebody

If a tweet starts with the @ symbol, meaning you're tweeting to somebody in particular - this won't appear in your standard feed unless you follow both people. This means that you don't see every random conversation that might be going on.

Sometimes you might see a reply that starts like this:
". @somebody blah blah"
Sometimes people Prefixing with a period to reply with a particular point to make, but make it so anyone following will see it.

You can also include @somebody at the end of your tweet to clue them in on the conversation, or prefix with "via @somebody" to help direct people to the author of a linked post.

Retweets 

If you retweet something, it will appear on your feed as if you wrote it, but you won't get credit - only for finding what might be an interesting tweet worthy enough of retweeting. It's similar to multiple people sharing the same thing on Facebook.

Interesting nuggets get retweeted. More people see it, it gets retweeted. Come up with some gold and you'll be amazed how far it goes.

Favourites

I think people use favourites in different ways. Some people use them similar to bookmarks, to follow up later. Others use them to help represent who they are when people look at your profile and decide if they want to follow you. A link exists on your profile that shows all your favourite tweets.

Lists

I've tried to allocate each person I follow to a particular list, like filing emails into particular categories. One advantage of this is if you feel like seeing what your Oracle contacts currently have to say, twitter software can list only tweets from that list. If you feel like seeing what's in your science news feed, ditto.

Another advantage it provides is an easy way to find others you might like to follow. I have an "Oracle" list which is actually quite generic to all things technology. I have JavaScript, general programming, HTML, and tech news feeds in my list - anything that might help my career in some form. Feel free to see who's on it.

Other uses

I started using twitter for anything work related, then quickly found it useful for other things. For instance I encountered the hashtag #PerthTraffic to help notify me of any bungles before I drive to work.

I like science stuff, so I started following certain scientists and science educators for interesting information. Phil Plait and @NeilTyson offer great snippets and often "live tweet" astronomical events or scientific achievements like the #Philae lander. See how this works?

In fact twitter is the perfect tool for obtaining information about current events. Want to see photos about flooding in a nearby town? Hop in twitter and you'll get photos from people on the scene.

Open any article from a news website, chances are it includes a bunch of tweets from random citizens. Does this make some journalists even more lazy? This blogger thinks yes.

Everyone also needs a laugh. Plenty of parody accounts exist, and some accounts you have to wonder if it's a 'Poe'.

At the other end of the scale, even Mars landers and other space probes have twitter accounts. You can discover accounts that suit your interest over time, and with the help of critical mass.

Work vs Personal

There appears to be conflicting opinions on this, googling "twitter work vs personal" returns over a billion hits. Jeff & Kellyn's slides suggested to keep it separate. Like his two blogs, Steven Feuerstein created a twitter account for PL/SQL and one for is own ramblings. I respect that and the reasons for it, but I subscribe to the opposite opinion.

I started just using it for Oracle stuff, you know, 'work'. Then I started re-posting tech articles I find, or interesting science stories like a future generation hard drive technology. Then I started having conversations with other people about things they were posting. I was using twitter as a tool to garner information.

Where is the line? I initially had a disclaimer on my profile stating it was my own opinions blah blah and I might post about stuff that wasn't Oracle. I'm me, why should I have to filter my thoughts and uses of twitter into two distinct accounts? How do I decide which account to use if I think a post would be of interest to followers of both accounts? Do I rely on twitter apps to help manage this? What if I post to the wrong account?

I decided not to sanitise my thoughts in that way. I still have boundaries regarding what I post, but my @swesley_perth account represents mostly technology and some outside interests in science and skepticism. I've even drafted a blog post describing a pie chart meme generator using APEX that describes the general distribution of my posts.

People can choose to follow me based on the list of hashtags I have in my profile, and by the tweets I tweet. All work and no play makes Jack Scott a dull boy. I'm human and have other thoughts to share, scroll past if you're not interested ;p

Bullying Kills

There is a golden rule: don't be a bully. Bullying can kill people. It's so easy to type an ad hominem attack towards someone, but you really don't know who's on the other and and what frame of mind they might be in.

This might be a complete divergence from the rest of this post but I feel it's necessary to mention.
Nobody deserves it, and people shouldn't need thick skin just to use the internet. I would like to think it's just a really small percentage of people, but I see it so often.

Block, report, ignore - but that's only the start.

Conclusion

If you're using Oracle technology and want to learn & keep up to date, you shouldn't ignore Twitter, just like you shouldn't ignore forums and blogs.

It's worth the dive, how far you go is up to you.

Monday, 16 February 2015

APEX Best Practices Survey

Hi All,

I was inspired by Peter Raganitsch to use the Survey Builder packaged application to create a survey that will help with my "Evidence Based APEX" presentation.

I would appreciate 5-10 minutes of your time if you could fill out my survey:

https://apex.oracle.com/pls/apex/f?p=70347:Q::AAC3
How-to Geek

You'll find a small set of questions over five major topics.

  1. Instrumentation (debugging)
  2. Performance
  3. Security
  4. PL/SQL
  5. JavaScript
In time I'll discuss the results, I look forward to reading your responses.

Scott

Thursday, 5 February 2015

2014 Blog Review

Yup, it's February. Early adopter 3 for APEX5 has just become available, and I've just logged of an APEX 3.2 instance.

I've had this sitting my drafts for some time (it has company), thought it best I finally finish it off an move on.

Considering I fathered an awesome girl in January with my bare hands, surrounded by kangaroos with warm pouches waiting (some facts may have been distorted in this story), I think I did pretty well on the nerd front.

Blogging

Last year I aimed for a post per week and I think I honoured that fairly well, with a few peaks and troughes. I'll have nothing on Mr 100 Day Dimitri.

My March series on APEX 5 drew the biggest crowd, and my non-event webinar was the other blip thanks to some really ordinary internet speeds. Certainly no good for streaming anything either, so nothing from me netflix australia, though I hear I won't miss much.

This year I'm going to need to cut right down due to some other projects afoot. That being said I have a heap to process that have been drafted, from tech posts to observations (that will be dated) & other ramblings. And I'll certainly mention a thing or two if/when I get to play on APEX5 EA3. Luckily the community is right on top it, 1180 workspaces and counting.

For some reason my older posts still get the most hits though, I guess I need to post some new corkers.

I'm 99% certain I want to ditch Google+ comments and go back to one that accepts a wide range of identities. One that also notifies me of comments so I can interact in a timely manner with visitors so kind to leave some thoughts. Just a matter sitting down one day. Only trouble is I think I'll lose some history in the process.

APEX 5

Congratulations to the APEX development team on the amazing release that is APEX5 (EA3). I suspect this release will be darn close to the final product, though I'm sure they'll receive some very useful feedback.

I look forward to getting some time to update our training course notes and finding the usual unexpected goodies. Hopefully I'll be writing client software on it without too much adieu, and I'll never want to go back to 4.x, let along 3!

Even after a few days play the 4.x blue seems dated.

ACE

I originally had a great segue, but I redrafted. Anyway, at least two fine humans gave the honour of ACE nomination in 2014. I've drafted some thoughts on the topic that I'll post separately one day.

Career

We had a fantastic APEX deployment to iPads that continues to make most of the team keen to use the tool. Resultant data is coming through now and they're liking the shapes it can make! More exploratory work with a variety of web components continues to demonstrate how flexible APEX can be as a framework to the database.

A colleague and I were recently reviewing the Kscope presentations that we would attend (there are so many!)
So many brilliant open source frameworks and technologies being talked about at Oracle conference that simply weren't around 3-4 years ago.

What must it be like for a software developer coming out of university these days? Spoilt for choice or overwhelmed with options? It's hard enough to keep up with 15 odd years under the belt. I'm starting to feel like the COBOL programmers I met in my first developer job, well, maybe not that bad.

“The most disastrous thing that you can ever learn is your first programming language.” - Alan Kay


Book

Yep, I'm halfway through... ok, maybe a third.. writing a book involving APEX. This shares top priority at the moment and I'm very excited about it. More detail in future and writing with Apress is such a different experience to developing a video series with Packt.  A future presentation, I sense.

And so much easy to refer to a 'book'. 'Video series' just doesn't have the same ring to it, or 'screen casts on APEX techniques'. Even producers of podcasts can summarise it in one word.

Anyway, I don't think it will be ready by Kscope15, maybe Oracle Open World. I think I'm ahead, but I've also got a presentation to finish/write.

Kscope15

Holy smokes I can't believe I'm finally heading to a conference like Kscope. I must say many thanks to Sage Computing Services for getting me there and supporting my growth over the past 7 years, I have the most wonderful boss. That being said, it will be the biggest dragon of time over the next 5 months!

ODTUG have been generous to me since I've got to swim so far to get to Florida, so they've given me two presentation slots. There was word on a third, not the bird, just the word. (Late night joke referencing this)

For some reason I decide that it's this year I'm inspired to write my first 'best practices' type presentation, but I'm excited about the theme I'm using, just working on the content. I'm jealous of Peter Raganitsch's survey idea and look forward to hearing his results. I'm still in mind to still do a super short one with some targeted questions, just to test the waters with certain topics.

I'm also running the jQuery deep dive, though I'll have to shrink it a little and make some upgrades to suit the flavour of Kscope. Rest assured this one is to help the masses take hold of such a ubiquitous toolset.

There will be some smart cookies at this conference but hopefully I can offer some fresh perspective. If anything you'll perhaps walk away knowing about a few more amazing scientists - the real heroes. I'm turning up the geek to eleven in my 'Evidence Based APEX' session ;p

My wife and (by then) 18 month old will be journeying with me. We'll hopefully be rampaging through New York, probably Niagra, Washington DC, Florida, Disney, Las Vegas, and San Fran for 4th July. And I welcome any suggestions, including what to do about the falling aussie dollar!

Youtube

Failed on that on this year. I was kinda waiting on APEX 5, I had a theme in mind. Maybe some this year. Maybe.

Ubuntu

Still running new laptop on it, skimming the surface. Still have Windows 7 laptop on side as backup for certain things. Ubuntu's not bad but not bug free. I think I'm liking it better than Windows, but there's a lot of catching up to do.

12c

Looks good, I like some of the new features a lot but still not sure I'll encounter it in 2015. I have friends on the same street however that have even done some pattern matching awesomeness.

Science

I wanna see this close up
There is so much groundbreaking stuff going on, hard not to read about it. This year during Kscope, a probe will be well within range for photos of Pluto with better resolution than Hubble, and zooms by in mid July. The plutoid hasn't even made one orbit since discovery, and has more known moons now than when the probe with a twitter handle (many do, it's @newhorizons2015) launched.

The LHC will be back online, who knows what widgets of mass and energy they'll learn about. Dark matter and dark energy are just waiting to be understood. All this done with a tool churning seriously big data.

Not bigger than the SKA, however, another amazing entity itching to look into the dark. Technology drives other technology. If you don't think this image makes you realise how insignificant we are, wait until this produces data. More data than society can currently handle, in fact.

Meditation

More of this required. We're encouraging it's return amongst our development teams. I find there is so much value in become more self aware. Put the smartphone down for a little while, allow yourself to get bored occasionally. It's healthy [citation needed]

2015

No predictions this year, it's going to be big enough as it is. Book, Kscope, APEX5 - no need to add to that!

Just remember, if it's 2015 and I bump into you, if you know there is a pool table nearby and I'll gladly play a game over a tasty beer.

Ramble over.