0

I am trying to select a table in Oracle redacting one column. This is the query that I tried, but Oracle does not like it.

SELECT (
    SELECT COLUMN_NAME
    FROM USER_TAB_COLS
    WHERE TABLE_NAME = 'LMNOP_USERS'
        AND COLUMN_NAME != 'USER_SECRET' )
FROM LMNOP_USERS;

FYI, the subquery does return the list of column names. Is there a way to write this query other than listing out all the columns?

Andrew
  • 467
  • 3
  • 7
  • 22
  • No, there's not. List all the columns. – Ben Dec 18 '13 at 22:39
  • Correct. You cannot dynamically select a column in Oracle (or any other database for that matter). If you really want to do this, you need to put the query into a string and execute the string. – Gordon Linoff Dec 18 '13 at 22:39

1 Answers1

0

You can run the query:

SELECT COLUMN_NAME||', '
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'LMNOP_USERS'
    AND COLUMN_NAME != 'USER_SECRET';

Then take the list of columns and put them after the select. Remember to delete the last comma. This saves typing.

You can use listagg() to put all the columns on one line, if you prefer.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Nice. but an additional note to the user :: in `Oracle 11g` it is `listagg`, it is `wm_concat` in 10g. Also, it fails when the concatenated string goes greater than 4000 characters in length. The remaining text simply gets truncated. – Maheswaran Ravisankar Dec 19 '13 at 00:17