RE: Parsing a BLOB
- From: "Jamadagni, Rajendra" <Rajendra.Jamadagni@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Feb 2004 12:21:06 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of John Flack
Sent: Thursday, February 19, 2004 12:11 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
