NOPRINT in sqlplus

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         fred@sanger.ac.uk                          M
ABBD     agrtdb12     barney@sanger.ac.uk                          M
ABIB     agddb         barney@sanger.ac.uk                          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..

Advertisements

About dnadba

I work with databases and stuff
This entry was posted in Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s