Oracle error ORA-00905 Missing keyword

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt my id number gt   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

User · Answer

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

User · Answer

Late answer  but I just came on this list today   CREATE TABLE assignment 20101120 AS SELECT   FROM assignment   Does the same

User · Answer

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

User · Answer

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  lt my id number gt   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

User · Answer

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

User · Answer

Late answer  but I just came on this list today   CREATE TABLE assignment 20101120 AS SELECT   FROM assignment   Does the same

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to ora-00905