Extracting RTF Text from BLOB - TIP Technologies

TIPsheet

DATE RELEASED: 4/28/2006 Version Compatibility: TIPQATM 6.5.x and higher Oracle 8i or better

CONTACT: support@ or (262) 544-1211 Ext: 115

TIPQATM Extracting RTF Text from Blobs

Purpose: With the Release of TIPQATM 6.5.0 TIP Technologies, Inc. has made changes to the method we use to format and store text. Prior to the release, all text was being stored as simple strings in one or more fields. Users were able to extract the text from our "MEMO_TEXT" tables using simple SQL Select statements with most any 3rd party reporting tool. As of TIPQATM 6.5.0, we have converted all existing text to RTF formatted text. We have also changed the way we store the text as it is now stored in a BLOB. (Binary Large Object).

In order to extract and display this RTF formatted text in Ad-Hoc reports, a method has been developed through collaboration among a few of our customers. This method may not work with all 3rd party reporting tools, but we have done some testing here using Microsoft Access with a RTF Plug-in. Other testing has been done with Crystal Reports as well.

Kudos to:

Joe Puzino ? BAE Systems - CNI Robert Neill - GDLS Troy Whetherhult - BAE Systems ? ASD Dan Miser ? TIP Technologies

Procedure

MSSQL SERVER 1. Select * from ac_memo_text 2. Process the RTF formatted text using 3rd Party Tool

ORACLE Option 1 Modified SQL Select Statement select mtsn, utl_raw.cast_to_varchar2(dbms_lob.substr(memo_text, 200, 1)) from ac_memo_text

This will give you the raw data. You then need to interpret the RTF formatting for the proper display.

Option 2 Create a Function and View (3 Steps) Step 1 - Create Function -- create_function_blob2string.sql

set lines 132 set pages 93 set serveroutput on

create or replace function blobtostring (p_blob in blob, p_amount in number, p_offset in number)

Copyright 1989-2006, TIP Technologies, Inc. The information contained herein is confidential and proprietary information which is legally privileged. This information is intended solely for the internal use of the addressed or intended party. This information is not to be distributed, in any manner, to any other party without the prior express written consent of TIP Technologies, Inc. 04282006 Page 1

TIPsheet

return varchar2 is v_nchars number; v_amount number; v_spos number; v_ascii integer; v_rstring varchar2(32767); v_hex varchar2(32767); v_char1 char(1); v_char2 char(1);

v_len_of_lob number;

begin

v_nchars := 0; v_amount := 0; v_spos := 0; v_ascii := 0;

v_rstring := ''; v_hex := '';

v_char1 := ' '; v_char2 := ' ';

v_len_of_lob := dbms_lob.getlength(p_blob) - p_offset + 1;

-- Set the number of bytes to read v_amount := least(p_amount, v_len_of_lob, (32764/2));

-- get the whole blob and use the built in exception when unreadable to -- return null as an in-line PL/SQL block, store it in hexadecimal form -- into v_hex begin

dbms_lob.read(p_blob, v_amount, p_offset, v_hex); exception

when others then return ''; end;

-- Good, now we have a good hex string. lets loop through the whole string -- and convert from hex to chars, we need to look at them as multi-byte characters -- so, lets take them two at a time.

-- set the end of the hex string v_nchars := length(trim(v_hex));

-- start at position #1 v_spos := 1;

Copyright 1989-2006, TIP Technologies, Inc. The information contained herein is confidential and proprietary information which is legally privileged. This information is intended solely for the internal use of the addressed or intended party. This information is not to be distributed, in any manner, to any other party without the prior express written consent of TIP Technologies, Inc. 04282006 Page 2

TIPsheet

-- Start with a null output string v_rstring := '';

loop -- grab the first of two hex values v_char1 := substr(v_hex,v_spos,1);

-- convert to ascii decimal. Get A-F convert to 10 - 15 if v_char1 between '0' and '9' then

v_ascii := (ascii(v_char1) - 48) * 16; else

