CODEWITHSUNDEEP
.netoracle-databaseodp.net
Bobcat1506
Bobcat1506

Reputation: 81

ODP.NET Procedure Compilation

When I try to execute a create procedure using ODP.NET I get back ORA-24344: success with compilation error. However, when I run the same statement in SQL Developer it compiles successfully. Does anyone know what I need to change to get my procedure to compile? Is it a character set issue?

I am using Oracle 10g Express, .NET 3.5 SP 1, and ODP.NET 2.111.7.20 (version from Oracle.DataAccess.dll)

    [TestMethod]
    public void OdpNet_CreateProcedure()
    {
        ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["ODP.NET"];
        using (var con = new OracleConnection(settings.ConnectionString))
        {
            con.InfoMessage += new OracleInfoMessageEventHandler(con_InfoMessage);
            con.Open();

            var cmd = new OracleCommand();
            cmd.Connection = con;

            cmd.CommandText = @"
                CREATE OR REPLACE PROCEDURE TABLE1_GET
                (
                  P_CURSOR OUT SYS_REFCURSOR
                )
                IS
                BEGIN

                  OPEN P_CURSOR FOR
                  SELECT * 
                  FROM TABLE1;

                END;";

            cmd.ExecuteNonQuery(); // ORA-24344: success with compilation error

            cmd.CommandText = @"ALTER PROCEDURE TABLE1_GET COMPILE";
            cmd.ExecuteNonQuery(); // ORA-24344: success with compilation error
        }
    }

    void con_InfoMessage(object sender, OracleInfoMessageEventArgs eventArgs)
    {
        System.Diagnostics.Debug.WriteLine(eventArgs.Message);
    }

Upvotes: 3

Views: 1047

Answers (2)

Bobcat1506
Bobcat1506

Reputation: 81

The solution is:

cmd.CommandText = cmd.CommandText.Replace("\r\n", "\n");

Upvotes: 5

Gary Myers
Gary Myers

Reputation: 35401

Do a SELECT * FROM USER_ERRORS and you should see what is being recorded as the error. There may be some odd characters creeping in..

PS. It seems odd to be creating procedures within an application.

Upvotes: 1

Related Questions