Raptor has been set free

As a slightly late Christmas present, Raptor is now available for download (at about 60 Mb for the Windows install).

On very first impressions, I'm not convinced on the PL/SQL editor (though maybe it doesn't match my formatting style), but as a straight query tool it is looking good.

12 Selects of christmas (part 1)

Since we are still in that festive spirit, here's a four parter on 12 different things to do with a SELECT (which is a nice alternative to 12 things to do with leftover turkey)

1. Plain select with a side order of flashback

You can't really start anywhere other than a plain select. However to add some flavour, I've include the FLASHBACK clause.

create table test_tab (id number, val varchar2(100));

begin
dbms_lock.sleep(60);
  -- In 9i you may have to wait three minutes, not just one.
end;
.
/

insert into test_tab
select rownum, to_char(add_months(sysdate,rownum),'fmMonth')
from all_tables
where rownum <= 12; 
commit;
SELECT * from test_tab AS OF TIMESTAMP SYSDATE-(1/(24*60)); 

Now, try this one.

delete from test_tab where rownum < 5;
select * from test_tab;
select * from test_tab as of
 SCN dbms_flashback.get_system_change_number; 

Using FLASHBACK to go to the current time/SCN allows you to select the current COMMITTED state of the table, that is without all the changes your session has made. An interesting feature, but I am unable to think of any practical use for it.

2. Inline View

Here I'll show the first and last days of the months in the current year.
Rather than repeat the TO_DATE(...rownum...) operation for each column, I'll select that in an inline view.

select to_char(val_date,'DD-Mon-YYYY') first_date,
      to_char(last_day(val_date),'DD-Mon-YYYY') last_date
from (select to_date('01-'||rownum||'-2005','DD-MM-YYYY') val_date
     from all_objects where rownum <=12) order by val_date;

3. Scalar subquery

Now how many objects did I create in each month.

select to_char(val_date,'DD-Mon-YYYY') first_date,
      to_char(last_day(val_date),'DD-Mon-YYYY') last_date,
      (select count(*) from user_objects
      where created >= val_date
      and created < add_months(val_date,1)) num_obj
from (select to_date('01-'||rownum||'-2005','DD-MM-YYYY') val_date
    from all_objects where rownum <=12)
order by val_date; 

Heading towards Christmas down under.

I've been in Australia for about seven years now. I'm finally adjusting to Christmas actually being HOT not cold. Sydney is expecting 38 tomorrow (100 for those in the US) and that takes a lot of adjusting. Apart from thirty years of associating Christmas with heavy coats, gloves and hoping my toes don't fall off, all the Christmassy songs (except for this one) have got that Northern Hemisphere bent. Snow, sleighs, Jack Frost nipping at your heels and winter wonderland all over the place. After watching a Winnie the Pooh Christmas cartoon, my three-year old son asked whether it would snow soon. Luckily he didn't like his only contact with snow, eighteen months ago on a day trip to Lithgow in the Blue Mountains, so he wasn't too disappointed when I said that it wasn't likely.

But I'm still having trouble with the daylight. There's just so much of it. Every night, my son wants to see the "Christmas house", which is basically any of several houses that has LOTS of lights. The problem is, it doesn't get dark until after his bedtime. Still we've allowed a few late nights, and one with his baby sister. He was pretty much asleep at the 'Carols by candlelight' before we even lit our 'candle' (one of those glowing plastic sticks which are less likely to start bushfires).

While I'm talking about Australia and children, the ABC (Australian Broadcasting Corporation, not the American one) is our national broadcaster. It is funded out of general taxation, unlike back in England where you had to be licenced to own anything as dangerous as a television set. It's children's television segment is presented by a rabbit called Mixy.


Rabbits were introduced into Australia over a hundred years ago, partly as food and partly as something to hunt. The thing about rabbits is that breed, well, like rabbits. Start with a couple and you soon have a whole continent full. So in the 1950's myxomatosis was introduced into Australia to kill off as many rabbits as possible. So the national broadcaster thinks it perfectly reasonable that its Children's TV segment is presented by a feral, diseased pest. I'm unsure whether that is because it is how Australians view children, or whether its what they want them to grow up to be.

