Re: Oracle Stored Procedures

Carlos Pereyra wrote:

> I have searched high and low for the answer to this question.
>
> Can Oracle stored procedures return a result set?
> To clarify: In MS SQL Server and Sybase, you can write a stored
> procedure that behaves just like a standard query returning a record
> set to the executor.
> The Oracle ODBC help documentation references a way to accomplish the
> same result by using ref cursors (?) They include a sample C program
> that shows how to do it. I have tried to emulate the functionality of
> the C program using perl but with no success.
> We have dozens of SQL Server stored procs that do select statements
> that we will need to migrate to Oracle when we switch.
> Any help appreciated.
>
> Carlos.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

The following code shows an example of this (should work on your system)

Step 1: Create the PL/SQL package

• Package to demonstrate use of ref cursors create or replace package sgainfo AS TYPE sga_cur is ref cursor return v$sga%ROWTYPE; function get_sga_info (v_in number) return sga_cur; end sgainfo; /

create or replace package body sgainfo AS function get_sga_info (v_in number)
return sga_cur
IS
  r sga_cur;
begin

   Open r for select * from v$sga;
   Return (r);
end get_sga_info;

end sgainfo;
/

Step 2: Execute the function in a host environment like SQL*Plus:

SQL-> var a refcursor
SQL-> exec :a := sgainfo.get_sga_info(1);

PL/SQL procedure successfully completed.

SQL -> print a

NAME                      VALUE
-------------------- ----------
Fixed Size                47264
Variable Size         424542208
Database Buffers       49971200
Redo Buffers           10493952

I am not familiar with Perl and so can't help you there. But this method of declaring and using a function that returns a ref cursor should work in any environment capable of interacting with Oracle. We use it routinely

in UNIX shell scripts, Pro*C, Developer 2000 etc.

Hope that helps

Harry Received on Mon Oct 30 2000 - 22:17:20 CET