Re: sql server stored procedure to oracle

"ping" <ping_at_nospam.com> wrote in
[Quoted] news:b00fsk$j1u$1_at_reader1.imaginet.fr:

> Hi Actualy migrating stored procedures from sql server 2000 to
> oracle 9i
>
> The main differance betwwen SQL server & Oracle stored procedure
> is that Oracle's SP can't return a result set

They can but it's cumbersome to say the least. SQL Server/Sybase handle it logically: you return one row or multiple rows the same way.

[Quoted] If you're going to return multiple rows using PL/SQL to an OCI 8/9 application, you have to code it differently than if you're only returning a single row (SQL Server/Sybase treat it the same).

[Quoted] The PL/SQL will have to return a REF CURSOR and the OCI 8/OCI 9 code needs to bind a result set (or ref cursor) to that cursor (depends on whether the connection was opened with objects or not). The OCI code needs to issue an OCIStmtExecute after the bind(s) which must have a statement handle pointer associated to the ref cursor too! After the 'execute, you must re-bind by position on the second statement handle to fetch the data. Confused yet? Oracle really drops the ball here.

Here's an excellent example from Mark Tomlinson:

http://groups.google.com/groups?q=ref+cursors+oci+8+group:comp.databa ses.*&hl=en&lr=&ie=UTF-8&selm=352bc955.225454947%40newshost.us.oracle .com&rnum=1

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com