I'm not familiar with Oracle, but this looks close enough to Postgresql that I'll show how I would do it in PG/SQL in hopes that it will be useful.
I would create the function in the database
CREATE OR REPLACE FUNCTIOM test_fucntion(_name TEXT)
RETURNS BIGINT AS $BODY$
DECLARE
_rv BIGINT
BEGIN
INSERT INTO test_table(name)
VALUES(_name) RETURNING id into _rv;
RETURN _rv;
END;
$BODY$
LANGUAGE plpgsql;
After adding that function to the database you can simply execute the query
SELECT * from test_function("name");
to get your ID.
As I said, this is Postgres code, but I'm pretty sure the Postgres authors imitated Oracle to some extent.
Mike
