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

User · Answer

To see the internal representation size in bytes you can use   REGEXP SUBSTR DUMP your column name    Len   d       1  1   c   1

User · Answer

Quick and dirty way  e g  to see how data is stored in oracle   SQL gt  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

User · Answer

select t data type    from user tab columns t   where t TABLE NAME    xxx      and t COLUMN NAME  aaa

User · Answer

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  gt 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 gt 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

User · Answer

Oracle  Get a list of the full datatype in your table   select data type           data length         from user tab columns where TABLE NAME    YourTableName

User · Answer

select column name  data type           data length        as datatype from all tab columns  where TABLE NAME   upper  myTableName

User · Answer

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

User · Answer

ALL TAB COLUMNS should be queryable from PL SQL   DESC is a SQL Plus command   SQL gt  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

User · Answer

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

User · Answer

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   gt  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  gt  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

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

Examples related to oracle

Examples related to plsql

Examples related to ddl

Examples related to privileges