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?
This question is related to
oracle
plsql
ddl
privileges
select column_name, data_type || '(' || data_length || ')' as datatype
from all_tab_columns
where TABLE_NAME = upper('myTableName')
Oracle: Get a list of the full datatype in your table:
select data_type || '(' || data_length || ')'
from user_tab_columns where TABLE_NAME = 'YourTableName'
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)';
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!
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)
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'
To see the internal representation size in bytes you can use:
REGEXP_SUBSTR(DUMP(your_column_name), 'Len=(\d+)\:', 1, 1, 'c', 1 )
select t.data_type
from user_tab_columns t
where t.TABLE_NAME = 'xxx'
and t.COLUMN_NAME='aaa'
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).
Source: Stackoverflow.com