Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to search on LONG datatype column
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-lReceived on Wed Oct 27 2004 - 08:02:26 CDT
![]() |
![]() |