Wrong symbol inside replace function (PL/SQL, ORAC

问题:

I have below procedure inside package:

PROCEDURE test1
IS
     InsertST varchar2(32000) : = 'INSERT INTO tableA (col1, col2) 
                                   (select cola, 
                                   INITCAP(REPLACE(colX, '_', ''))
                                   from tableB))';
Begin
    execute immediate InsertST;
END

during compilation I got error:

Error(1177,45): PLS-00103: Encountered the symbol "_" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset

Something is wrong with "_" inside function: INITCAP(REPLACE(colX, '_', ''))

How to fix it? Maybe is other way?

回答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:

insert into 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.