CODEWITHSUNDEEP
oracle-databaseoracle10gsqlplus
Jacob
Jacob

Reputation: 14741

Semicolon and Single Quote Issue in SQL Insert Statements

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)
);

Upvotes: 7

Views: 18151

Answers (3)

Buchas
Buchas

Reputation: 3

You can ignore semicolons in a string with

SET SQLTERMINATOR OFF

Upvotes: 0

Gnqz
Gnqz

Reputation: 3382

Espace the semicolon:

SET DEF OFF;
Insert into PROD_DESC(PROD_NO, PROD_DESC)
Values('XYZ', 'test semicolon test\;');

Upvotes: 3

BobC
BobC

Reputation: 4432

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
           ;

Upvotes: 3

Related Questions