| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Ordering by a Long
Actually, I just tried this on Personal Oracle 7.2 and got the following message:
select constraint_name , get_search_cond('DOUG',constraint_name)
*
I tried you example in the literal sense and got the same thing. I'll try this on the big box in a couple of minutes...but what on earth or who on earth is Diana? ..in the Oracle sense...I think I've heard this reference once before...
Thomas Kyte wrote:
> A copy of this was sent to Doug Cowles <dcowles_at_i84.net>
> (if that email address didn't require changing)
> On Sat, 06 Mar 1999 16:47:29 -0500, you wrote:
>
> >Using Oracle 7.3 -
> >I want to order on a long column, but get a message that it
> >is illegal use of the type.
> >What I am trying to do is an extract of user_constraints that
> >will be comparable to a similar extract from another schema on
> >another database on another box.
> >I have been instructed to verify the constraints between these two
> >places and make sure they are ok.
> >I use decode to drop the system generated portion of the constraint
> >names,
> >but I can't get around the delete_rule being a long column, and that's
> >where
> >the extracts are differing. I really want to be able to use a Unix diff
> >command
> >to verify the constraints are all the same.
> >Does anyone have a work around for ordering by a LONG??
> >
>
> the following method will work given the following constraints:
>
> o your search_conditions (not delete_rule) are all 32k or less. this is
> probably the case.
>
> o sorting by the first 2000 characters will be sorted enough for you. SQL can
> only deal with 2000 byte columns in 7.x (4000 in 8.x).
>
> SQL> create or replace function get_search_cond( p_owner in varchar2,
> 2 p_constraint_name in varchar2 )
> 3 return varchar2
> 4 as
> 5 l_str long;
> 6 begin
> 7 select search_condition
> 8 into l_str
> 9 from user_constraints
> 10 where owner = p_owner
> 11 and constraint_name = p_constraint_name;
> 12
> 12 return substr( l_str, 1, 2000 );
> 13 end;
> 14 /
>
> Function created.
>
> SQL>
> SQL> column sc format a20
> SQL> select constraint_name, owner, get_search_cond(owner, constraint_name) sc
> 2 from user_constraints
> 3 order by 3
> 4 /
>
> CONSTRAINT_NAME OWNER SC
> ------------------------------ ---------- --------------------
> SYS_C00781 TKYTE x is not null
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Sat Mar 06 1999 - 22:33:16 CST
 |
 |