Whenever I run the below script inside of a command window:

set serveroutput off
select * from dual;
select * from table(dbms_xplan.display_cursor);

I get the below output:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 2
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 2
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p

If I run the same script inside of Oracle SQL*Plus, I get the correct output:


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------


Am I doing something wrong in PL/SQL Dev or is there something else wrong?

Thanks,
Joe

Apparently the Command Window checks the transaction status between statements, and this conflicts with your script. We'll see if we can enhance this.

We are using version 8 on Oracle 11g. Could you please let us know when the issue of using dbms_xplan.display_cursor will be resolved.

Thanks
Lise

We're now two versions later and this still isn't fixed. I'm on PSD version 10.0.5.1710

I'm getting the exact same output when I call upon dbms_xplan.display_cursor, either without arguments, or with the sqlid parameter bypassed using named notation. According to the docs if sqlid isn't passed dbms_xplan will automatically grab the sqlid of the last statement executed in the current session. This works fine in SQL*Plus and TOAD, but returns the output seen in the original post above when attempted in PSD's Command window or SQL window.

In my opinion this is a larg-ish bug and ought to be fixed in the next patch. Anytime I'm forced to use TOAD or SQL*Plus to get correct behavior, it ought to be fixed pronto.

Hello Marco,

we would appreciate a fix/solution for this issue too.
We need this for some schemas which are not allowed to select the base tables of SQL. Thus a normal Explain Plan Window does not work too.

Thanks
Steffen