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

Hi,

I used Sql Server for a few month, and i can understand your intention.
Transac SQL and PL/SQL have a major difference about the execution context. if a transac stored proc could be considered like a "group of sql orders", a pl one should be seen similar to a java external function that is just "inside" the database.
You can notice that when trying for example to execute a ddl statement. In pl you should use "execute immediate" function to "submit" it to the sql engine whereas in transac you can even ask for a restoration by simpling writing the order.

I'm probably not very easy to understand, but i mean you shouldn't think the same way you did with msql.

A pl stored proc (or function) was not natively made to return a resulset. It is now possible, but i am not very "up to date" with pl/sql features. You should look after returning "ref cursor" or "pl table" and return casting with table() function. ( I hope a pl/sql expert will answer you and will be more accurate than i am)

In Oracle, the views are really powerful. They are very used, and you should use a stored proc only when you want to "totally" create a resulset or apply an heavy process to data before getting them.
these occasions should be rare in my opinion....

Hope this help
-----------------------------------
Shogre
Oracle DBA / Apave NO France

Thanks shogre. I am certainly finding differences to the approaches of PL/SQL vs T-SQL and I think it will just take me a little while to adjust!

The main reason for using the stored procedure is because I shall be calling it from a web application. In this way I can restrict my web server Oracle account only to have permission on a very small set of operations by only providing access to these procedures.

I have received a practical solution from Luciano Pimentel in another group which I shall post here for anyone looking for a similar solution.


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 just started working in pl/sql functions.
could you please tell me hot to retrieve a table of data using cursor and store it in a file? I am finding it hard to do it..!