STP stage for Oracle

[Ultramundane]

Would appreciate it if someone could please provide an example of a Oracle package with a simple procedure to return multiple rows to the STP stage? Please provide both the Specification and Body of package.

The example in the Ascential documentation is riddled with errors and does not even return one row.

Thanks.

[lstsaur]

Hi,
Try the following code:
CREATE OR REPLACE PACKAGE "LEOS_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;
/
CREATE OR REPLACE PACKAGE BODY "LEOS_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;
/

It works for me perfectly using STP stage. Good luck.

[Ultramundane]

Thanks. Your example package works. Nothing like the Ascential documentation.

However, the STP bug I wrote up exists with Sybase, Oracle, and DB2.

[Ultramundane]

A question related more to Oracle than the STP stage. Why is Oracle so slow at returning a result set of data in procedure? Is it because Ascential is looping through the result set through a cursor? Sybase can return the same data 25 times faster through a procedure on a IBM RS6000 server that is much slower than the Oracle instance's server.