Re: HELP !: how concat/select a LONG field ??

monsri_at_my-deja.com wrote:
>
> Hi,
> I'm trying to dynamically create a file containing all my constraints,
> but I've been stalling against the SEARCH_CONDITION LONG field of
> the DBA_CONSTRAINTS table. I can't concatenate it to the string
> 'add check ' for example, and when I do the following:
>
> > select 'alter table &&1'||chr(10)||
> > 'add check (',
> > search_condition,
> > ');'
> > from DBA_CONSTRAINTS a,
> > DBA_CONS_COLUMNS b
> > where a.TABLE_NAME = '&&1'
> > and b.TABLE_NAME = '&&1'
> > and a.constraint_name = b.constraint_name
> > and a.constraint_type = 'C';
>
> I get something very funny: Oracle obsviously gets confused and I'm
> returned a messy SQL statement, looking like:
>
> > alter table MY_TABLE STCAS = 'O' OR STCAS = 'C'
> > add check (
>
> (the string "STCAS = 'O' OR STCAS = 'C'" being the contents of my
> SEARCH_CONDITION field !)
> !!! Anybody can explain me this behaviour ? And does someone think
> I can solve my problem one way or another ????
>
> Thanks a lot !
> Seb
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

The 'easy' way to deal with LONG's is to use PL/SQL to select them into a VARCHAR2(32767) variable. As of 8i, you can use the TO_LOB feature to change them to LOB's which you can then use the flexibility of the DBMS_LOB package.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse