Re: sql server stored procedure to oracle

[Quoted] In article <Xns930342A43C7A2pingottpingottbah_at_216.166.71.233>, Pablo says...
>
>"ping" <ping_at_nospam.com> wrote in
>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.
>

and Oracle doesn't? I don't get it --

begin

   open ref_cursor for select * from emp where rownum = 1;

begin

   open ref_cursor for select * from emp;

one of those returns 1 row in a result set, one of them returns 14. How are they different??

In any case, see
http://asktom.oracle.com/~tkyte/ResultSets/index.html

has example code for oci, proc, sqlplus, java/jdbc, vb/asp, perl showing how to bind to a ref cursor and get result sets back.

[Quoted] neither harder, nor significantly different from the others.

>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] that -- i just don't get. You'd have to show me -- I would not code it any [Quoted] differently. True -- if i KNOW I'll get getting a single row, I might CHOOSE to use select ... into in the plsql routine and return them via formal parameters to the procedure -- but that is a choice, not a mandate.

you can open rc for select * from t -- to return a row as easily as zero rows or a billion.

>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

--
Thomas Kyte (tkyte_at_oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp