Oracle error : ORA-00905: Missing keyword

Question

Hi ,

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

Answer 1

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

Answer 2

Thank you very much I was trying to create a new backup table.

Answer 3

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!

Answer 4

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;