EntityFramework Oracle Insert procedure with result

Discussion of open issues, suggestions and bugs regarding Entity Developer - ORM modeling and code generation tool
Post Reply
brice.prunier
Posts: 1
Joined: Sun 07 Sep 2014 15:37

EntityFramework Oracle Insert procedure with result

Post by brice.prunier » Thu 23 Oct 2014 09:50

I have a basic use case : My TFILTER entity use a sequence and I want to use a procedure for insert that retrieve the new id.
I've follow your guidance for EF & procedure mapping but I can't make it work :
I can't map any output result :
On the Store Procedure Mapping dialog I can select my insert procedure but
Row Affected Parameters ComboBox is Empty
Result Column Bindings Grid can't be update

Here is my procedure :
CREATE OR REPLACE PROCEDURE EUROGAS.TFILTER__INSERT(
IN_STATUS VARCHAR2,
IN_NAME VARCHAR2,
IN_TABLE_NAME VARCHAR2,
IN_USER_ID VARCHAR2,
IN_TIMESTAMP DATE,
IN_FILTER_CONDITION VARCHAR2,
IN_COLUMN_DISPLAY_LIST VARCHAR2,
IN_CREATED_USER_ID VARCHAR2,
IN_PERSONAL_FLAG VARCHAR2,
IN_MULTI_TS_GROUP_FLAG VARCHAR2,
IN_UNIT_TYPE_CODE_ID NUMBER,
IN_DEFAULT_FLAG VARCHAR2,
OUT_CURSOR OUT NUMBER(9,0))
IS
P_FILTER_ID NUMBER(9,0);
BEGIN
SELECT EUROGAS.FILTER_ID.NEXTVAL INTO P_FILTER_ID FROM DUAL;
INSERT INTO EUROGAS.TFILTER (
FILTER_ID, STATUS, NAME, TABLE_NAME, USER_ID, TIMESTAMP, FILTER_CONDITION, COLUMN_DISPLAY_LIST, CREATED_USER_ID, PERSONAL_FLAG, MULTI_TS_GROUP_FLAG, UNIT_TYPE_CODE_ID, DEFAULT_FLAG
)
VALUES (P_FILTER_ID, IN_STATUS, IN_NAME, IN_TABLE_NAME, IN_USER_ID, IN_TIMESTAMP, IN_FILTER_CONDITION, IN_COLUMN_DISPLAY_LIST, IN_CREATED_USER_ID, IN_PERSONAL_FLAG, IN_MULTI_TS_GROUP_FLAG, IN_UNIT_TYPE_CODE_ID, IN_DEFAULT_FLAG);

OPEN OUT_CURSOR FOR SELECT P_FILTER_ID AS "OUT_FILTER_ID"
FROM DUAL;
END;

Shalex
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Re: EntityFramework Oracle Insert procedure with result

Post by Shalex » Thu 30 Oct 2014 15:32

by email you wrote:I found my error :
- I have to write the names of the columns return by the sys-refcursor by myself, it is not provided by the dialog.
Two tips:

1. As an alternative, you can rename "OUT_FILTER_ID" to "FILTER_ID" and "OUT_TIMESTAMP" to "TIMESTAMP" in your EUROGAS.TFILTER__INSERT procedure. In this case the column names in the cursor will be the same as the names of columns in the table, so:
a) EF runtime will initialize FILTER_ID / TIMESTAMP properties of the corresponding entity with values from cursor
b) you don't need to put any entries in the "Result Column Bindings" section on your screenshot

2. Please set StoreGeneratedPattern=Identity for FILTER_ID / TIMESTAMP properties in the storage (SSDL) part of model to make EF runtime to return values in FILTER_ID / TIMESTAMP properties after these values are generated in the database.
by email you wrote:By the way the doc is not clear on this point.
Refer to http://www.devart.com/entitydeveloper/e ... eloper.chm > ORM Support > Entity Framework > Concepts > Working with Classes > Stored Procedure Mapping.

Post Reply