Explain Plan Error Ora-00905 Missing Keyword
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack 00905 country code Overflow the company Business Learn more about hiring developers or posting ads with us
Ora-00955
Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community ora-00933 of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up explain query is not working in oracle db up vote 0 down vote favorite I use https://support.software.dell.com/toad-for-oracle/kb/31290 the following query in MySQL and it works fine: explain SELECT COUNT(userid) FROM tableone where userid='abc' I tried the same in Oracle but I get the following error: SQL Error [905] [42000]: ORA-00905: missing keyword ORA-00905: missing keyword When I execute explain query in mysql it result type,possible_keys,key,key_len,ref etc.... how can i get that result from oracle mysql sql oracle share|improve this question edited Nov 3 '15 at 12:24 asked Nov 3 http://stackoverflow.com/questions/33498362/explain-query-is-not-working-in-oracle-db '15 at 11:47 Priyanka Shaju 1991214 1 explain is handled quite differently in the two databases: docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm. –Gordon Linoff Nov 3 '15 at 11:49 3 as stated in the oracle documentation your are missing the keywords explain PLAN FOR. –Kevin Esche Nov 3 '15 at 11:50 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote explain SELECT COUNT(userid) FROM tableone where userid='abc' That is syntactically incorrect in Oracle. the correct syntax is: EXPLAIN PLAN FOR sql_statement; See How to create and display explain plan in Oracle. For example, SQL> EXPLAIN PLAN FOR SELECT * FROM EMP; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 518 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 4 (0)| 00:00:01 | -------------------------------------------------------------------------- 8 rows selected. Alternatively, you could achieve the same in SQL*Plus: SQL> set autot on explain SQL> SELECT empno FROM emp; EMPNO ---------- 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 --------------
27 Apr 2006 08:48:15 -0700 Message-ID: <1146152895.145831.249340@g10g2000cwb.googlegroups.com> http://www.orafaq.com/usenet/comp.databases.oracle.server/2006/04/27/1878.htm I'm trying to use a query analyzer on some pre-existing procedure code and can't get past this message. "Describe Error: Failed to execute https://www.experts-exchange.com/questions/22953470/execute-query-plan.html EXPLAIN plan: ORA-00905: missing keyword" Here's the procedure. Below it are the tables. /*************************************************************************** Deletes a row in the activities table by missing keyword the primary key value. Also cascade deletes all children and all other associated data. ****************************************************************************/ PROCEDURE delete_activity ( p_activity_id IN activities.activity_id%TYPE ) IS CURSOR v_children_activities(p_parent_activity_id activities.activity_id%TYPE) IS SELECT activity_id FROM activities WHERE parent_id = p_parent_activity_id; BEGIN UPDATE activities SET current_child_id = NULL WHERE activity_id = p_activity_id; 00905 missing keyword FOR rec IN v_children_activities(p_activity_id) LOOP delete_activity(rec.activity_id); END LOOP; DELETE FROM work_products WHERE activity_id = p_activity_id; DELETE FROM attachments WHERE activity_id = p_activity_id; DELETE FROM contacts WHERE activity_id = p_activity_id; DELETE FROM regulation_lookups WHERE activity_id = p_activity_id; DELETE FROM activities WHERE activity_id = p_activity_id; EXCEPTION WHEN others THEN pips_debug.raise_exception( 'pips_dml', 'delete_activity', 'Unable to delete a row by primary key in the activities table: '||SQLERRM); END; CREATE TABLE "PIPS_OWNER"."ACTIVITIES" ( "ACTIVITY_ID" VARCHAR2(32) NOT NULL PRIMARY KEY REFERENCES PIPS_OWNER.ACTIVITIES(ACTIVITY_ID) REFERENCES PIPS_OWNER.ACTIVITIES(ACTIVITY_ID), "MI_CATEGORY_ID" VARCHAR2(32), "PARENT_ID" VARCHAR2(32), "TYPE_ID" VARCHAR2(32) NOT NULL, "LEAD_ORGANIZATION_ID" VARCHAR2(32), "NAME_ID" VARCHAR2(32), "ENTERED_BY_USER_ID" VARCHAR2(32), "UPDATED_BY_USER_ID" VARCHAR2(32), "COMPLEXITY_ID" VARCHAR2(32), "UNITS_OF_TIME_ID" VARCHAR2(32), "PRIORITY_ID" VARCHAR2(32), "STATUS_ID" VARCHAR2(32) NOT NULL, "PRESENTATION_SEQUENCE" DOUBLE PRECISION, "NAME" VARCHAR2(50), "DESCRIPTION" VARCHAR2(4000), "RECORDED_DATE" DATE, "UPDATED_DATE" DATE, "START_DATE" DATE, "EXPECTED_COMPLETION_DATE" DATE, "BASELINE_COMPLETION_DATE" DATE, "ACTUAL_COMPLETION_DATE" DATE, "NOTE" VARCHAR2(4000), "EXPECTED_TIME_TO_COMPLETE" DOUBLE PRECISION, "SEQUENCE_ORDER" DOUBLE PRECISION, "CURRENT_CHILD_ID" VARCHAR2(32), "ACTION_TYPE_ID" VARCHAR2(32), "MIGRATED
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > execute query plan Want to Advertise Here? Solved execute query plan Posted on 2007-11-11 Oracle Database 1 Verified Solution 4 Comments 1,366 Views Last Modified: 2013-12-19 I was testing the following stored procedure. The sql script runs fine with result "anonymous block completed". But when I tried "execute query plan", the error msg comes Ora-00905: missing keyword". Below is the call to the SP "search_by_keyword": DECLARE v_searchResults sys_refcursor; v_numberOfRecords NUMBER; v_success NUMBER; v_errorMsg CLOB; BEGIN DBMS_OUTPUT.PUT_LINE('Test Result: '); document_search.search_by_keyword(1, 1,'car',1, v_searchResults, v_numberOfRecords, v_success, v_errorMsg); DBMS_OUTPUT.PUT_LINE('Value Returned Is: ' || v_numberOfRecords); DBMS_OUTPUT.PUT_LINE('Value Returned Is: ' || v_success); DBMS_OUTPUT.PUT_LINE('Value Returned Is: ' || v_errorMsg); END; Please help me...thanks! 0 Question by:suecnus Facebook Twitter LinkedIn Google Best Solution bysuecnus I wanted to see some statistics of the execution of the query plan. "Execute Query Plan" It's the tab in Oracle SQL Developer that one just click to get the results. Also available is "Auto trace" tab. Go to Solution 4 Comments LVL 15 Overall: Level 15 Oracle Database 13 Message Expert Comment by:ishando2007-11-11 What are you trying to do? "execute query plan" isn't a valid command 0 Message Accepted Solution b