How to insert special characters & amp; and 'in Oracle

Method 1: Add Set define off before the SQL statement to be inserted; when we run the SQL> show all command under SQL * PLUS in batches with the original SQL statement, we can find a parameter: define "&" (hex 26 ), as shown below ...... concat ". "(hex 2e) copycommit 0 copytypecheck ON define" & "(hex 26) describe DEPTH 1 linenum off indent off echo OFF ...... this setting is used in Oracle to identify custom variables. Now we disable it in SQL * PLUS: SQL> Set define OFF; then execute the import script again, OK! Solve the problem. NOTE: If it is executed in TOAD, we recommend that you disable define in the first line of each script to be imported, otherwise, an error occurs when you import the second script containing special characters. If it is executed in SQL * PLUS, you only need to set define OFF once, and then you can import it continuously. Until you reset define ON. · Method 2: replace '&' with chr (38) in an SQL statement because chr (38) is the '&' ASCII code SQL> Select 'Tom '| chr (38) | 'Jerry' from dual; · method 3: split the original string SQL> Select 'Tom '|' & '| 'Jerry' from dual; we can see that method 1 is the easiest and most efficient. Method 2: because there is a function call process, the performance is slightly poor. Method 3 requires two connection strings, with the worst efficiency! How can I insert a single quotation mark in the field? For example, It's fine. Method 3: Method 1: Use the Escape Character SQL> Select 'test' | ''' from dual. Note: what are the meanings of the four single quotes? First, the first and last are character strings in Oracle, and there is no objection. So what does the second and third sign mean? The second ''is an escape character, and the third'' is our real content. Method 2: The same escape character is used, but the method is different. SQL> Select 'test''' from dual; note: here, the second and third are the escape characters and real content mentioned in method 1 above. Method 3: replace "with chr (39) in SQL ), because chr (39) is 'ascii code SQL> Select 'it' | chr (39) | 'fine 'from dual;