0

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?

1
  • You literally mean it's the 4th of 5 instances of the table name? Oracle doesn't necessarily resolve identifiers in the order in which they appear in the query so I wouldn't be at all surprised if the error was thrown on the 5th of 5 instances of the table name. Can you provide a test case that shows the error being thrown on the 4th of 5 instances? May 21, 2021 at 19:26

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.