I have this code that is used to search within a trigger body ... you can maybe adapt this to get length ... you have to just change dbms_lob.instr to dbms_lob.getlength() maybe.
CREATE OR REPLACE FUNCTION long_contains
( row_id ROWID,
VALUE VARCHAR2) RETURN NUMBER
IS
--
ret_val NUMBER;
--
/* Table details */
OWN VARCHAR2(30);
tab VARCHAR2(30);
col VARCHAR2(30);
/* DBMS_SQL variables */
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
res PLS_INTEGER := 0;
pos PLS_INTEGER := 0;
len PLS_INTEGER := 0;
pce VARCHAR2(32767);
/* Temp CLOB to hold long data */
tmp CLOB;
--
BEGIN
/* Fetch the table name and long column name */
SELECT u.NAME
,o.NAME
,c.NAME
INTO OWN
,tab
,col
FROM sys.obj$ o
,sys.col$ c
,sys.USER$ u
WHERE u.USER# = o.owner#
AND c.TYPE# = 8
AND c.obj# = o.obj#
AND o.obj# = DBMS_ROWID.ROWID_OBJECT(row_id);
--
/* Fetch the long column into a temporary CLOB */
DBMS_LOB.CREATETEMPORARY(tmp,TRUE,DBMS_LOB.CALL);
DBMS_SQL.PARSE(cur, ' SELECT '||col|| ' FROM '||OWN||'.'||tab|| ' WHERE ROWID = :row_id',DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cur,':row_id',row_id);
DBMS_SQL.DEFINE_COLUMN_LONG(cur, 1);
res := DBMS_SQL.EXECUTE_AND_FETCH(cur);
LOOP
DBMS_SQL.COLUMN_VALUE_LONG(cur,1,32767,pos,pce,len);
EXIT WHEN len = 0;
pce := UPPER(pce);
DBMS_LOB.WRITE(tmp,len,pos+1,pce);
pos := pos + len;
END LOOP;
--
DBMS_SQL.CLOSE_CURSOR(cur);
--
/* Now use DBMS_LOB funcs. to examine */
IF DBMS_LOB.INSTR(tmp,UPPER(VALUE)) > 0 THEN
ret_val := 1;
ELSE
ret_val := 0;
END IF;
--
RETURN ret_val;
END;
/
HTH
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
From: Ed Bittel [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 05, 2003 6:53 AM
To: Multiple recipients of list ORACLE-L
Subject: Size of a Long Field
How do you determine the size, in bytes, of a long field?
Please. No tape measure jokes.
Ed
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
