0

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 ?

AKS
  • 81
  • 6
  • 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. –  Jun 20 '12 at 13:08
  • 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 Jun 20 '12 at 13:12
  • I've tried using the explicit LEFT OUTER JOIN syntax but the results are the same. – AKS Jun 20 '12 at 13:19
  • 2
    The comment about the `JOIN` syntax wasn't meant as a fix for your problem, it's meant as a general "good advice". –  Jun 20 '12 at 13:21

1 Answers1

1

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.

AKS
  • 81
  • 6