CODEWITHSUNDEEP
c#sqloracle-database
Martin
Martin

Reputation: 1159

Function remains INVALID only when using System.Data.OracleClient, works through SQL Developer

I'm using C# and System.Data.OracleClient to add functions to a database. This works for most functions, except one. The function is created but its state is INVALID. After examining the cause for this invalid state, I noticed I could simply compile the function within SQL Developer, but not from my c# application.

Any ideas why there is a difference using .NET and SQL Developer?

This is the function I use

string sql =
   @"CREATE OR REPLACE FUNCTION MYUSER.TEMPJOINSTRINGS 
    ( P_CURSOR SYS_REFCURSOR, 
      P_DEL VARCHAR2 := ', '
    ) RETURN VARCHAR2
   IS
     L_VALUE VARCHAR2(32767);
     L_RESULT VARCHAR2(32767);
   BEGIN
   LOOP
     FETCH P_CURSOR INTO L_VALUE;
     EXIT WHEN P_CURSOR%notfound;
     IF L_RESULT IS NOT NULL THEN
       L_RESULT := L_RESULT || P_DEL;
     END IF;
     L_RESULT := L_RESULT || L_VALUE;
   END LOOP;
   RETURN L_RESULT;
   END;";

try
{
    using (OracleConnection connection = new OracleConnection(@"Data source=TEST10;User Id=MYUSER;Password=MYPASS;"))
    {
        connection.Open();
        DbCommand cmd = connection.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
        connection.Close();
        return true;
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

and after that I execute

ALTER FUNCTION MYUSER.TEMPJOINSTRINGS COMPILE

But compiling will only work from SQL Developer, not my c# application.

Upvotes: 2

Views: 170

Answers (1)

DazzaL
DazzaL

Reputation: 21973

i'd take a guess that the String in .NET is bieng formatted as windows syle ie. \r\n linefeeds.

try this 

    sql = sql.Replace("\r", "");

after you have the function string built.

you can quickly confirm this if you did: 

SQL> show errors function TEMPJOINSTRINGS
Errors for FUNCTION TEMPJOINSTRINGS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/26     PLS-00103: Encountered the symbol "" when expecting one of the
         following:
         ( return compress compiled wrapped

SQL> select status from user_objects where object_name = 'TEMPJOINSTRINGS';

STATUS
-------
INVALID

SQL> select text, dump(text) from user_source where name = 'TEMPJOINSTRINGS' and line = 1;

TEXT
--------------------------------------------------------------------------------
DUMP(TEXT)
--------------------------------------------------------------------------------
FUNCTION TEMPJOINSTRINGS
Typ=1 Len=26: 70,85,78,67,84,73,79,78,32,84,69,77,80,74,79,73,78,83,84,82,73,78,
71,83,13,10

13,10 at the end being \r\n

Upvotes: 2

Related Questions