SQL & PL/SQL

Hi,

I'm working with the tool DataStage to transport data from database to another. Now I'm faced with an Oracle database (version 9.2) with some predefined stored procedures I can use to get the data I need. These stp's all return their dataset by using a ref cursor as output argument. The problem is that DataStage can't use these ref cursors. It needs something of this form:

PACKAGE "DSSP3" AS
cursor GET_COL return dept%rowtype;
procedure dssp3(DEPTNO_ARG OUT DEPT.DEPTNO%TYPE,
DNAME_ARG OUT DEPT.DNAME%TYPE,
LOC_ARG OUT DEPT.LOC%TYPE);
END;

PACKAGE BODY "DSSP3" AS
CURSOR GET_COL return dept%rowtype IS SELECT DEPTNO, DNAME, LOC FROM DEPT;
PROCEDURE DSSP3 (DEPTNO_ARG OUT DEPT.DEPTNO%TYPE,
DNAME_ARG OUT DEPT.DNAME%TYPE,
LOC_ARG OUT DEPT.LOC%TYPE) IS
BEGIN
IF NOT GET_COL%ISOPEN THEN
OPEN GET_COL;
END IF;
FETCH GET_COL INTO DEPTNO_ARG,DNAME_ARG,LOC_ARG;
IF GET_COL%NOTFOUND THEN
CLOSE GET_COL;
RAISE_APPLICATION_ERROR(-20001, 'EOD FOUND');
END IF;
END;
END;

The cursor has to be defined explicit, otherwise DataStage only processes the first row (DataStage calls the stored procedure once for every row it needs to process). DataStage stops processing rows as soon as the RAISE_APPLICATION_ERROR function is called with a certain code (20001 in this case). My idea was to call one of the stored procedures I can use from the Oracle database, however, it's not allowed to declare a ref cursor on package level. Is there a way to put the result of ref cursor into an explicitly declared cursor (like in the exmaple above) or is there another way around?

Thanks in advance for any ideas.

Regards,
Aart Onkenhout
Aegon
The Netherlands