Toad 12.9.0.71 (but saw this problem before as well).
but couldn't reproduce this bug all the time.
Brg
Damir
If you have part of a SQL statement selected, and you do an explain plan, Toad will attempt to run the explain plan just on the selected portion. This is so you can run it on a subquery. Your best bet to run it on an entire query is to include semicolons, so Toad can best determine where the query begins and ends.
John,
Maybe it was no so obvious, but repeating:
Somehow toad select first word and report an error
This means mine cursor was at the begin of the line and nothing was selected.
Toad did higlight (only word "select") and then shows an error. After that i can repeat run many times, but whenever I click on Explain plan, toad highlight "select" and reports an error.
On second picture you see that I have edite query (lines removed) and then Explain plan runs normally.
Oh, I understand what you mean now.
If something goes wrong in the explain plan (for example, no privileges or typo in query. missing explain plan table), then Oracle returns an error line and position. Toad will take that line/position data and select the nearest word. So in your case, I’m not sure what the query error is, but I believe the line/position data is 1/1, so that’s why the first word is becoming selected.
Next time the error happens, look down in Toad’s output window (I see you have it minimized) to see if there is an ORA-##### error. If you can repeat it, turn on spool SQL and that should also give you more information about what is going on.
when this “highlight” starts, i can run sql with no ora, but whenever i tried Explain plan, toad highlight the first word.
So in this case obviously it would be an ORA…but i cannot preven toad from highlighting …this is the problem…
TOAD itself, because of some reason highlight first word.
and gain, after i edit the query …all is fine until next random situation.
All I can tell you is that there is some ORA-##### error which happens first, then Toad tries to highlight the error position. Since the first word is being highlighted, then we are probably getting 0,0 as the error position (meaning the SQL is OK, which agrees with what you said about the SQL running OK). So maybe something is going on with the plan table. Maybe it’s locked or something? I’m just guessing.
See if you can capture the initial problem and ORA-#### with spool SQL. I think we’ll have our answer if you can catch the SQL.
Ok. Now I understand you
will return info …100%
db is 12.1.0.2, Toad 12.9.0.71 x64, Win 7 Pro (all 64 bit)
Query:
SELECT /*+ NO_CPU_COSTING */
LISTAGG('Invalid DB job: ’ || job.job_name || ‘; DB=FMS; SCHEMA=’ || rd.OWNER || ‘; WHAT=’ || job.JOB_ACTION || ‘; (failures=’ || TO_CHAR(job.failure_count) || ‘, broken=’ || DECODE(job.state, ‘BROKEN’, ‘Y’, ‘N’) || ‘)’ || CHR(13) || CHR(10)) WITHIN GROUP (ORDER BY job.job_name) alarm
FROM sys.user_schedul_job_run_details_x rd,
sys.user_scheduler_jobs_x job
WHERE 1 = 1
AND job.job_name = rd.JOB_NAME
AND rd.STATUS <> ‘SUCCEEDED’
AND rd.LOG_ID = (SELECT MAX(log_id)
FROM user_scheduler_job_run_details
WHERE job.job_name = job_name)
AND (0 < job.failure_count - UID * 0
AND ‘DISABLED’ <> job.state
OR ‘BROKEN’ = job.state);
11:44:09 Q383211@FMST>explain plan for
11:44:14 2 SELECT /*+ NO_CPU_COSTING */
11:44:16 3 LISTAGG('Invalid DB job: ’ || job.job_name || ‘; DB=FMS; SCHEMA=’ || rd.OWNER || ‘; WHAT=’ || job.JOB_ACTION || ‘; (failures=’ || TO_CHAR(job.failure_count) || ‘, broken=’ || DECODE(job.state, ‘BROKEN’, ‘Y’, ‘N’) || ‘)’ || CHR(13) || CHR(10)) WITHIN GROUP (ORDER BY job.job_name) alarm
11:44:16 4 FROM sys.user_schedul_job_run_details_x rd,
11:44:16 5 sys.user_scheduler_jobs_x job
11:44:16 6 WHERE 1 = 1
11:44:16 7 AND job.job_name = rd.JOB_NAME
11:44:16 8 AND rd.STATUS <> ‘SUCCEEDED’
11:44:16 9 AND rd.LOG_ID = (SELECT MAX(log_id)
11:44:16 10 FROM user_scheduler_job_run_details
11:44:16 11 WHERE job.job_name = job_name)
11:44:16 12 AND (0 < job.failure_count - UID * 0
11:44:16 13 AND ‘DISABLED’ <> job.state
11:44:16 14 OR ‘BROKEN’ = job.state);
Explained.
Elapsed: 00:00:00.32
11:44:18 Q383211@FMST>SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,‘ALLSTATS LAST’));
SQL_ID 14dvpmg2f3bzt, child number 0
explain plan for SELECT /*+ NO_CPU_COSTING */ LISTAGG('Invalid
DB job: ’ || job.job_name || ‘; DB=FMS; SCHEMA=’ || rd.OWNER || ‘;
WHAT=’ || job.JOB_ACTION || ‘; (failures=’ ||
TO_CHAR(job.failure_count) || ‘, broken=’ || DECODE(job.state,
‘BROKEN’, ‘Y’, ‘N’) || ‘)’ || CHR(13) || CHR(10)) WITHIN GROUP (ORDER
This seems like a privilege problem.
When I log in as SYS and create these views, I can do an explain on the query. If I then grant them to some other user and log in as that user, then try the explain plan, I get “ORA-01039 insufficient privileges on underlying objects of the view” and the USER_SCHEDULER_JOBS_X view becomes highlighted. If I then do another explain plan, without changing the hightlights, I get “ORA-00905 missing keyword”. The reason for the missing keyword error is that since USER_SCHEDULER_JOBS_X in the query is highlighted, Toad assumes that part is a subquery that you want to explain, so it sends this to ORacle:
explain plan set statement_id=‘Admin:091616075130’ into SYS.PLAN_TABLE$ For
user_scheduler_jobs_x;
Error: ORA-00905: missing keyword
and you can see why that would give the missing keyword error.
NO,
This seems like Toad forum problem, when i cannot delete the post after posting.
So please delete this one.
thx
P.S.
Concentrate on post of initial SQL which was shown.
It has a problem … and after joining lines there was no problem. Please understand that this is 1000000% Toad problem…maybe relatzed with some profile (which was imported since 10.x version) and shared between beta and regular versions, but not confirmed…and not any other problem at all…
The problem went away when you removed the line feed, but in doing so, you also removed the highlight on the word SELECT. Doesn’t the problem also go away when you just remove the highlight and try again?
Doesn't the problem also go away when you just remove the highlight and try again?
No
I can repeat it now. I missed the part at first about trying to do the EP while the SQL was still running.
It will be fixed in 12.11 beta #1.
In the meantime, either of these can be used as workarounds -
as you found, take out the line feeds. Actually, you only need to change the SQL by one character (so that the SQL it isn’t found in v$SQL)
right-click in the explain-plan area and uncheck the “Load cached plan if possible” option.
Oh, and I noticed one more thing you can do, if you don’t like the workarounds -
When the first word is selected, and the “[Error] Explain Plan (0,0):” message is shown, just click back over to the Explain Plan tab in the editor. The explain plan result is there, but you didn’t see it because the Editor went to the “Messages” tab.
Thank god it is Monday…
Thank you!
This one has been bothering me for a while.
I was able to see an underlying ORA- error that instigated the problem yesterday, but did not write it down (a parsing error like not all variables bound - when the SQL had no variables).
One way to get rid of it for a while seems to be re-creating / pointing to the explain plan table - or just altering the SQL and trying again.
Having to click away from the Messages tab to the Explain Plan tab gets to be a pain when performing some manual iterative tuning.
The problem had to do with variables not being initialized. So it could be that when you saw an error message that didn’t apply, it was a valid error message from the last time you did an explain plan, and it was being re-shown the time after that.
As I said, it’s fixed for 12.11 beta #1. Until then, there are 3 workarounds mentioned earlier in this thread. That’s the best I can give you for now. Sorry for the inconvenience.
