[ cf-dev ] OT: Problems with Stored Proc

[Osullivan Karl (RKB) Senior Analyst/Programmer]

Hello,

I'm trying to modify the example in the CF manual (under CFPROCPARAM) so I
can return a recordset made up from the Oracle-supplied emp and dept tables,
but I just keep getting package compilation errors.  I've added the param5
and the EDRec.  I think my main problem is in the package where I try and
define the EmpDeptTyp cursor as EDRec, as this always goes wrong here.  How
do I define my own recordset to return stuff from a stored procedure?

Thanks.



CREATE OR REPLACE PACKAGE empdept AS

        TYPE EDRec IS RECORD (
                ename varchar2(10),
                deptno number(2),
                dname varchar2(14));
        TYPE EmpTyp IS REF CURSOR RETURN emp%ROWTYPE;
        TYPE DeptTyp IS REF CURSOR RETURN dept%ROWTYPE;
        TYPE EmpDeptTyp IS REF CURSOR RETURN EDRec%TYPE;
        
        PROCEDURE refcurproc(pParam1 IN OUT EmpTyp,
                                                 pParam2 IN OUT DeptTyp,
                                                 pParam3 IN INTEGER,
                                                 pParam4 OUT VARCHAR2,
                                                 pParam5 IN OUT EmpDeptTyp);
END empdept;

CREATE OR REPLACE PACKAGE BODY empdept AS

        PROCEDURE refcurproc(pParam1 IN OUT EmpTyp,
                                                 pParam2 IN OUT DeptTyp,
                                                 pParam3 IN INTEGER,
                                                 pParam4 OUT VARCHAR2,
                                                 pParam5 IN OUT EmpDeptTyp)
IS
        BEGIN
                OPEN pParam1 FOR
                        SELECT  *       FROM emp;
                OPEN pParam2 FOR
                        SELECT *        FROM dept;
                OPEN pParam5 FOR
                        SELECT  e.ename, e.deptno, d.dname
                        FROM    e.emp, d.dept
                        WHERE   e.deptno = d.deptno;
                If pParam3 = 1 THEN
                        pParam4 := 'hello';
                ELSE
                        pParam4 := 'goodbye';
                END IF;
        END refcurproc;
END empdept;

Karl O'Sullivan
Senior Analyst Programmer
University Hospitals Coventry and Warwickshire NHS Trust
Tel: 024 7660 2020 x7578
[EMAIL PROTECTED]

-- 
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]