Re: sql server stored procedure to oracle

From: Pablo Sanchez <pablo_at_dev.null>
Date: Tue, 14 Jan 2003 10:34:50 -0600
Message-ID: <Xns930361747701Epingottpingottbah_at_216.166.71.233>

[Quoted] Thomas Kyte <tkyte_at_oracle.com> wrote in news:b01b8n012p3_at_drn.newsguy.com:

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

Nope.

> I don't get it --

Let me explain because your perspective/starting point is vastly [Quoted] different than mine. If I have an OCI application and I send the following raw SQL down the line:

  "select col_1 from my_big_table;"

I can bind col_1 to a host variable and OCTStmtFetch() to return all the rows in the query. No problem. I do essentially the same in Sybase and MS SQL Server.

Next step in the progression is to make the above call into a stored procedure.

In Sybase and MS SQL Server we do the following:

---

create proc do_it_babycakes as

   select col_1 from my_big_table
go

---

Done. Logical. Clean.

In Oracle, what do we need to do? Ah yes, the 'ref cursor' but wait there's more, not only do we need to do that, but because PL/SQL needs the type defined beforehand, we need to create a 'package' to define the data type before we can type the output parameter. Instead of two lines now we need 10 lines of code.

---

create or replace package my_package as

type ref_cursor_type is ref cursor;

procedure do_it_babycakes (o_data OUT ref_cursor_type) /

create or replace package body my_package as

procedure do_it_babycakes (o_data OUT ref_cursor_type)

begin

   open o_data for select col_1 from my_big_table; end;

---

[Quoted] and furthermore because we're now dealing with potentially more than one row, the OCI code that you wrote to handle the raw SQL case no [Quoted] longer works. Now we have to bind a ref cursor and an extra statement handle, then we execute that SQL, then we bind yet again to the extra statement handle and fetch the data again. All courtesy of Oracle's poor implementation of multiple rows.

[Quoted] What do we do with Sybase/MS SQL at the API level? Nothing. The code remains the same.

[Quoted] Yes, this might sound like I'm bashing Oracle but frankly, I'm above that, what I'd like to see is for Oracle to 'grow up' and do this right. It's silly to have people go through so many hoops for something trivial.

> neither harder, nor significantly different from the others.

I never said it was hard but it's definitely more cumbersome and frankly, if Oracle engineering would design PL/SQL cleaner (how long did it take to merge the PL/SQL engine with the SQL engine???!) we wouldn't have this mess.

>

>>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).
>>

>
> that -- i just don't get. You'd have to show me --

Done! See above.

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

[Quoted] The fact is that people code with raw SQL first, then migrate to stored procedures. Because Oracle treats the two differently, it's a royal pain in the pitusky.

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

Received on Tue Jan 14 2003 - 17:34:50 CET