Defining a cursor of %TYPE rather than %ROWTYPE

From: Ian <ian.ewing_at_gmail.com>
Date: Tue, 2 Oct 2012 10:04:22 -0700 (PDT)
Message-ID: <0497059e-62cb-4f29-bca7-684c3474d154_at_googlegroups.com>

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; Received on Tue Oct 02 2012 - 19:04:22 CEST