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

Gbenga Ajakaye

What specifically did you change in your Network Gateway?

2744216

Only the IP Address changed from A.B.C.D to A.B.C.E

Gbenga Ajakaye

That's what I thought. In your question mentioned that no IP was changed.

Recently we had to change the network gateway for our 2 node MySQL cluster running on top of Oracle Grid (RHEL). None of the IPs changed. We noted the following behavior:

Can you look in the location below and make sure that the file reflects your changed IP.

/etc/hosts

2744216

The gateway IP address is not in the  hosts file, it is (and has only ever been) in a routing table.

1 - 4

Post Details

Added on Nov 26 2020
3 comments
515 views