10 Replies Latest reply: Feb 20, 2013 8:54 AM by Turloch O'Tierney RSS

    Prompt for bind variables in SQL developer

    893982
      Hi!

      Just installed the latest 3.2 version of SQL Developer, and tried to do an explain plan.
      Now the tool prompts me for values for the bind variable. It didn't do that in my previous version (version 3. something).

      Thats anoying :-)

      How do I disable that feature?

      Example:
      explain plan for
      select * from emp
      where department_id = :1;

      If i right click on the statement or press F10, I get no prompt for the bind variable...

      Regards
      Søren
        • 1. Re: Prompt for bind variables in SQL developer
          Jeff Smith Sqldev Pm-Oracle
          Remove 'explain plan for' syntax, and just use the Worksheet 'Explain Plan' toolbar button or use the F5/Run Script button to emulate SQL*Plus behavior
          • 2. Re: Prompt for bind variables in SQL developer
            893982
            I know of that way to get the plan, without being prompted for the binds.

            However we prefer the old fashioned way.

            I noticed that
            set define off;
            immediately before the statement, disables the parsing of the binds, and I am happy.

            ex.
            set define off;
            explain plan for
            select * from emp
            where department_id = :1;


            But... then I have to remember 'set define off;' for every statement.

            This was not necessary prior to this version of the tool. Is there somewhere I can configure this behavior?

            Regards
            Søren
            • 3. Re: Prompt for bind variables in SQL developer
              Turloch O'Tierney
              Hi Søren,

              For me: Run Script f5 does not prompt and run statement (Ctrl-enter) does.

              Turloch
              -SQLDeveloper Team
              • 4. Re: Prompt for bind variables in SQL developer
                893982
                Pressing F5 executes everything else in the editor, making it an unusable option...

                I would like to be able to execute one single statement without being prompted for the binds. Without having to use 'set define off'.

                Is this behavior a bug or something that is intended?

                Regards Søren
                • 5. Re: Prompt for bind variables in SQL developer
                  Jeff Smith Sqldev Pm-Oracle
                  How would we execute the statement if it had binds but no values supplied? Just trying to figure out exactly what you're looking for.

                  You can execute only what you want executed with F5 if you highlight the code first with your mouse.
                  • 6. Re: Prompt for bind variables in SQL developer
                    893982
                    I'm trying to do an explain plan the old fashioned way :-)


                    explain plan for
                    select * from emp
                    where department_id = :1;

                    If i right click on the statement or press F10 to get the plan, I get no prompt for the bind variable...
                    But the plan looks different than what
                    select * from table (dbms_xplan.display(null, null, 'BASIC COST PREDICATE'));
                    • 7. Re: Prompt for bind variables in SQL developer
                      Jeff Smith Sqldev Pm-Oracle
                      Old fashioned way, like this:

                      EXPLAIN PLAN FOR
                      SELECT * FROM scott.emp
                      where deptno = :1;

                      SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

                      Executed with F5 gives me this output

                      plan FOR succeeded.
                      PLAN_TABLE_OUTPUT
                      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      Plan hash value: 3956160932

                      --------------------------------------------------------------------------
                      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                      --------------------------------------------------------------------------
                      | 0 | SELECT STATEMENT | | 5 | 190 | 3 (0)| 00:00:01 |
                      |* 1 | TABLE ACCESS FULL| EMP | 5 | 190 | 3 (0)| 00:00:01 |
                      --------------------------------------------------------------------------

                      Predicate Information (identified by operation id):
                      ---------------------------------------------------

                      1 - filter("DEPTNO"=TO_NUMBER(:1))

                      13 rows selected
                      • 8. Re: Prompt for bind variables in SQL developer
                        893982
                        Yes, that is correct and I can do that as well.

                        However...

                        I have this tool that generates a lot of theese explain plan statements (pairs of explain plan for..; select plan_table...;)
                        The editor may contain 50 of these statements pairs.
                        If I press F5 all of these are executed at once, and I can read the output from everyone of them in the log window.

                        But - I prefer to single step through these pairs of statements, executing them one-by-one. Being able to analyse the output, alter indexes and re-run the plan, before i go on to the next statement.
                        That was possible in a previous version of SQL Developer.

                        If this is an intended behavior we're not going to waste more time on this subject, but if it's an unintended feature I would like af fix :-)

                        Regards Søren
                        • 9. Re: Prompt for bind variables in SQL developer
                          Jeff Smith Sqldev Pm-Oracle
                          I would use autotrace then, and pin the output.

                          Then you can get the ACTUAL plan, not explains - and keep the performance details around as you run your other statements.

                          You'll want to input the binds so you can get accurate plans though.
                          • 10. Re: Prompt for bind variables in SQL developer
                            Turloch O'Tierney
                            Hi Søren,

                            For me: Run Script f5 runs (the selected command(s), if there is a selection) and does not prompt, and run statement (Ctrl-enter) does.

                            Turloch
                            -SQLDeveloper Team