Dave Webster's Blog

Tinkering with Oracle

Archive for the ‘tips and tricks’ Category

Concatenating strings from multiple rows and an insight into XML in the DB

Posted by anotherdavewebster on August 30, 2010

This little trick came my way courtesy of a colleague of mine named Matt Cobley. Over the years, I’ve frequently needed to produce a comma delimited list of strings from multiple rows of a query. This is easy enough if you know how many rows you have but if not, I always end up writing a little piece of PL/SQL containing a cursor which builds a string which I then output to dbms_output. All rather long winded and more than a little limiting. That was until Matt sent an eMail (on a subject I no longer recall) containing the little gem XMLAGG which works something like this:

SELECT RTRIM( XMLAGG( XMLELEMENT( any_old_name, table_name || ',' ) ORDER BY last_analyzed DESC ).EXTRACT( '//text()' )
            , ','
            )
  FROM dba_tables
 WHERE ROWNUM < 20 AND owner = 'SYS'

Now I don’t suppose for a minute that it’s cheap but it really is very easy and it provides us with a nice clean:
“CLU$,TAB$,IND$,COL$,UNDO$,USER$,TSQ$,FILE$,VIEWTRCOL$,TYPE_MISC$,SUBCOLTYPE$,REFCON$,OPQTYPE$,NTAB$,LOB$,LIBRARY$,ICOL$,COLTYPE$,ATTRCOL$”.

I made a mental note at the time to have a dig around the XML stuff in Oracle as I’m sure there are lots of other treats in there. I sorry to say that I’ve not yet got started.

UPDATE: A couple of you have kindly pointed out that 11r2 contains LISTAGG, an extension to the wonderful analytics functionality, which does the same job in a more elegant way.

Posted in tips and tricks | Leave a Comment »

n rows please

Posted by anotherdavewebster on August 9, 2010

Really nice and easy one to get us started. Say you want to generate ten thousand rows of data. The way I normally see this kind of thing done is by selecting values from dba_objects where rownum <= 10000. There’s nothing wrong with this approach of course but what if you need more rows than you have objects in the database? You can do some sort of cartesian product thing but even suggesting that is making me feel a little unclean. I prefer:

SELECT NULL
  FROM DUAL
CONNECT BY ROWNUM <= 10000;

It’s simple, unlimited and even a little cheaper to boot.

The first time I actually needed this, I was trying to generate all of the seconds between two times:

SELECT to_date('01/08/2010 14:26:09','DD/MM/YYYY HH24:MI:SS') /* low */ + ( rownum / 86400 ) the_date
  FROM DUAL
CONNECT BY ROWNUM <= ( to_date('01/08/2010 14:28:29','DD/MM/YYYY HH24:MI:SS') /* high */
                     - to_date('01/08/2010 14:26:09','DD/MM/YYYY HH24:MI:SS') /* low  */ ) * 86400

First one down. ┬áThat wasn’t too hard was it?

Posted in tips and tricks | Leave a Comment »