Re: SELECT A CLOB - GROUP BY

From: ddf <oratune_at_msn.com>
Date: Tue, 23 Jun 2015 08:07:49 -0700 (PDT)
Message-ID: <80d04723-b160-41b9-8edb-e08380e9fe1c_at_googlegroups.com>

On Tuesday, June 23, 2015 at 1:02:51 AM UTC-6, saluj..._at_gmail.com wrote:
> On Wednesday, 9 April 2008 13:23:51 UTC+5:30, Shakespeare wrote:
> > <trpost_at_gmail.com> schreef in bericht
> > news:e2cb990f-1c6f-40ac-b154-c89f864104e8_at_n58g2000hsf.googlegroups.com...
> > > Is there anything that can be done with a PLSQL function to convert a
> > > CLOB to a VARCHAR2 to a size larger than 4000 characters? I read that
> > > PLSQL can handle a VARCHAR2 with 32767 charcaters, so is it possible
> > > to write a function to return this conversion?
> > >
> > > I found the following, but couldn't get it to compile:
> > >
> > > CREATE OR REPLACE FUNCTION GetVarchar2 (iclCLOB IN OUT CLOB)
> > > return VARCHAR2
> > > IS
> > >
> > > cnuMAX_LENGTH Constant number := 32767 ;
> > > nuLength Number := DBMS_LOB.getlength(iclCLOB);
> > > sbBuffer varchar2(32767);
> > >
> > > DBMS_LOB.read(iclCLOB,nuLength,1,bBuffer);
> > > return sbBuffer;
> > > END
> > >
> > > Thanks
> > >
> >
> > Is the case_list the same for every 'PRODUCT_NAME'?
> > If it is, don't select it on forehand, but do the group by first and then
> > look up the case list
> > If it's not, it makes no sense to do a group by at all.
> >
> > Shakespeare
>
> I want to use group by function onm CLOB as I want to match part of one CLOB data to another.Can anyone please help

I'm lost -- how does group by match partial strings?

Here's an example of matching part of a string in a CLOB field:

SQL> create table mylob (myid number not null,   2 mytext clob);

Table created.

SQL>
SQL> begin

  2          for i in 1..100 loop
  3                  insert into mylob
  4                  values(i, 'Text:  '||to_char(to_date(i, 'J'), 'JSP')||' and a partridge in a pear tree');
  5          end loop;
  6
  7          commit;

  8
  9 end;
 10 /

PL/SQL procedure successfully completed.

SQL>
SQL> select dbms_lob.substr(mytext, dbms_lob.instr(mytext, 'ONE'), 8)   2 from mylob
  3 where dbms_lob.instr(mytext, 'ONE') > 0;

DBMS_LOB.SUBSTR(MYTEXT,DBMS_LOB.INSTR(MYTEXT,'ONE'),8)

---

ONE and
TWENTY-ONE and
THIRTY-ONE and

FORTY-ONE and
FIFTY-ONE and
SIXTY-ONE and

SEVENTY-ONE and
EIGHTY-ONE and
NINETY-ONE and
ONE HUND 10 rows selected.

SQL> David Fitzjarrell Received on Tue Jun 23 2015 - 17:07:49 CEST