ORA-00932 when using OUTER join on a view

Question

My simplified query is as follows:

INSERT INTO table1
(acct_no, name, description)
SELECT a.acct_no,
 b.name,
 TO_LOB(c.description)
FROM tableA a, viewB b, tableC c
WHERE a.person_id = b.person_id(+)
AND a.person_id = c.person_id;

Notes: I am using Oracle 10g, table1.description is of type LOB, c.description is of type LONG, viewB is a view, table1.name is of the same datatype as b.name

The above query returns:

SQL Error: ORA-00932: inconsistent datatypes: expected - got LONG 00932. 00000 - "inconsistent datatypes: expected %s got %s"

The error points to the line where I use the TO_LOB function.

However, if I remove the Outer join, it works fine. That is, the following works:

INSERT INTO table1
(acct_no, name, description)
SELECT a.acct_no,
 b.name,
 TO_LOB(c.description)
FROM tableA a, viewB b, tableC c
WHERE a.person_id = b.person_id
AND a.person_id = c.person_id;

But I do need to use the Outer join and I cannot understand why using the Outer join on viewB causes an inconsistent datatype error on a field in another table(tableC).

Basically, the TO_LOB() on its own (without the Outer join) works and the Outer join on its own works too, but when both are included in the SQL, it gives an error in an unlikely place.

Any ideas ?

What datatype is tablec.columnc? (Btw. obfuscating table and column names isn't really helpful). And you should really using explicit JOIN syntax not implicit joins in the where clause. — a_horse_with_no_name
Sorry about that. There are 26 columns and 9 tables in my original query, hence I thought it was easier to illustrate a simplied SQL. c.columnC is of type LONG as stated in the Notes — AKS
I've tried using the explicit LEFT OUTER JOIN syntax but the results are the same. — AKS
The comment about the JOIN syntax wasn't meant as a fix for your problem, it's meant as a general "good advice". — a_horse_with_no_name
Just updated the original question with more meaningful column names — AKS

AKS AKS · Accepted Answer · 2012-06-21T10:20:18

This issue could possibly be due to an Oracle bug as pointed out at the bottom of the following link: http://www.dba-oracle.com/sf_ora_00932_inconsistent_datatypes_expected_string_got_string.htm

In my case, I employed a work-around of avoiding the use of the view and instead used tables directly and it worked.

ORA-00932 when using OUTER join on a view

1 Answers