return a table/rowset with pl/sql procedure

[aco...@gmail.com]

Hi,

I have recently started working with Oracle having been using SQL
Server for the last few years and I having problems implementing a
simple procedure in PL/SQL to return a table of results. I would like
to create a set of procedures to be used by a web application to return
subsets of tables based on an input parameter.

In an example:

If I have a table [tbl_user_roles] linking user IDs and roles (and a
given user can have several roles):

user_id | role
----------------------
1 | Developer
1 | User
2 | System Admin

I want my procedure to take a username and return a list of roles. In
effect returning the result of:

SELECT role FROM tbl_user_roles WHERE user =
<PROCEDURE_INPUT_PARAMETER>

In this example I require only one column of data to be returned
however I also require some procedures to return tables with multiple
columns.

This should be straight forward however I am struggling to find any
examples to help!
What is the easiest way of achieving this?

Many thanks!

Andrew

[Luciano Pimentel]

CREATE OR REPLACE PACKAGE pkg_test AS

   -- Create a table type of the table you want
   TYPE tbl_test IS TABLE OF tbl_user_roles%ROWTYPE INDEX BY BINARY_INTEGER;
  
   -- Function that will return the table type
   FUNCTION fnc_test ( user_id VARCHAR2 ) RETURN tbl_test;
  
-- End package  
END;

CREATE OR REPLACE PACKAGE BODY pkg_test AS

   FUNCTION fnc_test ( param_user_id VARCHAR2 ) RETURN tbl_test IS
           
      CURSOR cur_test IS SELECT user_role
                           FROM tbl_user_roles
                          WHERE user_id = param_user_id;
                         
      -- Position of the array
      counter PLS_INTEGER := 1;
           
      -- Variable of the type tbl_test
      var_tbl_test tbl_test;     
     
   BEGIN  
      FOR reg IN cur_test LOOP
         var_tbl_test( counter ) := reg.user_role;
         counter := counter + 1;
      END LOOP;
     
      -- Returns the table type populated with roles of the user
      RETURN var_tbl_test;
     
   -- End function
   END;
  
-- End package body
END;

I think this should help

--
LUCIANO
"Ágatha, seja bem vinda! - ★ 17/01/2006 ★"
"Gulego, descanse em paz - † 15/09/2005 †"

[aco...@gmail.com]

Thanks for the quick reply Luciano.. my problem is solved!

Andrew