Defining a cursor of %TYPE rather than %ROWTYPE
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