Oracle Hates Microsoft

JvdL

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.


<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 TO my_savepoint;
	END;
</NonQuery>

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.

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.

Wooly

Don't you just hate Oracle...? I do... It's rubbish with a capital RUBBISH... <sigh>