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.