Maybe it’s because I use a Mac or maybe its just that text editors don’t like my lines lining up nicely. Whatever it is, it’s not unusual for my nicely aligned text strings to go thoroughly wonky when I try to paste it into a wiki.
Take this example:
SELECT d.database_name, d.server, LISTAGG(c.email,',') WITHIN GROUP (ORDER BY c.email) AS contacts, d.priority FROM pa_database d, pa_contact c WHERE d.database_id = c.database_id (+) GROUP BY d.database_name, d.server, d.priority ORDER BY 1,2,4;
The data looks great in sqlplus but as you can see below, it looks crap in a wiki 🙂
DATABASE SERVER CONTACTS P
——– ————— ———————————————————— –
ABCD oca29 email@example.com M
ABBD agrtdb12 firstname.lastname@example.org M
ABIB agddb email@example.com H
Pasting it into a different text editor or excel sometimes helps, but not always. I’ve found one technique that helps though:
In sqlplus define every column you are selecting and set them to ‘noprint‘ e.g.
col database_name noprint col server noprint col contacts noprint col priority noprint
If you run the query again you get lots of empty lines but you can just bring back the column you like by setting that column to print, e.g.
col server print
Run the query again and you’ll get back just the server column in the order you want. Next, set that column back to noprint, choose another column to print and select that column.
This way you are able to get the data you want one column at a time and in the right order. Paste it into the wiki (or more usually into a spreadsheet) and you’ll end up with nice pretty output in your wiki. Simples!
By the way, don’t worry about the ‘LISTAGG’ – this is a real world query whereas my example data is kept as simple as possible with only one contact per database. Sorry if that’s confusing things..