Re: "MINUS" Problems

From: Mike Krolewski <mkrolewski_at_rii.com>
Date: Fri, 22 Dec 2000 20:23:00 GMT
Message-ID: <920d6u$q1u$1@nnrp1.deja.com>

In article <920b2s$o6g$1_at_nnrp1.deja.com>,   wax_man_at_my-deja.com wrote:
> We are trying to compare the data in some schemas we have. The
 compare
> is being done across dblinks. Does anyone know how to get the sql to
> work when the tables have columns of type long? Everytime the sql
 hits
> a table with a long data type, we get the following errors:
> ORA-00997: illegal use of LONG datatype
> ORA-02063: preceding line from <remote_db>
>
> Here is the sql I'm running:
> select * from <table_name> minus select * from
 <table_name>@<remote_db>;
>
> Thanks for any and all help.
>
> Sent via Deja.com
> http://www.deja.com/
>

The problem is with the long not the database link....

LONG columns cannot appear in certain parts of SQL statements:

<> WHERE clauses, GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

<>  The UNIQUE operator of a SELECT statement
<>  The column list of a CREATE CLUSTER statement
<>  The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
<>  SQL functions (such as SUBSTR or INSTR)
<>  Expressions or conditions
<>  SELECT lists of queries containing GROUP BY clauses
<>  SELECT lists of subqueries or queries combined by the UNION,

INTERSECT, or MINUS set operators
<> SELECT lists of CREATE TABLE ... AS SELECT statements <> SELECT lists in subqueries in INSERT statements

Comparing longs is basically not doable in PL/SQL as the LONG in PL/SQL is 32K and in the database 2G.

The only way I was able to do this was compare the data externally through a program.

If the LONG column is not needed in the comparison, you could make the select explicit eg select column1, column2, ... , columnN from ...

By omitting the LONG, the SQL should work.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/

Received on Fri Dec 22 2000 - 14:23:00 CST