newbie HELP: converting Sybase/SQLServer multi-row select procedure to PL/SQL

From: Jeffrey Mark Braun <jeffb_at_halcyon.com>
Date: 11 Apr 1999 18:19:51 -0700
Message-ID: <7erhnn$6uq$1@halcyon.com>

I have a large number of stored procedures written for Sybase and SQLServer which I need to convert over to Oracle's stored procedure way of doing things, but I'm running into some problems (I think I'm missing something key with basic Oracle PL/SQL programming).

Here's an example of a simple stored procedure I might have in Sybase (still in the Sybase syntax):

   create procedure names
   as

      select
         LastName,
         FirstName,
         Alias,
         NickName,
         AgentCode
      from
         NameTable

   go

This stored procedure would return a result set (probably consisting of multiple rows) to the caller (in my case a client using JDBC, which would step through each row in the result set).

I tried doing a simple translation of the syntax to PL/SQL making something that looks more like this:

   create or replace procedure names
   as
   begin

      select
         LastName,
         FirstName,
         Alias,
         NickName,
         AgentCode
      from
         NameTable;

   end name;
/

But SQL*Plus complains that I need an INTO in my SELECT statement. So I put in more variables, and I ended up with something like this:

   create or replace procedure names

          (
            outLastName      OUT VARCHAR2,
            outFirstName     OUT VARCHAR2,
            outAlias         OUT VARCHAR2,
            outNickName      OUT VARCHAR2,
            outAgentCode     OUT VARCHAR2
         )

   as
   begin

      select
         LastName,
         FirstName,
         Alias,
         NickName,
         AgentCode
      into
         outLastName,
         outFirstName,
         outAlias,
         outNickName,
         outAgentCode
      from
         NameTable;

   end name;
/

And this seems to work for a single row of values (even though I have to now access a series of separate variables), but I don't know how I can get the next row of values from the SELECT. Besides the fact that it would be nice to not even bother with defining these output variables and simply return some sort of multiple result set to the caller (if this is possible).

This clearly must be a simple problem, and I think my newness to PL/SQL is hampering my general understanding of how to write a Oracle stored procedure. Can someone give me a brief explanation on how to write the procedure I'm looking for? I'd certainly be grateful.

Oh, and any email sent directly to me (which I find helpful) I promise to repost to this newsgroup for others to learn from.

Thanks a bunch.

Jeff Braun
jeffb_at_halcyon.com Received on Sun Apr 11 1999 - 20:19:51 CDT