Solve Special Character in Create and Insert Operation

Problem Description
When we want to create a table or insert one column data to a table,if the field of this table or the value of one field has special character, then our create or insert operation will fail.
** Special Characters: other characters are all special characters, except for alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#).

Problem Reappearance

        SQL> create table person (na-me varchar2(20));
                       *
        ERROR at line 1:
        ORA-00902: invalid datatype



        SQL> insert into person ("na-me") values(ju&lia);
        SP2-0552: Bind variable "LIA" not declared.
                           *

Solution
1. Using double quotation marks to wrap the field which has special character

        Example: create table person("na-me" varchar2(20)); 

2. Using single quotation mark to wrap the value which has special character

        Example: insert into person values('ju&lia');

Note: String in double quotation marks is case sensitive.

        Example:
         select * from PERSON where "na-mE"='ju&lia'
                           *
        ERROR at line 1:
        ORA-00904: "na-mE": invalid identifier

New Result

        SQ> create table person("na-me" varchar2(20));

        Table created.

        SQL> insert into person values('ju&lia');

        1 row created.

 

 

 

note
'&' can be recognized after writing the 'set define off' in the termina
without 'set define off', please refer to the catalog 38/ catalog 57

without 'set define off' in the termina

     
        SQL> insert into person values('a&b');
                    Enter value for b:    1( manual enter)
              -------------------------------------------
            old   1: insert into person values('a&b')
            new   1: insert into person values('a1')

            1 row created.
      
       SQL> insert into person values('a_b');  // same as '$'' #'

              1 row created.