oracle pl/sql execution with multiline string not inserted correctly

[barbetb]

Inserting certificates with sql is not done correctly. I have configured 'row' and '/' delimiter as needed for pl/sql. This works for single line commands, but as soon as the command contains a multiline string, it will fail. Linebreaks are not processed, and seem to be replaced with spaces.

INSERT INTO tablexyz (ID,VERSION,THUMBPRINT,CERTIFICATE) select max(ID) + 1, 0 , UPPER(REPLACE('DE 18 7C 64 89 27 5E E2 D3 23 20 FE A6 2D BD 33 20 0E 77 1F', ' ','')), '-----BEGIN CERTIFICATE----- MIIDGzCCAgOgAwIBAgIJAMfiwuu06QnsMA0GCSqGSIb3DQEBCwUAME0xCzAJBgNVBAYTAk5MMRAw DgYDVQQHEwdVdHJlY2h0MRIwEAYDVQQKEwlJZEJhbmtpbmcxGDAWBgNVBAMTD2lkeC5yYWJvYmFu ay5ubDAeFw0xNTEyMDcxMTQ3NDlaFw0xNTEyMDcyMDM0NTFaME0xCzAJBgNVBAYTAk5MMRAwDgYD VQQHEwdVdHJlY2h0MRIwEAYDVQQKEwlJZEJhbmtpbmcxGDAWBgNVBAMTD2lkeC5yYWJvYmFuay5u bDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAIXzpta8gKMU7zGM1xqZr1wGeIAjkecT GryXEPVFV0a0CPYe3K+a4nYm5THA2//JUzJQNSgdFZPXCEmon3CdQvd+OwePgcIHhGgwnctwr2Ar V4o42TSa43K1qQJZalSYjEKW9QP4XpxaRmp5PO7kAKL8KBrv8VPmcP86x+pytiz4txikq0dth3Xy eLkL8baHtu0/caFpfX3DnCqHad0qRQrG6r7Hx7cP0AgEyIJWVSYT4BOcN8cSL76LyrtjY/y/6ztO g8QiyTOWagc/tXmTzz9Bi4hFGW0S9eJUedrWgFpPvJer97XunWtY19xrgDKwNVLpPmJeGLUHrHQ0 uYLpj7cCAwEAATANBgkqhkiG9w0BAQsFAAOCAQEAK403ch/7rVjvjZrSKlh3Lo2Ak5Zm5lOM+t92 Dr1WtzRK9xoKgU2Dm1D8NHhJ819yMiA8wt3fC/+daFCHHHMi+aBZkmyG3Lg/Em+3bNv3vkmZ73eM pLzhomqNr+2A7dSPbmuKU6x8Oy+MzezlGki5O8vTDZByMfsKzD0Wm4uLhKR0pVmoWxsHYvT8caOl PFH1ZDtjexlOrcegun+FLMUUJNwF0wpZpakId/Iv4TwFgIfjOrFMmnZ8ihRzeeC1zX9tC1FVTa71 dTRK8HwFcpxGQkB2YYC3i9blzODjB3zFe+DYTL9UbvZvhXTPA05hMqwt1aMkVQ69XmoI4Q1ZcEVm cw== -----END CERTIFICATE-----' from tablexyz;

Can be executed, but line breaks are replaced with spaces.

[barbetb]

So as proof, this works (though obviously not a desired solution):

execute immediate 'INSERT INTO tablexyz (ID,VERSION,THUMBPRINT,CERTIFICATE) VALUES ( ''5'', ''0'' , UPPER(REPLACE(''DE 18 7C 64 89 27 5E E2 D3 23 20 FE A6 2D BD 33 20 0E 77 1F'', '' '','''')), ''-----BEGIN CERTIFICATE-----'' || chr(10) || ''MIIDGzCCAgOgAwIBAgIJAMfiwuu06QnsMA0GCSqGSIb3DQEBCwUAME0xCzAJBgNVBAYTAk5MMRAw'' || chr(10) || ''DgYDVQQHEwdVdHJlY2h0MRIwEAYDVQQKEwlJZEJhbmtpbmcxGDAWBgNVBAMTD2lkeC5yYWJvYmFu'' || chr(10) || ''ay5ubDAeFw0xNTEyMDcxMTQ3NDlaFw0xNTEyMDcyMDM0NTFaME0xCzAJBgNVBAYTAk5MMRAwDgYD'' || chr(10) || ''VQQHEwdVdHJlY2h0MRIwEAYDVQQKEwlJZEJhbmtpbmcxGDAWBgNVBAMTD2lkeC5yYWJvYmFuay5u'' || chr(10) || ''bDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAIXzpta8gKMU7zGM1xqZr1wGeIAjkecT'' || chr(10) || ''GryXEPVFV0a0CPYe3K+a4nYm5THA2//JUzJQNSgdFZPXCEmon3CdQvd+OwePgcIHhGgwnctwr2Ar'' || chr(10) || ''V4o42TSa43K1qQJZalSYjEKW9QP4XpxaRmp5PO7kAKL8KBrv8VPmcP86x+pytiz4txikq0dth3Xy'' || chr(10) || ''eLkL8baHtu0/caFpfX3DnCqHad0qRQrG6r7Hx7cP0AgEyIJWVSYT4BOcN8cSL76LyrtjY/y/6ztO'' || chr(10) || ''g8QiyTOWagc/tXmTzz9Bi4hFGW0S9eJUedrWgFpPvJer97XunWtY19xrgDKwNVLpPmJeGLUHrHQ0'' || chr(10) || ''uYLpj7cCAwEAATANBgkqhkiG9w0BAQsFAAOCAQEAK403ch/7rVjvjZrSKlh3Lo2Ak5Zm5lOM+t92'' || chr(10) || ''Dr1WtzRK9xoKgU2Dm1D8NHhJ819yMiA8wt3fC/+daFCHHHMi+aBZkmyG3Lg/Em+3bNv3vkmZ73eM'' || chr(10) || ''pLzhomqNr+2A7dSPbmuKU6x8Oy+MzezlGki5O8vTDZByMfsKzD0Wm4uLhKR0pVmoWxsHYvT8caOl'' || chr(10) || ''PFH1ZDtjexlOrcegun+FLMUUJNwF0wpZpakId/Iv4TwFgIfjOrFMmnZ8ihRzeeC1zX9tC1FVTa71'' || chr(10) || ''dTRK8HwFcpxGQkB2YYC3i9blzODjB3zFe+DYTL9UbvZvhXTPA05hMqwt1aMkVQ69XmoI4Q1ZcEVm'' || chr(10) || ''cw=='' || chr(10) || ''-----END CERTIFICATE-----'' )';