Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 7th, 2006, 05:58 AM
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




  #2 (permalink)  
Old June 7th, 2006, 05:15 PM
Registered User
 
Join Date: Jun 2006
Location: Rouen, France, France.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




  #4 (permalink)  
Old February 14th, 2008, 01:50 AM
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;




  #5 (permalink)  
Old February 14th, 2008, 01:54 AM
Registered User
 
Join Date: Feb 2008
Location: chennai, tamilnadu, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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..!

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
index by table in pl/sql vipin1982 All Other Wrox Books 1 October 1st, 2008 12:19 PM
PL/SQL Procedure madhav007 Oracle 1 May 29th, 2005 01:22 AM
PL/SQL cooldude87801 Oracle 1 March 3rd, 2005 10:54 AM
PL/SQL i need help loveboy23 Oracle 3 January 6th, 2005 01:14 AM
PL/SQL help Blue Oracle 0 August 12th, 2003 02:54 PM



All times are GMT -4. The time now is 01:43 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.