CODEWITHSUNDEEP
pythonsqloracle-database
Krish Kk
Krish Kk

Reputation: 21

Is there a function which will take schema name and table name and return all the column names in the table

I am trying to use python to download a table from oracle database using CX_Oracle module

This below command is executed in a notebook along with the credential

temp = list()
for row in c.execute('SELECT * FROM dbname.tablename'):
        temp.append(row)
df = pd.DataFrame(temp)

I am able to get all the rows into the variable temp but. I also would like to get the column names as well.

So my approach was to get the column name separately and add them to the above df.

For a given schema name and a table name, I want to find the column names of that table in Oracle.

"This doesn't return anything so far but the column exsist ' 

select table_name
from all_tab_columns 
where column_name = 'modby'

Thanks in advance for your assistance

Upvotes: 0

Views: 405

Answers (2)

Popeye
Popeye

Reputation: 35910

You need to use upper or lower on both sides of comparison as Oracle DB behave case-sensitivity differently. object names (table, column, etc) are stored case-sensitively if they are wrapped in " while creating else oracle DB converts all the object names without double quotes into the uppercase name.

Try the following query:

select table_name
from user_tab_columns 
where lower(column_name) = 'modby'

If you want all column names of the table then try the following query:

select column_name
from user_tab_columns 
where lower(table_name) = lower('<your_table_name>');

Cheers!!

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142743

By default, Oracle stores object and column names in uppercase, so try

select table_name
from all_tab_columns 
where column_name = 'MODBY'          --> instead of 'modby'

Upvotes: 2

Related Questions