This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Searching CLOBs
From: Christopher R Key <chris.key@wcom.com>
Date: Wed, 09 Aug 2000 13:47:49 -0500
Is there anyway to search for a substring within a CLOB field in a
table? For instance, say I have a table:
Field Type
---------------
bid number(11)
chapter CLOB
And I'm storing parts of a story (chapter) in the chapter field. Is
there anyway to search that field for a specific string of text?
I'm using Oracle 8.0.4 and DBD::Oracle 1.06.
===
Subject: Re: Searching CLOBs
From: Ronald J Kimball <rjk-dbi@focalex.com>
Date: Wed, 9 Aug 2000 15:06:31 -0400
I think you can do this with INSTR, which is like Perl's substr(), except
that the offset starts at 1 instead of 0, and the fourth argument is for
saying which occurence to locate. So, INSTR(chapter, 'princess', 300, 3)
finds the 3rd occurence of 'princess' in chapter, starting from the 300th
character.
===
Subject: Re: Searching CLOBs
From: "Stephen Clouse" <stephenc@theiqgroup.com>
Date: Wed, 9 Aug 2000 14:23:04 -0500
BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I think you can do this with INSTR, which is like Perl's substr(),
> except that the offset starts at 1 instead of 0, and the fourth
> argument is for saying which occurence to locate. So,
> INSTR(chapter, 'princess', 300, 3) finds the 3rd occurence of
> 'princess' in chapter, starting from the 300th character.
None of Oracle's string functions are kind enough to work on *LOBs,
only *CHAR datatypes. Attempting this will net you an ORA-00932
(inconsistent datatypes) error for your trouble.
The only way I know of to search *LOBs (aside from rolling your own
inefficient retrieve/search loop in Perl itself) is with the
InterMedia Text addon, but this level of Oracle use is beyond my
level of sadomasochism.
===
Subject: Re: Searching CLOBs
From: "Stephen Clouse" <stephenc@theiqgroup.com>
Date: Wed, 9 Aug 2000 14:31:50 -0500
BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> The only way I know of to search *LOBs (aside from rolling your own
> inefficient retrieve/search loop in Perl itself) is with the
> InterMedia Text addon, but this level of Oracle use is beyond my
> level of sadomasochism.
Addendum: There is nothing at the SQL level to search LOBs, but a
PL/SQL function exists: DBMS_LOB.INSTR. And thankfully these CAN be
called from DBI. I'll lift from the Oracle example procedure:
/* Note that the example procedure instringLOB_proc is not part of
the
DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE instringLOB_proc IS
Lob_loc CLOB;
Pattern VARCHAR2(30) := 'children';
Position INTEGER := 0;
Offset INTEGER := 1;
Occurrence INTEGER := 1;
BEGIN
/* Select the LOB: */
SELECT Story INTO Lob_loc
FROM Multimedia_tab
WHERE Clip_ID = 1;
/* Opening the LOB is optional: */
DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
/* Seek for the pattern: */
Position := DBMS_LOB.INSTR(Lob_loc, Pattern, Offset, Occurrence);
IF Position = 0 THEN
DBMS_OUTPUT.PUT_LINE('Pattern not found');
ELSE
DBMS_OUTPUT.PUT_LINE('The pattern occurs at '|| position);
END IF;
/* Closing the LOB is mandatory if you have opened it: */
DBMS_LOB.CLOSE (Lob_loc);
END;
This was buried in the Application Developer's Guide, one of those
books I never ever use :)
===
Subject: Re: Searching CLOBs
From: Honza Pazdziora <adelton@informatics.muni.cz>
Date: Wed, 9 Aug 2000 21:33:02 +0200
On Wed, Aug 09, 2000 at 02:23:04PM -0500, Stephen Clouse wrote:
>
> The only way I know of to search *LOBs (aside from rolling your own
> inefficient retrieve/search loop in Perl itself) is with the
> InterMedia Text addon, but this level of Oracle use is beyond my
> level of sadomasochism.
Under 8.0.4 it's called ConText cartridge and I really recommend using
it if you have the budget for it (which you probably have, if you're
using Oracle in the first place).
The select is then
select something from table where contains(chapter, 'your phrase') > 0
===
Subject: Re: Searching CLOBs
From: Ronald J Kimball <rjk-dbi@focalex.com>
Date: Wed, 9 Aug 2000 15:42:29 -0400
On Wed, Aug 09, 2000 at 02:23:04PM -0500, Stephen Clouse wrote:
>
> > I think you can do this with INSTR, which is like Perl's substr(),
> > except that the offset starts at 1 instead of 0, and the fourth
> > argument is for saying which occurence to locate. So,
> > INSTR(chapter, 'princess', 300, 3) finds the 3rd occurence of
> > 'princess' in chapter, starting from the 300th character.
>
> None of Oracle's string functions are kind enough to work on *LOBs,
> only *CHAR datatypes. Attempting this will net you an ORA-00932
> (inconsistent datatypes) error for your trouble.
According to Oracle8: The Complete Reference:
"The INSTR function within the DBMS_LOB package performs the SQL INSTR
function on a LOB value." (Ch. 27, p. 675)
I just can't figure out how to call the INSTR function in the DBMS_LOB
package. :(
===
Subject: Re: Searching CLOBs
From: "Stephen Clouse" <stephenc@theiqgroup.com>
Date: Wed, 9 Aug 2000 14:45:27 -0500
BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> According to Oracle8: The Complete Reference:
>
> "The INSTR function within the DBMS_LOB package performs the SQL
> INSTR function on a LOB value." (Ch. 27, p. 675)
>
> I just can't figure out how to call the INSTR function in the
> DBMS_LOB package. :(
See my other mail. I crossed this INSTR with the SQL INSTR function
(what genius gave them the same name anyway...).
===
Subject: CLOB IN PERL DBI
From: sujatha.shyamsunder@qxl.com
Date: Tue, 29 Aug 2000 15:19:22 +0100
> I tried to retrieve clob data (as refcursor) using perl dbi from an
> oracle stored procedure
> but i get some error.
> pl. find the program and the error (down)
>
>
> #!/InterShop/bin/perl
>
> require 5.000;
> require Exporter;
>
> use lib "/InterShop/";
> use lib::Config::Config_DBI qw ( Connect Disconnect );
> use lib::config_qx qw ( Config_XL );
>
> use strict;
>
> main:
> {
> my ($dbi) = Connect("xl");
> my $result;
> my ($sph) = $dbi->prepare(q(
> #!/InterShop/bin/perl
>
> require 5.000;
> require Exporter;
>
> use lib "/InterShop/";
> use lib::Config::Config_DBI qw ( Connect Disconnect );
> use lib::config_qxl qw ( Config_XL );
>
> use strict;
>
> main:
> {
> my ($dbh) = Connect("xl");
> my $result;
> my ($results) = $dbh->prepare('SELECT 1 FROM DUAL');
> my ($sph) = $dbh->prepare(q(
> BEGIN
> qxl.lobtestproc(:result);
> END;));
>
> $sph->bind_param_inout(":result", \$results, 1000, { ora_type => 102
> });
> $sph->execute();
> while (my @row = $results->fetchrow_array)
> {
> foreach (@row)
> {
> print "dsfsf";
> }
> }
>
> Disconnect($dbh);
>
> Error
>
> LOB Locators are not directly accessible yet. at clobtest1.pl line 32.
>
> can anybody let me know, how to handle clob data in perl dbi when returned
> as a refcursor from an oracle stored procedure
>
===
Subject: Re: CLOB IN PERL DBI
From: "Michael A. Chase" <mchase@ix.netcom.com>
Date: Tue, 29 Aug 2000 09:52:58 -0700
This belongs on dbi-users.
What version of OS, Oracle, Perl, DBI and DBD::Oracle are you using?
What value is returned by this one liner?
perl -MDBD::Oracle -e "print DBD::Oracle::ORA_OCI()"
===
Subject: Re: CLOB IN PERL DBI
From: "Michael A. Chase" <mchase@ix.netcom.com>
Date: Tue, 29 Aug 2000 18:37:42 -0700
Try this sequence:
perl -de 42
use DBD::Oracle;
x $DBD::Oracle::VERSION, DBD::Oracle::ORA_OCI()
q
The reason I keep picking at the OCI version is that if your DBD::Oracle was
built with the -8 option (disable Oracle 8 OCI) it can't handle CLOB. If it
was compiled without that option, you just select *LOB columns like any
other column. Run 'perldoc DBI' and look at the sections on LongReadLen,
LongTruncOk, and the section titled "Handling BLOB / LONG / Memo Fields".
===