从后面的代码调用时，存储过程中的Oracle插入查询不起作用 [英] Oracle Insert query in stored procedure not working when called from code behind

问题描述

下面是我的存储过程，该过程在从SQL编辑器调用时可以正常工作.但是，从代码调用时，只有第一个语句(截断)有效.插入无效.谁能帮帮我吗.预先感谢.

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;

代码:

 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;
        }

推荐答案

您的存储过程和代码都不包含COMMIT操作，因此您的插入只是回滚(表截断无法回滚，因此无论如何都可以看到其效果) ).看起来您的SQL编辑器启用了autocommit选项，但是您的程序的连接参数却没有.因此，您有几种选择:

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:

1. 在您的存储过程中添加明确的COMMIT
2. 执行查询后致电objConn.commit().
3. 在连接上设置autocommit选项(请参阅详细信息

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

这篇关于从后面的代码调用时，存储过程中的Oracle插入查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！