[sql] Oracle error : ORA-00905: Missing keyword

Excuting the line of SQL:

SELECT * 
  INTO assignment_20081120 
  FROM assignment ;

against a database in oracle to back up a table called assignment gives me the following ORACLE error: ORA-00905: Missing keyword

You can use select into inside of a PLSQL block such as below.

Declare
  l_variable assignment%rowtype
begin
  select *
  into l_variable
  from assignment;
exception
  when no_data_found then
    dbms_output.put_line('No record avialable')
  when too_many_rows then
   dbms_output.put_line('Too many rows')
end;

This code will only work when there is exactly 1 row in assignment. Usually you will use this kind of code to select a specific row identified by a key number.

Declare
  l_variable assignment%rowtype
begin
  select *
  into l_variable
  from assignment
  where ID=<my id number>;
exception
  when no_data_found then
    dbms_output.put_line('No record avialable')
  when too_many_rows then
   dbms_output.put_line('Too many rows')
end;

Late answer, but I just came on this list today!

CREATE TABLE assignment_20101120 AS SELECT * FROM assignment;

Does the same.

You can use select into inside of a PLSQL block such as below.

Declare
  l_variable assignment%rowtype
begin
  select *
  into l_variable
  from assignment;
exception
  when no_data_found then
    dbms_output.put_line('No record avialable')
  when too_many_rows then
   dbms_output.put_line('Too many rows')
end;

This code will only work when there is exactly 1 row in assignment. Usually you will use this kind of code to select a specific row identified by a key number.

Declare
  l_variable assignment%rowtype
begin
  select *
  into l_variable
  from assignment
  where ID=<my id number>;
exception
  when no_data_found then
    dbms_output.put_line('No record avialable')
  when too_many_rows then
   dbms_output.put_line('Too many rows')
end;

First, I thought:

"...In Microsoft SQL Server the SELECT...INTO automatically creates the new table whereas Oracle seems to require you to manually create it before executing the SELECT...INTO statement..."

But after manually generating a table, it still did not work, still showing the "missing keyword" error.

So I gave up this time and solved it by first manually creating the table, then using the "classic" SELECT statement:

INSERT INTO assignment_20081120 SELECT * FROM assignment;

Which worked as expected. If anyone come up with an explanaition on how to use the SELECT...INTO in a correct way, I would be happy!

Though this is not directly related to the OP's exact question but I just found out that using a Oracle reserved word in your query (in my case the alias IN) can cause the same error.

Example:

SELECT * FROM TBL_INDEPENTS IN
JOIN TBL_VOTERS VO on IN.VOTERID = VO.VOTERID

Or if its in the query itself as a field name

 SELECT ..., ...., IN, ..., .... FROM SOMETABLE

That would also throw that error. I hope this helps someone.

First, I thought:

"...In Microsoft SQL Server the SELECT...INTO automatically creates the new table whereas Oracle seems to require you to manually create it before executing the SELECT...INTO statement..."

But after manually generating a table, it still did not work, still showing the "missing keyword" error.

So I gave up this time and solved it by first manually creating the table, then using the "classic" SELECT statement:

INSERT INTO assignment_20081120 SELECT * FROM assignment;

Which worked as expected. If anyone come up with an explanaition on how to use the SELECT...INTO in a correct way, I would be happy!

Unless there is a single row in the ASSIGNMENT table and ASSIGNMENT_20081120 is a local PL/SQL variable of type ASSIGNMENT%ROWTYPE, this is not what you want.

Assuming you are trying to create a new table and copy the existing data to that new table

CREATE TABLE assignment_20081120
AS
SELECT *
  FROM assignment

Late answer, but I just came on this list today!

CREATE TABLE assignment_20101120 AS SELECT * FROM assignment;

Does the same.

Unless there is a single row in the ASSIGNMENT table and ASSIGNMENT_20081120 is a local PL/SQL variable of type ASSIGNMENT%ROWTYPE, this is not what you want.

Assuming you are trying to create a new table and copy the existing data to that new table

CREATE TABLE assignment_20081120
AS
SELECT *
  FROM assignment

If you backup a table in Oracle Database. You try the statement below.

CREATE TABLE name_table_bk
AS
SELECT *
  FROM name_table;

I am using Oracle Database 12c.

Unless there is a single row in the ASSIGNMENT table and ASSIGNMENT_20081120 is a local PL/SQL variable of type ASSIGNMENT%ROWTYPE, this is not what you want.

Assuming you are trying to create a new table and copy the existing data to that new table

CREATE TABLE assignment_20081120
AS
SELECT *
  FROM assignment

If you backup a table in Oracle Database. You try the statement below.

CREATE TABLE name_table_bk
AS
SELECT *
  FROM name_table;

I am using Oracle Database 12c.

Unless there is a single row in the ASSIGNMENT table and ASSIGNMENT_20081120 is a local PL/SQL variable of type ASSIGNMENT%ROWTYPE, this is not what you want.

Assuming you are trying to create a new table and copy the existing data to that new table

CREATE TABLE assignment_20081120
AS
SELECT *
  FROM assignment