读取long类型数据

?

1 2 3 4 5 6 7 8 9

CREATE OR REPLACE FUNCTION LONG_TO_CHAR(uTABLE IN VARCHAR2,uID IN NUMBER) RETURN VARCHAR2 AS   uVal VARCHAR2(32767); BEGIN   SELECT DATA_DEFAULT INTO uVal FROM USER_TAB_COLS WHERE TABLE_NAME = UPPER(uTABLE) AND COLUMN_ID = uID;   uVal := SUBSTR(uVal,1,50);   RETURN uVal;  END LONG_TO_CHAR;

　　创建了以后，就可以调用之了

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

--在第一行和最后一行改表名             查询表结构 SELECT 'TABLEA'  as 表名, A.COLUMN_NAME as 字段,                            case                              when A.DATA_TYPE = 'NUMBER' and A.DATA_PRECISION IS NULL AND a.DATA_SCALE = 0 then                               'INTEGER'                              when A.DATA_TYPE = 'TIMESTAMP(6)'then                               'TIMESTAMP'                              else                                A.DATA_TYPE                            END as 数据类型,                            case                              when A.DATA_TYPE in ('CHAR','NCHAR') then                               to_char(A.CHAR_COL_DECL_LENGTH)                              when A.DATA_TYPE in ('VARCHAR2','NVARCHAR2'，'RAW','UROWID') then                               to_char(A.CHAR_LENGTH)                              when A.DATA_TYPE = 'FLOAT' then                               to_char(A.DATA_PRECISION)                              when A.DATA_TYPE = 'NUMBER' and A.DATA_PRECISION IS NULL AND a.DATA_SCALE = 0 then                               ''                              when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE = 0 then                               to_char(A.DATA_PRECISION)                              when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE != 0 then                               to_char(A.DATA_PRECISION || ',' || A.DATA_SCALE)                              when A.DATA_TYPE = 'TIMESTAMP(6)' then                               '6'                               ELSE                                ''                            END as 长度,                            decode(A.NULLABLE,'Y',null,'N','N') AS 可为空,                            LONG_TO_CHAR(A.TABLE_NAME,A.COLUMN_ID) as 默认值,                            B.comments as 备注                                                                                 from sys.user_tab_cols A                       left join sys.user_col_comments B on A.TABLE_NAME = B.table_name                        and A.COLUMN_NAME = B.column_name                                   where A.TABLE_NAME = 'TABLEA'                                 order by A.TABLE_NAME, A.COLUMN_ID

　　

 

 

一段时间后，又发现了新问题，我想获得user_constraints表中的search_condition信息，但这是视图中的一个字段，不能使用上面调用的方法

于是重新写了一个

 

create or replace function search_cond( p_cons_name in varchar2 ) return varchar2
as
l_search_condition varchar2(4000);
begin
select SEARCH_CONDITION into l_search_condition
from user_constraints
where constraint_name = p_cons_name;

return l_search_condition;
end;