Problem with getting the size of a column with SQL-Developer

Question

I am trying to write a query that returns the size of a column in a particular table in my database

I am using SQL Developer environment and I am new to it

I have tried this simple code to which I applied what I found in the Internet :

CREATE TABLE tab1 (
       col1      VARCHAR2(15) PRIMARY KEY,
       col2      Number(4) NOT NULL)

select data_type, data_length 
  from user_tab_columns
 where table_name = 'tab1'
   and column_name = 'col1';

I expect for my second query to get this: VARCHAR2 and 15

But I get this :

no data found

Do you have any idea of what I'm doing wrong?

Try 'TAB1' and 'COL1' (using capital letters). Unless table and/or column names are given in double-quotes (which is a bad practice), names are case insensitive. However, when written to the dictionary tables, they are written in upper case letters, and that's how you must use them when you query the dictionary tables. — user5683823
– user5683823, Commented Jun 8, 2019 at 14:30

Lukasz Szozda · Accepted Answer · 2019-06-08 14:49:18Z

3

You should use uppercase version of identifiers:

select data_type, data_length 
  from user_tab_columns
 where table_name = UPPER('tab1')   -- 'TAB1'
   and column_name = UPPER('col1'); -- 'COL1'

db<>fidde demo

Schema Object Names and Qualifiers

You can use either quoted or nonquoted identifiers to name any database object. However, database names, global database names, and database link names are always case insensitive and are stored as uppercase. If you specify such names as quoted identifiers, then the quotation marks are silently ignored.

edited Jun 8, 2019 at 14:49

answered Jun 8, 2019 at 14:43

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5683823 Over a year ago

The use of upper() around hard-coded values seems excessive - why not simply write 'TAB1' and 'COL1'? Is that how you query catalog tables?

Lukasz Szozda Over a year ago

@mathguy Matter of taste. It could be uppercased string literal as well.

tinakoua nadia · Accepted Answer · 2019-06-11 08:18:15Z

0

Amazing it worked!! Thank you all ! See you soon for the next question :D

answered Jun 11, 2019 at 8:18

tinakoua nadia

212 silver badges4 bronze badges

Collectives™ on Stack Overflow

Problem with getting the size of a column with SQL-Developer

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related