And finally, on children's TV, there's also a disturbing similarity between the Teletubbies and the Australian Federal government. For those without small children, the Teletubbies are group of four rather over-fed individuals who live in a grass-covered dome in the far from anywhere. Often, with overtones of Big Brother, trumpets will emerge from the ground from which anonymous voices will tell the Teletubbies what to do (though they may be naughty and ignore the advice).
The Australian Federal Government is based in a grass-covered dome in Canberra, built roughly equidistant between Sydney and Melbourne with the sole purpose of being a place to put the government. They often get advice from anonymous public servants, some of which is followed. And both Teletubbies and politicians speak in a babble from which you can understand the occasional word but which, on the whole, generally makes pretty little sense.
The Teletubbies at least have the Noo Noo who comes to clean up after them.....

Campaign for the abolition of commented out code

AskTom recently had a post on the ten commandments of Oracle development. The commandment that generated the most comment was on commenting code.

I do agree with useful comments in code though I'm sure I don't do it as much as I should (New Year's resolution time). However my personal pet hate is commented out code. These days I general use a pretty editor with syntax highlighting, so it's a lot easier to ignore all the 'green stuff'. In the days of black and white editors (and some people still LIKE vi), it is very easy to lose track of what is 'real' code and what was code three years ago but isn't anymore. [I've read that some 'spawn of vi' editors do have syntax highlighting, but all I get is plain vanilla vi.]

