How to return a long columns size

最新推荐文章于 2024-02-01 20:14:02 发布

jiawancai

最新推荐文章于 2024-02-01 20:14:02 发布

阅读量685

点赞数

分类专栏： oracle日记文章标签： buffer integer query function

oracle日记专栏收录该内容

43 篇文章

订阅专栏

本文介绍了一个PL/SQL函数getLongsize，该函数用于查询并返回Oracle数据库中长字段的实际大小。通过使用dbms_sql包，此函数能够有效处理长字段的数据检索，避免了因字段过长而引发的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace function getLongsize (p_query varchar2) return number as
v_cursor         integer default dbms_sql.open_cursor;
i                number;
v_temp           varchar2(250);
v_returned_size number;
v_buffer_size    number := 250;
v_marker         number := 0;
begin
dbms_sql.parse( v_cursor, p_query, dbms_sql.native );
dbms_sql.define_column_long(v_cursor, 1);
i := dbms_sql.execute(v_cursor);
if (dbms_sql.fetch_rows(v_cursor)>0) then
    loop
      dbms_sql.column_value_long(v_cursor, 1, v_buffer_size, v_marker , v_temp, v_returned_size );
      v_marker := v_marker + v_returned_size;
      exit when v_returned_size = 0;
    end loop;
end if;
return v_marker;
end;

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

select owner, view_name,
getLongsize ('select text from dba_views where owner=''SYS'' and view_name=''ALL_OBJECTS''') long_size
from dba_views
where view_name='ALL_OBJECTS';

OWNER VIEW_NAME LON_SIZE
----- --------- --------
SYS ALL_OBJECTS 9039