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

"Vesa Nopanen" <nopanen_at_quartal.com> wrote in message news:9md4ta$118$1_at_news.clinet.fi...
> 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?

YES YOU NEED TO DO THAT Sequence is actually a better answer as it is not bound to a table.

>
> 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?

OF COURSE
>
> 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
>
>
>

PLEASE I PRAY AND BEG TO ALL people exposed to SQLserver NOT TO ASK THOSE TWO QUESTIONS ANYMORE
The newsgroups archives are FLOODED with answers to them by colleagues who also never read an Oracle manual.

Just using
http://groups.google.com/groups?q=identity&hl=en&safe=off&btnG=Google+Search &meta=site%3Dgroups%26group%3Dcomp.databases.oracle.server

returns 207! hits!!!
and

http://groups.google.com/groups?q=resultset&hl=en&safe=off&meta=site%3Dgroup s%26group%3Dcomp.databases.oracle.server

559 hits!

http://asktom.oracle.com has answers to those 2 questions. Usenet and the web is *flooded* with answers to those 2 questions.

Assuming Sqlserver people actually *can* read and *can* search, why do they never read a manual.

Also, about the WORST WAY to develop a proper Oracle application is to PORT it from Sqlserver!

Sorry for venting. Just check how often I answered those 2 questions. I'm getting fed up with it.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Mon Aug 27 2001 - 04:58:42 CDT