IMHO (and that's all this blog is) dead code belongs in source control history and no place else. If you absolutely MUST comment out code, use the -- form of comments so that people don't have to page up and down to work out whether the code they are looking at is still being used or not.

Shock - Wikipedia found to be accurate

Okay, not quite, but at least no less inaccurate than Britannica in a few places.

But how important is accuracy ? A while ago I considered a posting about the various Oracle forums around, and then got side-tracked into how responses can be measured. When a question is posted in a forum, what the questioner is looking for in an answer is USEFULNESS rather than ACCURACY. There are other factors, such as PROMPTNESS too, but lets not go there now.

There's a lot of overlap between 'usefulness' and 'accuracy'. You can be inaccurate by being incomplete (missing exceptions that may apply in different environments, Oracle versions and so forth) but if the answer applies to the questioners system, they will still find it useful. You can be inaccurate by being wrong about the 'why' but if the 'what' is right, you may still get away with it. It's only when the suggested action does not bring about the desired result that the response is bad.

And accurate answers aren't necessarily useful. "You'll need to upgrade to version ... to do that" isn't normally helpful and some questions simply don't have a useful answer. "I'm in noarchivelog mode, haven't backed up since the weekend, and just poured beer all over my disk drive at the Christmas party. What do I do now ?" Update the resume perhaps ?

Encyclopedias aren't forums of course. For the most part, the information in an Encyclopedia isn't at all useful. Are you likely to consult an Encylopedia before buying a car or camera, moving house, rewiring the electrics or trying a spot of open-heart surgery ? Generally, you'd look for a specialist resource before attempting any of those tasks, or just look for a specialist.
Encyclopedias are great for when you have a casual interest in a topic, such as working out where the country you team drew in the World Cup actually is. Curiosity satisfied, you can put the book back on the shelf (or close the browser), job done. You don't actually act on the information.

The other main use for Encyclopedias is the School Project, which just about says it all as far as usefulness is concerned. And there the advantage is with Wikipedia. If the teacher sees a word-for-word copy of the article, you can simply claim that you wrote both. That doesn't quite work for the Britannica.

So when you are thinking about a response, remember to aim for usefulness, and hopefully you'll be accurate too. And remember, AskTom rankings are based on 'Not Useful' ... 'Most Useful' too.

A little blog rework

A while back Andrew Clarke referred to an article about the Top Ten Blog Mistakes. In light of its content, I've made a couple of small changes. Also, Blogger now has a trackback option, so I've enabled that.

1. No Author Biographies
I've got a author biography, and I'm happy with it at the moment. If you think you want to know more about me (no, not my credit card number) add a comment.

2. No Author Photo
I've added one, though its about four years old. I'll confess to a habit, when I'm burning CDs (and DVDs), of not wanting to waste space. So I tend to throw in any old digital camera images laying around on the grounds that any sort of backup copy is better than none (which is my normal backup strategy). This photo was on a CD I've carried round containing a bunch of handy Oracle documentation, SQL scripts, emergency applications....

3. Nondescript Posting Titles, 4. Links Don't Say Where They Go and
9. Forgetting That You Write for Your Future Boss
Those are ones to deal with for individual posts. However in regards to the latter, a blogger should never forget they are writing for their current and past bosses too. Confidentiality should be respected, and remember your workmates may be reading what you say about them....

5. Classic Hits are Buried and 6. The Calendar is the Only Navigation
Not sure I've got any 'Classic Hits' yet, but I've set up an link in my entry page to index my 'Oracle Tips and Tricks'. Now I've just got to remember to keep that page up to date. In future I may add one for 'Opinion'.

"Most weblog software provides a way to categorize postings so users can easily get a list of all postings on a certain topic."
Don't know of this in blogger/blogspot. If you do, I'd be interested in it.

7. Irregular Publishing Frequency
I disagree with this. Given the prevelance of RSS feeds I think people can keep up to date with a blog irrespective of how long between posts. At least I hope so. I had a month long gap when time was in short supply.

With blogger/blogspot, the main limit of RSS is that it doesn't cover comments. I do get email for all new comments and I'm going to make a new resolution to make more of an effort to acknowledge them.

8. Mixing Topics
"If you have the urge to speak out on, say, both American foreign policy and the business strategy of Internet telephony, establish two blogs. "
Not convinced on this. I subscribe to orablogs, and I can see that non-Oracle posts don't really belong there. I'll see if there's any real demand for a split. If so, I'll carve out a second blog (Igor's Rest Room ?). Also, it's nice to hear what is going on in other Oracle blogger's lives. Reminds us that, underneath it all, we are people first.

10. Having a Domain Name Owned by a Weblog Service
Not convinced on this one either. I can see a benefit in having your blog on your own hosted service, but there's a lot more admin hassle and cost there. The Domain name is really a separate issue, and while domain names are cheap I don't see the benefit in having a domain name that points to service I don't have real control over.

An intro to my Oracle Tips and Tricks

I expect this entry to undergo regular updating as I add in more Oracle Tips and Tricks.

Variants on the STRAGG (String Aggreagte) function

A new variant on stragg - the ordered aggregate
Analytics with ORDER BY and DISTINCT STRAGG

Performance Tuning

My query is slow.... What we need before we can help
Another Stats Extraction Routine
10053 - The Optimizer dump
Running DBMS_XPLAN against V$SQL_PLAN
Explain Plans and Scalar Subqueries
Views, DB Links and the CBO and Views, DB Links, the CBO and indexes

An Oracle Forms Ad-Hoc Query tool
Functions as datasources and NO_DATA_FOUND
Locating an error in a query run using Dynamic SQL
PL/SQL Garbage Collection - How memory used by variables is reclaimed
Recording the commit time on a record
When empty is not empty - Why a table with no rows can still be big

PL/SQL Garbage Collection

In another blog a question was raised about PL/SQL garbage collection, and how memory used, for example, by PL/SQL table is reused. I'm sure it's been covered elsewhere, but it never hurts to restate things so....

set serveroutput on size 1000000

declare
  procedure show_mem (p_text in varchar2) is
    cursor c_mem is 
     select a.name, b.value
     from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
     and a.name like '%pga %';
  begin
    dbms_output.put_line('---'||p_text||'---');
    for i in c_mem loop
      dbms_output.put_line(rpad(i.name,35)||':'||lpad(i.value,20));
    end loop;
    dbms_output.put_line('------');
  end show_mem;
begin
  show_mem('before');
  declare
    type tab_char is table of varchar2(2000) index by pls_integer;
    t_char tab_char;
  begin
    for i in 1..2000 loop
      t_char(i) := rpad('a',2000);
    end loop;
    show_mem('in scope 1');
  end;
  show_mem('out scope 1');
  declare
    type tab_char is table of varchar2(2000) index by pls_integer;
    t_char tab_char;
  begin
    for i in 1..2000 loop
      t_char(i) := rpad('a',2000);
    end loop;
    show_mem('in scope 2');
  end;
  show_mem('out scope 2');
end;
.
/

---before---
session pga memory                 :             1556052
session pga memory max             :             1556052
------
---in scope 1---
session pga memory                 :             7781972
session pga memory max             :             7781972
------
---out scope 1---
session pga memory                 :             7781972
session pga memory max             :             7913044
------
---in scope 2---
session pga memory                 :            14007892
session pga memory max             :            14007892
------
---out scope 2---
session pga memory                 :            14007892
session pga memory max             :            14138964
------

PL/SQL procedure successfully completed.

USER@xe> /
---before---
session pga memory                 :             1687124
session pga memory max             :            14138964
------
---in scope 1---
session pga memory                 :             7781972
session pga memory max             :            14138964
------
---out scope 1---
session pga memory                 :             7781972
session pga memory max             :            14138964
------
---in scope 2---
session pga memory                 :            14007892
session pga memory max             :            14138964
------
---out scope 2---
session pga memory                 :            14007892
session pga memory max             :            14138964
------

At the original 'before' point, I had a current value of about 1.5 million.
It jumped up to 8 million when the PL/SQL table was loaded.
It stayed there even when the PL/SQL table went out of scope.
It jumped again to over 14 million when a new PL/SQL was loaded, and again stayed there.
However at the start of a rerun, it returns to under 2 million before jumping again as in the original run.

As a slight variation, I used an explict named procedure rather than an anonymous PL/SQL block.

set serveroutput on size 1000000

declare
  procedure show_mem (p_text in varchar2) is
    cursor c_mem is 
     select a.name, b.value
     from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
     and a.name like '%pga %';
  begin
    dbms_output.put_line('---'||p_text||'---');
    for i in c_mem loop
      dbms_output.put_line(rpad(i.name,35)||':'||lpad(i.value,20));
    end loop;
    dbms_output.put_line('------');
  end show_mem;
  procedure set_tab (p_text in varchar2) is
    type tab_char is table of varchar2(2000) index by pls_integer;
    t_char tab_char;
  begin
    show_mem('before:'||p_text);
    for i in 1..2000 loop
      t_char(i) := rpad('a',2000);
    end loop;
    show_mem('after:'||p_text);
  end;
begin
  set_tab('1');
  set_tab('2');
  show_mem('end');
end;
.
/

---before:1---
session pga memory                 :             1752660
session pga memory max             :            14138964
------
---after:1---
session pga memory                 :             7847508
session pga memory max             :            14138964
------
---before:2---
session pga memory                 :             7847508
session pga memory max             :            14138964
------
---after:2---
session pga memory                 :             7847508
session pga memory max             :            14138964
------
---end---
session pga memory                 :             7847508
session pga memory max             :            14138964
------

This time, the memory used in the first execution of the named procedure is reused for the second, so there isn't that extra jump. This shows that there can be a benefit in putting a memory hungry process into a named procedure (or function).

As a final test, I tried

declare
  procedure show_mem (p_text in varchar2) is
    cursor c_mem is
     select a.name, b.value
     from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
     and a.name like '%pga %';
  begin
    dbms_output.put_line('---'||p_text||'---');
    for i in c_mem loop
      dbms_output.put_line(rpad(i.name,35)||':'||lpad(i.value,20));
    end loop;
    dbms_output.put_line('------');
  end show_mem;
begin
  show_mem('before');
  declare
    procedure set_tab (p_text in varchar2) is
      type tab_char is table of varchar2(2000) index by pls_integer;
      t_char tab_char;
    begin
      show_mem('before:'||p_text);
      for i in 1..2000 loop
        t_char(i) := rpad('a',2000);
      end loop;
      show_mem('after:'||p_text);
    end;
  begin
    show_mem('before scope 1');
    set_tab('point 1');
    show_mem('after scope 1');
  end;
  show_mem('out scope 1');
  declare
    type tab_char is table of varchar2(2000) index by pls_integer;
    t_char tab_char;
  begin
    for i in 1..2000 loop
      t_char(i) := rpad('a',2000);
    end loop;
    show_mem('in scope 2');
  end;
  show_mem('out scope 2');
end;
.
/

---before---
session pga memory                 :             1818196
session pga memory max             :            14138964
------
---before scope 1---
session pga memory                 :             1818196
session pga memory max             :            14138964
------
---before:point 1---
session pga memory                 :             1818196
session pga memory max             :            14138964
------
---after:point 1---
session pga memory                 :             7913044
session pga memory max             :            14138964
------
---after scope 1---
session pga memory                 :             7913044
session pga memory max             :            14138964
------
---out scope 1---
session pga memory                 :             7913044
session pga memory max             :            14138964
------
---in scope 2---
session pga memory                 :             7913044
session pga memory max             :            14138964
------
---out scope 2---
session pga memory                 :             7913044
session pga memory max             :            14138964
------

That demonstrates that, you can get the benefits of the memory cleanup and retain a single program flow (ie not skipping to an out of line procedure), by defining a named PL/SQL procedure within the anonymous PL/SQL block.
These tests were done in XE.

A new variant on stragg - the ordered aggregate

In a previous post, I included an example of a variant of Tom Kyte's STRAGG (string aggregate) function that ordered the elements alphabetically. Here I'm showing one where the elements can be ordered differently. Basically, for every string to be aggregated, you need to pass in the value itself and its order position. It doesn't test to see if that order position is already taken, so if you have two values in sort position 3, one of them will be lost. Also, the order position should be an integer.

Firstly, create the necessary types. The first two are pretty obvious, the third is the aggregate type:

create or replace type sort_element is object
   (sort_seq number, sort_value varchar2(2000))
.
/

create type tab_sort is table of sort_element
.
/

create or replace type str_ord_agg_type as object
(
   t_sort tab_sort,
   static function
        ODCIAggregateInitialize(sctx IN OUT str_ord_agg_type )
        return number,
   member function
        ODCIAggregateIterate(self IN OUT str_ord_agg_type ,
                             value IN sort_element )
        return number,
   member function
        ODCIAggregateTerminate(self IN str_ord_agg_type,
                               returnValue OUT  varchar2,
                               flags IN number)
        return number,
   member function
        ODCIAggregateMerge(self IN OUT str_ord_agg_type,
                           ctx2 IN str_ord_agg_type)
        return number,
   member function
        ODCIAggregateDelete(self IN OUT str_ord_agg_type,
                            value in sort_element)
        return number
)
.
/

Then the type body for the aggregation operation

create or replace type body str_ord_agg_type
is
--
static function ODCIAggregateInitialize
    (sctx IN OUT str_ord_agg_type)
return number
is
begin
    sctx := str_ord_agg_type( null );
    --Initialize the sortage part
    sctx.t_sort := tab_sort();
    return ODCIConst.Success;
end;
--
member function ODCIAggregateIterate
     (self IN OUT str_ord_agg_type,
      value IN sort_element )
return number
is
begin
    --Extend the array and add the new element.
    t_sort.extend;
    t_sort(t_sort.last) := value;
    return ODCIConst.Success;
end;
--
member function ODCIAggregateTerminate
          (self IN str_ord_agg_type,
           returnValue OUT varchar2,
           flags IN number)
return number
is
  type tab_char is table of varchar2(4000) index by pls_integer;
  t_char tab_char;
  v_ind number;
  v_out_line varchar2(4000);
begin
  --Store the elements in the array using the sort seq
  v_ind := t_sort.first;
  while v_ind is not null loop
    t_char(t_sort(v_ind).sort_seq) := t_sort(v_ind).sort_value;
    v_ind := t_sort.next(v_ind);
  end loop;
  --
  --Go through in sort seq order, moving elements to the output string
  v_ind := t_char.first;
  while v_ind is not null loop
    v_out_line := v_out_line||t_char(v_ind)||',';
    v_ind := t_char.next(v_ind);
  end loop;
  --Remove last comma
  v_ind := length(nvl(v_out_line,',')) -1;
  returnValue := (substr(v_out_line,1,v_ind));
  return ODCIConst.Success;
end;
--
member function ODCIAggregateDelete
      (self IN OUT str_ord_agg_type,
       value in sort_element)
return number
is
  v_point number;
begin
  v_point := t_sort.first;
  while v_point is not null loop
    if  t_sort(v_point).sort_value = value.sort_value
    and t_sort(v_point).sort_seq = value.sort_seq then
      t_sort.delete(v_point);
      return ODCIConst.Success;
    end if;
    v_point := t_sort.next(v_point);
  end loop;
  return ODCIConst.Success;
end;
--
member function ODCIAggregateMerge
         (self IN OUT str_ord_agg_type,
           ctx2 IN str_ord_agg_type)
return number
is
  v_point number;
begin
  v_point := ctx2.t_sort.first;
  while v_point is not null loop
    t_sort.extend;
    t_sort(t_sort.last) := ctx2.t_sort(v_point);
    v_point := ctx2.t_sort.next(v_point);
  end loop;
  return ODCIConst.Success;
end;
end;
.
/

And finally the function :

CREATE or replace
  FUNCTION strordagg(input sort_element )
  RETURN varchar2
  PARALLEL_ENABLE AGGREGATE USING str_ord_agg_type;
.
/

grant execute on strordagg to public;

select table_name, index_name,
       strordagg(sort_element(column_position, column_name)) sorted
from user_ind_columns
group by table_name, index_name
/

In 10G, we can use the COLLECT function using the same sort_element and tab_sort types, but with a much simpler function, though the select looks a little uglier.

create or replace function sorted_agg
    (p_tab_sort in tab_sort) return varchar2 is
  type tab_char is table of varchar2(4000) index by pls_integer;
  t_char tab_char;
  v_ind number;
  v_out_line varchar2(4000);
begin
  v_ind := p_tab_sort.first;
  while v_ind is not null loop
    t_char(p_tab_sort(v_ind).sort_seq) := p_tab_sort(v_ind).sort_value;
    v_ind := p_tab_sort.next(v_ind);
  end loop;
  --
  v_ind := t_char.first;
  while v_ind is not null loop
    v_out_line := v_out_line||t_char(v_ind)||',';
    v_ind := t_char.next(v_ind);
  end loop;
  --Remove last comma
  v_ind := length(v_out_line) -1;
  return(substr(v_out_line,1,v_ind));
end;
.
/

select index_name,
       sorted_agg(cast(collect(sort_element(column_position, column_name)) as tab_sort)) sorted
from all_ind_columns
group by index_name
/

PL/SQL warnings in 10GR2

It's been over a month since my last post. I'm going to have to try to improve on that.
I've got several ideas, but not much free time to flesh them out.

I have been playing around with a sandbox install of XE, my first exposure to 10GR2.
The first thing I noticed was that GROUP BY often (mostly ?) does NOT sort in the GROUPed order.
Yes, I know that without an ORDER BY it was never guaranteed, but it is a wake-up call, and I can start getting into better habits now.

Often though, while the order isn't necessary, it can be useful. For example, when processing clients are using DBMS_APPLICATION_INFO to record the 'current' one, when they are ordered you get a feel for how far through the job it is.
Now I'll have to think about whether it is worth doing the additional sort to keep that feeling.

Now with XE, I've turn the PLSQL compiler warnings on by default.
Hopefully something else to get me into good habits.

However, I've found one where the warning is incorrect. Anyone know any others ?

USER@xe> alter session set plsql_warnings='ENABLE:ALL';

Session altered.

USER@xe> create or replace FUNCTION tf_to_char (p_boolean IN BOOLEAN) RETURN VARCHAR2 IS
2  BEGIN
3    --Converts a boolean (true/false/null) to a string which can be used for output
4    IF p_boolean THEN
5      RETURN 'TRUE';
6    ELSIF NOT p_boolean THEN
7      RETURN 'FALSE';
8    END IF;
9    return 'NULL';
10  END tf_to_char;
11  .
USER@xe> /

SP2-0806: Function created with compilation warnings

USER@xe> show errors
Errors for FUNCTION TF_TO_CHAR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/9      PLW-06002: Unreachable code
USER@xe>

USER@xe> declare
2    v_test boolean;
3  begin
4    dbms_output.put_line(tf_to_char(v_test));
5    v_test:=true;
6    dbms_output.put_line(tf_to_char(v_test));
7    v_test := false;
8    dbms_output.put_line(tf_to_char(v_test));
9  end;
10  .
USER@xe> /
NULL
TRUE
FALSE

PL/SQL procedure successfully completed.