Posts

Showing posts from December, 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 :-)

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.