14 Replies Latest reply on Oct 4, 2017 8:40 AM by Pavel_p

    Completion insight - how to get parameters list

    Pavel_p

      Hi,

      please, how to get parameters for a given function? Let's have the following simple anonymous block

      declare
        l_vc2   varchar2( 100 );
      begin
        dbms_output.put_line(--here it shows the suggestion correctly
          a   => ' hi '
        );
        select apex_item.text(/* here i would like to get the list of parameters */ 1, 'text' )
        into
          l_vc2 --here it ideally should suggest the l_vc2 variable (sadly no suggestion at all)
        from dual;
      
      end;
      

      If I start typing and press CTRL+Space to get parameters for dbms_output, it suggests parameter(s) correctly

      correct_suggestion.jpg

      however if I start typing sys.dbms_output.put_line([CTRL+Space], l_vc2 variable is automatically inserted (don't know why) and also I'm getting the following (quite bizarre) suggestion.

      sys_dbms_output.jpg

      But the real magic begins when I start typing select apex_item.text(

      with_lvc2.jpg

      and what is even more surprising, the suggestion is completely different if the l_vc2 variable is declared or not.

      without_lvc2.jpg

      Please, is there any way how to consistently get function/procedure parameters and insert them into the editor?

      We're getting tons of new features (I've never used and will never use) with every new version but I would definitely prefer if the "basic ones" like code completion and formatting worked consistently.

      Thanks a lot

      Pavel

       

      edit:  Version 17.2.0.188

        • 1. Re: Completion insight - how to get parameters list
          thatJeffSmith-Oracle

          We're getting tons of new features (I've never used and will never use) with every new version but I would definitely prefer if the "basic ones" like code completion and formatting worked consistently.

           

          I would prefer and demand that all the features work. That's our goal.

           

          I just tested this with HR.ADD_JOB_HISTORY() and was able to get consistent parameter completion, no problems in v17.2. I don't have APEX_ITEM in my install to test with.

          • 2. Re: Completion insight - how to get parameters list
            Pavel_p

            Hi Jeff,

            thanks for your response. Yes, it would be certainly nice if everything worked but we don't live in an ideal world. I think you don't have to test necessarily on APEX packages (however they are even more "complicated" because there is a synonym for the package and code completion automatically renames them from apex_whatever to their "real" name, e.g. htmldb_whatever). On a simple procedure/function the completion seems to be working as expected but not on functions/procedures encapsulated in packages (just my suspicion).

            Regards,

            Pavel

            • 3. Re: Completion insight - how to get parameters list
              thatJeffSmith-Oracle

              for package proc i see it pulling up the full list of parameters with assignments and not the list of individual ones to choose from ... I'm in a dev build so I'm looking 'into the future', will drop the dev a note.

              1 person found this helpful
              • 4. Re: Completion insight - how to get parameters list
                Vadim Tropashko-Oracle

                I can reproduce one issue:

                 

                select dbms_output.get_lines( --<-- fails to prompt argument list here

                ) from dual;

                 

                The local variable within the INTO clause is fine:

                 

                Bug 26759591.png

                 

                (The DUMMY column from dual should not be listed which is a minor bug).

                1 person found this helpful
                • 5. Re: Completion insight - how to get parameters list
                  Pavel_p

                  Guys, thank you very much for investigating these issues. First of all, let me tell you that you're doing a great job with SQL Developer. I've always wanted to have something that runs on any platform, offers all the nice things like code completion and insights, debugging etc. and also supports Emacs keyboard bindings (well, sort of because the only software that implements properly Emacs keyboard shortcuts is Emacs itself, but with a little customization it is decent). Something like NetBeans, which is for me the top-notch IDE, something how things should look like (just my personal opinion ofc). And SQL Developer is pretty close to this ideal. And what is even better, it's for free. I used most of the commonly known IDEs for PL/SQL development, I even own a valid license for one of them but with SQL Developer I can type very efficiently without using the mouse and arrows, which makes it my favorite IDE (by far). Sadly if it comes to code insight and formatting, any of these IDEs were better.

                  I thought there might be something wrong with my environment, so I downloaded and installed a clean VM (Win10 since for windows SQL Developer comes with the JDK) and tried a fresh copy of SQL Developer. The same story again - instead of apex_item.text input parameters is being suggested DUMMY and l_vc2 but the cursor is on the opening bracket and if I have the cursor where is "x" on Vadim's snapshot and I type l_[CTRL+Space], no suggestions at all.

                  apitem_text.jpg

                  Regards,

                  Pavel

                  • 6. Re: Completion insight - how to get parameters list
                    Pavel_p

                    Hello again,

                    sorry for bringing up this thread...

                    Please, is the parameter list supposed to work in PL/SQL editor while editing packege bodies? I'm sometimes able to get the parameter list in anonymous block, but unfortunately never when editing package bodies, even if the function/procedure is defined in the same package.

                    Thank you,

                    Pavel

                    • 7. Re: Completion insight - how to get parameters list
                      Vadim Tropashko-Oracle

                      If procedure is not prefixed with package name, it fails to work:

                       

                      proc arguments.png

                       

                      which can be considered as a bug. The other possible reason is too many syntactic errors so that completion insight fails to error recover. For example, drop the ");" after "ptest(" and then the insight above wont work anymore.

                      1 person found this helpful
                      • 8. Re: Completion insight - how to get parameters list
                        Pavel_p

                        Vadim,

                        thank you for taking care of this. Of course I immediately tried your suggestion since it could be taken as a reasonable workaround to write package_name.proc_name and then eventually delete the package body, but I experienced some strange behaviour. First time I tried without the package name and got exactly the result you described, second time I tried with the package name and was able to successfully pull out the parameter list (pure happiness). So I compiled the package, got back to the function call, deleted parameters and tried to get the list again, unfortunately this time with the following result.

                        insight2.jpg

                        I have no idea what can be possibly wrong, no matter if there is the closing bracket and/or the semicolon, still getting these suggestions and I'm not able to pull out the parameter list anymore no matter what I try. Please, can it have something in common with my all-lower-case settings and Emacs keyboard shortcuts?

                        Thanks a lot,

                        Pavel

                        • 9. Re: Completion insight - how to get parameters list
                          Vadim Tropashko-Oracle

                          I would suspect dictionary query performance problem. Do you see

                           

                          SELECT 'ARGUMENT' type, null owner,  a.owner||'.'||a.package_name||'.'||a.object_name||'.'||a.overload object_name,  a.argument_name||'=>'||substr(a.argument_name,1,1)||'/*'||a.data_type||'*/' column_name, a.position column_id, null data_type

                          FROM all_arguments a, all_objects o

                          WHERE a.object_id=o.object_id and rownum <=50 and    argument_name is not null

                          and (o.object_name = ? and a.package_name = ? or o.owner=?)  and o.object_name = ? and a.object_name = ?

                           

                          in the log? What is the elapsed time?

                          1 person found this helpful
                          • 10. Re: Completion insight - how to get parameters list
                            Pavel_p

                            Hi Vadim,

                            I'm not sure if I did everything properly to enable debugging and if I look at the right place. I followed this http://www.thatjeffsmith.com/archive/2012/10/using-debug-mode-in-oracle-sql-developer-to-log-sql/ blogpost and set IncludeConfFile sqldeveloper-debug.conf in the sqldeveloper.conf file. Something definitely happened as I'm getting tons of log messages but I see there no executed select statements at all. If I hit the CTRL+Space, only these two messages are added to the window

                            FINEST    1427    0    oracle.ide.db.execute.DBRequestProcessor$DBRunnable    Comparing COMPLETION_TEST Body subprograms finished.

                            FINEST    1426    0    oracle.ide.db.execute.DBRequestProcessor$DBRunnable    Comparing COMPLETION_TEST Body subprograms completed successfully.

                            Just to add... I'm using for this testing 11g XE (SSH connection type), so I tried also on the developer VM Developer Day - Hands-on Database Application Development . My SQLDev version is 17.2.0.188 and there are two tabs - Messages and Logging page while on the VM there is preinstalled 4.2.0.17.089 and its tabs are Messages and Statements. Was the Statements tab just renamed or did I accidentally close it and I'm not able to reopen it.

                            However when I try to run it in the preconfigured VM, I'm getting this select

                            SELECT 'ARGUMENT' type, null owner,  a.owner||'.'||a.package_name||'.'||a.object_name||'.'||a.overload object_name,  a.argument_name||'=>'||substr(a.argument_name,1,1)||'/*'||a.data_type||'*/' column_name, a.position column_id, null data_type
                            FROM all_arguments a, all_objects o
                            WHERE a.object_id=o.object_id and rownum <=50 and    argument_name is not null
                            and o.object_name = ?
                            

                            but the completion insight is exactly the same like shown on the picture above (this time taken from the VM, 4.2.0.17.089).

                            insight3.jpg

                            Regards,

                            Pavel

                            • 11. Re: Completion insight - how to get parameters list
                              Vadim Tropashko-Oracle

                              Hi Pavel,

                               

                              Thank you for helping with test case. Let's try to restore your 17.2 environment, first. It looks like the Statement tab has been accidentally closed. However, I don't have trouble getting it back if I restart sqldeveloper. All I need to do is invoking SQLWorsheet for some connection.

                              If the statement tab is absent, this state must be persisted somewhere in

                              C:\Users\UserName\AppData\Roaming\SQL Developer\system17.2.0.184.1501

                              If you don't mind nuking your settings, you can try progressively deleting

                              1. C:\Users\UserName\AppData\Roaming\SQL Developer\system17.2.0.184.1501\o.ide.13.0.0.1.42.170225.201\settings.xml

                              2. C:\Users\UserName\AppData\Roaming\SQL Developer\system17.2.0.184.1501\o.ide.13.0.0.1.42.170225.201 - directory

                              3.  C:\Users\UserName\AppData\Roaming\SQL Developer\system17.2.0.184.1501\ - directory

                              The step #3 is the most drastic and should work, but perhaps deleting a file or two in o.ide.13.0.0.1.42.170225.201 would do.

                               

                              From your 4.2 statement log it seems that completion insight fails to see the fully qualified call on the next line, and somehow grabs procedure name from the previous line. I can't reproduce that.

                               

                              To simplify troubleshooting, you can try the simple anonymous block

                               

                              BEGIN

                                      --PUT_LINE(SQLERRM);

                                      DBMS_OUTPUT.PUT_LINE(SQLERRM);

                              END;

                               

                              in SQL Worksheet with variations of package and function names? (Because, there is no difference how completion insight works in worksheet and pl/sql editor). If this code snippet works, but more sophisticated doesn't, can you please exhibit that failing code sample?

                              1 person found this helpful
                              • 12. Re: Completion insight - how to get parameters list
                                Pavel_p

                                Hi Vadim,

                                thanks a lot again and let's make some more testing.

                                As you suggested, I deleted files/folders in the following order with following results.

                                deleted settings.xml - there remained only Messages - Log tab, the Logging Page tab is gone

                                deleted o.ide.13.0.0.1.42.170225.201 - directory - my keyboard shortcuts are gone, the log window is closed and if I recall it (View => Log), the log window opens but only with Messages - Log tab

                                deleted the entire directory, SQLDev wants to import preferences, so obviously it's clean - still the same

                                deleted both SQL Developer and sqldeveloper directories and finally I have 3 tabs - Messages, Logging Page and Statetements. Hallelujah!

                                 

                                Now let's try the first testcase you suggested:

                                BEGIN
                                
                                        --PUT_LINE(SQLERRM);
                                    dbms_output.put_line(
                                    /*ctrl+space and it's here, pure beauty :-) */  A=>A/*VARCHAR2*/
                                    );
                                END;
                                

                                So it works exactly as expected.

                                 

                                Now my (previously compiled) package

                                 

                                create or replace package completion_test as
                                  function bugtest ( p1 number, p2 varchar2 ) return number;
                                
                                end completion_test;
                                
                                create or replace package body completion_test as
                                
                                  function bugtest ( p1 number, p2 varchar2 ) return number as
                                    l_ret  number;
                                  begin
                                    l_ret := bugtest( 1, 'abc' );
                                    --l_ret := completion_test.bugtest();
                                    l_ret := completion_test.bugtest(1,'def');
                                  end bugtest;
                                
                                end completion_test;
                                

                                 

                                My etitor is suddenly somehow more "alive", when I hold the mouse cursor above the l_ret variable, I'm getting a tooltip l_ret in package etc... (happened to me for the first time, nice feature I was not aware of - probably did not work before).

                                However if I go to the statement l_ret := completion_test.bugtest(1,'def'); and delete parameters 1,'def' and hit CTRL+Space between empty brackets, I'm still getting the same code insight as on the snapshot in my previous posts and the Statements Log line looks like this:

                                90    centex_testing_lan    0    SELECT 'ARGUMENT' type, null owner,  a.owner||'.'||a.package_name||'.'||a.object_name||'.'||a.overload object_name,  a.argument_name||'=>'||substr(a.argument_name,1,1)||'/*'||a.data_type||'*/' column_name, a.position column_id, null data_type

                                FROM all_arguments a, all_objects o

                                WHERE a.object_id=o.object_id and rownum <=50 and    argument_name is not null

                                and o.object_name = ?    1="BUGTEST"

                                 

                                 

                                and if I replace the ? parameter with 'BUGTEST', so the select looks like this

                                SELECT 'ARGUMENT' type, null owner,  a.owner||'.'||a.package_name||'.'||a.object_name||'.'||a.overload object_name,  a.argument_name||'=>'||substr(a.argument_name,1,1)||'/*'||a.data_type||'*/' column_name, a.position column_id, null data_type
                                FROM all_arguments a, all_objects o
                                WHERE a.object_id=o.object_id and rownum <=50 and    argument_name is not null
                                and o.object_name ='BUGTEST';
                                

                                it returns no rows, which is not surprising as the following select returns no rows as well.

                                 

                                select * from all_objects where object_name = 'BUGTEST';
                                

                                 

                                If I do the same in the simple case with dbms_output.put_line, the following select is being executed

                                SELECT 'ARGUMENT' type, null owner,  a.owner||'.'||a.package_name||'.'||a.object_name||'.'||a.overload object_name,  a.argument_name||'=>'||substr(a.argument_name,1,1)||'/*'||a.data_type||'*/' column_name, a.position column_id, null data_type

                                FROM all_arguments a, all_objects o

                                WHERE a.object_id=o.object_id and rownum <=50 and    argument_name is not null

                                and (o.object_name = ? and a.package_name = ? or o.owner=?)  and o.object_name = ? and a.object_name = ? 

                                 

                                with these parameters

                                 

                                1="DBMS_OUTPUT", 2="DBMS_OUTPUT", 3="DBMS_OUTPUT", 4="DBMS_OUTPUT", 5="PUT_LINE"

                                 

                                and after parameter replacement the select

                                SELECT 'ARGUMENT' type, null owner,  a.owner||'.'||a.package_name||'.'||a.object_name||'.'||a.overload object_name,  a.argument_name||'=>'||substr(a.argument_name,1,1)||'/*'||a.data_type||'*/' column_name, a.position column_id, null data_type
                                FROM all_arguments a, all_objects o
                                WHERE a.object_id=o.object_id and rownum <=50 and    argument_name is not null
                                and (o.object_name = 'DBMS_OUTPUT' and a.package_name = 'DBMS_OUTPUT' or o.owner='DBMS_OUTPUT')  and o.object_name = 'DBMS_OUTPUT' and a.object_name = 'PUT_LINE';
                                

                                 

                                returns one row, so that's the difference why we're getting the correct insight in this case.

                                I can repeat it pretty consistently on my laptop (Win10) and verified on absolutely clean Linux VM with clean SQL Developer installation, so hopefully you'll be able to figure out what's wrong.

                                Thanks a lot again,

                                Pavel

                                • 13. Re: Completion insight - how to get parameters list
                                  Vadim Tropashko-Oracle

                                  I have reproduced it in 17.2.0.188:

                                  17.2 completion.png

                                   

                                  However, it works fine in 17.3 (available for download) -- this might explain why it took so long to figure this out!

                                  • 14. Re: Completion insight - how to get parameters list
                                    Pavel_p

                                    Vadim,

                                    thanks a lot for your patience, it works as expected in 17.3. I did not notice there was a new version available (well, maybe was not at the time I created this thread).

                                    Also my SQL tab is back, there are some more formatter options...pure beauty.

                                    Have a nice day,

                                    Pavel