I try to switch from Microsoft provider for Oracle to Oracle Managed Driver for .NET.
Update: I use the dlls from the Oracle client 12.1.0.1.0 -> Version=4.121.1.0
For the following code I get the exception ORA-01013: user requested cancel of current operation:
using (OracleConnection connection = new OracleConnection(CustomConfigurationManager.ProjectManagerWebConnectionString.ConnectionString))
{
connection.Open();
using (OracleCommand command = new OracleCommand("insert into CONDITION (COND_PROJ_ID, COND_DESC) values (:pn_proj_id, to_char(:pd_date,'yyyy') || to_char(:pd_date,'yyyy')) returning COND_ID INTO :RETURN_VALUE", connection))
{
command.BindByName = true;
command.Parameters.Add(new OracleParameter("pn_proj_id", 1241));
command.Parameters.Add(new OracleParameter("pd_date", DateTime.Today));
OracleParameter retParam = new OracleParameter("RETURN_VALUE", -1);
retParam.Direction = System.Data.ParameterDirection.ReturnValue;
command.Parameters.Add(retParam);
int retVal = command.ExecuteNonQuery();
Response.Write(retVal);
}
connection.Close();
}
This is the condition table:
CREATE TABLE CONDITION
(
COND_ID NUMBER(8) NOT NULL,
COND_PROJ_ID NUMBER(8) NOT NULL,
COND_DESC VARCHAR2(500 BYTE) NOT NULL,
)
And it has an Id sequence trigger:
CREATE OR REPLACE TRIGGER TRG_CONDITION_BIU
BEFORE INSERT OR UPDATE ON CONDITION
FOR EACH ROW
DECLARE
/******************************************************************************
PURPOSE: Insert PK for each row if NULL
******************************************************************************/
BEGIN
IF :NEW.COND_ID IS NULL THEN
SELECT SEQ_COND.NEXTVAL INTO :NEW.COND_ID FROM DUAL ;
END IF;
END TRG_CONDITION_BIU;
/
END TRG_CONDITION_BIU;
The problem arises from the to_char(:pd_date,'yyyy') || to_char(:pd_date,'yyyy') and returning COND_ID INTO :RETURN_VALUE combination which is only an example of almost any function, package function call that takes two parameters or a parameter used twice.
Interestingly this works fine, if I do not use the RETURNING clause. Also it works fine with just one function parameter, or if no function is applied on the parameter.
Furthermore, if I wrap the INSERT statement around a BEGIN ....; END; clause and use ParameterDirection.InputOutput, it starts to work again (actually I employed this workaround).
So this is pretty weird, hope someone can reproduce it and tell me what I am doing wrong.