Re: Problem migrating from SQL Server to Oracle 8i

From: Michael Krolewski <donotreply_at_interbulletin.bogus>
Date: Sat, 17 Mar 2001 14:58:22 +0000
Message-ID: <3AB37B8E.531A0BBF@interbulletin.com>

"Jon" <jleather_at_adelphia.net> wrote in article <UIBr6.27648$St6.17998727_at_news2.news.adelphia.net> :
>I'm currently migrating a database from a SQL server to Oracle (and I am a
>newbie to Oracle). The database is used by an thin client VB app which
>expects recordsets returned from the database when it calls a stored
>procedures. However, I've come across a problem i cant seem to find a
>solution to. Stored procedures in oracle do not seem to be able to return
>recordsets... simple example
>
>in SQL server (Transact-SQL):
>
> create or replace procedure sp_test
> (
> @cid int
> )
> AS
>
> select * from CITIES where CityId = @CID;
>
>
>
>however the following in PL/SQL generates error cause its expecting an into
>
>
> create or replace procedure sp_test (CID in CITIES.CityId%TYPE) is
>
> select * from CITIES where CityId = CID;
>
> end sp_test;
>
>
>
>Can someone give me some help on how to return complete recordsets?
>
>Thanks.
>
>
>

Without drawing too much flack, basically Oracle does not have recordsets. It has cursors. They are different. Cursor control the selection and fetching of data from the database. They require more upfront work (IMHO) but give you more control on what and when things happen (IMHO) than recordsets.

Oracle's java does support the simplest type of java recordsets. IMHO, this simple form of recordset is behaving like a cursor -- only forward scrolling and working with one record at a time.

Oracle can obviously do any task that you can do in SQLServer. The methodology is different.

If you look into the SQLServer, you will find that it also supports cursors. You need to adapt your code as such.

The basics are found in the manuals. Also the O'Reilly books on PL/SQL have reasonable examples.

In the above example it is not possible to determine if one or more rows are going to be returned. If you are only expecting 0 or 1 rows, you could write:

create or replace function sp_test (CID in CITIES.CityId%TYPE) returning CITIES%ROWTYPE is

      aRow CITIES%ROWTYPE;
begin

       select * 
       into aRow
       from CITIES where CityId = CID;

       return aRow;
exception
       when NO_DATA_FOUND then
           return null;
       when <more than one row returned> then
           return aRow;

end;

Here the single row is written into aRow. The first exception is to handle no rows being returned. (needed) The second exception ( I cannot recall the specific exception name) is to handle if more than one row is returned. Oracle considers this an error.

The cursor is one mechanism for getting back data. There is also the 'reference cursor' which is too simply, a pointer to a cursor. It can be transferred between procedures. Once opened, other procedures can fetch rows.

There are other ways to transfer information between functions/procedures including simple datatypes, PL/SQL tables ( a simple table structure with some restrictions), and user defined records.

Michael Krolewski
mkrolewski_at_rii.com

---

Submitted via WebNewsReader of http://www.interbulletin.com Received on Sat Mar 17 2001 - 08:58:22 CST