RE: Working with a BLOB
Walter,
Investigate the DBMS_LOB supplied package.
The Instr function in that package does exactly what you want.
Jack
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068
-Original Message-
Sent: Wednesday, May 30, 2001 5:30 PM
To: Multiple recipients of list ORACLE-L
I've been through the documentation on LOBs but am
still stuck trying to figure out how to interrogate
the contents of a BLOB.
We have a table with a BLOB column in it. All it
contains in text data (i.e. memo notes). Why it was
created as a BLOB and not a CLOB is unknown to me and
done before I was hired. All I need to do is determine
if a particular string ('.com') pattern exists in the
column, within the first 75 bytes, and return its
starting position.
Would someone help me out? Thanks!
-w
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack C. Applewhite
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Working with a BLOB
Walter;
The package that comes with Oracle, DBMS_LOB, has functions to Append,
Compare, Copy, Erase, GetLength, INSTR, LoadFromFile, Read, Substr, Trim,
and Write BLOBS.You should probably try starting from that one.
Oracle Built-In Packages from Oriely Press (ISBN 1-56592-375-8) Chapter 8,
Managing Large Objects.
Kevin
-Original Message-
Sent: Wednesday, May 30, 2001 5:30 PM
To: Multiple recipients of list ORACLE-L
I've been through the documentation on LOBs but am
still stuck trying to figure out how to interrogate
the contents of a BLOB.
We have a table with a BLOB column in it. All it
contains in text data (i.e. memo notes). Why it was
created as a BLOB and not a CLOB is unknown to me and
done before I was hired. All I need to do is determine
if a particular string ('.com') pattern exists in the
column, within the first 75 bytes, and return its
starting position.
Would someone help me out? Thanks!
-w
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Working with a BLOB
> All I need to do is determine
> if a particular string ('.com') pattern exists in the
> column, within the first 75 bytes, and return its
> starting position.
The secret is the built-in DBMS_LOB.INSTR.
Haven't actually run this code, but something along these lines ought to
work:
DECLARE
lobloc BLOB;
rawstr RAW := UTL_RAW.CAST_TO_RAW('.com'); /* notice, case sensitive!
*/
pos PLS_INTEGER;
BEGIN
SELECT lobcol INTO lobloc WHERE primary_key = 'whatever';
pos := DBMS_LOB.INSTR(lobloc, rawstr);
IF pos > 0 and pos <= 75
THEN
DBMS_OUTPUT.PUT_LINE('found it!');
END IF;
END;
/
__
http://www.datacraft.com/http://plnet.org/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Pribyl
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Working with a BLOB
just a guess but try
select dbms_lob.instr(blob_col, '.com')
from atable
where dbms_lob.instr(blob_col, '.com') between 0 an 75
-Original Message-
Sent: 30 May 2001 23:30
To: Multiple recipients of list ORACLE-L
I've been through the documentation on LOBs but am
still stuck trying to figure out how to interrogate
the contents of a BLOB.
We have a table with a BLOB column in it. All it
contains in text data (i.e. memo notes). Why it was
created as a BLOB and not a CLOB is unknown to me and
done before I was hired. All I need to do is determine
if a particular string ('.com') pattern exists in the
column, within the first 75 bytes, and return its
starting position.
Would someone help me out? Thanks!
-w
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_
This message has been checked for all known viruses by Star Internet
delivered through the MessageLabs Virus Scanning Service. For further
information visit http://www.star.net.uk/stats.asp or alternatively call
01285 884400.
This message is intended only for the use of the person(s) (the "intended recipient
(s)") to whom it is addressed.
It may contain information which is privileged and confidential.
If you are not the intended recipient, please contact the sender as soon as possible.
The views expressed in this communication may not necessarily be the views of InterX
plc.
Any copyright in this message shall remain vested in InterX plc © and the intended
recipient may only copy the same for internal business purposes or as otherwise stated
in this message.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vipul Lakhani
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Working with a BLOB
Sometimes the obvious isn't obvious...
I had the bulk of the query but was just getting hung
up on the "1st 75 bytes" requirement. It never clicked
with me that the instr( ) function in the dbms_log
package already gave me a position and all I needed to
do was simply constrain it with a "between" clause.
The query below won't work with a blob because the
data is stored as binary so the '.com' needs to be
wrapped in the UTL_RAW.CAST_TO_RAW( ) function first
so the datatypes are compatible.
Thanks again!! :-)
-w
--- Vipul Lakhani <[EMAIL PROTECTED]> wrote:
> just a guess but try
>
> select dbms_lob.instr(blob_col, '.com')
> from atable
> where dbms_lob.instr(blob_col, '.com') between 0 an
> 75
>
>
>
> -Original Message-
> Sent: 30 May 2001 23:30
> To: Multiple recipients of list ORACLE-L
>
>
> I've been through the documentation on LOBs but am
> still stuck trying to figure out how to interrogate
> the contents of a BLOB.
>
> We have a table with a BLOB column in it. All it
> contains in text data (i.e. memo notes). Why it was
> created as a BLOB and not a CLOB is unknown to me
> and
> done before I was hired. All I need to do is
> determine
> if a particular string ('.com') pattern exists in
> the
> column, within the first 75 bytes, and return its
> starting position.
>
> Would someone help me out? Thanks!
> -w
>
> __
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail -
> only $35
> a year! http://personal.mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Walter K
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
>
_
> This message has been checked for all known viruses
> by Star Internet
> delivered through the MessageLabs Virus Scanning
> Service. For further
> information visit http://www.star.net.uk/stats.asp
> or alternatively call
> 01285 884400.
>
>
> This message is intended only for the use of the
> person(s) (the "intended recipient (s)") to whom it
> is addressed.
> It may contain information which is privileged and
> confidential.
> If you are not the intended recipient, please
> contact the sender as soon as possible.
> The views expressed in this communication may not
> necessarily be the views of InterX plc.
> Any copyright in this message shall remain vested in
> InterX plc © and the intended recipient may only
> copy the same for internal business purposes or as
> otherwise stated in this message.
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Vipul Lakhani
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
