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?

I am running into the same issue, have you ever resolved this. Any insight would be helpful. — Kar
– Kar, Commented May 6, 2015 at 14:53

pat capozzi · Accepted Answer · 2015-06-17 18:50:35Z

13

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.

answered Jun 17, 2015 at 18:50

pat capozzi

1,4691 gold badge21 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Debbie A Over a year ago

FYI: EF6.1.3 - I was able to add a package stored procedure that did a SELECT just fine. But it wouldn't add a package stored procedure that did an UPDATE - acted like it added it but it didn't. Unchecking the "Import selected stored procedures..." finally got it to recognize the UPDATE package stored procedure and added it to the model.

diziaq · Accepted Answer · 2015-11-26 09:45:43Z

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;
```

Collectives™ on Stack Overflow

Importing procedure from an oracle package using Entity Framework 5

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related