dbTalk Databases Forums  

SELECT A CLOB - GROUP BY

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc SELECT A CLOB - GROUP BY

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss SELECT A CLOB - GROUP BY in the comp.databases.oracle.misc forum.



Reply
Page 3 of 3 < 1 2 3
 
Thread Tools Display Modes
  #21  
Old   
trpost@gmail.com
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-07-2008 , 05:06 PM





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


Reply With Quote
  #22  
Old   
Ed Prochak
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-08-2008 , 07:17 AM





On Apr 7, 5:06 pm, trp... (AT) gmail (DOT) com wrote:
Quote:
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
If you are returning it to SQL, then no this doesn't work.

You never really answered the question: why would you want to group by
the CLOB column?
What makes you think the value of one CLOB will match another?
You know your data. I'm just asking because often CLOBs are used for
notes, comments and other text that is unlikely to match another entry
exactly.

I suspect what you will have to do is redesign your tables. You CLOB
column seems to be a list of some sort. Perhaps it could be normalized
to its own table, splitting the data in the CLOB to different rows.
Getting the grouping you want may still be troublesome but more likely
to be doable. But it depends on the nature of the data in the CLOB
column.

HTH,
Ed

--
Magic Interface, Ltd.
www.magicinterface.com
440-498-3700
Hardware/Software Alchemy


Reply With Quote
  #23  
Old   
Ed Prochak
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-08-2008 , 07:17 AM


On Apr 7, 5:06 pm, trp... (AT) gmail (DOT) com wrote:
Quote:
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
If you are returning it to SQL, then no this doesn't work.

You never really answered the question: why would you want to group by
the CLOB column?
What makes you think the value of one CLOB will match another?
You know your data. I'm just asking because often CLOBs are used for
notes, comments and other text that is unlikely to match another entry
exactly.

I suspect what you will have to do is redesign your tables. You CLOB
column seems to be a list of some sort. Perhaps it could be normalized
to its own table, splitting the data in the CLOB to different rows.
Getting the grouping you want may still be troublesome but more likely
to be doable. But it depends on the nature of the data in the CLOB
column.

HTH,
Ed

--
Magic Interface, Ltd.
www.magicinterface.com
440-498-3700
Hardware/Software Alchemy


Reply With Quote
  #24  
Old   
Ed Prochak
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-08-2008 , 07:17 AM


On Apr 7, 5:06 pm, trp... (AT) gmail (DOT) com wrote:
Quote:
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
If you are returning it to SQL, then no this doesn't work.

You never really answered the question: why would you want to group by
the CLOB column?
What makes you think the value of one CLOB will match another?
You know your data. I'm just asking because often CLOBs are used for
notes, comments and other text that is unlikely to match another entry
exactly.

I suspect what you will have to do is redesign your tables. You CLOB
column seems to be a list of some sort. Perhaps it could be normalized
to its own table, splitting the data in the CLOB to different rows.
Getting the grouping you want may still be troublesome but more likely
to be doable. But it depends on the nature of the data in the CLOB
column.

HTH,
Ed

--
Magic Interface, Ltd.
www.magicinterface.com
440-498-3700
Hardware/Software Alchemy


Reply With Quote
  #25  
Old   
Ed Prochak
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-08-2008 , 07:17 AM


On Apr 7, 5:06 pm, trp... (AT) gmail (DOT) com wrote:
Quote:
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
If you are returning it to SQL, then no this doesn't work.

You never really answered the question: why would you want to group by
the CLOB column?
What makes you think the value of one CLOB will match another?
You know your data. I'm just asking because often CLOBs are used for
notes, comments and other text that is unlikely to match another entry
exactly.

I suspect what you will have to do is redesign your tables. You CLOB
column seems to be a list of some sort. Perhaps it could be normalized
to its own table, splitting the data in the CLOB to different rows.
Getting the grouping you want may still be troublesome but more likely
to be doable. But it depends on the nature of the data in the CLOB
column.

HTH,
Ed

--
Magic Interface, Ltd.
www.magicinterface.com
440-498-3700
Hardware/Software Alchemy


Reply With Quote
  #26  
Old   
Shakespeare
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-09-2008 , 02:53 AM



<trpost (AT) gmail (DOT) com> schreef in bericht
news:e2cb990f-1c6f-40ac-b154-c89f864104e8 (AT) n58g2000hsf (DOT) googlegroups.com...
Quote:
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




Reply With Quote
  #27  
Old   
Shakespeare
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-09-2008 , 02:53 AM



<trpost (AT) gmail (DOT) com> schreef in bericht
news:e2cb990f-1c6f-40ac-b154-c89f864104e8 (AT) n58g2000hsf (DOT) googlegroups.com...
Quote:
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




Reply With Quote
  #28  
Old   
Shakespeare
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-09-2008 , 02:53 AM



<trpost (AT) gmail (DOT) com> schreef in bericht
news:e2cb990f-1c6f-40ac-b154-c89f864104e8 (AT) n58g2000hsf (DOT) googlegroups.com...
Quote:
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




Reply With Quote
  #29  
Old   
Shakespeare
 
Posts: n/a

Default Re: SELECT A CLOB - GROUP BY - 04-09-2008 , 02:53 AM



<trpost (AT) gmail (DOT) com> schreef in bericht
news:e2cb990f-1c6f-40ac-b154-c89f864104e8 (AT) n58g2000hsf (DOT) googlegroups.com...
Quote:
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




Reply With Quote
Reply
Page 3 of 3 < 1 2 3

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top