Report6i: Order by CLOB column?

[Tiago Rocha]

Hi,

I have a very simple report made with Reports6i, report is fine, but
user wants that it be ordered by the clob column, but clob column
cannot be used on a order by... Does anyone knows any trick or will I
have to convert the CLOB to LONG?

Could not find an answer browsing metalink...

DB is 8.1.7

thanks in advance.
--
Tiago Rocha
Recife - Brasil
www.diariodastrilhas.cjb.net

[Daniel Morgan]

Tiago Rocha wrote:
Hi, I have a very simple report made with Reports6i, report is fine, but user wants that it be ordered by the clob column, but clob column cannot be used on a order by... Does anyone knows any trick or will I have to convert the CLOB to LONG? Could not find an answer browsing metalink... DB is 8.1.7 thanks in advance.

Users the bane of our existance. ;-)

Rather than trying to create deprecated LONGs or whatever ... have your
end-users clearly define what they intend by sorting. Then, with that
criteria in hand use the DBMS_LOB built-in package to create a
pseudo-column that you can use in the ORDER BY clause.

Here's an example of sorting with a pseudo-column that does not involve
a CLOB but the principle is the same.

SELECT address, TO_NUMBER(TRIM(TRANSLATE(address,
'0ABCDEFGHIJKLMNOPOQRSTUVWXYZabcdefghijklmnopqrstuvwxyz.,','0'))) SORTCOL
FROM test
ORDER BY SORTCOL;

Note that SORTCOL does not exist in the data but is created from the
address column purely for sorting purposes.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[Mark C. Stock]

"Tiago Rocha" <please.see.link.at.signature.for.email.address@zaz.com.br>
wrote in message news:qinqvv44svojplmsmiqm74aj5msbr0qmso@4ax.com...
| Hi,
|
| I have a very simple report made with Reports6i, report is fine, but
| user wants that it be ordered by the clob column, but clob column
| cannot be used on a order by... Does anyone knows any trick or will I
| have to convert the CLOB to LONG?
|
| Could not find an answer browsing metalink...
|
| DB is 8.1.7
|
| thanks in advance.
| --
| Tiago Rocha
| Recife - Brasil
| www.diariodastrilhas.cjb.net


converting to LONG wouldn't work -- can sort on LONGs in 8.1.7 (or 9.2)

however, you can order by wrapping the CLOB in UTL_RAW.CAST_TO_VARCHAR2(
DBMS_LOB.SUBSTR( ) ) calls -- and limiting the length of the substring to
the number of characters that would be significant in the sort


--
Mark C. Stock
mcstock -> enquery(dot)com
www.enquery.com training & consulting

[Tiago Rocha]

On Thu, 8 Jan 2004 10:51:28 -0500, "Mark C. Stock" <mcstockX@Xenquery
..com> wrote:
however, you can order by wrapping the CLOB in UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR( ) ) calls -- and limiting the length of the substring tothe number of characters that would be significant in the sort

When trying to use utl_raw, it gave me ORA-6502... But I just removed
the utl_raw and used just dbms_lob.substr and it worked great!

Thanks!

--
Tiago Rocha
Recife - Brasil
www.diariodastrilhas.cjb.net

[Tiago Rocha]

On Thu, 08 Jan 2004 07:39:54 -0800, Daniel Morgan
<damorgan@x.washington.edu> wrote:Users the bane of our existance. ;-)Rather than trying to create deprecated LONGs or whatever ... have yourend-users clearly define what they intend by sorting. Then, with thatcriteria in hand use the DBMS_LOB built-in package to create apseudo-column that you can use in the ORDER BY clause.

Creating longs is the last thing I want to do... I have bad experience
from using them back when DB was 7.x... Well, the report just show an
error log from an import of a text file. I did a little Forms program
to do what sql loader could do, but in a user friendly mode, using
text_io, errors goes to an error log table... The user say that it's
faster to check the error log if the error messages are in alpha
order...

Thanks for answering!

--
Tiago Rocha
Recife - Brasil
www.diariodastrilhas.cjb.net

[Mark C. Stock]

"Tiago Rocha" <please.see.link.at.signature.for.email.address@zaz.com.br>
wrote in message news:2i4rvvgmg5grioo2j5okv01gclvk61qogn@4ax.com...
| On Thu, 8 Jan 2004 10:51:28 -0500, "Mark C. Stock" <mcstockX@Xenquery
| .com> wrote:
|
| >however, you can order by wrapping the CLOB in UTL_RAW.CAST_TO_VARCHAR2(
| >DBMS_LOB.SUBSTR( ) ) calls -- and limiting the length of the substring to
| >the number of characters that would be significant in the sort
|
| When trying to use utl_raw, it gave me ORA-6502... But I just removed
| the utl_raw and used just dbms_lob.substr and it worked great!
|
| Thanks!
|
| --
| Tiago Rocha
| Recife - Brasil
| www.diariodastrilhas.cjb.net


sorry -- i grabbed my example from a BLOB (the document upload table used by
the PL/SQL gateway)

CLOB doesn't need the utl_raw conversion

-- mcs