2

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!

Suren
  • 21
  • 2
  • Possible duplicate of [Insert text with single quotes in PostgreSQL](https://stackoverflow.com/questions/12316953/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 May 01 '18 at 21:08
  • Try douling each single quote within the quoted text. – sticky bit May 01 '18 at 21:12
  • 2
    There is also the q-quote syntax - see the [documentation for Text Literals](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Literals.html#GUID-1824CBAA-6E16-4921-B2A6-112FB02248DA). – William Robertson May 01 '18 at 21:27

2 Answers2

4

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}' );
Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
0

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';
eaolson
  • 14,717
  • 7
  • 43
  • 58