MS SQL Server - SQL2k Ora9 ORA-22816 error via OraOLEDB

Author

SQL2k Ora9 ORA-22816 error via OraOLEDB

Jos Potts

2005-06-30, 8:23 pm

In SQL2k, I have a linked server to an Ora9 db set up using the Oracle OLEDB
driver.

I get the error from a SQL Server stored procedure at the point it inserts
data into an Oracle view which has an INSTEAD OF INSERT trigger, something
like:
....
INSERT INTO LINKED_SERVER_NAME..ORACLE_USERNAME.ORACLE_VIEWNAME
(FULL_ORACLE_VIEW_CO
LUMN_LIST)
SELECT column_list
FROM @table_variable
....

The error is:
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: ORA-22816: unsupported feature with
RETURNING clause]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
IRowsetChange::Inser
tRow returned 0x80004005: ].

It might be significant that the INSTEAD OF INSERT trigger doesn't insert
data into the view's underlying table (it puts it into various other tables)
(or it might not, I don't know!).

I have looked around the web quite a bit (including Oracle Metalink) and
can't see anything which makes sense to me - I'm not using the RETURNING
clause explicitly, so I guess this is something SQL Server's asking for.

Is there some OLEDB setting which will stop it trying to use this clause? I
did see the DisableRetClause registry setting for OraOLEDB, but it's already
set to "1", and I can't find any documentation on it anyway.

Thanks, Jos.

Jos Potts

2005-06-30, 8:23 pm

I've just had another look on Oracle Metalink and have found a recorded bug
after all:
Bug# 2717859 "RECORDSET.ADDNEW THROWS ORA-22816 IF EXECUTED ON A VIEW WITH
INSTEAD-OF TRIGGER", raised on 18-Dec-2002, last updated on 27-Feb-2004.

It just about describes the problem I've got completely, but there's no fix
for it yet, so I'll have to re-engineer that bit of the app.

Thanks for your attention, Jos.

Sponsored Links