I seeing error 'ORA-00905: missing keyword' while trying to rewrite the any query using Auto Optimize SQL from Toad Xpert Edition 12.0 version.
SELECT SYSDATE FROM DUAL;
No results are fetched. Please help if anyone has the similar issue. Let me know if I'm missing anything here.
I can't reproduce your issue with my Optimizer (Oracle) 9.3.2.
However, I've seen similar messages when Toad users try to launch the Tuning component from Toad AND the user has (perhaps inadvertently) highlighted a part of the query to be tuned.
Make sure you're not highlighting an invalid portion of your query before hitting the AutoOptimize or Advanced SQL Optimizer button within Toad, if that's where you're launching the SQL Optimizer from. Alternatively, make sure that your editor's cursor is located somewhere within the context of the query you intend to tune. You don't have to highlight anything... Toad will pass to SQL Optimizer the entire query surrounding your cursor.
Finally, you can always paste the query into the SQL Optimizer. Any valid SQL statement should work in the product.
Thanks Gary for your reply. I tried both ways, selecting the whole SQL and click the Auto Optimize SQL button & copy the SQL, paste it in the Database -> Optimize -> Auto Optimize SQL -> Statement to Tune and running the optimize. Both didn't work. Is it due to any installation issues.
On the other side, I also have another issue where if I select any object from the Schema Browser and select the constraints tab, it hangs and throws me 'index out of bounds' error. For this I've to move to next tab and then come to constraints tab to see the constraints. However, the indentation of that tab becomes smaller after that.
Please help to resolve these both issues.
Would be helpful to understand what version and edition of Toad you're on, what OS version, etc.
Almost sounds like a partially-corrupted install, but it's hard to troubleshoot without more detailed info. Best, then, to open up a Support ticket, or call Quest's Support team directly. That team has access to different environments/Databases/Versions, and can more readily try to reproduce your issue better than most of us on ToadWorld.
Thanks Gary. Here are the details.
Toad for Oracle - 12.10.0.30, Xpert Edition.
OS - Windows 7.
Oracle Client - 11.2.0.4
Already opened a request with Quest and they asked me to upgrade my above said s/w :-(, which is a tedious task in a big firm, it takes time. Meanwhile, I was checking if anyone has faced similar kind of issue and I can get some workarounds without upgrade:-).
Reset Toad's user files will likely solve both problems.
This will restore Toad to a clean state as if it were freshly installed. Your existing user files will be backed up to C:\Users\YOUR_WINDOWS_USERNAME\AppData\Roaming\Dell\Toad for Oracle\12.10_bak .
Now, start Toad, make connection, and try again. If it works you can close Toad and copy any configuration files over from the backed up set to the new ( C:\Users\YOUR_WINDOWS_USERNAME\AppData\Roaming\Dell\Toad for Oracle\12.10 ) . I'd recommend copying only what is absolutely necessary. For example...
If there are some other setting you'd like to find, let me know and I'll add them to the list.
Thanks John for your suggestion, I did tried the way you suggested, but it hasn't resolve the issue.
I even tried exporting the connections to an xml file and imported, that also didn't work. Please advise, if you've any more options or it requires fresh installation?
It must be something in SQL Optimizer then. I'll ask that team if they have any ideas.
Please try to optimize this SQL (no “;” after SQL text):
Select 1 from dual
What’s you Windows7 language?
Please try to change Control panel | Region and Language | Format, please set to “English (United States)” and try again.
I have this exact same situation with Toad 13.3.0.181.
I try something even as simple as 'Select 1 from dual', and I always get the same error above.
My Windows 10 language setting is 'English (United States)'.
Was anyone able to resolve this problem?
Thanks!
Chad
Hi addhankkiv/Chad,
All databases in your environment have this problem or only some databases have this problem?
Please drop these 2 SQL explain plan tables in your database. These 2 tables are created by Toad to store SQL explain plan information. Next time if Toad needs these 2 tables, Toad will auto-create it. These 2 tables are:
QUEST_SL_TEMP_EXPLAIN1
TOAD_PLAN_TABLE
After you drop these 2 explain plan tables, please close Toad and start it again. Input "select 1 from dual" and click "Auto Optimize SQL".
If you still get the error, please try these steps:
Go to Toad install folder, you can find a sub-folder: SQLTracker.
Run "sqldbmon.exe" in this sub-folder.
Select "OptimizerEngine.exe".
Click "Start Monitoring".
Back to Toad editor, click "Auto Optimize SQL" again.
After you get the error, back to sqldbmon.exe, copy all script in the editor, and email to me.
Please also include this information in the email:
Database server version?
RAC or single instance?
Database language setting?
select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET','NLS_LANGUAGE', 'LANGUAGE','NLS_TERRITORY', 'TERRITORY') name,value
from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')
Oracle client version, 32bit or 64bit? Full Oracle client or instant client?
Any special char in object names? For example, single quotation marks in schema name: "Peter'Schema".
My email is: ken.taiATQuest.com, please replace AT to at sign.
Hi addhankkiv/Chad,
From the "sqldbmon" log that the Toad explain plan table "TOAD_PLAN_TABLE" has some problem. Please drop these 2 SQL explain plan tables in your database. These 2 tables are created by Toad to store SQL explain plan information. Next time if Toad needs these 2 tables, Toad will auto-create it. These 2 tables are:
QUEST_SL_TEMP_EXPLAIN1
TOAD_PLAN_TABLE
If you still have this problem, please send us the "TOAD_PLAN_TABLE" script (Schema Browser | Select "TOAD_PLAN_TABLE" | Select "Script" tab).
Please also try this script to create a new "TOAD_PLAN_TABLE":
DROP TABLE TOAD_PLAN_TABLE CASCADE CONSTRAINTS;
CREATE TABLE TOAD_PLAN_TABLE
(
STATEMENT_ID VARCHAR2(30 BYTE),
PLAN_ID NUMBER,
TIMESTAMP DATE,
REMARKS VARCHAR2(4000 BYTE),
OPERATION VARCHAR2(30 BYTE),
OPTIONS VARCHAR2(255 BYTE),
OBJECT_NODE VARCHAR2(128 BYTE),
OBJECT_OWNER VARCHAR2(128 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
OBJECT_ALIAS VARCHAR2(65 BYTE),
OBJECT_INSTANCE INTEGER,
OBJECT_TYPE VARCHAR2(30 BYTE),
OPTIMIZER VARCHAR2(255 BYTE),
SEARCH_COLUMNS NUMBER,
ID INTEGER,
PARENT_ID INTEGER,
DEPTH INTEGER,
POSITION INTEGER,
COST INTEGER,
CARDINALITY INTEGER,
BYTES INTEGER,
OTHER_TAG VARCHAR2(255 BYTE),
PARTITION_START VARCHAR2(255 BYTE),
PARTITION_STOP VARCHAR2(255 BYTE),
PARTITION_ID INTEGER,
OTHER LONG,
DISTRIBUTION VARCHAR2(30 BYTE),
CPU_COST INTEGER,
IO_COST INTEGER,
TEMP_SPACE INTEGER,
ACCESS_PREDICATES VARCHAR2(4000 BYTE),
FILTER_PREDICATES VARCHAR2(4000 BYTE),
PROJECTION VARCHAR2(4000 BYTE),
TIME INTEGER,
QBLOCK_NAME VARCHAR2(128 BYTE),
OTHER_XML CLOB
)
Looks like the Optimizer does not like block comments formatted like /* ,a.header_id */
It seems to handle line comments like --,a.header_id with no error.
