How do I get column datatype in Oracle with PL-SQL with low privileges?

Question

I have "read only" access to a few tables in an Oracle database. I need to get schema information on some of the columns. I'd like to use something analogous to MS SQL's sp_help.

I see the table I'm interested in listed in this query:

SELECT * FROM ALL_TABLES

When I run this query, Oracle tells me "table not found in schema", and yes the parameters are correct.

SELECT 
DBMS_METADATA.GET_DDL('TABLE', 'ITEM_COMMIT_AGG', 'INTAMPS') AS DDL
FROM DUAL;

After using my Oracle universal translator 9000 I've surmised this doesn't work because I don't have sufficient privileges. Given my constraints how can I get the datatype and data length of a column on a table I have read access to with a PL-SQL statement?

Adam Musch · Accepted Answer · 2013-06-14T14:18:11.310

ALL_TAB_COLUMNS should be queryable from PL/SQL. DESC is a SQL*Plus command.

SQL> desc all_tab_columns;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)

@leanne You use this Table like this: `SELECT * FROM user_tab_columns WHERE table_name = 'FILL_IN_THE_TABLE_NAME' AND column_name = 'FILL_IN_THE_COLUMN_NAME';` — Tenzin, Oct 01 '15 at 12:45
Yep, @Tenzin - multiple examples such as yours were provided here a couple of years ago, including the edit to this very post. If you click the 'edited ...' line in this answer, you'll see what Adam added to explain better. (Thanks, Adam!) — leanne, Oct 02 '15 at 19:42

score 49 · Answer 2 · answered Feb 26 '10 at 02:07

49

You can use the desc command.

desc MY_TABLE

This will give you the column names, whether null is valid, and the datatype (and length if applicable)

answered Feb 26 '10 at 02:07

akf

38,619
8
86
96

sev3ryn · Answer 3 · 2013-07-09T09:45:19.613

22

The best solution that I've found for such case is

select column_name, data_type||
case
when data_precision is not null and nvl(data_scale,0)>0 then '('||data_precision||','||data_scale||')'
when data_precision is not null and nvl(data_scale,0)=0 then '('||data_precision||')'
when data_precision is null and data_scale is not null then '(*,'||data_scale||')'
when char_length>0 then '('||char_length|| case char_used 
                                                         when 'B' then ' Byte'
                                                         when 'C' then ' Char'
                                                         else null 
                                           end||')'
end||decode(nullable, 'N', ' NOT NULL')
from user_tab_columns
where table_name = 'TABLE_NAME'
and column_name = 'COLUMN_NAME';

@Aaron Stainback, thank you for correction!

edited Jul 09 '13 at 09:45

answered Jun 19 '13 at 14:50

sev3ryn

1,055
1
9
17

1

You need to add in CHAR_USED so you can tell if it was defined as byte or char in something like a varchar2 – Aaron Stainback Jun 19 '13 at 21:55
this is good code, but in case of INT field it returns "NUMBER(*,0)" It needs some correction – Firas Nizam Jan 13 '14 at 07:00
@FirasNizam try it - maybe I don't understand you correctly but when data_scale is 0 it returns NUMBER(*), when it is != 0 then it returns NUMBER(*,data_scale) – sev3ryn Jan 14 '14 at 15:34
That is completely what I have searched for. The separation in the cases are very important to get a correct type definition. Thank you for this! – Michael Baarz Feb 19 '19 at 12:12

score 20 · Answer 4 · edited Nov 07 '12 at 19:17

20

Note: if you are trying to get this information for tables that are in a different SCHEMA use the all_tab_columns view, we have this problem as our Applications use a different SCHEMA for security purposes.

use the following:

EG:

SELECT
    data_length 
FROM
    all_tab_columns 
WHERE
    upper(table_name) = 'MY_TABLE_NAME' AND upper(column_name) = 'MY_COL_NAME'

edited Nov 07 '12 at 19:17

andrewsi

10,807
132
35
51

answered Nov 07 '12 at 18:59

Lorin Davis

229
2
4

The data_length is not important at all. The correct length definitions are defined with precision and scale. – Michael Baarz Feb 19 '19 at 12:11

score 15 · Answer 5 · edited Feb 06 '14 at 21:34

15

select t.data_type 
  from user_tab_columns t 
 where t.TABLE_NAME = 'xxx' 
   and t.COLUMN_NAME='aaa'

edited Feb 06 '14 at 21:34

Doug Porter

7,721
4
40
55

answered Mar 04 '10 at 06:38

Ibrahim Bayer

151
2

Nice, for tables that are outside the current schema `user_tab_columns` can be replaced with `all_tab_columns` – AlexanderD Jun 23 '16 at 14:01

Eric Leschinski · Answer 6 · 2022-03-16T16:56:59.913

14

Oracle 11.2: Get a list of the full datatype in your table:

create table SOMETABLE (foo integer, bar varchar(300));
select data_type || '(' || data_length || ')' thetype
from user_tab_columns where TABLE_NAME = 'SOMETABLE';

