CODEWITHSUNDEEP
oracle-databaseplsqlddlprivileges
James
James

Reputation: 12806

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

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?

Upvotes: 78

Views: 331522

Answers (11)

Eric Leschinski
Eric Leschinski

Reputation: 153973

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: enter image description here

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

Upvotes: 14

Michał Barbarski
Michał Barbarski

Reputation: 3

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;
/

Upvotes: 0

PiC
PiC

Reputation: 143

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

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

Upvotes: 0

R Muruganandhan
R Muruganandhan

Reputation: 11

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)';

Upvotes: 1

Tagar
Tagar

Reputation: 14911

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).

Upvotes: 4

mattpltt
mattpltt

Reputation: 353

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

Upvotes: 12

Ibrahim Bayer
Ibrahim Bayer

Reputation: 151

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

Upvotes: 15

sev3ryn
sev3ryn

Reputation: 1087

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!

Upvotes: 22

Adam Musch
Adam Musch

Reputation: 13583

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)

Upvotes: 66

Lorin Davis
Lorin Davis

Reputation: 239

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'

Upvotes: 21

akf
akf

Reputation: 39485

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)

Upvotes: 54

Related Questions