RE: Size of a Long Field

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 05 Feb 2003 13:25:33 -0800
Message-ID: <F001.00545010.20030205132533@fatcity.com>

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

Sent: Wednesday, February 05, 2003 6:53 AM To: Multiple recipients of list ORACLE-L

How do you determine the size, in bytes, of a long field?

Please. No tape measure jokes.

Ed

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (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). Received on Wed Feb 05 2003 - 15:25:33 CST