How to write the back end code correctly for this procedure?

Yasas Randeeka

PROCEDURE InsertCustomerEvaluation(
cef_cust_id IN VARCHAR,
cef_ce_id out number)AS
cef_c_e_id NUMBER;
BEGIN
BEGIN
SELECT CEF_CUSTOMER_EVA_SEQ.NEXTVAL INTO cef_c_e_id FROM dual;
cef_ce_id:=cef_c_e_id;
INSERT
INTO CEF_CUSTOMER_EVALUATION
(
C_E_ID,
CUST_ID,
E_R_ID,
SCORE,
FILLED_DATE
)
VALUES
(
cef_ce_id,
cef_cust_id,
1,
'',
sysdate
);
END;
END InsertCustomerEvaluation;

Below is my code in back end.

public int InsertCustomerEvaluation(string CUST_ID)
{
OracleParameter C_E_ID = new OracleParameter("C_E_ID", OracleDbType.Decimal);

C_E_ID.Direction = ParameterDirection.Output;
try
{
ce_id = _context.Database.ExecuteSqlRaw
("DECLARE C_E_ID NUMBER; BEGIN CEF_APP.InsertCustomerEvaluation(:CUST_ID,:C_E_ID);:C_E_ID:=C_E_ID; END;",
new OracleParameter("CUST_ID", CUST_ID), C_E_ID);
}
catch (Exception e)
{

}
return ce_id;
}

Have you any suggestions to correct above backend code? It gives me -1 return. Procedure works correctly in Oracle sql developer.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul3y ago
Part 1

From the above code, there are few points worth mentioning:

1. The method name "InsertCustomerEvaluation" doesn't provide much information about what the method does. A better name would be something like "CreateCustomerEvaluation" or "AddCustomerEvaluation".

2. It is important to validate the input parameter CUST_ID to ensure that it is not null or empty. If it is, then you should throw an exception or return an appropriate error message.

3. The code is not properly disposing the OracleParameter object C_E_ID. It's a good practice to use try-with-resources block to ensure that database resources are properly released.

4. If an exception occurs, the method returns -1. It would be better to return a boolean value indicating whether the operation was successful or not, and throw an exception if an error occurred.

5. Instead of just catching the exception and returning -1, it would be better to log the exception to a log file or a monitoring system, so that you can investigate the issue and fix the problem.

Rajkiran Swain3y ago

public int InsertCustomerEvaluation(string CUST_ID)
{
    OracleParameter C_E_ID = new OracleParameter("CEF_CE_ID", OracleDbType.Decimal);
    C_E_ID.Direction = ParameterDirection.Output;
    
    try
    {
        _context.Database.ExecuteSqlRaw(
            "BEGIN CEF_APP.InsertCustomerEvaluation(:CEF_CUST_ID, :CEF_CE_ID); END;",
            new OracleParameter("CEF_CUST_ID", CUST_ID),
            C_E_ID
        );
        
        return Convert.ToInt32(C_E_ID.Value);
    }
    catch (Exception e)
    {
        // handle the exception
    }
    
    return -1; // or throw an exception if needed
}