Ef6 code first with MapToStoredProcedures insert — oracle-tech

    Forum Stats

  • 3,715,507 Users
  • 2,242,778 Discussions
  • 7,845,371 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Ef6 code first with MapToStoredProcedures insert

User_90SJH
User_90SJH Member Posts: 2

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; 


Answers

Sign In or Register to comment.