ORA-00917 Escaping apostrophe in sql query

Question

Below code works fine for most of the cases. It blows up when LIB_SEGCO has any single quotes such as ABC'DEF

In these cases it gives error ORA-00917: missing comma

req := 'INSERT INTO CLIENTS(
                    IDEVT,
                    ID_CLIENT,
                    CATEGORIE_CLIENT,
                    SEGCO,
                    LIB_SEGCO,
                    NOM,
                    ADRESSE,
                    CODE_POSTAL,
                    VILLE,
                    PAYS,
                    STATUT) 
                    values(''' || UNID ||''','''||ID||''','''||SEGCO||''','''||L_SEGCO ||''','''||**LIB_SEGCO** ||''','''|| NOM||''','''||
                    ADRESSE||''','''||CODE_POSTAL||''','''||cVILLE||''','''||PAYS||''','''||CODE_STATUT||''')';

         execute immediate req;

Any ideas to escape the apostrophe when it passed in variable?

Thanks

user_2568341 · Answer

Use:

req := q'{INSERT INTO CLIENTS(
                    IDEVT,
                    ID_CLIENT,
                    CATEGORIE_CLIENT,
                    SEGCO,
                    LIB_SEGCO,
                    NOM,RCS_ADRESSE,
                    CODE_POSTAL,
                    VILLE,
                    PAYS,
                    STATUT) 
                    values( :1, :2, :3, :4, :5, :6
                             :7, :8, :9, :10)}';

         execute immediate req
         USING  UNID  , ID , SEGCO , L_SEGCO  , LIB_SEGCO ,
                NOM , ADRESSE , CODE_POSTAL , cVILLE , PAYS ;

Note: I ommited , CODE_STATUT because there are only 10 columns listed in the INSERT command, but CODE_STATUT is 11th, so a number of columns doesn't match with number of values.

ORA-00917 Escaping apostrophe in sql query

1 Answers