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.


    <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.



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


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.