Home » SQL & PL/SQL » SQL & PL/SQL » explain plan error
explain plan error [message #196860] Sun, 08 October 2006 13:37 Go to next message
learningTiger
Messages: 2
Registered: October 2006
Junior Member
i am trying to use the " explain plan".
the statement is executed and the plan is written to the plan_table.
when i try to read the plan using
select * from table(dbms_xplan.display); i get the error :
ERROR at line 1:
ORA-00904: invalid column name which points to display.


how do i resolve this. what mistake am i doing?
Re: explain plan error [message #196863 is a reply to message #196860] Sun, 08 October 2006 16:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>how do i resolve this.
Use valid SQL syntax
>what mistake am i doing?
using invalid SQL syntax
select * from dbms_xplan;
Re: explain plan error [message #196882 is a reply to message #196860] Mon, 09 October 2006 00:39 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Which version of the database are you using?

@anacedent: (On a 9i db) The OP used valid sql-syntax. dbms_xplan.display is a pipelined function.
Your code yields an error.
SQL> select *
  2  from   table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                            |  Name                      | Rows
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |
|   1 |  SORT ORDER BY                       |                            |
|*  2 |   HASH JOIN                          |                            |
|*  3 |    TABLE ACCESS FULL                 | VER_POL_MUTATIES           |    1


etc..

SQL> select * from dbms_xplan;

select * from dbms_xplan

ORA-04044: procedure, function, package, or type is not allowed here

Re: explain plan error [message #197062 is a reply to message #196860] Mon, 09 October 2006 18:47 Go to previous messageGo to next message
learningTiger
Messages: 2
Registered: October 2006
Junior Member
i am using oracle 9i.

dbms_xplan.display is valid i guess. but i get the invalid cloumn name.
Re: explain plan error [message #197099 is a reply to message #197062] Tue, 10 October 2006 01:18 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you, perhaps, create the PLAN_TABLE using UTLXPLAN.SQL of a different Oracle version than the one you are using at the moment? For example, as you're on 9i now, did you create this table using 8i UTLXPLAN.SQL file? This *could* end up with such an error (i.e. table description changed between versions).

Another possibility is that the original query is invalid - check does it run properly without trying to explain it - just see whether all columns in the statement are valid.
Previous Topic: selecting multiple values from a select list
Next Topic: question on getting first date of a month
Goto Forum:
  


Current Time: Mon Feb 17 22:24:44 CST 2025