Toad World® Forums

SQL Nav 7.2 Beta : ora-22816 unsupported feature with returning clause

Hi

I don't know if the error is related to the beta version or not, but in 6.7 it works fine.

I launch an SQL from one database to another with a link (@). When I get the record, I change a value on this record. When I commit or go to the next record, I get this oracle error : ora-22816 unsupported feature with returning clause. Exactly the same manipulation in V6.7 works fine.

I removed all trigger from the table, still the same error.

Regards

Martin

Hi Martin,

Thanks for your report this issue, we have created a new story (SQLNAV-1804) for it, if the problem has been fixed, we will notify you immediately.

Regards,

Alan

Hi Alan

Another problem related to this bug : the “remote” selected records remain locked, even if I close the statement window. I’ve to quit SQL Nav to unlock them.

Regards

Martin

Hi Martin,

Thanks for your feedback, would you like to help me to confirm the following problems please?

1.“the “remote” selected records” means to use a database link to launch a SQL statement?

2.“remain locked” means to turn off the “Updateable” option ?

besides, if it is convenient for you, please show more detailed steps to me.

Thanks and regards.

Alan

Hi Alan

1 : Yes, link with a @

2 : I didn’t turn of the “Upd opt”, but made a rollback, closed the editor.

Sample

SELECT succ, application, localite FROM s_suc_doua@pseabird_plda WHERE succ = 372
==>
SUCC APPLICATION LOCALITE ROWID*


372 CS1 WELKENRAEDT AAAr4AAAmAAAMNmAAM
372 CS2 WELKENRAEDT AAAr4AAAmAAAMNmAAN
372 CSW WELKENRAEDT AAAr4AAAmAAAMNcAAR
372 EME WELKENRAEDT AAAr4AAAmAAAMNgAAF
372 EMI WELKENRAEDT AAAr4AAAmAAAMNgAAB

I edit the first one, and make COMMIT; -> 09:11:56 ORA-22816: unsupported feature with RETURNING clause

The days before (beta 3860 ?) I do a rollback, everything seems to be OK in the editor. Impossible to edit the record directly in the destination database, it’s still locked. I closed the editor and record still locked, I had to close completely SQL Navigator to unlock the record.

Today (beta 3883 just installed) : I still get the ORA-22816 error, but when I do the ROLLBACK, record is released.

Regards

Martin

Hi Martin

According to your steps, we also can’t reproduce this problem in beta 3884, and we will continue to investigate the first problem (SQLNAV-1804).

Thanks for your prompt feedback.

Best Regards.

Alan

Hi Alan

Still have this (BIG) problem with official v 7.2.

Sample : SELECT blocking FROM prg_tests**@**pseabird WHERE dat_update >= TRUNC(SYSDATE). When I change the record an make commit : ORA-22816: unsupported feature with RETURNING clause

I have to make updates every day on several remote databases. I still have to use v6.7 to do this.

Regards

Martin

Hi Martin,

Thanks for your remind, i’m sorry about this, we will do our best to fix it in the current version (v7.3).

Thanks and regards.

Alan

Hi Alan

I was able to make an update in a related database. Didn’t see that it was solved.

The only problem I still have is : ORA-22992: cannot use LOB locators selected from remote tables, when I select records containing a clob field. Is this an Oracle limitation, or a problem in SQL Nav ?

Regards

Martin

Hi Martin,

I’m sorry to hear this, but SQLNAV-1804 (can’t update the column’s value with dblink) have been fixed in the previous beta.

The problem of “ORA-22992: cannot use LOB locators selected from remote tables” is maybe Oracle’s limitation, I finds some information about it. in “My Oracle Support” website exist the description of this problem:

“ORA-22992 When Trying To Select Lob Columns Over A Database Link [ID 119897.1]”;

"SELECT with a LOB and DBLink Returns an ORA-22992: Cannot Use LOB Locators Selected from Remote tables [ID 1234893.1], because the use of DBLinks and LOBs via the SELECT from PL/SQL is not supported. "

Besides, as a work-around, you can create a new table or materialized view in local databse by using “create table/materialized view object_name as select * from remote_table@dblink”.

I hope this can help you.

Regards,

Alan

Many thanks Alan