Hello,

I have a question about cursors. I have created a simple package, package body, and procedure. Everything works fine (code to follow), my goal is to move the cursors out of the procedure and into the package body, and declare them in the package. However I have only been able to use cursors that return %ROWTYPE to declare effectively, if I use %TYPE is complains:
4/1      PL/SQL: Declaration ignored
4/32     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed.
Is there a way to create a cursor that returns %TYPE, can a reference cursor be used for that?

Thanks!
code:
----------------------------------------PACKAGE HEADER-------------------------------------------

create or replace package TESTING
is
cursor their_names return customers%ROWTYPE;
procedure InvoiceCalc;
function ship_dates(a in orders.ono%TYPE)
return date;
end;
------------------------------------------PACKAGE BODY----------------------------------------------
create or replace package body TESTING
is
        cursor their_names return customers%ROWTYPE
        is
                select *
                from customers;
        function ship_dates(a in orders.ono%TYPE)
        return date
        is
                ship_date date;
        begin
                select shipped
                into ship_date
                from orders
                where orders.ono=a;
                return ship_date;
        end;
        procedure InvoiceCalc
        is
                CUST_ID customers%ROWTYPE;
                test_ono orders.ono%TYPE;
                test_ODpno odetails.pno%TYPE;
                test_ODono odetails.ono%TYPE;
                test_ODqty odetails.qty%TYPE;
                test_Ocno orders.cno%TYPE;
                test_Pprice parts.price%TYPE;
                test_Psubtotal parts.price%TYPE;
                test_TOTAL parts.price%TYPE;
                test_TEMP number;
                test_Cname customers.cname%TYPE;

        cursor their_ordernumber
        is
                select distinct orders.ono
                from customers,orders
                where customers.cno=orders.cno
                and   customers.cno=CUST_ID.cno;
        cursor their_orderdetails
        is
                select a.pno,a.qty,b.price*a.qty
                from odetails a, parts b
                where a.pno=b.pno
                and   a.ono=test_ODono;
begin
        open their_names;
        loop
                fetch their_names into CUST_ID;
                exit when their_names%NOTFOUND;
                dbms_output.put_line('Customer: '||CUST_ID.CNAME||'     ID: '||CUST_ID.CNO);
                test_TEMP:=0;
                open their_ordernumber;
                loop
                        fetch their_ordernumber into test_ODono;
                        exit when their_ordernumber%NOTFOUND;
                        dbms_output.put_line('Customer Order Number is: '||test_ODono);
                        open their_orderdetails;
                        loop
                                fetch their_orderdetails into test_ODpno,test_ODqty,test_Psubtotal;
                                exit when their_orderdetails%NOTFOUND;
                                dbms_output.put_line('Part #: '||test_ODpno||'    Quantity Ordered: '||test_ODqty||'    SubTotal: '||test_Psubtotal);
                                test_TEMP:=test_TEMP+test_Psubtotal;
                                test_TOTAL:=test_TEMP;
                        end loop;
                        if(ship_dates(test_ODono) is NOT NULL) then
                                dbms_output.put_line('SHIPPED ON: '||ship_dates(test_ODono));
                        else
                                dbms_output.put_line('SHIPPED ON: NOT YET SHIPPED');
                        end if;
                        close their_orderdetails;
                end loop;
                dbms_output.put_line('                                            Grand Total: '||test_TOTAL);
                DBMS_output.NEW_LINE;
                close their_ordernumber;
        end loop;
        close their_names;
        end InvoiceCalc;
end;

Comments embedded.

As it should; cursors return rows of data, even those that return single columns.

No, that is not possible.  Not in a currently available release of Oracle.

David Fitzjarrell

Ahh, thank you!
Ian