C# calling Oracle Function - Error: "Invalid operation on null data"

Question

I'm getting an error "Invalid operation on null data" when my C# code is calling Oracle Function. This happens only if no data is found. If data is found and function returns a value, then everything works ok. I'm a little confused, as - to my understanding at least - function should return 100 if no data found (see function exception).

Oracle Function:

create or replace FUNCTION F_SCO_DPD
(
    p_tip IN NUMBER,
    p_dav IN VARCHAR2
)
RETURN NUMBER 
IS
    sco NUMBER;
BEGIN
    SELECT max(score) keep(dense_rank first order by vrednost)
        INTO sco
        FROM sco_sif_score
        WHERE sif_kat = 11
        AND tip_pod = p_tip
        AND vrednost >= (SELECT a.dpd
                         FROM sco_dpd a                                      
                         WHERE a.par_davcna = p_dav);
    RETURN sco;
EXCEPTION
        WHEN NO_DATA_FOUND 
        THEN 
             RETURN 100;   
END F_SCO_DPD;

C# Code:

using (OracleCommand cmd = new OracleCommand())
{
    cmd.Connection = conn;
    cmd.CommandText = "F_SCO_DPD";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new OracleParameter("p_tip", Podjetje.TipSub));
    cmd.Parameters.Add(new OracleParameter("p_dav", Podjetje.Davcna));
    cmd.Parameters.Add(new OracleParameter("sco", OracleDbType.Decimal, ParameterDirection.ReturnValue));
    cmd.BindByName = true;

    cmd.ExecuteScalar();

    Score.ScoDpd = (int)(OracleDecimal)cmd.Parameters["sco"].Value;                                    
}

Gordon Linoff · Accepted Answer · 2017-02-27 11:58:55Z

5

You are running an aggregation function. max(score) keep (dense_rank first order by vrednost) is as much an aggregation function as max(score).

That means that your query is an aggregation query with no GROUP BY. All such queries return exactly 1 row. If no rows match the WHERE clause, then the value is NULL.

So, the exception is never triggered. Instead, check if the returned value is NULL.

The resulting code is:

create or replace FUNCTION F_SCO_DPD
(
    p_tip IN NUMBER,
    p_dav IN VARCHAR2
)
RETURN NUMBER 
IS
    v_sco NUMBER;
BEGIN
    SELECT max(score) keep (dense_rank first order by vrednost)
    INTO v_sco
    FROM sco_sif_score
    WHERE sif_kat = 11 AND
          tip_pod = p_tip AND
          vrednost >= (SELECT a.dpd
                       FROM sco_dpd a                                      
                       WHERE a.par_davcna = p_dav
                      );

    RETURN COALESCE(v_sco, 100);
END F_SCO_DPD;

answered Feb 27, 2017 at 11:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Flin Over a year ago

Thanks, Gordon. For your swift reply and very clear explanation. Of course, it's working as expected now. Learned something new, so thanks for that. Up-voted your answer, but cannot be seen, as I have <15 reputation.

Collectives™ on Stack Overflow

C# calling Oracle Function - Error: "Invalid operation on null data"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related