CODEWITHSUNDEEP
sqlplsqlscripting
ETD
ETD

Reputation:

PL/SQL "SQL Command not properly ended" error

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 error:

invalid character

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; 
    )

Upvotes: 2

Views: 5792

Answers (4)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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;
/

Upvotes: 0

Jordi Cabot
Jordi Cabot

Reputation: 8208

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

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18940

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.

Upvotes: 2

oglester
oglester

Reputation: 6670

Put a semicolon at the end of each statement.

Upvotes: 7

Related Questions