Returning Id To Query After Insert

[former_member1170711]

Hello,

I am looking for an efficient design pattern for performing a database insert and getting the unique row id back to MII.

After performing a database insert into an Oracle table I am looking for the best way to get back a trigger-generated unique id.

Currently I am using a PL/SQL procedure that uses the "returning" clause of an insert statement to get the id. After getting the id, it is returned to an MII query using a sys_refcursor. The code looks like this:

PROCEDURE TEST_INSERT_RETURNING_ID (oc_output OUT sys_refcursor)

IS

l_id NUMBER;

BEGIN

insert into tmse_cmnt (cmnt, person_creator, person_modr)

values ('MIKETEST', 'LANGEMI', 'LANGEMI') returning cmnt_id into l_id;

OPEN oc_output FOR select l_id as id from dual;

END TEST_INSERT_RETURNING_ID;

and the MII FixedQueryWithOutput query looks like this:

call pkg.TEST_INSERT_RETURNING_ID(?)

This works but I am thinking there must be a better way to do this. Is there another way to get the id back to MII without using the sys_refcursor? Since I only need to return one number (and not a table) this seems like it is probably inefficient.

I tried creating a function that does the insert and returns the id as a number and calling is from a select like this:

select insert_and_get_id() from dual

but this does not work since you cannot execute DML (an insert in this case) from a select statement.

Anyone have a better way to perform an insert and get the id back to MII?

Thanks,

Mike

[jcgood25]

For what it's worth there were some ideas floated in this thread:

[former_member1170711]

Thanks Jeremy. I looked through that thread and, unfortunately, it did not help.

On that thread Rick mentioned being able to do multiple SQL statements from a single Fixed Query. I have never done that. How do you separate them? Even if that can be done, you can't execute an insert statement from a Fixed Query so I think you would still need to go to PLSQL--unless you can somehow accomplish it using multiple statements in a Command query.

Any insights would be appreciated. I still think there must be a cleaner way to get the id back without using a cursor but so far I don't know what it is.

Thanks,

Mike