Returning Identity from an InsertFunction

[DavePark]

Using dotConnect for Oracle v6.00.70

Due to various limitation in Entity Framework, I need to use a stored procedure as an InsertFunction for inserting new rows into one of my tables that also happens to have StoreGeneratedPattern="Identity". Unfortunately, I've tried various approaches and have still not had any luck. I'm now looking for guidance on what else to try.

Here's what I've tried so far and the results:

1a) Attempting to return the ID as a ReturnType:
Error 2046: A mapping function binding specifies a function NodeModel.Store.INSERTNODEFREEFORMTEXT that is not supported. Only functions that cannot be composed are supported.
1b) Changing IsComposable from true to false:
Error 137: Functions that cannot be composed must not declare a return type.
1c) Removing the ReturnType:
System.ServiceModel.DomainServices.Client.DomainOperationException: Submit operation failed due to conflicts. Please inspect Entity.EntityConflict for each entity in EntitiesInError for more information.
1d) Adding a RowsAffectedParameter:
The generated ID does not get sent back up the stack through RIA to Silverlight
2) Attempting to use a PIPELINED function:
ORA--14551: cannot perform a DML operation inside a query
3) Attempting to return ID as an OUT parameter:
Error 2047: A mapping function binding specifies a function NodeModel.Store.INSERTNODEFREEFORMTEXT2 with an unsupported parameter: PID. Output parameters may only be mapped through the RowsAffectedParameter property. Use result bindings to return values from a function invocation.

Any assistance would be much appreciated!

Thanks,

Dave

[DavePark]

I looked at how a standard insert works:

Code: Select all

declare
  updatedRowid ROWID;
begin
  insert into T(C1, C2)
  values (:p0, :p1)
  returning ROWID into updatedRowid;
  open :outParameter for select ID from T where ROWID = updatedRowid;
end;

So, I re-coded my stored function to return SYS_REFCURSOR, as follows:

Code: Select all

CREATE OR REPLACE
FUNCTION
	InsertT
(
	pC1 T.C1%type,
	pC2 T.C2%type
)
RETURN
	sys_refcursor
IS
	ID T.Id%type;
	csr sys_refcursor;
BEGIN
	INSERT
	INTO
		T
	(
		C1,
		C2
	)
	VALUES
	(
		pC1,
		pC2
	)
	RETURN Id INTO ID;
	open csr for select ID ID from dual;
	RETURN csr;
END;

This worked perfectly.

Note that I no longer needed the RowsAffectedParameter either.

Also note that I needed "ID" twice in the SELECT clause. Since I am selecting a local variable from dual, that local variable needs an alias.

[AndreyR]

Thank you for sharing the solution. This is an appropriate workaround.