View Single Post
  #3 (permalink)  
Old June 8th, 2006, 10:22 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

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;