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

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/04/13
Message-ID: <8d4s1n$mto$1@nnrp1.deja.com>#1/1

In article <8d4pae$jnu$1_at_nnrp1.deja.com>,   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
>

I do not have the logic to compare against constrained columns but here is some sql that generates check constraints that you could modify:

column col1 format a80;
column col2 format a80;
column col3 format a80;
column col4 format a80;
column col5 format a80;

set heading off
spool all_cons3.lst

SELECT rpad('alter table '||

         lower(owner)||'.'||
         dc.table_name,78,' ') col1,
         rpad(' add constraint '||dc.constraint_name,78,' ') col2,
         '  check (' col3,
         dc.search_condition col4,
         ');' col5
FROM     sys.dba_constraints dc
WHERE  ( dc.owner = 'JIT'

   or dc.owner = 'EDSJIT'
   or dc.owner = 'WAR'

       )
and      dc.constraint_type = 'C'
and      dc.constraint_name not like 'SYS_C%'

order by dc.constraint_name;
spool off

The output looks like:
 alter table edsjit.ORA_IMS_REFERENCE
 add constraint ORA_IMS_REF_IMS_PARM_DISP_CHK   check (
ims_parm_disp >= 0 and ims_parm_disp < 1912 );

Obviously you would want to make a couple of changes such as the owner names and if you allow or have system named constraints to contend with.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Apr 13 2000 - 00:00:00 CDT