Defining a cursor of %TYPE rather than %ROWTYPE - comp.databases.oracle.server

The old Google Groups will be going away soon, but your browser is incompatible with the new version.

« Groups Home

comp.databases.oracle.server

Discussions

+ new post

About this group

Subscribe to this group

This is a Usenet group - learn more

Defining a cursor of %TYPE rather than %ROWTYPE

Options

4 messages - Collapse all

Ian

View profile

More options Oct 2 2012, 1:04 pm

Newsgroups: comp.databases.oracle.server

From: Ian <ian.ew...@gmail.com>

Date: Tue, 2 Oct 2012 10:04:22 -0700 (PDT)

Local: Tues, Oct 2 2012 1:04 pm

Subject: Defining a cursor of %TYPE rather than %ROWTYPE

Print | Individual message | Show original | Report this message | Find messages by this author

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;