Get data type of field in select statement in ORACLE
You can query the all_tab_columns view in the database.
SELECT table_name, column_name, data_type, data_length FROM all_tab_columns where table_name = 'CUSTOMER'
How do I get column datatype in Oracle with PL-SQL with low privileges?
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)
How can I find out the field type of my query
This can be achieved using the utilities available in DBMS_SQL package. You don't have to create a table. Use any query you wish to describe as the argument to this generic procedure.
Procedure
CREATE OR REPLACE PROCEDURE desc_query (p_query VARCHAR2) AS
v_static_rc SYS_REFCURSOR;
v_desc DBMS_SQL.DESC_TAB;
v_cols BINARY_INTEGER;
v_cursor BINARY_INTEGER;
v_datatype VARCHAR2(40) ;
BEGIN
OPEN v_static_rc FOR p_query;
v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(v_static_rc);
DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);
DBMS_SQL.CLOSE_CURSOR(v_cursor);
dbms_output.put_line(rpad('COL',3)
|| ' ' || RPAD('COLUMN_NAME',15) ||'DATA TYPE' );
FOR i IN 1 .. v_cols LOOP
DBMS_OUTPUT.PUT( rpad(i,3) || ' ' || RPAD(v_desc(i).col_name,15));
select
decode(v_desc(i).col_type, 1,
decode(v_desc(i).col_charsetform, 2, 'NVARCHAR2',
'VARCHAR2'),
2, decode(v_desc(i).col_scale, null,
decode(v_desc(i).col_precision, null,
'NUMBER', 'FLOAT'), 'NUMBER'),
8, 'LONG',
9, decode(v_desc(i).col_charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE',
23, 'RAW', 24, 'LONG RAW',
69, 'ROWID',
96, decode(v_desc(i).col_charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, 'REF',
112, decode(v_desc(i).col_charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'USER_TYPE',
122, 'USER_TYPE',
123, 'USER_TYPE',
178, 'TIME(' ||v_desc(i).col_scale|| ')',
179, 'TIME(' ||v_desc(i).col_scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||v_desc(i).col_scale|| ')',
181, 'TIMESTAMP(' ||v_desc(i).col_scale|| ')'||' WITH TIME ZONE',
231, 'TIMESTAMP(' ||v_desc(i).col_scale|| ')'||' WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR(' ||v_desc(i).col_precision||') TO MONTH',
183, 'INTERVAL DAY(' ||v_desc(i).col_precision||') TO SECOND(' ||
v_desc(i).col_scale || ')',
208, 'UROWID',
'UNDEFINED') type_name into v_datatype from dual;
dbms_output.put_line(v_datatype);
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR OCCURED: '|| SQLERRM);
END;
/
Now, describe any query by executing the procedure like this (or using EXEC ) :
BEGIN
desc_query('SELECT 1 as id, CAST ( ''STR'' as VARCHAR2(10) ) as str ,
SYSTIMESTAMP as tstamp,trunc (SYSTIMESTAMP) as trunc_tstamp
from dual'
);
END;
/
Result
COL COLUMN_NAME DATA TYPE
1 ID NUMBER
2 STR VARCHAR2
3 TSTAMP TIMESTAMP(6) WITH TIME ZONE
4 TRUNC_TSTAMP DATE
DEMO
How to select a LONG column in Case Select Query
As proposed here you may workaround using a global temporary table
First insert the relevant part (record) of your table in the temporary table. Than you can select from it as required, because both columns in teh temporary table have same type - CLOB.
You need not to delete from the temp. table, a COMMIT will do it.
Example
create table tab
(short_text varchar2(10),
long_text LONG,
datatype_id number);
insert into tab values ('xxx','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 1);
insert into tab values ('yyy','yyyxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 2);
This fails:
select datatype_id,
case when datatype_id=1 then long_text
else short_text end as txt from tab;
ORA-00932: inconsistent datatypes: expected LONG got CHAR
But copying data in temporary table works
CREATE GLOBAL TEMPORARY TABLE t2
(short_text CLOB,
long_text CLOB,
datatype_id number)
ON COMMIT DELETE ROWS;
insert into t2 (short_text, LONG_TEXT, datatype_id )
select short_text, to_lob(LONG_TEXT), datatype_id from tab;
select datatype_id,
case when datatype_id=1 then long_text
else short_text end as txt from t2;
DATATYPE_ID TXT
----------- --------------------------------------------------------------------------------
1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
2 yyy
Oracle: Is there a way to get the column data types for a view?
You can use user_tab_columns (or all_tab_columns and dba_tab_columns respectively) regardless if table_name refers to a view or a table.
Get column name, data type, size and comments of Oracle table
You need to add column name connection:
SELECT all_tab.column_name,
all_tab.data_type,
all_tab.data_length,
(SELECT COMMENTS
FROM user_col_comments t
where t.TABLE_NAME = all_tab.TABLE_NAME
and t.COLUMN_NAME = all_tab.column_name)
FROM all_tab_columns all_tab
WHERE all_tab.TABLE_NAME = 'MY_TABLE'
Oracle Object : How to show all the fields in select query?
I don't use SQL Developer, but according to this article Showing TYPE’d Column Values in SQL Developer you could use option:
Preferences / Database / Advanced / Display Struct Value in Grid
Also you can query user_type_attr (or all_type_attr) to obtain column names. Then copy/paste select part from output and run it or create view as proposed by @sep. Here is my test data and code block:
insert into mytable values (person_typ(1, 'Paulina', 'Thomson'), date '2017-12-17');
insert into mytable values (person_typ(7, 'Keanu', 'Stevens'), date '2017-12-28');
declare
v_sql varchar2(32000);
begin
select listagg('T.CONTACT.'||attr_name||' '||attr_name, ', ')
within group (order by attr_no)
into v_sql
from user_type_attrs
where type_name = 'PERSON_TYP';
v_sql := 'SELECT '||v_sql||' FROM MYTABLE T';
dbms_output.put_line(v_sql);
execute immediate 'CREATE OR REPLACE VIEW VW_CONTACTS AS '||v_sql;
end;
select * from vw_contacts;
Result:
ID FIRST_NAME LAST_NAME
------ -------------------- -------------------------
1 Paulina Thomson
7 Keanu Stevens
How can I get column names from a table in Oracle?
You can query the USER_TAB_COLUMNS table for table column metadata.
SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'
SQL statement to get column type
Using SQL Server:
SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'yourSchemaName' AND
TABLE_NAME = 'yourTableName' AND
COLUMN_NAME = 'yourColumnName'
Related Topics
How to Update Ms Access Database Table Using Update and Sum() Function
Join Tables on Columns of Composite Foreign/Primary Key in a Query
In MySQL, How to Copy the Content of One Table to Another Table Within the Same Database
MySQL Create Time and Update Time Timestamp
Postgresql - View Schema Privileges
Calculating Percentile Rankings in Ms SQL
How to Find the Last Modified Date, Modified User of an Stored Procedure in SQL Server 2008
Is It a Bad Idea to Use Guids as Primary Keys in Ms SQL
Rollback Event Triggers in Postgresql
Replacing Sequence with Random Number
T-Sql: Separate String into Multiple Columns
Activerecord: List Columns in Table from Console
What Is the Easiest Way to Update an Image Field with the Content of a File