Re: How to search on LONG datatype column
- From: Sami Seerangan <dba.orcl@xxxxxxxxx>
- To: Connor McDonald <hamcdc@xxxxxxxxxxx>
- Date: Wed, 27 Oct 2004 09:06:29 -0400
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@xxxxxxxxxxx> 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%'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
