At the UKOUG 2011 conference, I attended a presentation by Michael Salt about indexes. During the course of the presentation, he mentioned something along the lines of “DBA’s should be responsible for indexes (including creating them)” (that’s not a quote; I can’t remember exactly what he said, unfortunately!). I more or less recoiled, because as a database developer, I would expect to be the person worrying about whether I should be creating an index on a column(s) or not, as part of the design / coding phase. Or even as part of the “why is this query going soooo sloooooooowly?!” prod issue investigation phase.

When I mentioned this on Twitter and Facebook, one of my friends said it was because I was a control freak. He’s not wrong – I am a control freak – but I think that’s irrelevant in this case; the time for thinking about index creation is at design phase, and that (at least, to my mind) is firmly in the developer’s area.

Don’t get me wrong; DBAs are fantastic, knowledgable people (I have to say that, I know far more DBAs than Oracle devs! Anyway, it’s true – at least for most of the DBAs I know) and I’m more than willing to take up my DBA’s advice whenever he’s got some to dispense, but DBA’s are always busy looking after far more systems than I do, so I try hard not to take up more of their time than I have to.

It seems that I’m in a minority over where the dev responsibility ends and the DBA’s starts – things like schema / app design, index creation, SQL optimisation etc are dev activities, whereas things like backups, restores, db creations, GoldenGate setup and tinkering, db tuning, giving advice on poorly performing areas of the code, etc etc are more DBA concerns.

Is it just me? Who should deal with thinking about indexes? Devs or DBA’s? What are your thoughts?

Here are the Social highlights of this year’s conference for me (I could detail every meeting, but even I would get bored of reading all that! Needless to say, everyone I met was brilliant, and the discussions etc that we had were entertaining.):

People I met/met up with during the conference (in no particular order!):

• David (@Farkough on Twitter), who is an old friend (and fellow spod!(*))
• Martin Widlake (@MDWidlake on Twitter). A fellow Oracle London Beer conspiritor
• Neil Chandler (@ChandlerDBA on Twitter). A fellow Oracle London Beer conspiritor
• Doug Burns (@orcldoug on Twitter). Yet another fellow Oracle London Beer conspiritor (they get around, right?!)
• David Kurtz (@davidmkurtz on Twitter).
• Niall Litchfield (@nlitchfield on Twitter). An occasional Oracle London Beer attendee
• Patrick Hurley (@phurley on Twitter).
• Piet de Visser (@pdevisser on Twitter).
• Tony Sleight (? I think that was his surname!)
• “Big” Dave Roberts (blog).
• Karen, Tony, Kalyan, and Aftab (? I think I’ve probably got the wrong name here; hope somebody will correct me!) – ex-colleagues of Martin Widlake’s.
• Jonathan Lewis (blog). Another occasional Oracle London Beer attendee.
• Connor McDonald (@connor_mc_d on Twitter).
• Kyle Hailey (@dboptimizer on Twitter).
• Marco Gralike (@MGralike on Twitter).
• Alex Gorbachev (@alexgorbachev on Twitter).
• Debra Lilley (@debralilley on Twitter).
• Lisa Dobson (@lj_dobson on Twitter).
• Alex Nuijten (@alexnuijten on Twitter).
• Tim Hall (@oraclebase on Twitter).
• Ben Burrell, aka Munky (@munkyben on Twitter).
• Martin Bach (@MartinDBA on Twitter).

(If I’ve forgotten to add anyone, it’ll be my shoddy memory, and not down to them!)

Highlights

• Going out for a meal on the Monday night with David (@Farkough), Martin W and Neil, and having a laugh
• Going on to the Tap and Spile following the focus pubs on the Monday night with Niall, Martin, Neil, Dave R and Tony, with Munky popping his head in occasionally. I shall never look at carrots in the same way again!
• Going out for a meal on the Tuesday night with Martin W, Dave R and Martin W’s ex-colleagues, then heading off to the masked ball, and bumping into Connor, Kyle, Doug, Niall and Tony amongst others
• Ending up at the Jury’s bar until the wee small hours of Monday evening, and talking lots of random stuff with various people, including particle physics, and also getting told off by Marco about my shoddy XML db design *{;-)
• The Oak Talks, whilst not strictly a social thing, were fun and entertaining. I hope they have them again next year!

(*) We’re both members of the same BBS: http://www.mono.org/ – still accessible via telnet!)

Then it was off to the first presentation of the day...

