Re: sql server stored procedure to oracle

Thomas Kyte <tkyte_at_oracle.com> wrote in news:b03tjq02k8k_at_drn.newsguy.com:

> I know, I was never the keeper of the Sybase FAQ ;)

[Quoted] Heh heh! Not sure if that's a Good Thing [tm] or not. That was one of those things where no one else wanted the job and silly me, I jumped on the grenade.

> I was just a user of Sybase (i did dblib -- believe me -- you are
> 100% on the perspective thing, given that my background was sql/ds
> on VM/CMS, db2 on MVS, ingress/informix/oracle on unix -- Sybase was
> as *different* as you could get)

[Quoted] Now if you want to raise the ante, I was doing HP/3000 MPE with Relate/3000 and before that, with the HP DBMS -- gosh I can't recall the name.... blah, blah, blah... :)

> create view do_it_babycakes as select col_1 from my_big_table;

Hmmm, mine was an example to keep things simple and drive the point home so distilling it to a view for the example works, but in real life ... we won't go there.

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

and a handle de-allocate for that "handle" -- we do _want_ to ensure we don't bloat our code.

> o a bind call

But not by 'name' but by position. The issue here is brittleness.

> [ example snipped ]

> is that really such a cludge?

Hmmm, let me look at it another way, is this an argument of whether you like strawberry and I like vanilla? I think it might be to some degree. Not entirely but as I think about it, I realize that _technically_ you're correct as well as I am.

There are several ways to build a system and some are tidier than others. I argue that how Oracle is implementing multi-row sets with stored procedures is inconsistent than when raw SQL is used.

I may not necessarily like the brittleness of having to bind by position nor the extra code in the sproc and the OCI but frankly, tough tootles.

> Another way to look at this -- a different perspecive -- is that the
> sqlserver approach is messy, undocumented, you need to READ the code
> of the procedure in order to understand what it'll be returning. How
> many result sets does that give out? 1, 2, N? As least Oracle
> provides formal named parameters for self documenting code.

Thomas, that's very bad ... come on now, no one really belives in 'self documenting code!'

[ As an aside, the Oracle code would only tell you that it's _possibly_ returning more than one line of code. <g> But really, I have to assume you're joking ... ]

>

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

Hold a sec... we're talking two lines of code for the sproc and _no_ changes to the API. Oracle has to affect the sproc _and_ the front-end.

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

Let's not go to views as we know the issues with them ... search google on proponents of Oracle who advise _against_ their use!

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

Sorry if that irked ya... I've already listed that it's only two lines of code for Sybase with no changes to the Sybase API.

> well, you know what -- I've *never* started with raw sql first -- never.

Neither have I but unfortunately, my experience with my clients is contrary. These are not small shops either.

> Maybe you need to just move the sql into a view?

No, I want the Optimizer to not wedge itself. :)

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