How do you access or SELECT Scale and Precision of a Data

im simply trying to print out a report from SQL Developer. When i SELECT the Data_Type i get the data type as expected, but I want the Scale and Precision also right beside it...for example

CHAR(3, 0) NUMBER(7, 0)

Right now im only getting the data type itself like:

CHAR NUMBER

What syntax am I missing to access or SELECT the Scale and Precision please?

CodePudding user response：

If you are "selecting" the data type, I assume you are "selecting" from something like all_tab_columns. Right? Like this:

select column_name, data_type
from   all_tab_columns
where  owner = 'SCOTT' and table_name = 'EMP'
;

COLUMN_NAME  DATA_TYPE   
------------ ------------
EMPNO        NUMBER      
ENAME        VARCHAR2    
JOB          VARCHAR2    
MGR          NUMBER      
HIREDATE     DATE        
SAL          NUMBER      
COMM         NUMBER      
DEPTNO       NUMBER

Now, you want to know the maximum length for VARCHAR2 columns, and the precision and scale for NUMBER, right?

Add more columns to the select. Like so:

select column_name, data_type, char_col_decl_length, data_precision, data_scale
from   all_tab_columns
where  owner = 'SCOTT' and table_name = 'EMP'
;

COLUMN_NAME  DATA_TYPE    CHAR_COL_DECL_LENGTH DATA_PRECISION DATA_SCALE
------------ ------------ -------------------- -------------- ----------
EMPNO        NUMBER                                         4          0
ENAME        VARCHAR2                       10                          
JOB          VARCHAR2                        9                          
MGR          NUMBER                                         4          0
HIREDATE     DATE                                                       
SAL          NUMBER                                         7          2
COMM         NUMBER                                         7          2
DEPTNO       NUMBER                                         2          0

Alternatively, in SQL*Plus (or other interfaces that implement its functionality) you can use the SQL*Plus command describe:

describe scott.emp

Name     Null? Type         
-------- ----- ------------ 
EMPNO          NUMBER(4)    
ENAME          VARCHAR2(10) 
JOB            VARCHAR2(9)  
MGR            NUMBER(4)    
HIREDATE       DATE         
SAL            NUMBER(7,2)  
COMM           NUMBER(7,2)  
DEPTNO         NUMBER(2)

Very likely, SQL*Plus implements its own describe command with a select query just like the one I shared earlier.

CodePudding user response：

Why not just describe the table using

   Desc tablename

  Describe tablename