Statistics On Partitioned Objects – Doug Burns

I have a feeling that I’ve seen this presentation before – it’s possible that I haven’t seen the entire presentation, but I believe Doug did a couple of work presentations around this subject. Anyway, whether it was those or his blog entries on the subject, a lot of it felt familiar. Doug is an engaging speaker, and the subject is an interesting one. I’m not sure whether it’s applicable to the way our databases are designed, but I will be recalling this talk (amongst others) when I think of ways to improve our stats gathering when we go to 11g.

Using GoldenGate To Minimize Database Upgrade Risk – Marc Fielding

I didn’t fully understand what this talk was going to be about when I signed up for it, but having just worked with and upgraded GoldenGate following a bug in the version we use, plus the upcoming 11g upgrade, I thought it would be a good thing to go to.

Ultimately, I don’t think it’s applicable in our situation this time, but it was about using GoldenGate to copy the data changes from a, for example, 10g database up to the same db but in 11g. Potentially very useful, so a good idea to have in the back of my mind, should I ever come across a situation where it could be used.

The talk was aimed more at DBAs than devs, I think, but I was pleased that I understood most of the GG terms he used – our recent issues at least consolidated my GG knowledge, so it wasn’t all bad!

Optimizer Statistics – A Fresh Approach – Connor McDonald

Yes, the keener eyed/memoried amongst you will spot that I attended this on Sunday. Well, I did have another session lined up to go to, but by this time, my brain couldn’t handle much! Connor was just as amazing as he was on Sunday (no surprise there, then!), and I was able to just sit back and be entertained! (Thanks Connor!)

Faster, Leaner, More Efficient Databases – Index Organised Tables – Martin Widlake

Martin has done a series of blog entries on IOTs and this proved to be an interesting talk. I’ve used IOTs in the past, and will perhaps again, now that our version of GG has gone up to the latest version (the version we had previously couldn’t handle IOTs; I’m not sure about the new version, but I’d hope it would!).

The performance stuff was especially interesting, so I’ll have to go away and see how it might be applied in our databases. A good talk to have gone to.

What Shape Is Your Data? – Niall Litchfield

This was the last talk of the conference, and it was interesting. Niall took us through some examples of how data might be organised and what can be done to best optimize it.

Truth be told, I was flagging a little by this point, so didn’t retain as much of the talk as I’d have liked – I’ll definitely be downloading the slides, when they appear!!

Who’s Afraid Of Analytic Functions? – Alex Nuijten

Alex is someone I’ve known from the OTN SQL & PL/SQL forum for quite some time now, although I’ve only met him in person a couple of times now. He’s a massive proponent of analytic functions (as am I – seriously, they *rock*!) and I couldn’t miss his analytics talk. Sorry Niall – I would have loved to have gone to yours too, but this just pipped yours!

This was one of the few that I came out of thinking that maybe I wasn’t actually so dumb after all! As with everything, there are lots of ways of doing the same thing, and although I’m fairly comfortable with analytic functions, there are still ways that I approach things that aren’t the most efficient. Some of Alex’s examples highlighted that for me!

If you aren’t familiar or comfortable with analytic functions yet, I would highly recommend you go see one of Alex’s talks on the subject!

A Year In Purgatory – Diary Of An 11.2 Upgrade – Connor McDonald

This was a presentation that very nearly wasn’t given! Debra Lilley had seen it when Connor did it in Perth, and was determined to get him to do it at UKOUG. Given that he’d already used up both slots for other presentations, this one was going to be saved for next year but, due to someone else having to pull out at the last minute, Debra got her wish!

All I can say about this is poor, poor Connor and his team (I assume there was a team?!). He took us through all the mishaps that occured in the past year or so when upgrading to an 11g Rac environment. As per his normal presentation style there was plenty of humour and entertainment, but I’m amazed that he’s got any hair left after the nightmare he described!

Troubleshooting The Most Complex Oracle Performance Problem I’ve Ever Seen – Tanel Poder

This was an intersting talk in that for most of it, I was scratching my head as to why it was an Oracle problem! It turned out to be a problem that Doug Burns had encountered (sorry, no spoilers here!), and whilst I basically followed things, I think it was more aimed at a DBA than someone like me. Still, it’s interesting to pick up little tips and tricks on the O/S side of things. And of course, the more I know about the underneath areas of Oracle, the easier it is to talk to my DBAs!

It’s All In The Index – Michael Salt

