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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 28 Aug 2001 06:54:34 -0700
Message-ID: <9mg7qq031dv@drn.newsguy.com>

In article <9mg5ss$d3m$1_at_panix2.panix.com>, stanb_at_panix.com says...
>
>In <9m6f8801dp3_at_drn.newsguy.com> Thomas Kyte <tkyte_at_us.oracle.com> writes:
>
>>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 /
>
>Neat, and thanks.
>
>One more question, could this function be made slightly more genereic? That
>is would it be possible to pass in the table name, and the collumn name?

ops$tkyte_at_ORA815> create or replace function get_long( p_pk_name in varchar2,

  2                                       p_pk_val  in varchar2,
  3                                       p_long_name in varchar2,
  4                                       p_tname in varchar2 ) return varchar2
  5  is
  6      l_long long;
  7  begin
  8      execute immediate
  9      'select ' || p_long_name || ' from ' || p_tname ||
 10      ' where ' || p_pk_name || ' = :x'
 11      into l_long
 12      using in p_pk_val;
 13  
 14      return substr( l_long, 1, 4000 );

 15 end;
 16 /

Function created.

ops$tkyte_at_ORA815> 
ops$tkyte_at_ORA815> column sc format a40
ops$tkyte_at_ORA815> 
ops$tkyte_at_ORA815> select constraint_name,
2         get_long( 'constraint_name', constraint_name, 'search_condition',

'user_constraints' ) sc
  3 from user_constraints
  4 where rownum < 5
  5 /

CONSTRAINT_NAME                SC
------------------------------ ----------------------------------------
SYS_C00790                     "OWNER" IS NOT NULL
SYS_C00791                     "OBJECT_NAME" IS NOT NULL
SYS_C00792                     "OBJECT_ID" IS NOT NULL
SYS_C00793                     "CREATED" 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 Tue Aug 28 2001 - 08:54:34 CDT