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
</Query>
<NonQuery name="My Statement">
BEGIN
SAVEPOINT my_savepoint;
MERGE INTO config dest
USING (
SELECT name, username, value
FROM config
WHERE name = :configname and username = :configusername and username != '*'
) src
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 TOmy_savepoint
;
END;
</NonQuery>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>