select column_name,data_type from user_tab_cols where table_name = upper('VIEW_***')
如果是JAVA,可以用ResultSetMetaData获取。如果是存储过程,可以用DBMS_SQL获取。 参考例子:sqlplus '/as sysdba' SQL>SET SERVEROUTPUT ON 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; DBMS_SQL.PARSE(c, 'SELECT * FROM DBA_USERS', DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE(c);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);/* * Following loop could simply be for j in 1..col_cnt loop. * Here we are simply illustrating some of the PL/SQL table * features. */ 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; /
desc user还有,user是oracle的关键字,尽量不要在业务系统中使用它
select a.colname,b.[name] from sys.types b inner join ( select [name] as colName,system_type_id from sys.columns where [object_id]= (select [object_id] from sys.tables where [name]= 'user')) a on a.system_type_id = b.system_type_id
现在的问题是,有办法获取视图中各列的数据类型?
参考例子:sqlplus '/as sysdba'
SQL>SET SERVEROUTPUT ON
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; DBMS_SQL.PARSE(c, 'SELECT * FROM DBA_USERS', DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE(c);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
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;
/
select [name] as colName,system_type_id from sys.columns
where [object_id]= (select [object_id] from sys.tables
where [name]= 'user')) a
on a.system_type_id = b.system_type_id