Importing procedure from an oracle package using Entity Framework 5

Question

CREATE OR REPLACE PACKAGE johns_test_pkg AS

  PROCEDURE test(some_parameter IN  NUMBER,
                 success_id     OUT NUMBER);

  PROCEDURE test_no_out_parameter(some_parameter IN NUMBER);

  PROCEDURE test_no_in_parameter(success_id OUT NUMBER);

END johns_test_pkg;
/

CREATE OR REPLACE PACKAGE BODY johns_test_pkg AS

  --
  PROCEDURE test(some_parameter IN  NUMBER,
                 success_id     OUT NUMBER)
  IS
    v_app_user_session_id INTEGER;
    BEGIN
      v_app_user_session_id := 1 + some_parameter;

      success_id := v_app_user_session_id;
    END;

  --
  PROCEDURE test_no_out_parameter(some_parameter IN NUMBER)
  IS
    v_app_user_session_id INTEGER;
    BEGIN
      v_app_user_session_id := 1 + some_parameter;
    END;

  --
  PROCEDURE test_no_in_parameter(success_id OUT NUMBER)
  IS    
    v_app_user_session_id INTEGER;
    BEGIN
      v_app_user_session_id := 1 + 10;
      success_id := v_app_user_session_id;
    END;

END johns_test_pkg;
/

Given the above simple Oracle package with three procedures within. I have tried to add these procedures to my model using Entity Framework 5 with no avail. I have been able to add a few Oracle procedures that are not in packages.

I have been reading about this and some other questions are similar how-to-call-oracle-function-with-return-value-using-linq-to-entities and read on from the selected answer. The person states that IN OUT parameters or OUT parameters should work, but none of my three were imported. I would have expected that test_no_in_parameter procedure would get loaded?

Is it possible to load a procedure under a package?

Answer 1

When in the edmx model and you right click "Update Model from Database" Un-tick "Import selected stored procedures and functions into the entity model", select the procedures out of the package and update. (It works) Then in "Model Browser" under "Model.Store\ Stored Procedures/Functions" you will see your procedures from the package (finally). Then map them into "Function Imports".

I do not understand why un-ticking the import stored procedures works, but it does.

Answer 2

If EF 5 has troubles with importing procedures from Oracle packages there are two possibilities to work around.

• Call any procedure in an anonymous block:

  var param1 = new OracleParameter("p_param_in", OracleDbType.Number, 123,  ParameterDirection.Input);
  var param2 = new OracleParameter("p_param_out", OracleDbType.Number, ParameterDirection.Output);
  
  var param2_val = db.Database.SqlQuery<YourTestEntity>(
                 "BEGIN johns_test_pkg.test(:p_param_in, :p_param_out); END;" 
                 , param1).Single();
  

• Create a wrapping stored procedure for every needed procedure in the package. I think it's an appropriate way to satisfy EF and keep your code robust (without shallow quickie anonymous blocks). The only problem is you are to find good naming style for wrappers considering that Oracle has 30 symbols name limitation.

  CREATE OR REPLACE my_wrap_prefix__test(some_parameter IN  NUMBER,
                                         success_id     OUT NUMBER)
  IS BEGIN
      johns_test_pkg.test(some_parameter, success_id);
  END;