Sorry Allan, perhaps I didn't express myself clearly enough. In 8i
PL/SQL can cope with VARCHAR2
variables (explicit or implicit) up to 32767 characters in length.
SQL columns and SELECTed results cannot be longer than 4000 characters. Try this and you'll see:
Code:
DECLARE
dummy VARCHAR2(32767);
BEGIN
NULL;
END;
It compiles happily. Try to change the maximum length to 32768 and it fails.
Anyway, maybe this isn't true on older versions - it's not really all that relevant as long as you are aware of the constraints on your particular box. I've written and tested (more-or-less) an "industrial strength" version of my function that finds matches in CLOBs of any length:
Code:
CREATE OR REPLACE FUNCTION clob_like (p_CLOB IN CLOB,
p_search IN VARCHAR2) RETURN VARCHAR2 IS
-- PL/SQL (as opposed to SQL) can cope with varchar2s up to 32K (on 8i).
-- This may not be the case on older versions, in which case set the
-- following constant to 4000
c_maxvarchar2 CONSTANT NUMBER := 32767;
l_offset NUMBER := 1;
l_length NUMBER;
l_nextbite NUMBER;
l_answer VARCHAR2(1) := 'N';
BEGIN
-- Determine the length of the CLOB
l_length := DBMS_LOB.GETLENGTH(p_CLOB);
-- The offset between one bite and the next is less than the size of each
-- bite. Bites need to overlap by (length of search string - 1) to allow
-- for an occurrence of the string spanning the join.
l_nextbite := c_maxvarchar2 + 1 - length(p_search);
-- Look through the CLOB as a series of VARCHAR2 bites
WHILE l_offset <= l_length LOOP
IF UPPER(DBMS_LOB.SUBSTR(p_CLOB, c_maxvarchar2, l_offset)) LIKE UPPER(p_search) THEN
l_answer := 'Y';
EXIT; -- A match has been found, so bang out of the loop
END IF;
-- Find the start of the next bite.
l_offset := l_offset + l_nextbite;
END LOOP;
RETURN l_answer;
END;
I'm not sure I agree with your view on multiple actions generally occurring to result sets. It may be the case, it may not, depending on circumstances. If that series of operations includes any COMMITs, you'll have the potential problem of multiple users' result sets being saved into relevantrows_tmp and conflicting with eachother. If you
really need the relevantrows_tmp table to do a series of SELECTs, you could populate it with my function thus:
INSERT INTO relevantrows_tmp
SELECT rowid from aol where clob_like(aol.text,'whatever');
I think it would often be more trouble than it's worth.
In summary, whilst your solution works,I think my solution has a number of advantages:
[ul][li]It works for CLOBs of any length
[li]It works for any CLOB from any table, you have to write a version of RelevantRows for each one.
[li]It uses the larger PL/SQL flavour of VARCHAR2s, and hence requires fewer comparison operations.
[li]It only requires one pass through the table, not several.
[li]Queries can be done in one statement, not two.
[li]It allows the calling process to make use of other (perhaps indexed) columns: "select title from plays where clob_like(text,'%falstaff%') and author = 'SHAKESPEARE'"
[li]It doesn't require the a temporary table (and thus doesn't have to worry about multiple users issuing queries at the same time)
[li]It doesn't require an extra column in the source table, nor the overhead of keeping it up to date.
[li]I think it's a more self-contained, elegant solution - but then I would, wouldn't I?
[/ul]
I've not tangled much with CLOBs. It's been interesting, but it took me ages to remember that DBMS_LOB.SUBSTR() has it's arguments in a different order to SUBSTR() (doh!). Why
DID they do that?
-- Chris Hunt
Extra Connections Ltd
)..