MSSQLServer->Oracle: Identity and returning a result set using oracle procedure or function

Hello

We are converting a MS SQLServer database and functionality to Oracle database and so far we have ran into two bigger problematics. IDENTITY. We use automatically updating ID field is several tables. Oracle does not seem to have a similar feature. SEQUENCE is almost the answer week are seeking; but you can not place call to sequence's nextval into table definition (DEFAULT). Are we correct if we assume that we only have two ways of solving this issue: either updating every INSERT from our application logic to include call to sequence to get nextval or write a trigger to each table that requires a Identity value?

Second question is of result sets. In SQLServer you can write a stored procedure with a select clause at the end. So when you call a stored procedure from business logic you get a result set back like you had called any other select. This is possible to do in DB2 also if Stored Procedure is defined to return result sets , opening a cursor in procedure and exiting from stored procedure without closing it. Is there a way to archieve the same result in Oracle?

Thank you for your time!
Vesa Nopanen

--

Q u a r t a l    O y

Vesa Nopanen, Senior Database Designer (nopanen_at_quartal.com)
http://www.quartal.com