SQL & PL/SQL

Hi,

I'm trying to create function with an insert statement that is built dynamically and executed.

I've got this working ok. but I'd like to be able to return the id of the new record created so I can return it from my main function.

I've got something like (this is a grossly simplified version)

create or replace my_function(P_1 in varchar2, P_2 in varchar) return number;

--variable to hold insert statement
v_insert_statement varchar2(32000);

--variable to hold the new record id
v_new_id number;

begin

v_insert_statement := 'insert into my_table(col1, col2) values ('||P_1||', '||P_2||') returning id into v_new_id';

execute immediate v_insert_statement;

return v_new_id;

end;

The above works fine for a simple insert but when I try to return the id. my v_new_id variable is not being assigned the value from the insert.

I'm guessing that the execute immediate command is running the insert in a different session so the v_new_id in the insert statement won't correspond to the one in my function.

How do I return the id of my new record in this instance?

Thanks

Yog