I suppose I've been spoiled by using SQL Server for so long now. I've got a really slow connection to a data warehouse that lives offsite (new company that shopped out most of it's work initially). So I want to write SP's to query faster and return data. in Sybase and Microsoft's SQL Server, you can simply but the query in an SP construct and everything is fine. I understand you need a cursor, or some sort of return variable, to do the same with Oracle (8i, if that's important).
Here is an example given to me which, unfortunately, doesn't work. However, it at least contains a simple query that does work on it's own. Can someone help me with the syntax I need to return the resultset?
note: the package compiles with no errors, but the SP doesn't
CREATE OR REPLACE PACKAGE types
TYPE ref_cursor IS REF CURSOR;
CREATE OR REPLACE PROCEDURE sptest
types.ref_cursor IS REF CURSOR;
OPEN types.ref_cursor FOR
SELECT ES.EMPLOYEE_NUMBER, ES.NAME
FROM CPC1.EMPLOYEE_SNAPSHOT_CPC ES;