Simple INSERT Statements

This was tested on Oracle 11.2. Before I could can do any INSERT statements, I needed to create a table:

SQL> create table source

  2  (col1 varchar2(1),

  3   col2 varchar2(1))

  4  /

Table created. 

SQL>

Then I tried various single-row INSERT statements. Oracle converted the NUMBER columns (i.e. the ones without quotes) into VARCHAR2 format with or without the TO_CHAR function:

SQL> insert into source

  2  (col1,col2) values(to_char(1),'2')

  3  /

1 row created.

SQL> insert into source values('3',4)

  2  /

1 row created.

SQL> insert into source (col1,col2)

  2  select 5,'6' from dual

  3  /

1 row created.

SQL> insert into source

  2  select '7',to_char(8) from dual

  3  /

1 row created.

SQL> select * from source

  2  /

C C

- -

1 2

3 4

5 6

7 8 

SQL> 

Next I created an empty copy of the table:

SQL> create table target

  2  as select * from source

  3  where 1=2

  4  /

Table created. 

SQL> 

... and copied the rows from SOURCE to TARGET with 2 different multi-row INSERT statements:

SQL> insert into target select * from source

  2  /

4 rows created.

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

4 rows created. 

SQL> 

I dropped a column from the SOURCE table:

SQL> alter table source drop column col2

  2  /

Table altered. 

SQL> 

... and both the INSERT statements then failed albeit with different error messages:

SQL> insert into target select * from source

  2  /

insert into target select * from source

            *

ERROR at line 1:

ORA-00947: not enough values

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

select col1,col2 from source

            *

ERROR at line 2:

ORA-00904: "COL2": invalid identifier

SQL>

I replaced the dropped column and added an extra one. In my experience, adding columns is far more common than dropping columns:

SQL> alter table source add

  2  (col2 varchar2(1),

  3   col3 varchar2(1))

  4  /

Table altered.

SQL>

Then only the first INSERT failed:

SQL> insert into target select * from source

  2  /

insert into target select * from source

            *

ERROR at line 1:

ORA-00913: too many values

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

4 rows created. 

SQL>

How to Compare More Than 1 Column From a Subquery (revisited)

In my earlier post with the same name, the main query and the subquery looked at the same number of columns. The examples below were tested on Oracle 11.2. If the main query has more columns than the subquery, you get an ORA-00947:

 

SQL> l

  1  select count(*) from dba_tables

  2  where (owner, table_name, tablespace_name) not in

  3* (select table_owner, table_name from dba_indexes)

SQL> /

(select table_owner, table_name from dba_indexes)

*

ERROR at line 3:

ORA-00947: not enough values

 

SQL>

 

... and if the subquery has more values, you get an ORA-00913:  

 

SQL> l

  1  select count(*) from dba_tables

  2  where (owner, table_name) not in

  3  (select table_owner, table_name, table_type

  4*  from dba_indexes)

SQL> /

(select table_owner, table_name, table_type

*

ERROR at line 3:

ORA-00913: too many values

 

SQL>