0

I am trying to do some exercises in Oracle and when i'm trying to create a view I get this error: SQL command not properly ended. As I thought it might be my error I tried to directly copy paste some code from official oracle slides:

 CREATE VIEW view_employees
 AS SELECT employee_id emp_id, first_name, last_name, email
 FROM employees
 WHERE employee_id BETWEEN 100 and 124

 SELECT *
 FROM view_employees

but I keep getting the same error, even if modify the create statement adding CREATE OR REPLACE VIEW.

Improve this question
7
  • 1
    At the very least you need a semicolon ; after 124, Oracle does not allow the WHERE clause to contain SELECT, SQL Server does allow a new SELECT after a CREATE VIEW (separated by the batch operator GO). Commented Feb 20, 2018 at 10:47
  • 1
    Those statements should work. So the problem must be how you have typed them into your client, or some other peculiarity in how you are running them. As only you can see your screen there is not much more we can do. You need to cast a cool eye over what you're trying to spot the bloomer. Commented Feb 20, 2018 at 10:59
  • 1
    Which client are you running this is in? Maybe it doesn't understand multiple statements? What happens if you run the create view part on its own, without the following query? Commented Feb 20, 2018 at 10:59
  • 2
    Which application do you use to execute these commands? Can you show SQL*Plus log for it or something similar? Commented Feb 20, 2018 at 10:59
  • 1
    The choice of SQL terminator character is up to the application and not part of the SQL language, which has no terminator. It is normally ; by convention, but it's configurable in SQL*Plus. Commented Feb 20, 2018 at 12:41

2 Answers 2

Reset to default
2

If you change your statements in the following way, they should work:

CREATE VIEW view_employees
  AS SELECT employee_id emp_id,first_name, last_name, email
  FROM employees
  WHERE employee_id BETWEEN 100 and 124;

SELECT *
FROM view_employees;

You need to terminate the CREATE VIEW with ; or /. Otherwise Oracle does not recognize that the CREATE VIEW statement has ended, so when it sees the SELECT it determines something is wrong.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I tried to use your code but i keep getting the same error
@sanna: which SQL client do you use to run those statements? sqlplus? "Oracle SQL Developer"? Something else?
@a_horse_with_no_name, I use DBeaver Community mostly, but some SQLPlus. @Littlefoot, I agree that OR REPLACE is a good inclusion, I also added a COMMIT;
The question says they already tried or replace; but that doesn't explain the error they get anyway. It's usually a good idea anyway of course. DDL commits implicitly so no need to add that.
@AlexPoole, you are right. Verified (docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/…) A transaction ends when any of the following actions occur: a user runs a DDL command such as CREATE, DROP, RENAME, or ALTER. The database issues an implicit COMMIT statement before and after every DDL statement.
|
-1

Try this...

CREATE OR REPLACE VIEW view_employees
     AS SELECT employee_id emp_id, first_name, last_name, email
     FROM employees
     WHERE emp_id BETWEEN 100 and 124;

     SELECT *
     FROM view_employees;
Improve this answer

2 Comments

@SergeFonville Don't understand what you mean?
@ThomasVerhoeven - you've made the view query invalid. The only change you seem to have made - apart from adding the semicolons which Serge had already done - is to change WHERE employee_id to WHERE emp_id. That will not work. That column alias can only be used in the order-by clause of the same level of query, so this will get an invalid-identifier error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.