I want to use a stored procedure with an insert on a table with a sequence. I have been unable to get the correct combination of parameters and C# code. This question on StackOverflow has a great code example of what I'm attempting to do. Unfortunately, it's been there for three years without an answer. I'm hioping for better results here.
Question 46201628 - entity-framework-is-not-able-to-map-out-param-from-oracle-stored-procedure
I can't link so I will borrow/quote the code from that post.
I start with a stored procedure. The output param (Number type) should return the just inserted Primary Key/sequence value to the C# code.
CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE
(
p_employee_name IN EMPLOYEE.EMPLOYEE_NAME%TYPE
,OUT_EMPLOYEE_ID OUT EMPLOYEE.EMPLOYEE_ID%TYPE
)
AS
BEGIN
INSERT INTO EMPLOYEE
(
employee_name
)
VALUES
(
p_employee_name
) RETURNING employee_id into OUT_EMPLOYEE_ID;
COMMIT;
END INSERT_EMPLOYEE;
Here is my C# code (i.e. with the help of MapToStoredProcedures) making a call to INSERT_EMPLOYEE stored proc using EF6 and attempt to get the value in the OUT param (OUT_EMPLOYEE_ID in this case).
this.ToTable("EMPLOYEE");
.HasKey(model => model.EmployeeId);
// Property Mappings
this.Property(model => model.EmployeeId)
.HasColumnName("EMPLOYEE_ID")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(model => model.EmployeeName)
.HasColumnName("EMPLOYEE_NAME");
// Configure Stored Procedures
this.MapToStoredProcedures(p =>
{
p.Insert(sp =>
sp.HasName("INSERT_EMPLOYEE")
.Parameter(b => b.EmployeeName, "p_employee_name")
.Result(re => re.EmployeeId, "OUT_EMPLOYEE_ID")
);
});
When the application runs, it throws this exception. I can see that the output parameter has not been sent to the application.
OracleException: ORA-06550: line 1, column 8:
PLS-00306: wrong number or types of arguments in call to 'INSERT_EMPLOYEE'
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
I have tried the same thing using a SYS_REFCURSOR (partial snippet only) without success.
RETURNING employee_id into v_EMPLOYEE_ID;
OPEN OUT_ResultSet FOR SELECT v_EMPLOYEE_ID AS "OUT_EMPLOYEE_ID" FROM dual;