Are packages the only way to return data from an Oracle db?

I've mostly worked with SQL Server so far and now I'm moving to Oracle for a new project.

I'm trying to create a proc that will return data to a .net app. The only way I got this to work is by using packages like this:

CREATE OR REPLACE PACKAGE GetAllDepartments AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE p_GetAllDepartments
(
    cur_Result OUT T_CURSOR
);
END GetAllDepartments;

CREATE OR REPLACE PACKAGE BODY GetAllDepartments AS
PROCEDURE p_GetAllDepartments
(
    cur_Result OUT T_CURSOR
)
IS
BEGIN
    OPEN cur_Result FOR
    SELECT * FROM DEPARTMENTS;

END 开发者_开发问答p_GetAllDepartments;
END GetAllDepartments;

Is this the only way to go with Oracle?, can't I just create the proc and call that directly? Thanks

Assuming you have a supported version of Oracle, you should be able to do something like

CREATE OR REPLACE PROCEDURE get_all_departments( p_result OUT SYS_REFCURSOR )
AS
BEGIN
  OPEN p_result
   FOR SELECT *
         FROM departments;
END get_all_departments;

That said, you are normally better off from an organization standpoint using packages to collect procedures that do related things. In your case, for example, it would generally make sense to have a package that had all the procedures that dealt with departments (i.e. create_department, delete_department, etc.).

And from a general stylistic standpoint, it is more common in Oracle to create a view that implements whatever logic you would put in the stored procedure and to query that view rather than creating a stored procedure that just does a query.

You can do that without a package, e.g. by creating a function that returns result sets.

Those functions can be used like tables, e.g.: SELECT * FROM my_function()

This is especially efficient with pipelined function because the result is not buffered on the server but sent row by row to the client:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#i1008519

But the basic technique is still the same i.e. you have to define a type that is used for the return type of the function. You cannot have it return an "anonymous" result set like in PostgreSQL or SQL Server.