Re: LONG

From: Dima <dima_at_cit.org.by>
Date: Fri, 12 May 2000 12:21:43 +0300
Message-Id: <10495.105461@fatcity.com>

Hi,

It is possible, if the length of substring is less than 32K. There is no any limitations on LONG col value length. Example:

CREATE OR REPLACE FUNCTION Substr_LONG(
  pTable_Name VARCHAR2,
  pLong_Column_Name VARCHAR2,
  pStart_Pos INTEGER,
  pLength INTEGER
) RETURN VARCHAR2 IS
  vCur INTEGER;
  vDummy INTEGER;
  vResult VARCHAR2(32000);
BEGIN
  IF pLength > 32000 THEN
    Raise_Application_Error(-20000, 'Length of substring cannot be more than 32000 bytes');
  END IF;
  vCur := DBMS_SQL.Open_Cursor;
  DBMS_SQL.Parse(vCur, 'SELECT ' || pLong_Column_Name ||     ' FROM ' || pTable_Name || ' WHERE RowNum = 1', DBMS_SQL.Native);   DBMS_SQL.Define_Column_Long(vCur, 1);
  vDummy := DBMS_SQL.Execute_And_Fetch(vCur);   DBMS_SQL.Column_Value_Long(vCur, 1, pLength, pStart_Pos,     vResult, vDummy);
  DBMS_SQL.Close_Cursor(vCur);
  RETURN vResult;
EXCEPTION
  WHEN Others THEN
    IF DBMS_SQL.Is_Open(vCur) THEN
      DBMS_SQL.Close_Cursor(vCur);
    END IF;
END;
/

It works fine on my Oracle 7.3.4.

var Substr varchar2(2000);
exec :Substr := Substr_LONG('all_views', 'text', 40, 100); print substr

SUBSTR

---

ype, 2, 'TABLE', 4, 'VIEW')
from sys.user$ u, sys.obj$ o
where o.owner# = u.user#
  and o.linkname i

HTH
Dima

• Original Message ----- From: "Lyall Barbour" <lyallbarbour_at_sanfranmail.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, May 12, 2000 4:18 AM Subject: RE: LONG

> No.
>
> What version of Oracle? In 8, you can use LOB's (CLOB, BLOB, etc) and use
> DBMS_LOB to search through a Large OBject or Binary FILE.
>
> HTH
>
> Lyall Barbour
>
> ------Original Message------
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: May 11, 2000 6:27:04 AM GMT
>
>
> Hi guru4s
> i want to know if is possible to do a substr or a instr in plsql with a
LONG
> field?
> thanks for all,
>
> --
> Author: Solis Llera, Eduardo
> INET: esolis_at_mapfre.com
>
> 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: 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).
>
> ______________________________________________
> FREE Personalized Email at Mail.com
> Sign up at http://www.mail.com/?sr=signup
>
> --
> Author: Lyall Barbour
> INET: lyallbarbour_at_sanfranmail.com
>
> 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: 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
Received on Fri May 12 2000 - 04:21:43 CDT