View Single Post
  #4 (permalink)  
Old February 14th, 2008, 01:50 AM
raja084 raja084 is offline
Registered User
 
Join Date: Feb 2008
Location: chennai, tamilnadu, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by acoles
 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;