Prints:

NUMBER(22)
VARCHAR(300)

Screenshot:

Documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4462.htm#REFRN26277

edited Mar 16 '22 at 16:56

answered Jul 18 '11 at 20:05

Eric Leschinski

146,994
96
417
335

score 10 · Answer 7 · answered Aug 20 '14 at 10:58

10

select column_name, data_type || '(' || data_length || ')' as datatype
from all_tab_columns 
where TABLE_NAME = upper('myTableName')

answered Aug 20 '14 at 10:58

mattpltt

333
1
4
18

score 3 · Answer 8 · answered Sep 22 '15 at 19:05

Quick and dirty way (e.g. to see how data is stored in oracle)

SQL> select dump(dummy) dump_dummy, dummy
     , dump(10) dump_ten
from dual

DUMP_DUMMY       DUMMY DUMP_TEN            
---------------- ----- --------------------
Typ=1 Len=1: 88  X     Typ=2 Len=2: 193,11 
1 row selected.

will show that dummy column in table sys.dual has typ=1 (varchar2), while 10 is Typ=2 (number).

score 1 · Answer 9 · edited Mar 02 '19 at 05:39

You can try this.

SELECT *
  FROM (SELECT column_name,
               data_type,
               data_type
               || CASE
                     WHEN data_precision IS NOT NULL
                          AND NVL (data_scale, 0) > 0
                     THEN
                        '(' || data_precision || ',' || data_scale || ')'
                     WHEN data_precision IS NOT NULL
                          AND NVL (data_scale, 0) = 0
                     THEN
                        '(' || data_precision || ')'
                     WHEN data_precision IS NULL AND data_scale IS NOT NULL
                     THEN
                        '(*,' || data_scale || ')'
                     WHEN char_length > 0
                     THEN
                        '(' || char_length
                        || CASE char_used
                              WHEN 'B' THEN ' Byte'
                              WHEN 'C' THEN ' Char'
                              ELSE NULL
                           END
                        || ')'
                  END
               || DECODE (nullable, 'N', ' NOT NULL')
                  DataTypeWithLength
          FROM user_tab_columns
         WHERE table_name = 'CONTRACT')
 WHERE DataTypeWithLength = 'CHAR(1 Byte)';

Welcome to stackoverflow. In addition to your code it is a good idea to also add a short text on why your answer is correct and inputs for the OP to correct whatever mistake they are facing. — Sashi, Mar 02 '19 at 05:23

score 0 · Answer 10 · answered Nov 14 '19 at 15:41

0

To see the internal representation size in bytes you can use:

REGEXP_SUBSTR(DUMP(your_column_name), 'Len=(\d+)\:', 1, 1, 'c', 1 )

answered Nov 14 '19 at 15:41

PiC

137
10

There is a vsize function for that. It returns internal representation size of an expression. – Anton Zaviriukhin Jun 17 '22 at 13:20

score 0 · Answer 11 · answered Jul 28 '21 at 09:11

DECLARE
  c           NUMBER;
  d           NUMBER;
  col_cnt     INTEGER;
  f           BOOLEAN;
  rec_tab     DBMS_SQL.DESC_TAB;
  col_num    NUMBER;

  PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
  BEGIN
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('col_type            =    '
                         || rec.col_type);
    DBMS_OUTPUT.PUT_LINE('col_maxlen          =    '
                         || rec.col_max_len);
    DBMS_OUTPUT.PUT_LINE('col_name            =    '
                         || rec.col_name);
    DBMS_OUTPUT.PUT_LINE('col_name_len        =    '
                         || rec.col_name_len);
    DBMS_OUTPUT.PUT_LINE('col_schema_name     =    '
                         || rec.col_schema_name);
    DBMS_OUTPUT.PUT_LINE('col_schema_name_len =    '
                         || rec.col_schema_name_len);
    DBMS_OUTPUT.PUT_LINE('col_precision       =    '
                         || rec.col_precision);
    DBMS_OUTPUT.PUT_LINE('col_scale           =    '
                         || rec.col_scale);
    DBMS_OUTPUT.PUT('col_null_ok         =    ');
    IF (rec.col_null_ok) THEN
      DBMS_OUTPUT.PUT_LINE('true');
    ELSE
      DBMS_OUTPUT.PUT_LINE('false');
    END IF;
  END;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;

-- YOUR SELECT HERE
  DBMS_SQL.PARSE(c, '

SELECT *
FROM table1 a
     bable2 b
     table3 c
where a.id = b.id
  and b.id2 = c.id

  ', DBMS_SQL.NATIVE);

  d := DBMS_SQL.EXECUTE(c);

  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

  col_num := rec_tab.first;
  IF (col_num IS NOT NULL) THEN
    LOOP
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);
      EXIT WHEN (col_num IS NULL);
    END LOOP;
  END IF;

  DBMS_SQL.CLOSE_CURSOR(c);
END;
/

How do I get column datatype in Oracle with PL-SQL with low privileges?

11 Answers11

Linked

Related