Re: very simple question

From: JustAnotherDBA <jadba_at_bellsouth.net>
Date: Sat, 18 Jan 2003 15:00:48 -0600
Message-ID: <mUiW9.10743$F_3.5949@news.bellsouth.net>

You cannot do standard SQL statements on LONGS.

But, you could write a Plsql function to search (using function INSTR) a long for a string and then do whatever (like use SUBSTR to extract certain data).

See the following Plsql function that processes the text (a LONG) column of dba_views . Also, included first below is a sample query using this function...

Note: Both the following should run as is in version 7.1 and newer.

• ====SqlPlus Script - Looking for views hitting ALL_ catalog =======
• ==== output should fit on an 80 column window =============== column owner format a12 column view_name format a25 column view_text format a43 select owner,view_name,parse_view_text(owner,view_name,'ALL_') view_text from sys.dba_views where parse_view_text(owner,view_name,'ALL_') is not null and owner != 'SYS' and owner != 'SYSTEM' order by owner,view_name /

======Plsql Function======
CREATE OR REPLACE FUNCTION PARSE_VIEW_TEXT

  (p_owner      VARCHAR2,
   p_view_name  VARCHAR2,
   p_substr     VARCHAR2,
   p_substr_nbr NUMBER  default null )

RETURN VARCHAR2 IS p_view_text VARCHAR2(4000) ;

--

---

• Module Name: PARSE_VIEW_TEXT
• Language: PL/SQL
• ------------------------------------------------------------------------ -
• Purpose: This function returns the following if the substr occurs
• p_substr_nbr times for the given owner, view_name:
  --
  
• 1) 100 characters before the input string
• Could be less if input string is at the beginning.
• 2) input string
• 3) 100 characters after the input string
• Could be less if input string is at the end .
  --
  
• Notes:
  --
  
• 1) p_substr_nbr will default to 1
• 2) If the owner, view_name passed is not found, then an error message
• '***** View Not Found *****' is returned.
• 3) The account that creates this function will need an explicit grant from SYS on DBA_VIEWS .
  --
  --
  

  ---

  
  --
  
• Local Cursors
  --
  CURSOR lc_view_text_csr IS SELECT text FROM sys.dba_views WHERE owner = p_owner AND view_name = p_view_name ;

--

• Local Variables
  --
  l_text LONG ; l_pos_in_long_nbr NUMBER ; l_before_text VARCHAR2(2000) ; l_after_text VARCHAR2(2000) ; l_substr_nbr NUMBER ; l_temp_start_nbr NUMBER ; l_temp_length_nbr NUMBER ; l_bef_aft_nbr NUMBER ;

BEGIN   l_bef_aft_nbr := 100 ;
  if p_substr_nbr is null then
    l_substr_nbr := 1 ;
  else
    l_substr_nbr := p_substr_nbr ;
  end if ;

  OPEN lc_view_text_csr ;
  FETCH lc_view_text_csr
   INTO l_text ;

  if lc_view_text_csr%NOTFOUND or l_text is null then     p_view_text := '***** View Not Found *****' ;   else

    l_pos_in_long_nbr := instr(l_text,p_substr,1,l_substr_nbr) ;
    if l_pos_in_long_nbr = 0 then
      p_view_text := Null ;
    else
      l_temp_start_nbr  := l_pos_in_long_nbr-l_bef_aft_nbr ;
      l_temp_length_nbr := l_bef_aft_nbr ;
      if l_temp_start_nbr < 0 then
        l_temp_start_nbr  := 1 ;
        l_temp_length_nbr := l_pos_in_long_nbr-1 ;
      end if ;
      l_before_text := substr(l_text,l_temp_start_nbr,l_temp_length_nbr) ;
      l_after_text  :=
substr(l_text,l_pos_in_long_nbr+length(p_substr),l_bef_aft_nbr) ;
      p_view_text   := l_before_text || p_substr || l_after_text ;

    end if ;
  end if ;

  CLOSE lc_view_text_csr ;

  RETURN p_view_text ;

END PARSE_VIEW_TEXT ;
/
show errors function PARSE_VIEW_TEXT

"Paul Taylor" <paul.taylor_at_zen.co.uk> wrote in message news:vp1j2v417904b4nos5ak56dil63dl4v3kr_at_4ax.com...

> How do I search for something in a LONG or CLOB field? I read that
> Oracle 9 allows these types to be treated like VARCHAR2. But I'm using
> Oracle 8i. I want to do things like LEN and LIKE on a LONG field and
> have never needed to in the past.

Received on Sat Jan 18 2003 - 15:00:48 CST