Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to search on LONG datatype column

Re: How to search on LONG datatype column

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Wed, 27 Oct 2004 09:06:29 -0400
Message-ID: <f09dd6280410270606793fd55b@mail.gmail.com>


Thank you so much for all who responded.

Finally I am able to drop SYS generated constraints based on serach condition. I just modified RICH's function into anonymous PL/SQL.

declare
l VARCHAR2(32767);
cn VARCHAR2(100);
BEGIN
for c0rec in (
 SELECT constraint_name,search_condition  FROM sys.user_constraints
 WHERE table_name='TABLE_NAME'
 and owner='TABLE_OWNER'
 and constraint_type='C')
loop
l :=c0rec.search_condition;
cn :=c0rec.constraint_name;
if substr(l,1,12) ='My_Search_Condition_goes_here' then dbms_output.put_line('constraint name '||cn); dbms_output.put_line('search condition '||l); execute immediate('alter table crmf.logondetails drop constraint '||cn); end if;
end loop;
END;
/

On Wed, 27 Oct 2004 08:23:58 +0100 (BST), Connor McDonald <hamcdc_at_yahoo.co.uk> wrote:
> If your condition is less than 32k (which I presume is likely), then you can use PL/SQL
>
> function X(c varchar2) return varchar2 is
> l varchar2(32767);
> begin
> select search_cond
> into l
> from xxx_constraints
> where constraint_name = c;
> return l;
> end;
>
> then
>
> select ...
> from dba_constraints
> where X(constraint_name) like '%LogOffMode%'

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 27 2004 - 08:02:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US