Semicolon and Single Quote Issue in SQL Insert Statements
Solution 1
Espace the semicolon:
SET DEF OFF;
Insert into PROD_DESC(PROD_NO, PROD_DESC)
Values('XYZ', 'test semicolon test\;');
Solution 2
I also like to use the q-quote syntax. It removes the need for escaping, and makes quoting quotes much easier.
The syntax is
q'[ <your string> ]'
<Your string>
can contain quotes or any other characters the normally need escaping. The [ can actually be substituted for or "paired" characters, such as {} or ().
For example:
SQL>
SQL> CREATE TABLE prod_desc
2 (
3 prod_no VARCHAR2 (10),
4 prod_desc VARCHAR2 (100)
5 );
Table created.
SQL>
SQL> SET DEFINE OFF;
SQL> Insert into PROD_DESC
2 (PROD_NO, PROD_DESC
3 )
4 Values
5 ('XYZ', q'[test
6 semicolon test
7 ; ]'
8 );
1 row created.
SQL>
SQL>
SQL> select * from prod_desc;
PROD_NO PROD_DESC
---------- ----------------------------------------------------------------------------------------------------
XYZ test
semicolon test
;
Comments
-
Jacob almost 2 years
I am executing a SQL script which has spaces, semicolons, single-quotes, special characters etc. in of the column called prod_desc. I need to execute the script from SQLPLUS. I have the set the following in the SQLPLUS however, single quotes, semicolons are not taken into consideration.
set sqlblanklines on
See the below insert statement for instance, semicolon is after the line break. This is causing an error during execution. How can I resolve the issue?
SET DEFINE OFF; Insert into PROD_DESC (PROD_NO, PROD_DESC ) Values ('XYZ', 'test semicolon test; ' );
Table Structure
CREATE TABLE prod_desc ( prod_no VARCHAR2 (100), prod_desc VARCHAR2 (1000) );
-
Jacob about 7 yearsIs there a way to SET in sqlplus like SET DEFINE OFF:?
-
Jacob about 7 yearsThe reason is I have a huge SQL file which has many cases with case I have shown in the question.
-
Gnqz about 7 yearsTo set the escape character or to escape all characters with just a directive?
-
Jacob about 7 yearsBoth examples would be highly helpful. Thanks
-
Gnqz about 7 yearsYou can use the " set escape '\' " to set the escape character; However, I'm not sure you can escape all characters with just a directive (at least I don't know a way).