Re: sql server stored procedure to oracle

In article <Xns930361747701Epingottpingottbah_at_216.166.71.233>, Pablo says...
>
>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
>different than mine.

[Quoted] [Quoted] I know, I was never the keeper of the Sybase FAQ ;) 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)

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

well, if you didn't

create package types as type rc is ref cursor; end;

you are right -- you would have to do that (or in 9i, just

create procedure do_it_babycakes( p_cursor in out sys_refcursor ) as
begin

    select col_1 from my_big_table;
end;

or -- using the correct technique for Oracle

[Quoted] create view do_it_babycakes as select col_1 from my_big_table;

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

[Quoted] you have how many extra lines of code here? How many lines more code do you have? Umm, two

o a handle allocate for a stored procedure "handle" o a bind call
o an execute of the stored procedure

It would be ("extra" code marked with a "*" ):

   *OCIHandleAlloc(g_pOciEnvironment, (void **)(&pOciStatement),

                  OCI_HTYPE_STMT, 0, NULL);
   OCIStmtPrepare(pOciStatement, pOciError, (unsigned char *)sqlCharArray, 
                  strlen(sqlCharArray),OCI_NTV_SYNTAX, OCI_DEFAULT);

   OCIHandleAlloc(g_pOciEnvironment, (void **)(&cursor), OCI_HTYPE_STMT, 0, 
                  NULL);

   *OCIBindByPos(pOciStatement,&pBind, pOciError, 1, &cursor, 0,SQLT_RSET,
                 pIndicator2, 0,NULL, 0,0,OCI_DEFAULT);

   OCIStmtExecute(g_pOciServiceContext, pOciStatement, pOciError, 1, 0, NULL, 
                  NULL, OCI_COMMIT_ON_SUCCESS);

   OCIDefineByPos(cursor,&pOciDefine, pOciError,2,&id,sizeof(int),
                  SQLT_INT,pIndicator, 0, 0,OCI_DEFAULT);
   OCIDefineByPos(cursor,&pOciDefine2, pOciError,1,ename,40,
                  SQLT_STR,pIndicator3, 0, 0,OCI_DEFAULT);
 
  while ((answer = OCIStmtFetch(cursor,pOciError, 1,
                                OCI_FETCH_NEXT,OCI_DEFAULT)) == 0)
       printf("fetched id %d and name %s\n",id,ename);

[Quoted] [Quoted] is that really such a cludge? 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.

> 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] it is *two lines of code* you are talking about -- *two lines*. in order to run a query straight we:

allocate handle
prepare SQL
bind any inputs
define any outputs
execute it
fetch

in order to run a query from a plsql routine we:

allocate handle
prepare PLSQL
bind any inputs

• allocate SQL handle for result set
• bind it as an output execute it define outputs for SQL fetch

it is not significantly different -- there is no double execute, not heinous bind, and rebind.

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

[Quoted] use a view, same net effect.

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

[Quoted] *it is two lines of code*

>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] well, you know what -- I've *never* started with raw sql first -- never. So, maybe it is a matter of perspective. I start with plsql -- do all things data in the database. Maybe it is a matter of technique. you do sql in the code and [Quoted] then move the sql into stored procedures. Maybe you need to just move the sql into a view?

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