Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Example retrieving data using Oracle REF cursors (Read 4487 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Example retrieving data using Oracle REF cursors
25.01.07 at 21:01:08
 
Example retrieving data using Oracle REF cursors
 
Data can be retrieved from the Oracle database through REF cursors. SQL*XL has a special command that can be used in the SQL select statement to retrieve data from such a REF cursor: recordset_from_proc.
 
The usage in a SQL statement is as follows:
Code:
select * from recordset_from_proc('granted.list_roles(?)'); 


 
This statement calls the list_roles procedure in the granted package. It will return a ref cursor marked with the placeholder ?. The returning rows will be put in Excel by SQL*XL.
 
If you would like to try this out yourself please create the granted package:
PL/SQL package specification
Code:
CREATE OR REPLACE package granted is
    type rc_roles is ref cursor return user_role_privs%rowtype;
    procedure list_roles(o_roles out rc_roles);
end;
 


 
PL/SQL package definition
Code:
CREATE OR REPLACE package body granted as
  procedure list_roles(o_roles out rc_roles) is
  begin
	open o_roles for 
	   select * from user_role_privs;
  end;
end;
 

Back to top
 
« Last Edit: 25.01.07 at 21:03:50 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1