PL/SQL "SQL Command not properly ended" error

Question

I am currently working on developing some SQL scripts in PL/SQL Developer and I am getting an "SQL Command not properly ended" error that I am unsure how to solve.

The code I am using looks something like this

CREATE TABLE temp_table as
    SELECT * FROM table_x

INSERT INTO temp_table
    SELECT * FROM table_y

If I execute the two pieces (create table and the insert) as separate pieces everything runs fine, i.e. select each code block and execute. However if I try to execute everything, select all code and execute, I get an error saying that "SQL Command not properly ended".

I don't mind dealing with this when I am dealing with very small tables but when I have a significant number of operations I need to execute sequentially and when each operation takes a long time to run I would like to be to execute the code and walk away.

Adding a semicolon raises a new error which is an "invalid character" error.

This is the code that raises the invalid character error.

CREATE TABLE temp_table as 
    SELECT * FROM table_x where x > 1; 

INSERT INTO temp_table 
    ( 
    SELECT * FROM table_y where x > 1; 
    )

Answer 1

Put a semicolon at the end of each statement.

Answer 2

Try this:

CREATE TABLE temp_table as
SELECT * FROM table_x;

INSERT INTO temp_table
SELECT * FROM table_y;

I think the parentheses were messing you up.

Answer 3

The parenthesis can be used to specify the columns on which you want to insert the data as in:

INSERT INTO temp_table ( col1,col2,...coln) SELECT ...

You get an error because instead of a list of columns, the parser finds a select expression between parenthesis

Answer 4

If you're running the script in one step in PL/SQL Developer, you may need to convert it to a "SQL*Plus" script - i.e. the semicolons are required to delimit the statement, then the / (forward slash) is required to run the statement, e.g.:

CREATE TABLE temp_table as 
    SELECT * FROM table_x where x > 1;
/

INSERT INTO temp_table 
    SELECT * FROM table_y where x > 1;
/