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).
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).