v_ascii := (ascii(v_char1) - 55) * 16; end if;

-- grab the second of two hex values v_char2 := substr(v_hex,v_spos+1,1);

-- convert to ascii decimal and add to first value to get the final value if v_char2 between '0' and '9' then

v_ascii := v_ascii + (ascii(v_char2) - 48); else

v_ascii := v_ascii + (ascii(v_char2) - 55); end if;

if v_ascii between 32 and 127 then -- only allow printable ascii characters through 32 - 127 ascii, 128 - 255 extended ascii

v_rstring := v_rstring||chr(v_ascii);

elsif v_ascii = 10 then -- only unprintable exception is a line feed v_rstring := v_rstring||chr(v_ascii);

end if;

v_spos := v_spos + 2;

exit when v_spos >= v_nchars;

end loop;

return v_rstring;

end; /

show errors

Step 2 - Create View Please Note: A view will need to be created for each Memo Table. This is an example for the NC Memo Table.

Copyright 1989-2006, TIP Technologies, Inc. The information contained herein is confidential and proprietary information which is legally privileged. This information is intended solely for the internal use of the addressed or intended party. This information is not to be distributed, in any manner, to any other party without the prior express written consent of TIP Technologies, Inc. 04282006 Page 3

TIPsheet

-- create_nc_memo_text_v.sql

create or replace view nc_memo_text_view as select mtsn,

blobtostring(MEMO_TEXT,4000,1) as memo_text1, case

when dbms_lob.getlength(memo_text) > 4000 then blobtostring(MEMO_TEXT,4000 ,4000 +1)

end as MEMO_TEXT2, case

when dbms_lob.getlength(memo_text) > (4000 *2) then blobtostring(MEMO_TEXT,4000 ,(4000 *2)+1)

end as MEMO_TEXT3, case

when dbms_lob.getlength(memo_text) > (4000 *3) then blobtostring(MEMO_TEXT,4000 ,(4000 *3)+1)

end as MEMO_TEXT4, case

when dbms_lob.getlength(memo_text) > (4000 *4) then blobtostring(MEMO_TEXT,4000 ,(4000 *4)+1)

end as MEMO_TEXT5, case

when dbms_lob.getlength(memo_text) > (4000 *5) then blobtostring(MEMO_TEXT,4000 ,(4000 *5)+1)

end as MEMO_TEXT6, case

when dbms_lob.getlength(memo_text) > (4000 *6) then blobtostring(MEMO_TEXT,4000 ,(4000 *6)+1)

end as MEMO_TEXT7, case

when dbms_lob.getlength(memo_text) > (4000 *7) then blobtostring(MEMO_TEXT,4000 ,(4000 *7)+1)

end as MEMO_TEXT8, case

when dbms_lob.getlength(memo_text) > (4000 *8) then blobtostring(MEMO_TEXT,4000 ,(4000 *8)+1)

end as MEMO_TEXT9, case

when dbms_lob.getlength(memo_text) > (4000 *9) then blobtostring(MEMO_TEXT,4000 ,(4000 *9)+1)

end as MEMO_TEXT10, r_status, business_unit from nc_memo_text /

show errors

Step 3 - Use 3rd Party Reporting Tool Enabled to Read RTF Formatted Text

Copyright 1989-2006, TIP Technologies, Inc. The information contained herein is confidential and proprietary information which is legally privileged. This information is intended solely for the internal use of the addressed or intended party. This information is not to be distributed, in any manner, to any other party without the prior express written consent of TIP Technologies, Inc. 04282006 Page 4

TIPsheet

For example, Microsoft Access has a plug-in that will allow RTF formatted text to be displayed in a report or screen.

Copyright 1989-2006, TIP Technologies, Inc. The information contained herein is confidential and proprietary information which is legally privileged. This information is intended solely for the internal use of the addressed or intended party. This information is not to be distributed, in any manner, to any other party without the prior express written consent of TIP Technologies, Inc. 04282006 Page 5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download