replace single quote with dbouble quote inside clob text

2
votes

i am inserting a CLOB data into a table. as part of this, I am inserting a complete plsql procedure into a clob column. Procedure has many dynamic sql statements. when inserting its throwing ora error.

sample code:

insert into t_prc_cmpre( prc_nm,vrsn_nbr,v_CLOB ,envr) 
    (select 'PRC_1','3.7.5',
  'CREATE OR REPLACE PROCEDURE PRC1 
IS

v_sql clob;

BEGIN

v_stmt:='INSERT INTO '||v_targetschema||'.'|| PI_TABLE ||' (COL1,COL2,COL3...)

execute immediate v_stmt;
end;
/'

since insert statement has single quote ,its not allowing to insert into clob column. Please help me to resolve the issue.

Many a thanks!

2
sqloracleplsql
Possible duplicate of Insert text with single quotes in PostgreSQL Short answer: Escape each single quote with a second single quotes 'So your string literal looks like ''this'''Although this practice of storing procedure code in a field is scratching an anti-pattern itch. - JNevill
Try douling each single quote within the quoted text. - sticky bit
There is also the q-quote syntax - see the documentation for Text Literals. - William Robertson

2 Answers

4
votes

An easy way to insert 'awkward' data is with the the 'q' syntax, eg

insert into t ( c) values ( q'{ This is some text with 'quotes' etc}' );
0
votes

I'm not sure what you're trying to do and your block is incomplete and won't compile. But if you're trying to put a ' character inside a varchar or clob, you enter it as two 's, like this:

x := 'The cat''s whiskers';