0

How do I insert a record in a column having CLOB data type(so big text) having single quote in it?

I've already seen

How to handle a single quote in Oracle SQL

but the solution is make it manual and I'm trying to insert Long text which contains lot of single quotes. Once Oracle detects ', my INSERT doesn't work.

My question is if there is a kind of command like "set define off" where I can tell Oracle to disable the ' in the text

Community
  • 1
  • 1
Enrique Benito Casado
  • 1,914
  • 1
  • 20
  • 40

3 Answers3

3

Try with the Q' operator; for example:

create table t_clob ( a clob)

insert into t_clob values (q'[START aa'a''aa aa 'a'' aa'a'  a'a' a END]')
Aleksej
  • 22,443
  • 5
  • 33
  • 38
2

You can use quoted notation:

SELECT q'|text'containing'quotes|' FROM DUAL

The pipes can be replaced by any matching symbol. It must be the same symbol at the beginning and at the end except when parentheses are used, then (), [] or {}.

See https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00218

Husqvik
  • 5,669
  • 1
  • 19
  • 29
  • hi @Husqvik i dont want to sound so stupied but how can i that implemet it like an insert ,i mean , i m reading somethings about "sele from dual" but what i want is in the form inser into tablename values (Value1,Value2..) what have that in relationship with a select ? – Enrique Benito Casado Jan 27 '16 at 11:22
  • 1
    @Enrique The answer is explaining the syntax you should use, you have to adapt it to your needs. Just edited my answer to show an `insert` example – Aleksej Jan 27 '16 at 11:36
2

You can use '' to skip the meaning of the Quotation 

select 'hello this''s an example for '' in between string ' from dual
Narasimha Maiya
  • 1,009
  • 4
  • 12
  • 35