Tuesday, December 7, 2010

Concatenate rows in Oracle

Recently, I had to concatenate and put in one line (comma separated) the results of a query in Oracle. After one version using replace, xmlagg and xmlelement which had several problems and some reading online I compiled the following code:

select
rtrim(dbms_xmlgen.convert(xmlagg(xmlelement(e,
mycolumn || ', ')).extract('//text()').getClobVal(), 1),
', ')

from mytable

The extract/getClobVal trick came from "ORA-19011 Character string buffer too small when used with XMLAGG function":
http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/ora19011-character-string-buffer-too-small-when-used-with-xmlagg-function-3882635

The dbms_xmlgen.convert trick came from "HOWTO: Avoid Conversion of Escape Codes":
http://www.liberidu.com/blog/?p=635

Enjoy :-)

Friday, December 3, 2010

Little batch file for StatSVN

StatSVN retrieves information from a Subversion repository and generates various tables and charts describing the project development. You can get the software this page:
http://www.statsvn.org/downloads.html

Inside the archive (statsvn-0.7.0.zip) you have a readme.txt and a statsvn.jar . It would've been nice to be able to run StatSVN directly so I played a bit to create this bat file:


set home=%cd%
set /p project=Project folder:

set report=report


cd /d %project%

del svn.log

svn log --xml -v > svn.log

cd /d %home%

rd /s /q %report%

java -jar statsvn.jar -output-dir %report% %project%\svn.log
%project%

del %project%\svn.log

%report%\index.html


Just create the bat file in the folder where you extracted StatSVN, run it, paste your project's folder path and wait for the magic to happen. The report will be generated in a folder named report and the report's index.html should be opened in your browser.