2 Replies Latest reply on Nov 22, 2019 10:48 AM by Buntoro

    SQL Dev 19.x problem with Code Completion cause Connection Drop

    Buntoro

      Hi,

       

      I started a new thread for the previous one is not looked into.

       

      As have been stated in several threads,

      19.2 and 19.1 Auto complete cause Connection Drop

      SQL Developer 19.2 freeze on connection

       

      Some of these issue might relate with the connection which is being dropped.

       

      I had disabled both "Enabled Completion Auto-Popup in SQL Worksheet" and "Enabled Completion Auto-Popup in PL/SQL Editor", therefore I manually trigger the completion using Ctrl + Space.

      Several people has posted the issue but seems this is not addressed yet.

      It seems any code completion which takes longer than 3 - 4 seconds to show, will cause the connection drop issue.

       

      Step to reproduce :

      1. Open a connection

      2. type

          select *

          from   bran <invoke ctrl + space>, to display list of table start with bran* (branches, brands, etc)

      4. code completion works okay, albeit the result take quite long to show, pick one table

      5. add more code

          where bran <invoke ctrl + space>, to display list of column start with bran*, but nothing showed up

      6. after several tries, I leave the code as (notice : I put semicolon to help SQL Dev understand where the line stop)

      select *

      from   branch_dimension

      where  bran;

       

      7. create another code below step 6

      select *

      from   dual;

       

      8. F9, will show

      Capture.PNG

       

       

      Environment :

      SQL Dev 19.2.1

      Java(TM) Platform 1.8.0_221

      Oracle IDE 19.2.1.247.2212

      Oracle Database 11.2.0.4 & Oracle Database 12.1.0.2

      (at first, the problem was only on 11g, but now even on 12c suffer the same problem)

       

      Regards,

       

      Buntoro

        • 1. Re: SQL Dev 19.x problem with Code Completion cause Connection Drop
          Vadim Tropashko-Oracle

          If you set the database query dictionary mode to parallel

           

          parallel.png

          then it would run on a separate connection.

           

          Another suggestion is disabling Semantic Analysis Info Tip. This would fix the Table Advisor errors that you have exhibited in the other forum thread.

           

          P.S. Please try these experiments individually, because we'll get the maximum information when knowing which one did make a difference.

          • 2. Re: SQL Dev 19.x problem with Code Completion cause Connection Drop
            Buntoro

            Hi Vadim,

             

            Sorry for not updating you early because I was quite busy.

             

            I had tried to experiment mode set to PARALLEL as your suggestion.

            After changing it, the code completion does not even work anymore. No popup ever show.

             

            I try to capture screen from View - Log - Statements below.

            Sequence 47 : show where 1=0 is generated from PARALLEL

            After I revert to UNION, it logs the correct query Sequence 48, which sometimes display correctly, but the problem on my first post still occur.

            sql auto complete.png

             

            I try to change it into PARALLEL again to make sure whether PARALLEL is working or not.

            And after several try to invoke the code using Ctrl + Space, it seems the "separate connection" cause my account to be locked because of wrong password attempt.

             

            To be honest, I gave up using this 18.x and 19.x version and went back to 17.4 which everything seems to work for me.

             

            I notice the query being invoked is different between 17.4 and 19.2.1

            19.2.1

            SELECT 'SCHEMA' type, username owner, username object_name, null column_name, null column_id, null data_type

            FROM all_users WHERE rownum <=50

            and username like ?

            union all

            SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type

            FROM all_objects

            WHERE object_type = 'TABLE' and object_name not like 'BIN$%' and rownum <=50

            and object_name like ?

            union all

            SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type

            FROM all_objects

            WHERE object_type ='VIEW' and object_name not like 'BIN$%' and rownum <=50

            and object_name like ?

            union all

            select 'TABLE' type, owner, object_name, null column_name, null column_id, null data_type

            FROM  dba_objects

            WHERE object_name like ? and object_type = 'SYNONYM'

            and rownum <=50

            and owner in (SYS_CONTEXT('USERENV','CURRENT_SCHEMA') ,'PUBLIC')

             

            17.4

            SELECT 'COLUMN' type, owner, table_name object_name, column_name, column_id, data_type

            FROM sys.all_tab_cols

            WHERE hidden_column = 'NO' and rownum <=50 and owner = ? and table_name = ?  and column_name like ?

            union all

            select case type# when 7 then 'PROCEDURE'

                           when 8 then 'FUNCTION'

                           when 9 then 'PACKAGE'

                           when 33 then 'OPERATOR'

            end type,

            ''||o.owner# owner, name object_name, null column_name, null column_id, null data_type FROM sys.obj$ o

            where name not like 'BIN%'

            and type# in (7,8,9)

            and rownum <=50

            and name like ?

            union all

            SELECT 'SEQUENCE' type, USER owner,  object_name, 'NEXTVAL' column_name, null column_id, null data_type

            FROM sys.user_objects

            WHERE object_type = 'SEQUENCE' and rownum <=50   and object_name like ?

             

            Hopefully you will find resolution for this.

             

            Regards,

             

            Buntoro