Oracle Insert query in stored procedure not working when called from code behind

Question

Below is my Stored procedure which works fine while calling from the SQL editor. But when calling from code, only the first statement (truncate) works. The insert does not work. Can anyone please help me. Thanks in advance.

SP:

create or replace PROCEDURE GETALARMLIST
(
pEqCode              ESPC_O_ALARM_DATA.EQCODE%TYPE,
pOccMinTime          ESPC_O_ALARM_DATA.OCCTIME%TYPE,
pOccMaxTime          ESPC_O_ALARM_DATA.OCCTIME%TYPE,
displayparam         OUT SYS_REFCURSOR
)

AS 
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE tempalarmlist';

INSERT INTO tempalarmlist(OCCDATE,FIRSTOCCTIME,COUNT)
SELECT substr(a.OCCTIME, 1, 4) || '/' || substr(a.OCCTIME, 5, 2) || '/' || substr(a.OCCTIME, 7, 2),min(OCCTIME), count(*)
FROM ESPC_O_ALARM_DATA a WHERE EQCODE = pEqCode AND a.OCCTIME > pOccMinTime  AND a.OCCTIME < pOccMaxTime 
GROUP BY substr(a.OCCTIME, 1, 4) || '/' || substr(a.OCCTIME, 5, 2) || '/' || substr(a.OCCTIME, 7, 2)
ORDER BY substr(a.OCCTIME, 1, 4) || '/' || substr(a.OCCTIME, 5, 2) || '/' || substr(a.OCCTIME, 7, 2);


UPDATE tempalarmlist
SET 
ALMID=(select ALMID from ESPC_O_ALARM_DATA where ESPC_O_ALARM_DATA.OCCTIME = tempalarmlist.FIRSTOCCTIME and ESPC_O_ALARM_DATA.EQCODE = pEqCode),
ALMCODE =(select ALMCODE from ESPC_O_ALARM_DATA where ESPC_O_ALARM_DATA.OCCTIME = tempalarmlist.FIRSTOCCTIME and ESPC_O_ALARM_DATA.EQCODE = pEqCode),
ALMTXT= (select ALMTXT from ESPC_O_ALARM_DATA where ESPC_O_ALARM_DATA.OCCTIME = tempalarmlist.FIRSTOCCTIME and ESPC_O_ALARM_DATA.EQCODE = pEqCode);


OPEN displayparam FOR  SELECT  * FROM tempalarmlist;

END GETALARMLIST;

Code:

 public static DataTable GetAlarmList(string datasource, string eqCode, string OccMinTime, string OccMaxTime){
            OracleConnection objConn = new OracleConnection();
            objConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings[datasource];

            OracleCommand objCmd = new OracleCommand();
            objCmd.Connection = objConn;
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "GETALARMLIST";

            objCmd.Parameters.Add("pEqCode", eqCode);
            objCmd.Parameters.Add("pOccMinTime", OccMinTime);
            objCmd.Parameters.Add("pOccMaxTime", OccMaxTime);
            objCmd.Parameters.Add("displayparam", OracleType.Cursor).Direction = ParameterDirection.Output;

            DataTable dtAlarmList = new DataTable();
            try
            {
                objConn.Open();
                OracleDataAdapter oda = new OracleDataAdapter(objCmd);
                oda.Fill(dtAlarmList);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                objConn.Close();
            }
            return dtAlarmList;
        }

"Does not work" isn't enough information to answer your question. If there's an error coming back when you run it, please mention what it is. — Peter M.
– Peter M., Commented Jul 19, 2016 at 2:59
Hi Peter, I do not get any error. The SP does not execute the insert statement. — Preethi Ganesan
– Preethi Ganesan, Commented Jul 19, 2016 at 3:22
Is there anything I should change? Please suggest since I am a newbie to Oracle. — Preethi Ganesan
– Preethi Ganesan, Commented Jul 19, 2016 at 3:30

Andrew Lygin · Accepted Answer · 2016-07-19 06:04:27Z

1

Neither your stored procedure nor your code contain COMMIT operation, so your insertion just rolls back (table truncation cannot be rolled back, so you see its effect anyway). It looks like your SQL editor has autocommit option on, but your program's connection parameters has not. So, you have several options:

Add explicit COMMIT to your stored procedure
Call objConn.commit() after executing your query.
Set autocommit option on your connection (see details here).

answered Jul 19, 2016 at 6:04

Andrew Lygin

6,2271 gold badge36 silver badges38 bronze badges

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

2 Comments

Preethi Ganesan Over a year ago

Yes. Adding COMMIT statement solved my problem. Thanks!

Andrew Lygin Over a year ago

I'm glad it helped you. You can mark the answer as correct for other people to know they can rely on it.

Collectives™ on Stack Overflow

Oracle Insert query in stored procedure not working when called from code behind

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related