1 + NULL is NULL

If you try to add a null to a number, the result is a null. You can see what I mean in the example below, which I tested in Oracle 10:

SQL> select 1 from dual
  2  /

         1
----------
         1

SQL> select nvl(null,'null') from dual
  2  /

NVL(NULL,'NULL')
----------------
null

SQL> select nvl(to_char(1+null),'null') from dual
  2  /

NVL(TO_CHAR(1+NULL),'NULL')
---------------------------
null

SQL>

Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list

  2  as select * from dba_objects

  3  /

Table created.

SQL>

When you create a table it has no statistics so the num_rows column is null:

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------

NULL

                                                  

SQL>

When you calculate statistics, the num_rows column is updated:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

----------------------------------------
7932

SQL>

Moving the table deletes the statistics so num_rows is null afterwards:

SQL> alter table object_list move

  2  /

Table altered.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

---------------------------------------- NULL                                       

SQL>

To reinstate the statistics, simply analyze the table again:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------
7932

SQL>

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>

ORA-01841

There was no year 0 in the calendar and Oracle's to_date function recognises this but for some reason it rejects -4713. If anybody knows why, perhaps they could add a comment below: 

SQL> select
  2  to_char(to_date('-4714','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-4714','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('-4713','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-4713','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('-4712','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
4712 BC

SQL> select
  2  to_char(to_date('-1','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
0001 BC

SQL> select
  2  to_char(to_date('-0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('+0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('+0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('1','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
0001 AD

SQL> select
  2  to_char(to_date('9999','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
9999 AD

SQL>