About ORA-1719 error occurring in SQL Window

[myildiz]

When OPTIMIZER_FEATURES_ENABLE value is set to 8.1.7 in Oracle 19c database, ORA-1719 error occurs when clicking anywhere in the result window of SQL Window. Is there any way to solve this problem?

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 15\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 15 directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer 15).

 

[myildiz]

Our license is for "PL/SQL Developer 14". We are experiencing the problem in "PL/SQL Developer 14".

The same problem occurs when we try it with "PL/SQL Developer 15".

I am sending the "debug.txt" file and screenshot for both versions.

The problem occurs when the optimizer is changed with the following command in "Oracle Database 19c". In "Oracle Database 11g R2", this problem does not occur.
Alter System Set Optimizer_Features_Enable = '8.1.7';

In "Oracle Database 19c", the problem disappears when the optimizer change is rolled back.
Alter System Set Optimizer_Features_Enable = '19.1.0';

You can download "debug.txt" files and screenshots from this link.
https://go.wetransfer.com/t-D3bp8ypTHt

According to the "debug.txt" file, the problem occurs when the following SQL is running. I think this SQL works when the grid is focused or clicked. When I try to run this SQL, there is a problem with the " (+) in " part.

select col.*, com.Comments
from sys.all_tab_columns col,
sys.all_col_comments com
where col.owner = 'SYS'
and col.table_name = 'USER_TABLES'
and com.Owner (+) = 'SYS'
and com.Table_Name (+) = 'USER_TABLES'
and com.Column_Name (+) = col.Column_Name
and com.origin_con_id (+) in (1, sys_context('userenv', 'con_id'))
order by col.column_id

 

[myildiz]

I tried SQL for all possible OPTIMIZER_FEATURES_ENABLE values according to Oracle's 19c documentation.

Doesn't work for 8.0.0 through 10.2.0.4 .
Works for 10.2.0.5 to 19.1.0
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_FEATURES_ENABLE.html

----------------------------------------------------------------------------------------------------
Alter System Set Optimizer_Features_Enable = ' 8.0.0 ';
Alter System Set Optimizer_Features_Enable = ' 8.0.3 ';
Alter System Set Optimizer_Features_Enable = ' 8.0.4 ';
Alter System Set Optimizer_Features_Enable = ' 8.0.5 ';
Alter System Set Optimizer_Features_Enable = ' 8.0.6 ';
Alter System Set Optimizer_Features_Enable = ' 8.0.7 ';
Alter System Set Optimizer_Features_Enable = ' 8.1.0 ';
Alter System Set Optimizer_Features_Enable = ' 8.1.3 ';
Alter System Set Optimizer_Features_Enable = ' 8.1.4 ';
Alter System Set Optimizer_Features_Enable = ' 8.1.5 ';
Alter System Set Optimizer_Features_Enable = ' 8.1.6 ';
Alter System Set Optimizer_Features_Enable = ' 8.1.7 ';
Alter System Set Optimizer_Features_Enable = ' 9.0.0 ';
Alter System Set Optimizer_Features_Enable = ' 9.0.1 ';
Alter System Set Optimizer_Features_Enable = ' 9.2.0 ';
Alter System Set Optimizer_Features_Enable = ' 9.2.0.8 ';
Alter System Set Optimizer_Features_Enable = ' 10.1.0 ';
Alter System Set Optimizer_Features_Enable = ' 10.1.0.3 ';
Alter System Set Optimizer_Features_Enable = ' 10.1.0.4 ';
Alter System Set Optimizer_Features_Enable = ' 10.1.0.5 ';
Alter System Set Optimizer_Features_Enable = ' 10.2.0.1 ';
Alter System Set Optimizer_Features_Enable = ' 10.2.0.2 ';
Alter System Set Optimizer_Features_Enable = ' 10.2.0.3 ';
Alter System Set Optimizer_Features_Enable = ' 10.2.0.4 ';

Select Col.Column_Name, Com.Comments
From Sys.All_Tab_Columns Col,
Sys.All_Col_Comments Com
Where Col.Owner = 'SYS'
And Col.Table_Name = 'USER_TABLES'
And Col.Column_Name = 'TABLE_NAME'
And Com.Owner (+) = 'SYS'
And Com.Table_Name (+) = 'USER_TABLES'
And Com.Column_Name (+) = Col.Column_Name
And Com.Origin_Con_Id (+) In (1, Sys_Context('userenv', 'con_id'))
Order By Col.Column_Id;

ORA-01719: outer join operator (+) not allowed in operand of OR or IN
----------------------------------------------------------------------------------------------------
Alter System Set Optimizer_Features_Enable = ' 10.2.0.5 ';
Alter System Set Optimizer_Features_Enable = ' 11.1.0.6 ';
Alter System Set Optimizer_Features_Enable = ' 11.1.0.7 ';
Alter System Set Optimizer_Features_Enable = ' 11.2.0.1 ';
Alter System Set Optimizer_Features_Enable = ' 11.2.0.2 ';
Alter System Set Optimizer_Features_Enable = ' 11.2.0.3 ';
Alter System Set Optimizer_Features_Enable = ' 11.2.0.4 ';
Alter System Set Optimizer_Features_Enable = ' 12.1.0.1 ';
Alter System Set Optimizer_Features_Enable = ' 12.1.0.2 ';
Alter System Set Optimizer_Features_Enable = ' 12.2.0.1 ';
Alter System Set Optimizer_Features_Enable = ' 18.1.0 ';
Alter System Set Optimizer_Features_Enable = ' 19.1.0 ';

Select Col.Column_Name, Com.Comments
From Sys.All_Tab_Columns Col,
Sys.All_Col_Comments Com
Where Col.Owner = 'SYS'
And Col.Table_Name = 'USER_TABLES'
And Col.Column_Name = 'TABLE_NAME'
And Com.Owner (+) = 'SYS'
And Com.Table_Name (+) = 'USER_TABLES'
And Com.Column_Name (+) = Col.Column_Name
And Com.Origin_Con_Id (+) In (1, Sys_Context('userenv', 'con_id'))
Order By Col.Column_Id;

COLUMN_NAME COMMENTS
-------------------- --------------------
TABLE_NAME Name of the table
----------------------------------------------------------------------------------------------------

 

[myildiz]

Changing the SQL that runs when focused on the result grid as follows will solve the problem.

Select Col.*,
(Select Max(Com.Comments)
From Sys.All_Col_Comments Com
Where Com.Owner = Col.Owner
And Com.Table_Name = Col.Table_Name
And Com.Column_Name = Col.Column_Name
And Com.Origin_Con_Id In (1, Sys_Context('userenv', 'con_id'))) Comments
From Sys.All_Tab_Columns Col
Where Col.Owner = 'SYS'
And Col.Table_Name = 'USER_TABLES'
Order By Col.Column_Id

 

[Marco Kalter]

You are probably running into Oracle bug 6610822. It is fixed in recent database versions, but maybe the fix is not enabled. To verify, can you connect as SYS and execute the following statement?

alter system set "_fix_control"='6610822:ON'

This enables the fix for Oracle bug 6610822. Restart PL/SQL Developer and try the operation again.

 

[myildiz]

Thanks. Alter System Set "_fix_control"='6610822:ON'; it worked.