How Does Oracle's `SELECT INTO` Differ from Other SQL Dialects, and How Can I Avoid 'ORA-00905'?

Oracle's Unique Interpretation of SELECT INTO

In Oracle, the typical operation of the SELECT INTO statement differs from other SQL dialects. While SELECT INTO usually associates data retrieval with the creation of a new table, Oracle implements it distinctly.

To address the error "ORA-00905: missing keyword," it's crucial to understand Oracle's implementation. The correct syntax for creating a new table with data from an existing one is as follows:

Creating a New Table:

If the destination table, NEW_TABLE, already exists:

insert into new_table
select * from old_table;

To create NEW_TABLE based on the data in OLD_TABLE:

create table new_table as
select * from old_table;

Creating an Empty Table Structure:

If the purpose is to create an empty table with the same structure as OLD_TABLE, use a WHERE clause with an impossible condition:

create table new_table as
select * from old_table
where 1 = 2;

Note that while creating a table using SELECT INTO, Oracle only replicates the table's schema, excluding any constraints, triggers, or indexes present in the original table. These must be added manually if desired.

The above is the detailed content of How Does Oracle's `SELECT INTO` Differ from Other SQL Dialects, and How Can I Avoid 'ORA-00905'?. For more information, please follow other related articles on the PHP Chinese website!