Poor Michael only just made his presentation, by the sounds of it – massive train problems! However, he made it and produced a useful talk on indexes and how they can be used to improve the performance of poorly running SQL statements.

I didn’t always agree with what Michael said (add an index to aid queries on tables where the data is stored in the wrong datatype? Fix the table/data, not mitigate the symptoms! Meanwhile, back in the real world, I know that’s not always possible!), but he did throw up an interesting off-the-cuff remark that completely stopped me in my tracks! I’ll be blogging on this shortly!

Beating The Oracle Optimizer – Jonathan Lewis

This was a tour of how to optimise a two-table query in 7 different ways – some of which I would never have come up with in a million years! An excellent presentation, as always.

This also happened to be the one that Jonathan asked me to be his alarm clock! I was so worried about missing the 10min / 5min warnings that he’d requested, I was checking the time every other minute or so throughout his presentation… only for him to finish 3 minutes before I was due to give him his 10 minute warning! By this time, I was panicking that the time on both my watch and my mobile phone was wrong or something, whereas Jonathan had apparently been thinking that I’d be waving at him any second for quite a while! Hehehe; I got all worked up for no reason!

Challenges And Chances Of The 11g Query Optimizer – Christian Antognini

This was a bit of a whistlestop tour through the 11g Query Optimizer changes and what we could expect to see once we’ve upgraded to 11g. Some of these I’d already seen covered in earlier presentations, such as SQL Plan Management, but others I was completely unaware of! A useful session to go to if, like me, you’re contemplating upgrading to 11g.

Before Mark came on, we had Debra Lilley and Lisa Dobson give a short talk about the changes in structure in the UKOUG organisation, which was interesting – I wasn’t aware that there’d been such big changes.

Mark came on and gave a talk about how fab Oracle was, and … well, that was my take-home message *{;-) I suspect I may not have been part of the ideal target audience!

SQL Tuning – Kyle Hailey

I’ve seen a couple of presentations in previous UKOUG conferences that were similar to what Kyle presented here – one by Jonathan Lewis on diagraming SQL statements and one by Wolfgang Breitling on tuning sql by cardinality. What I particularly liked about this one is that Kyle used a program to do the diagramming – it can be done by hand, but boy, do I now want that program! I’ll have to research it and see if I can navigate my work’s purchasing maze to get it, assuming we don’t already have it!

I also need to get Dan Tow’s book, which Kyle had based his talk upon.

Partitioning 101 – Connor McDonald

Yes, another entertaining Connor show! This time, all about partitions. I knew most of the pre-11g stuff, but not too much about the new 11g features (I’d heard about the new Interval partions, but not the rest). Connor took us through the different types of partitions and gotchas, tips etc. I shall definitely be revisiting partitions when we go to 11g!

Oracle Optimizer – Upgrading to Oracle Database 11g Without Pain – Maria Colgan The Optimizer Lady

Maria took us through the steps required to successfully upgrade to 11g with minimal pain due to the optimizer changes. A lot of this revolved around SQL Plan Baselines, which sound useful, but I’m not sure I fully “got” it, or how it could apply in our 11g migrations. Yet more things I’ll be adding into the “Things To Consider” pile for our migration!

With all the steps Maria was covering, I was beginning to panic about our migration, until right at the very end, someone asked a question of the rest of the audience: “Has anyone had a bad upgrade to 11g?” which got a tumbleweed! Someone replied and said it had gone smoothly for them, and another response was that they’d not heard of a bad upgrade, so that somehwat put my mind at rest!

Database I/O Performance Measuring and Planning – Alex Gorbachev

I suspect this was aimed mostly towards DBAs, but it sounded interesting, so I thought I’d go along anyway. Some of the technicalities went over my head, but it was useful – I can at least raise the discussion of I/O capacity planning with my DBAs and at least have some vague understanding of what I’m talking about! *{:-)

Performance and Stability with Oracle 11g SQL Plan Management – Doug Burns

