[SOLVED] ORA-00932 when using OUTER join on a view

ORA-00932 when using OUTER join on a view

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 ?

Solution

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.