How to SELECT a comma-separated list of column names for all tables in an Oracle schema
Recently I needed to perform a merge of two Oracle database schema. Both schema held the same tables, with the same columns, but due to the different ways that these schema had been created, there was no guarantee that the columns of a table in one schema were in the same order as the same columns in the same table in the other schema.
You'd maybe hope that when performing an INSERT INTO SELECT * FROM that Oracle would cleverly perform some wizardry to map column names explicitly by name. Sadly, this isn't the case, and it would be pretty catastrophic for the Amount column to end up in the VatAmount column of the merged schema.
So, in order to do this I needed to quickly generate INSERT statements that mapped the columns explicitly. Thankfully, the LISTAGG function in Oracle since 11gR2 allows us to do this in combination with the USER_TAB_COLS table that holds this data, with very few lines of code. Here's how to use SQL with an Oracle database schema to get a list of columns per table.
I've needed to do this quite a few times, but couldn't easily find a clear example online. Here's how to unmerge cells and fill values in Excel.
For the simple case where the range only contains one distinct value, it's trivial to copy the value to all cells. However, where there are many distinct values in the range, there are a couple more steps involved. This still only takes a few seconds to do.
If you want to keep just values and remove the formula, simply highlight the range again, copy, paste, and paste values only. E.g. Ctrl + C, Ctrl + V, Ctrl, V
A couple of examples
There are a lot of people posting their EDC on the internet nowadays. A lot of this content is posted through everydaycarry.com or the EDC subreddit, others have their own blogs. Here are a couple of posts I think are worth reading.
Matt is one of the founding developers of WordPress, and also founded Automattic, a platform that powers a lot of impactful web products including Wordpress itself. His impressive career history is worth a read. He’s into photography and is a professional developer, so I share a lot of his requirements in an EDC.
Matt’s 2016 EDC post is here.
The Urban Prepper
The Urban Prepper is a Seattle-based EDCer who focuses on emergency-situation urban preparedness. He puts an impressive amount effort into carrying a lot of gear in as little space as possible.
His YouTube channel is here.
In the previous post I said that my EDC motto was to carry the minimum set of efficient tools to prepare for any realistic situation. I’ll explain a bit more about what that means.
The minimum set: don’t carry it if it doesn’t serve a purpose. Don’t carry more than two of anything that does serve a purpose, and only carry more than one if one of them might break. This is known as “two is one, one is none” (which was ostensibly practiced by Abraham Lincoln, and some claim that the phrase originated from the U.S. Navy SEALs).
Efficient tools: if one tool can replace two, carry the one rather than the two. However, if that one tool can’t do both jobs effectively, it’s not a suitable replacement for the two. Whilst you can open boxes and cut paper both with a knife, knives can rip paper and damage your cutting surface. Knives aren’t as efficient as scissors at the job. An efficient tool in this case would be one that contains both a knife and scissors without adding much weight or bulk.
Any realistic situation: I will carry items that I don’t use every day, but only where they would efficiently serve a purpose in a realistic situation. Most of the time I’ll carry some basic first aid equipment, but only include anti-allergy medication in summer, as it won’t realistically be needed in winter. I’ll carry a mini-DisplayPort to VGA adapter to connect my laptop to a projector, because realistically I might need to give a presentation at work and they won’t be set up with DP or HDMI. Living in the UK, realistically I don’t need to carry sunglasses very often.
When thinking about what to pack for a trip, work or photography, this motto helps me to be prepared, and to minimise the risk of something going wrong that can't easily be sorted. If you’re prepared for every realistic situation, when something goes wrong it can be righted in a few minutes, rather than ruining the whole trip.
Tech, quotes and EDC.