Wrong Symbol Inside Replace Function (pl/sql, Oracle)

I have below procedure inside package: PROCEDURE test1 IS InsertST varchar2(32000) : = 'INSERT INTO tableA (col1, col2) (select cola,

Solution 1:

The quoted string starting 'INSERT ends at colX, '. To quote a quote you need to either double up the quotes:

'INSERT INTO tableA (col1, col2) 
 (select cola, 
 INITCAP(REPLACE(colX, ''_'', ''''))
 from tableB))'

or else use q-quoting syntax:

q'[INSERT INTO tableA (col1, col2) 
 (select cola, 
 INITCAP(REPLACE(colX, '_', ''))from tableB))]';

Also, the assignment operator is := not : =.

It looks like you want to generate a statement like this:

insertinto tablea ( col1, col2 )
select cola, initcap(replace(colx, '_', ''))
from   tableb

which has a couple less brackets.

It doesn't look like it needs to be dynamic at all, but I'm assuming this is a simplified version of something that does.