Oracle insert Escape Character practices

The following articles mainly introduce the Oracle insert escape characters, single quotation marks and & characters. If you think their names are weird, in addition, if they are rarely involved in practical applications, the following articles will answer relevant questions.

When importing a batch of data to Oracle today, I encountered a problem: Toad prompts me to assign a value to a custom variable AMP. At first I was wondering that data is a series of Insert statements, how can I have custom variables? Then I searched for the keyword "AMP" and found that,

Oracle regards the URL parameter connector & amp; as a custom variable, so I need to assign a value to the variable AMP. What should we do? There are three methods:

Method 1: Add Set define off before the SQL statement to be inserted; execute it in batches with the original SQL statement

When we execute the SQL> show all command under SQL * PLUS, we can find a parameter: define "&" (hex 26), as shown in

 
 

  
  
......  
  
  
concat "." (hex 2e)  
  
  
copycommit 0  
  
  
copytypecheck ON  
  
  
define "&" (hex 26)  
  
  
describe DEPTH 1 LINENUM OFF INDENT OFF  
  
  
echo OFF  
  
  
...... 
 
 

This setting is used to identify custom variables in Oracle insert escape characters. Now we disable it in SQL * PLUS:

 
 

  
  
SQL> Set define OFF; 
 
 

Run 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. There are also three methods

Method 1: Use escape characters

 
 

  
  
SQL > Select 'test' || '''' from dual; 
 
 

Note: What are the meanings of the four single quotes? First, the first and last are character strings in the Oracle insert escape characters, which have no objection. So what does the second and third sign mean? The second 'is an escape character.

The third is our real content.

Method 2: escape characters are used in different ways.

 
 

  
  
SQL > Select 'test ''' from dual; 
 
 

Note: 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; 
 
 

The above content is an introduction to Oracle insert to insert escape characters. I hope you will get something.