Re: How to "order by" on long data type

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 24 Aug 2001 13:59:52 -0700
Message-ID: <9m6f8801dp3@drn.newsguy.com>

In article <9m643l$gg1$1_at_panix2.panix.com>, stanb_at_panix.com says...
>
>I am selecting "check" constraints from some system tables. The "search"
>collumn in these tables is of data type long. for some reason, I hvae many
>copies of the same constrain, with different constraint names.
>
>Yes I could/should clean this up, but that probably won't prevent it form
>happening again. So how can I do the equivelant of "order by" on this
>collumn in my select. so that I can ignore duplicates?
>
>

ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace function get_search_condition( p_constraint_name in varchar2 ) return varchar2   2 as
  3 l_data long;
  4 begin

  5          select search_condition into l_data
  6            from user_constraints
  7           where constraint_name = p_constraint_name;
  8  
  9          return substr( l_data, 1, 4000 );

 10 end;
 11 /

Function created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> select * 2 from ( select constraint_name, get_search_condition(constraint_name) search_condition

  3             from user_constraints
  4             )

  5 where search_condition is not null
  6 order by search_condition;

CONSTRAINT_NAME

---

SEARCH_CONDITION

---

SYS_C0016469
"BIRTHDATE" IS NOT NULL SYS_C0016794
"COLLECTION_YEAR" IS NOT NULL ....

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Aug 24 2001 - 15:59:52 CDT