We have a PHP website that makes an update to our AS400 database via a Public Database Link in our Oracle database. The site has worked for years. Several months ago we upgraded our AS400 and a new database link was created. Now the update command no longer works.
I checked the obvious (to me) things like permissions and everything appears in working order. I can run the command directly on the AS400 without issue. So, I opened up sqldeveloper and tried to run the command there. The same error occurs. Here's a sample of what I'm trying to do:
UPDATE SCHEMA.TABLE@AS400
SET VALUE1 = 'SOME STRING',
VALUE2 = 'ANOTHER STRING',
VALUE3 = 1234,
VALUE4 = 12345
WHERE FIELD1 = 54321
AND FIELD2 = 'DD'
AND FIELD3 = 0
Obviously that's not the exact query, but you get the idea. When I attempt to run the query in sqldeveloper, I get the following error:
SQL Error: ORA-02070: database AS400 does not support some function in this context
02070. 00000 - "database %s%s does not support %s in this context"
*Cause: The remote database does not support the named capability in
the context in which it is used.
*Action: Simplify the SQL statement.
Not a lot of help (in my opinion). What named capability are we talking about here? So, I took the suggested action to heart and I simplified the statement to:
UPDATE SCHEMA.TABLE@AS400
SET VALUE1 = 'SOME STRING'
WHERE FIELD1 = 54321
This time the statement worked without issue. Interesting, so I added another piece to the update:
UPDATE SCHEMA.TABLE@AS400
SET VALUE1 = 'SOME STRING',
VALUE2 = 'ANOTHER STRING'
WHERE FIELD1 = 54321
That resulted in the same error. Any combinations of multiple "VALUES" and/or "FIELDS" gave me the error. If I limited myself to a single "VALUE" and "FIELD", then statement worked just fine.
So, obviously, the database link is valid because I can insert/update/query, but something about updating multiple fields at the same time or using multiple fields in the update's where clause is giving me issue.
Now the worst part is that our Oracle DBA is no longer with the company and I'm just a lowly application developer. So, I'm banging my head against this with no progress. Any assistance you can give would be greatly appreciated.
Thanks in advance for your help.