Table name both exists and does not exist in the same query

oracle

I'm trying to send some data to an Oracle database. The parameterized query looks like this:

INSERT ALL
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
SELECT 1 FROM DUAL

When I run it, I get a truly bizarre error message:

Error: 942, Position: 506, Error msg: ORA-00942: table or view does not exist

Position 506 is the fourth iteration of mySchema.myTable in my query. But they're all the same table name! How is it that the first three copies of the table name exist just fine, and then the fourth one is nowhere to be found?

I know Oracle has a reputation for being difficult to deal with, but this is just straight-up insane! Has anyone seen this before, who might know how to get it working?

Best Answer

I know Oracle has a reputation for being difficult to deal with,

No comment

but this is just straight-up insane! Has anyone seen this before, who might know how to get it working?

It's working just fine. As it parses that single sql statement, it is finding multiple errors . . . multiple times it refers to a non-existent table. The error that it reports is simply the last one ...

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  SELECT 1 FROM DUAL
  4  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 2:
ORA-00942: table or view does not exist


SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  SELECT 1 FROM DUAL
  5  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 3:
ORA-00942: table or view does not exist


SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  SELECT 1 FROM DUAL
  6  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 4:
ORA-00942: table or view does not exist


SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  6  SELECT 1 FROM DUAL
  7  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 5:
ORA-00942: table or view does not exist


SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  6  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  7  SELECT 1 FROM DUAL
  8  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 6:
ORA-00942: table or view does not exist

Your reported error points, and the fact that you mention parameterized values, indicate that your actual use case is more complex than my demo, raising the possibility of there being other issues at play. But since we can only go with what we see, we can't address those potential issues.

Related Question