Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Defining a cursor of %TYPE rather than %ROWTYPE
61 views
Skip to first unread message
Ian
Oct 2, 2012, 1:04:22 PM10/2/12
to
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;
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;
ddf
Oct 2, 2012, 4:11:19 PM10/2/12
to
Comments embedded.
On Tuesday, October 2, 2012 11:04:22 AM UTC-6, Ian wrote:
> 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.
>
As it should; cursors return rows of data, even those that return single columns.
> Is there a way to create a cursor that returns %TYPE, can a reference cursor be used for that?
>
No, that is not possible. Not in a currently available release of Oracle.
>
>
> Thanks!
>
David Fitzjarrell
On Tuesday, October 2, 2012 11:04:22 AM UTC-6, Ian wrote:
> 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!
>
David Fitzjarrell
Mladen Gogala
Oct 2, 2012, 6:18:11 PM10/2/12
to
On Tue, 02 Oct 2012 13:11:19 -0700, ddf wrote:
> No, that is not possible. Not in a currently available release of
> Oracle.
>
Jonathan posted a teaser about partitioning in 12C. Larry said that 12C
> No, that is not possible. Not in a currently available release of
> Oracle.
>
will become available at the early January 2013. Maybe then....
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Ian
Oct 3, 2012, 11:46:39 AM10/3/12
to
Ian
0 new messages