Oracle Exception: SQL command not properly ended

Question

I am developing web application using JSP and Servlet (IDE: Eclipse, Container: Tomcat7.0, DB: Oracle 10)

I want to get data from two table in a single query

Query:

query = "select * from PROTOCOL as a, ACTIONS as b where a.PROTOCOL_ID = b.PROTOCOL_ID";

But after running the application I am getting the following exception:

java.sql.SQLException: ORA-00933: SQL command not properly ended

is there anything wrong in query?

Answer 1

The problem you have is keyword AS. This is used for columns in SELECT section. It is not valid for FROM where you specify the tables.

You have

select * from PROTOCOL as a, ACTIONS as b

should be

select * from PROTOCOL a, ACTIONS b...

From Oracle Docs

t_alias

Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.

Example:

SELECT select_list 
 FROM table1 t_alias1 
 WHERE expr operator 
 (SELECT column_list 
 FROM table2 t_alias2 
 WHERE t_alias1.column 
 operator t_alias2.column); 

• Damian Leszczyński - Vash: @gdoron, Yeap. That is common. But in Oracle docs using as is not allowed there.
• DazzaL: @Bhushan so does PROTOCOL and ACTIONS exist and are they visible to the user who is querying them (additionally, if you're not logging on as the table owner, ensure a synonym exists or you'll have to prefix with the owner name)?
• gdoron: @Vash, not sure where you seen there anything about alias or as grammer.
• Damian Leszczyński - Vash: @gdoron, I have added the doc to answer. You will not find anything about As there because it is not allowed.
• gdoron: @Vash, so what exactly the docs you added adds(?!) anyway, I saw it's allowed tomorrow at work I'll check the AS, I admit I never used it...

Answer 2

wrong alias syntax. try the following:

query = "select * from PROTOCOL a,ACTIONS b where a.PROTOCOL_ID = b.PROTOCOL_ID";

• Vicks: that is because the PROTOCOL or ACTIONS table does not exist, or in a different schema, on which you do not have any grants. check the table name is correct first, if so, find the details from all_objects/dba_objects view. check the object_name,object_type & owner. if the owner is say apps change the query with APPS.PROTOCOL & APPS.ACTIONS

Answer 3

A comment here for anyone may need it.. PostgreSQL seems to accept AS next to FROM/JOIN