Retrieve column name through db link in Oracle 11g

Question

I want to retrieve column names from a table through a db link but I'm not able to do it...

Although this query is working

SELECT *
FROM myTableName@myDbLink;

the following one is not:

SELECT column_name
FROM all_tab_columns@myDbLink
WHERE table_name = 'myTableName'

What's the correct way of retrieving the column names?

score 4 · Answer 1 · answered Mar 12 '18 at 09:15

4

CaSE mATterS.

In Oracle, table names are - by default - in UPPERCASE, so - try with

SELECT column_name
FROM all_tab_columns@myDbLink
WHERE table_name = 'MYTABLENAME'

answered Mar 12 '18 at 09:15

Littlefoot

if myTableName in the select works, why should 'MYTABLENAME' works? – JaviOverflow Mar 12 '18 at 09:42
2

@JaviOverflow : Please read https://stackoverflow.com/questions/7425153/reason-why-oracle-is-case-sensitive – Kaushik Nayak Mar 12 '18 at 09:44
That's really interesting! I didn't know that. However, when I use "myTableName" in my select query, it's still working, so I guess I'm using the same-case-sensitive name. – JaviOverflow Mar 12 '18 at 09:57
1

Mixed case is *evil*, avoid creating Oracle objects using mixed case. If you want to do that, you'll have to use double quotes. And if you do that, you'll have to use double quotes everywhere, always, whenever you reference that object, and *never* fail letter case. – Littlefoot Mar 12 '18 at 10:19

1 Answers1