This discussion is archived
10 Replies Latest reply: Feb 20, 2013 6:54 AM by TurlochO'Tierney RSS

Prompt for bind variables in SQL developer

893982 Newbie
Currently Being Moderated
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 ACE Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points