Oracle Sql Missing Expression Error
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ORA-00936: missing expression tips Oracle Error Tips by Burleson Consulting Question: When trying to use the SQL below, I keep getting the ORA-00936 error. What is the problem? select from customer where cust_name = 'IBM'; How do I address this ORA-00936 error? Answer: The Oracle oerr utility notes this about the ORA-00936 error: ORA-00936 missing expression Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE. Action: Check the statement syntax and specify the missing component. The ORA-00936 happens most frequently: 1 - When you forget list of the column names in your SELECT statement. 2. When you omit the FROM clause of the SQL statement. �� Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services Copyright © 1996 - 2016 All rights reserved by Burleson Oracle is the registered trademark of Oracle Corporation.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 Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to resolve ORA 00936 Missing Expression Error? http://www.dba-oracle.com/sf_ora_00936_missing_expression.htm up vote 3 down vote favorite select /*+USE_HASH( a b ) */ to_char(date, 'MM/DD/YYYY HH24:MI:SS') as LABEL, ltrim(rtrim(substr(oled, 9, 16))) as VALUE, from rrfh a, rrf b, where ltrim(rtrim(substr(oled, 1, 9))) = 'stata kish' and a.xyz = b.xyz The "from " (3rd line) part of the above query is giving me ORA-00936 Missing EXPRESSION error ... Please Help me NOTE :: rrfh table contains no data. oracle oracle10g http://stackoverflow.com/questions/12156399/how-to-resolve-ora-00936-missing-expression-error ora-00936 share|improve this question edited Aug 28 '12 at 11:16 John Doyle 4,37742334 asked Aug 28 '12 at 9:29 user1466466 833613 1 This error always means that Oracle is expecting another column and or table... it's usually caused by unneeded trailing commas as in your case or by unbalanced parenthesis. –Ben Aug 28 '12 at 12:19 add a comment| 2 Answers 2 active oldest votes up vote 6 down vote accepted Remove the comma? select /*+USE_HASH( a b ) */ to_char(date, 'MM/DD/YYYY HH24:MI:SS') as LABEL, ltrim(rtrim(substr(oled, 9, 16))) as VALUE from rrfh a, rrf b where ltrim(rtrim(substr(oled, 1, 9))) = 'stata kish' and a.xyz = b.xyz Have a look at FROM SELECTING from multiple tables You can include multiple tables in the FROM clause by listing the tables with a comma in between each table name share|improve this answer edited Aug 28 '12 at 9:53 answered Aug 28 '12 at 9:32 Adriaan Stander 107k11180220 I tried it and still getting the same error at same line –user1466466 Aug 28 '12 at 9:48 Repost the new query please. –Adriaan Stander Aug 28 '12 at 9:51 select /*+USE_HASH( a b ) */ to_char(date, 'MM/DD/YYYY HH24:MI
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies http://stackoverflow.com/questions/10440306/syntax-error-missing-expression-ora-00936 of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges http://oracle-error.blogspot.com/2008/04/ora-00936missing-expression.html Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a oracle sql minute: Sign up Syntax error: missing expression (ORA-00936) up vote 1 down vote favorite I have 2 tables, Facilities and Services. CREATE TABLE Facilities ( facility_id NUMBER(2) NOT NULL, facility_name VARCHAR2(20) NOT NULL, CONSTRAINT pk_facil_id PRIMARY KEY (facility_id) ); CREATE TABLE Services ( service_id NUMBER(2) NOT NULL, service_name VARCHAR(20) NOT NULL, service_facility NUMBER(2) NOT NULL, CONSTRAINT pk_serviceid PRIMARY KEY (service_id) ); ALTER oracle sql missing TABLE Services add CONSTRAINT fk_servicefacility FOREIGN KEY(service_facility) REFERENCES Facilities(facility_id); If I try entering records into the 'Services' table like so: INSERT INTO Services (service_id, service_name, service_facility) SELECT 06, 'Rooms', (SELECT facility_id, FROM Facilities WHERE facility_name = 'Hotel') FROM Dual; I get an error "missing expression" for 3 out of the 7 insert statements. What expression is missing? sql oracle database-design foreign-keys ora-00936 share|improve this question edited May 3 '12 at 23:12 Justin Cave 160k14204250 asked May 3 '12 at 22:42 adohertyd 1,22292958 Your service should have a FacilityID, which is a foreign key to the Primary Key on the Facilities table. The Facility table should not have a reference to the Service table. You then need to create a service record for each service at a facility. So if 2 facilities offer water, then you would have 2 records in service, one for Water and Facility 1, one for Water and Facility 2 –Stefan H May 3 '12 at 22:49 @StefanH I don't know if you understand what I am trying to do. I have a facility say Health Centre
required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE. Action:Check the statement syntax and specify the missing component. ORA-00936:missing expression This Oracle error is mainly related to the SQL SELECT statements. One obvious reason is select column list is missing or expressions in the selected columns are incomplete. Example: One Simple example is SELECT FROM EMPLOYEE; raise ORA-00936 error as the column list is missing.. This ORA-00936 may also caused with respective to the usage of the exponential operator "**" in the SQL statements. It may result the following errors: ORA 936 missing expressionPLS-936ORA 6550 line , column <>num: ORA-00936 Exponential Operator Scenario: Symptoms Using the exponential operator '**' in a SQL statement results in ORA-936 or PLS-936 depending on whether the SQL statement is executed from SQLPLUS prompt or within a PL/SQL block.SQL> select 2**2 from dual;select 2**2 from dual*ERROR at line 1:ORA-00936: missing expressionSQL> declare2 x number := 2;3 y number := 4;4 z number;5 begin6 select x**y into z from dual;7 end;8 /y number := 4;*ERROR at line 3:ORA-06550: line 6, column 10:PL/SQL: ORA-00936: missing expressionORA-06550: line 6, column 1:PL/SQL: SQL Statement ignored Cause '**' is a valid exponential character in PL/SQL and cannot be used in a SQL statement. The exponential operator '**' can be used in PL/SQL statements:SQL> declare2 x number := 2;3 y number := 4;4 z number;5 begin6 z := x**y;7 end;8 /PL/SQL procedure successfully completed. Fix Use power() function instead of '**' in SQL statement'.SQL> select power(2,2) from dual;POWER(2,2)----------4SQL> declare2 x number := 2;3 y number := 4;4 z number;5 begin6 select power(x,y) into z from dual;7 end;8 /PL/SQL procedure successfully completed. Oracle Bugs related to ORA-00936 error: There are also a few oracle bugs related to ORA-00936. Listed below are two bugs related to ORA-00936. You can find complete list of bugs at Oracle Metalink. 1) Bug#:4567818 base Bug#:4192