How do you concatenate LONG datatype with character??

[Alistair Thomson]

Hi

I'm trying to create a script to help rebuild all constraints on my
tables and have hit a problem with the field SEARCH_CONDITIONS in the
user_constraints table. When I run my script I get the error

ERROR at line 1:
ORA-00932: inconsistent datatypes

My script is:

SELECT
'alter table '
||table_name
||' add (constraint '
||constraint_name
||' check('
||search_condition
||'));'
from user_constraints
where constraint_type ='C';

If anyone has any ideas how I can get round this I'd be grateful for the
advice.

Thanks

Alistair...@sphinxcst.co.uk

[Thomas Kyte]

A copy of this was sent to Alistair Thomson <alistair...@spinxcst.co.uk>
(if that email address didn't require changing)

On Mon, 30 Mar 1998 16:56:37 +0100, you wrote:

>Hi
>
>I'm trying to create a script to help rebuild all constraints on my
>tables and have hit a problem with the field SEARCH_CONDITIONS in the
>user_constraints table. When I run my script I get the error
>
>ERROR at line 1:
>ORA-00932: inconsistent datatypes
>
>My script is:
>
>SELECT
> 'alter table '
> ||table_name
> ||' add (constraint '
> ||constraint_name
> ||' check('
> ||search_condition
> ||'));'
>from user_constraints
>where constraint_type ='C';
>

Do it in pl/sql instead, for example:

set serveroutput on
set feedback off
begin
for x in ( select table_name, constraint_name, search_condition
from user_constraints where constraint_type = 'C' ) loop
dbms_output.put_line( 'alter table ' || x.table_name );
dbms_output.put_line( 'add ( constraint '||x.constraint_name||' check(');
dbms_output.put_line( x.search_condition || '));' );
end loop;
end;

pl/sql can deal with longs <32k in size.

>If anyone has any ideas how I can get round this I'd be grateful for the
>advice.
>
>Thanks
>
>Alistair...@sphinxcst.co.uk

Thomas Kyte
tk...@us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you. Any bounced
email will be treated the same way i treat SPAM-- I delete it.