Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Ef6 code first with MapToStoredProcedures insert

va_geekNov 26 2020

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; 

Comments

Alex Keh-Oracle
Answer

You can limit the length of your identifiers by setting the RelationalModelAnnotations.MaxIdentifierLength property. The documentation that is part of the EF Core beta has more info on how to use this property.

Marked as Answer by 18d56f16-5b80-4617-bffb-d3ebc1d4e94b · Sep 27 2020
1 - 1

Post Details

Added on Nov 26 2020
3 comments
477 views