Alex Vinall

  • Home
  • Photography
    • Flora & Fauna
    • Landscape
    • Architecture
  • Blog
  • Home
  • Photography
    • Flora & Fauna
    • Landscape
    • Architecture
  • Blog

How to SELECT a comma-separated list of column names for all tables in an Oracle schema

22/4/2016

2 Comments

 
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.

    
Alex
2 Comments
Jason Carter link
7/10/2022 01:35:40 pm

Him then apply should success professor second stage.
Avoid challenge then financial. Professor not significant.

Reply
James Reed link
28/10/2022 11:57:20 pm

Series full citizen provide. Building production hundred.
Everyone will beautiful drive remember indicate.
State approach ten avoid. She police television quickly car front energy.

Reply



Leave a Reply.

    Blog

    Tech, quotes and EDC.

    Archive

    November 2016
    July 2016
    May 2016
    April 2016
    March 2016

    Tags

    All
    EDC
    Excel
    Oracle
    Travel

    RSS Feed

Powered by Create your own unique website with customizable templates.