View Single Post
  #1 (permalink)  
Old June 7th, 2006, 05:58 AM
acoles acoles is offline
Registered User
 
Join Date: Jun 2006
Location: London, , United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default return a table/rowset with pl/sql procedure

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