Inserting special characters in varchar2

In order for values to be entered into a field without having the user enter the value, the table must be created with a default value. If you are using SQL*Plus and part of the default value is a special character, you must set the DEFINE parameter to off. Below, I connected to scott, then set define to off. Next I dropped my table then re-created it with the default value with the special character "&".

SQL> connect scott/tiger
Connected.

SQL> set define off

SQL> drop table special_chars;

Table dropped.

SQL> create table special_chars(id number, 
spec_char varchar2(100) default 'mehra & co');

Table created.

In the next set of statements, I inserted a value only in the ID field. Notice when I perform a select, the value for the field spec_char shows "mehra & co".

SQL> insert into special_chars(id)  values (1);

1 row created.

SQL> select id, substr(spec_char,1,20)  
from special_chars;

        ID SUBSTR(SPEC_CHAR,1,2                                                 
---------- --------------------                                                 
         1 mehra & co                                                         

Next, I performed an insert using a subquery. Then when I performed a query, the query showed two records.

SQL> insert into special_chars(id, spec_char)
  2  select id, spec_char
  3  from special_chars
  4  where id = 1;

1 row created.

SQL> select id, substr(spec_char,1,20)  
from special_chars;

        ID SUBSTR(SPEC_CHAR,1,2
---------- --------------------                                                 
         1 mehra & co                                                           
         1 mehra & co