Doug went into SQL Plan Management a bit more in depth than Maria – whom he insisted on calling “The Optimizer Lady” throughout his talk! – did, but it wasn’t too indepth, which was perfect for the time of day (being the last presentation before the final keynote of the (very long and full) day, and also served as excellent consolidation for Maria’s earlier presentation.

So anyway, this year, UKOUG and the Oak Table guys got together and put on an extra day’s worth of sessions (which were repeated later on in the conference). Despite National Express East Anglia/Network Rail’s best efforts (Cambridge train station was closed!) I managed to get to Birmingham in time for the start of OakTable Sunday – mainly due to the fact that my other half very kindly drove me to Milton Keynes so that I could catch the train from there! This had the bonus side effect of me not having to get up early enough to catch the coach from Cambridge station at 5:50am!

Anyway, on with the presentation write-ups:

Tales From The OakTable – Mogens Nørgaard

I’ve never seen Mogens before, though I’ve heard plenty about him, and it was somewhat of a shock! He was incredibly laid back, and started off by saying that we were probably not going to learn anything over the conference, or at most, about 5% would stick with us after the conference.

He treated us to a few stories about the OakTable and its members, encouraging stories from Oakies in the audience too. I wasn’t quite sure what to make of this, really, as it was a very strange talk! Perhaps if I’d seen Mogens present before, then it wouldn’t have been a surprise!

About Multiblock Reads – Frits Hoogland

This was about the changes to multiblock reads in 11g, and how you cannot rely on scattered reads anymore – sometimes it will choose to do direct path reads, which means that you lose the advantages of query results being cached. This could be a good thing or not, but it was something I was unaware of (even though Doug has apparently already blogged about it!). Given that we will hopefully be migrating our 10g databases to 11g next year, I’m very glad I went to this session!

Optimizer Statistics – A Fresh Approach – Connor McDonald

Having seen Connor present at the 2009 UKOUG conference, I was determined that I was going to see as many of his presentations as I could. Thankfully, he presents on topics that are relevant to me, but even if he didn’t, I’d go along and see him in action! Honestly, if you’ve never seen him present, if you should ever get the chance to do so, do! He is *a* *maze* *ing!

Connor talked about statisics, both object and database stats, such as how you should gather stats, etc. There were some ideas in there that I’d never considered before (pending stats), but I’ll definitely go back to work and think about our stats strategies etc!

Statistics Gathering and Histograms – DOs and DON’Ts – Wolfgang Breitling

Following on from Connor’s presentation was Wolfgang’s. He went into more detail on histograms and was of the same opinion as Connor – don’t use histograms if you don’t need to; set them explicitly. Another talk I shall consider when looking at our stats gathering!

Performance and High Availablility Panel Session

Finally, the OakTable Sunday was topped off by a panel session of various OakTable members who took questions from the audience and wanted to know about the longest prod outage that people were brave enough to own up to! Thankfully, I’ve never caused a prod outage, but someone had had an overnight outage and someone else had had 18 hours although I don’t think it was caused by them. Connor won on the monetary outage front – he managed to hose a 10 million Aussie dollar truck through a bit of dodgy code!

When I first heard the requirement:

I need to display the numbers 1-12 in 3 rows of four columns, like so:

1 2 3 4
5 6 7 8
9 10 11 12

my first reaction was “really? Just the numbers 1 to 12? You won’t be given other numbers or values to display in that particular way?”

The answer to that was “no”, so the solution I gave in the last post did the trick.

What if the answer had been “yes – I need to display <some other list of twelve bits of info> in that format”?

Thankfully, we’ve already done most of the work – we know how to display twelve things in 3 rows of 4 columns. The trick with the new requirement is identifying which values go in which place.

We’d do that by artificially labelling the rows with a number from 1 – 12.

Lets take our sample data as:

with sample_data as (select 12 col1 from dual union all
                     select -1 col1 from dual union all
                     select 3 col1 from dual union all
                     select 13 col1 from dual union all
                     select -20 col1 from dual union all
                     select 31 col1 from dual union all
                     select 0 col1 from dual union all
                     select -9 col1 from dual union all
                     select -2 col1 from dual union all
                     select 18 col1 from dual union all
                     select 7 col1 from dual union all
                     select 4 col1 from dual)
select col1
from   sample_data
order by col1;

which gives us:

      COL1
----------
       -20
        -9
        -2
        -1
         0
         3
         4
         7
        12
        13
        18
        31

Reaching into our SQL toolbox, we’ll use the ROW_NUMBER() analytic function to assign a number for each row:

with sample_data as (select 12 col1 from dual union all
                     select -1 col1 from dual union all
                     select 3 col1 from dual union all
                     select 13 col1 from dual union all
                     select -20 col1 from dual union all
                     select 31 col1 from dual union all
                     select 0 col1 from dual union all
                     select -9 col1 from dual union all
                     select -2 col1 from dual union all
                     select 18 col1 from dual union all
                     select 7 col1 from dual union all
                     select 4 col1 from dual)
select col1,
       row_number() over (order by col1) rn
from   sample_data
order by col1;

which gives us:

      COL1         RN
---------- ----------
       -20          1
        -9          2
        -2          3
        -1          4
         0          5
         3          6
         4          7
         7          8
        12          9
        13         10
        18         11
        31         12

Aha, now we’re back in familiar territory – we’re back to the numbers 1-12 again!

So, amending the solution from the previous post slightly, we can add it in to give us the results we want:

with sample_data as (select 12 col1 from dual union all
                     select -1 col1 from dual union all
                     select 3 col1 from dual union all
                     select 13 col1 from dual union all
                     select -20 col1 from dual union all
                     select 31 col1 from dual union all
                     select 0 col1 from dual union all
                     select -9 col1 from dual union all
                     select -2 col1 from dual union all
                     select 18 col1 from dual union all
                     select 7 col1 from dual union all
                     select 4 col1 from dual),
         results as (select col1,
                            ceil(row_number() over (order by col1)/4) grp,
                            mod(row_number() over (order by col1), 4) col_pos
                     from   sample_data)
select max(case when col_pos = 1 then col1 end) col1,
       max(case when col_pos = 2 then col1 end) col2,
       max(case when col_pos = 3 then col1 end) col3,
       max(case when col_pos = 0 then col1 end) col4
from   results
group by grp
order by grp;

giving us:

      COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
       -20         -9         -2         -1
         0          3          4          7
        12         13         18         31

If you had to adjust the format of the columns – eg. you wanted the resuls in 4 rows of 3 columns, or 6 rows of 2 columns, etc, then you’d have to amend the number you’re using in the ceil and mod functions in the “results” subquery to reflect the number of columns you’re expecting.

Then, in the main query, you’d have to extend or reduce the number of columns accordingly.

I haven’t restricted the number of rows to 12 in my queries above, because my sample data had at most 12 rows. If your requirement was to only display the first (or last!) 12 values of a larger set of data, you’d have to restrict the rows accordingly. I’d simply expose the row_number() as a colum in the “results” subquery and call it something like “rn”, then I’d use “and rn <= 12″ in the main query.

Sunday

OakTable registration opening times 11:00 – 12:00

Exhibition registration opening times – 15:00 – 18:00

Monday

Registration opening times 08:00 – 20:00

Exhibition opening times 10:00 – 20:45

Mark Sunday, Oracle 10:00 – 10:45 SQL Tuning
Mr Kyle Hailey, ACE
Delphix
Hall: 10:45 – 11:05 Coffee break 11:05 – 12:05 Partitioning 101
Mr Connor McDonald, ACE & OakTable
Independent
Hall: 12:15 – 13:15 Oracle Optimizer – Upgrading to Oracle Database 11g without Pain
Ms Maria Colgan
Oracle
Hall: 13:15 – 14:30 Lunch 14:30 – 15:15 High Availability Options for Modern Oracle Infrastructures
Mr Simon Haslam, ACE Director
Veriton Limited
Hall: 15:25 – 16:10 Implementing Effective Oracle Database Auditing in 3 Steps
Ms Tammy Bednar
Oracle
Hall: 16:35 – 17:35 Database I/O Performance: Measuring and Planning
Mr Alex Gorbachev, ACE & OakTable
Pythian
Hall: 17:45 – 18:30 Performance and Stability with Oracle 11g SQL Plan Management
Mr Doug Burns, ACE Director & OakTable
Bayvale Consulting Services Ltd
Hall: 18:45 – 19:45 Learning about Life through Business and Software

Cary Millsap, Method R Corporation

Exhibition Welcome Drinks 19:45 – 20:45

Focus Pubs and Inspiring Presentation Awards 20:45 – late

Tuesday

Registration opening times 08:00 – 20:00

Exhibition opening times 08:00 – 18:30

Evening social and networking 20:00 – late

Wednesday

Registration opening times 08:30 – 13:30

Exhibition opening times 08:30 – 14:30

So, anyway, I thought I would start a little series on writing complicated sql, by taking you through the steps I would typically take in order to solve the requirement.

I’ll start with that simple request from my colleague:

I need to display the numbers 1-12 in 3 rows of four columns, like so:

1 2 3 4
5 6 7 8
9 10 11 12

Looking in my SQL toolbox, the very first thing I reach for is how to create the list of numbers from 1 to 12:

with nums as (select level rn
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN
-----
    1
    2
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12

Next thing, I know I need to pivot the results (I should add that I don't have 11g yet, so can't use the nice new functionality!), so now I know I need two things:

• how to identify the row a number belongs to
• how to identify the column a number belongs to

Let's take the first item - identifying the row each number belongs to. We know that the numbers 1-4 will go on the first row, 5-8 on the second and 9-12 on the third. I can acheive that by dividing each number by 4 and then take the ceiling value of it, like so:

with nums as (select level rn, ceil(level/4) grp
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN        GRP
----- ----------
    1          1
    2          1
    3          1
    4          1
    5          2
    6          2
    7          2
    8          2
    9          3
   10          3
   11          3
   12          3

Next, I need to work out the column position each number belongs in - that's where the mod() function comes in handy!

with nums as (select level rn, ceil(level/4) grp, mod(level, 4) col_pos
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN        GRP    COL_POS
----- ---------- ----------
    1          1          1
    2          1          2
    3          1          3
    4          1          0
    5          2          1
    6          2          2
    7          2          3
    8          2          0
    9          3          1
   10          3          2
   11          3          3
   12          3          0

So, having identified the row and the column position, we can now use pivoting to display the numbers in the desired format:

with nums as (select level rn, ceil(level/4) grp, mod(level, 4) col_pos
              from   dual
              connect by level <= 12)
select max(case when col_pos = 1 then rn end) col1,
       max(case when col_pos = 2 then rn end) col2,
       max(case when col_pos = 3 then rn end) col3,
       max(case when col_pos = 0 then rn end) col4
from   nums
group by grp;

Giving us:

 COL1       COL2       COL3       COL4
----- ---------- ---------- ----------
    1          2          3          4
    5          6          7          8
    9         10         11         12

Et voilà!

I felt sure the problem was something to do with the multibyte character set my database (10.2.0.4) was set up with, but everything in the documentation indicated that DBMS_LOB subprograms worked in terms of characters, not bytes, when CLOBs were involved.

I managed to recreate my issue:

declare
    l_finalSQL VARCHAR2(32767 char);
    l_innerSQL CLOB := 'a';
    l_outerSQL CLOB := 'b';
    l_userid   INT;
    gOutersql varchar2(113) := 'ccccccccccccccccccccccccccccccccccccccccc'||
                               'ccccccccccccccccccccccccccccccccccccccc'||
                               'ccccccccccccccccccccccccccccccccc';
begin
  for i in 1..1342
  loop
    l_outersql := l_outersql||'b';
  end loop;

  dbms_output.put_line('l_outersql = '||length(l_outersql));

  for i in 1..10860
  loop
    l_innersql := l_innersql||'a';
  end loop;

  dbms_output.put_line('l_innersql = '||length(l_innersql));

  l_userid := 123;

  dbms_output.put_line('l_userid = '||length(l_userid));

  l_outerSQL := l_outerSQL||' FROM ('||l_innerSQL||gOUTERSQL||
                                      TO_CHAR(l_userid);

  dbms_output.put_line('appended l_outersql = '||
                                dbms_lob.getlength(l_outersql));

  IF dbms_lob.getlength(l_outerSQL) <= 32767 THEN
    l_finalSQL := dbms_lob.substr(l_outerSQL, 32767, 1);
  else raise too_many_rows;
  end if;

  dbms_output.put_line('l_finalsql = '||length(l_finalsql));
end;
/

which produced the following output:

l_outersql = 1343
l_innersql = 10861
l_userid = 3
appended l_outersql = 12327
l_finalsql = 10922

Note the 12327 vs 10922 – the expected and actual lengths of the l_finalSQL string.

I could not spot what the problem was for the life of me, so I asked for help on my work’s Oracle chat channel (don’t think I didn’t have a chuckle about that given my previous blog entry!). Thankfully, someone else had the presence of mind to check My Oracle Support, and it turned out that I have most likely tripped over bug 9020537. This isn’t fixed until 12.1 (!) or 11.2.0.2 (Server Patch Set) or 10.2.0.5.3 (Patch Set Update).

Unfortunately, upgrading to any of these versions is not an option for us in the timescale I needed my code to be working, and there is no workaround (boo!) so I’ve had to rewrite my code to work around the issue. Annoying! (Even more so when what I’m doing is to work around the fact that EXECUTE IMMEDIATE doesn’t handle CLOBs until 11g…)