RE: Parsing a BLOB

I used following code to stream clob data from a select .... feel free = to change the code to suit your needs ...

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D cut here =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D

DROP TYPE SYSTEM.UTIL$STRINGS_TBL=20
/
DROP TYPE SYSTEM.UTIL$STRINGS=20
/
CREATE OR REPLACE TYPE SYSTEM.UTIL$STRINGS AS OBJECT (string_text = VARCHAR2(4000))
/
CREATE OR REPLACE TYPE SYSTEM.UTIL$STRINGS_TBL AS TABLE OF = SYSTEM.UTIL$STRINGS
/
CREATE OR REPLACE PUBLIC SYNONYM UTIL$STRINGS FOR SYSTEM.UTIL$STRINGS /
GRANT EXECUTE ON SYSTEM.UTIL$STRINGS TO PUBLIC /
CREATE OR REPLACE PUBLIC SYNONYM UTIL$STRINGS_TBL FOR = SYSTEM.UTIL$STRINGS_TBL
/
GRANT EXECUTE ON SYSTEM.UTIL$STRINGS_TBL TO PUBLIC /

CREATE OR REPLACE FUNCTION ST_DVDB2.Stream_Clob_Data (p_clobdata IN = CLOB, p_size IN NUMBER)
RETURN UTIL$STRINGS_TBL pipelined AS

--
-- #####  PVCS Indentifiers
-- $Archive:   N:/admin/oracle_dba/archives/sportsticker_ =
news/STREAM_CLOB_DATA.fnc-arc  $
-- $Author:   jamadagr  $
-- $Date:   Feb 03 2004 11:11:50  $
-- $Modtime:   Feb 03 2004 11:09:18  $
-- $Revision:   1.0  $
-- #####  End of PVCS Identifiers
-- The logic is fairly simple and you might hate me for doing this, but =
I think this
-- will help us avoid the delays ....
--
nBegin       PLS_INTEGER :=3D 1;
nLength      PLS_INTEGER :=3D LENGTH(p_clobdata);
nSize        PLS_INTEGER :=3D 1024;
szText       VARCHAR2(16384);
--
BEGIN
  IF p_size IS NULL OR p_size < 0 OR p_size > 16384 THEN
    nSize :=3D 1024;
  ELSE
    nSize :=3D p_size;
  END IF;
  --
  LOOP
    dbms_lob.READ(p_clobdata, nSize, nBegin, szText);
    pipe ROW (UTIL$STRINGS(szText));
    nBegin :=3D nBegin + nSize;
  END LOOP;
  RETURN;
  --
  EXCEPTION
    WHEN NO_DATA_FOUND
      THEN RETURN;
  --
END Stream_Clob_Data;
/


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D    cut here   =

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D


The way I call it is ...  (to get 1024 characters at a time)

    SELECT string_text
      FROM TABLE(CAST(Stream_Clob_Data(msg_text, 1024) AS =
util$strings_tbl));

This could be your source for instead of a table ...  If you see some =
data issues, you may have to use utl_raw around dbms_lob commands ...=20

or you can hack the code in function to break at every new line and send =
back one line at a time ...

Raj
-------------------------------------------------------------------------=
-------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
select standard_disclaimer from company_requirements;
QOTD: Any clod can have facts, having an opinion is an art !


-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of John Flack
Sent: Thursday, February 19, 2004 12:11 PM
To: oracle-l_at_freelists.org
Subject: RE: Parsing a BLOB


That is nice to know, and will help if I decide to write the parsing =3D
code myself.  However, I was hoping for something with less work.  Like =


=3D

can I make the BLOB the data source for an external table, without first =


=3D

writing it to a file on the server?

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------