Writing Oracle Packages and Procedures

Oracle stored procedures can perform non-query activities such as INSERTs, UPDATEs, and DELETEs that do not necessarily return data.

However, in order for a stored procedure that does return data to work with Logi applications, it must return a valid result set, such as the output from a SELECT statement; data cannot be returned as individual values in output parameters. The package and procedure should be similar to this example, which returns a list of UserIDs from a table in the database :

PACKAGE LGX_GET_DATA
AS
TYPE UserCur IS REF CURSOR;
PROCEDURE AllUser(o_EmpCursor OUT UserCur);
END LGX_GET_DATA;

PACKAGE BODY "LGX_GET_DATA"
AS
PROCEDURE AllUser(o_EmpCursor OUT UserCur)
AS
BEGIN
OPEN o_EmpCursor FOR
SELECT USER_ID
FROM USER_ACCOUNTS
ORDER BY USER_ID;
END AllUser;
END LGX_GET_DATA;

Your stored procedure can have any number of input parameters but it can have only one output cursor. In addition, the output cursor must be last in your procedure's argument list. For example, this will work

procedure X( iParam1 in integer, iParam2 in integer, ..., o_cur out ioCursor )

but this will not:

procedure X( o_cur out ioCursor, iParam1 in integer, iParam2 in integer, .... )