Oracle Hates Microsoft
When using ODP.NET, Oracle's client for .NET, I ran into the following issue.
My application needs to run against various types databases, including MS SQL, MySQL and Oracle. Therefore, it stores vendor-specific SQL queries and statements in separate XML files, like this.
The non-queries are executed with ExecuteNonQuery() after binding the appropriate parameters. This all works fine except for the last one, where Oracle insists that it was a PLS-00103 error. When I run the exact same statement with copy-paste in Oracle's SQL developer, it works fine. I've been looking around the internet for solutions how to run PL/SQL from C# , but there just seemed nothing wrong with it.
<Query name="My Query">
SELECT value FROM config
WHERE name = :configname AND username IN (:configusername,'')
ORDER BY username DESC
<NonQuery name="My Statement">
MERGE INTO config dest
SELECT name, username, value
WHERE name = :configname and username = :configusername and username != ''
ON (dest.name = src.name and dest.username = '')
WHEN MATCHED THEN
UPDATE SET dest.value = src.value
WHEN NOT MATCHED THEN
INSERT (dest.name, dest.username, dest.value)
VALUES (src.name, '', src.value)
DELETE FROM config WHERE name = :configname and username not in ('*',:configusername);
EXCEPTION WHEN OTHERS THEN ROLLBACK TO
In fact there is nothing wrong with it, except this: the statements from the XML file have \r\n line terminators in it as usual from Windows-based text editors. Apparently, using Windows offends Oracle's PL/SQL parser so much it chokes and crashes on the \r characters. Stripping \r characters out of the command text before executing it solves the problem.
Don't you just hate Oracle...? I do... It's rubbish with a capital RUBBISH... <sigh>