oracle 系统视图中使用了大量的 long 类型,虽然oracle自己也不建议使用long类型。
修改了tom的脚本,来支持 view 和 table :
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839298816582
create or replace function getlong( p_tname in varchar2,p_cname in varchar2,p_con in varchar2, p_value in varchar2 )
return varchar2
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(4000);
l_long_len number;
l_buflen number := 4000;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor, 'select ' || p_cname || ' from ' || p_tname ||' where '||p_con||' = :x', dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_value );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0) then
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , l_long_val, l_long_len );
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end getlong;
/
--支持view
SELECT getlong('USER_CONSTRAINTS','SEARCH_CONDITION','CONSTRAINT_NAME',CONSTRAINT_NAME) , tt1.* FROM user_constraints tt1 WHERE tt1.table_name IN ('STOGLD') ;
--支持table
create table t ( x long );
declare
data long default rpad( '*', 32000, '*' );
begin
insert into t values ( data );
end;
/
select getlong('T', 'X','rowid', rowid) from t;