Monday, February 18, 2008

Breaking the 32 character limit using OWA_TEXT

In this post discussed how to display text, from database into HTML Region, that has more than 32000 characters length.

There were a lot of ways. I think that the decision below is the most simple and clear.
For this purpose I use owa_text.multi_line data type.

This data type is a PL/SQL record that holds large amounts of text. The rows field, of type OWA_TEXT.VC_ARR DATA TYPE, contains the text data in the record.

TYPE multi_line IS RECORD (
rows vc_arr,
num_rows INTEGER,
partial_row BOOLEAN);

The function below, convert clob into owa_text.multi_line data type:


create or replace function clob_to_multi_line(v_clob clob) return owa_text.multi_line is
res owa_text.multi_line;
r_cnt integer;
rem integer;
b_pos integer;
b_siz integer;

begin
r_cnt := trunc(dbms_lob.getlength(v_clob) / 32767);

rem := mod(dbms_lob.getlength(v_clob), 32767);
b_pos :=
1;
for idx in
1..r_cnt loop
b_siz :=
32767;
dbms_lob.read(v_clob, b_siz, b_pos, res.rows(idx));
b_pos := b_pos + b_siz;
end loop;
res.num_rows := r_cnt;
if (rem >
0) then
dbms_lob.read(v_clob, rem, b_pos, res.rows(r_cnt +
1));
res.num_rows := res.num_rows +
1;
end if;
res.partial_row := false;
return res;

end clob_to_multi_line;

An example source of PL/SQL Dynamic Content Region:

declare
c clob;

begin
select
clob_column into c from table_of_clobs
where id=1594;
OWA_TEXT.PRINT_MULTI(clob_to_multi_line(c));
end;


That’s it.

No comments:

Google