Home » SQL & PL/SQL » SQL & PL/SQL » How to declare the parameter as cursor (oracle 9i)
How to declare the parameter as cursor [message #278136] Fri, 02 November 2007 01:10 Go to next message
san1014
Messages: 4
Registered: November 2007
Location: Hyd
Junior Member
Hi
I have a table
sql>select * from commodity_m_details;

comm_id start_year end_year increment
------- ---------- ---------- -----------------
C1 1996 2006 2
C2 2000 2004 1
C3 1990 2010 4

Now i want the o/p for every comm_id like
For c1

1996
1998
2000
2002
2004
2006

For c2

2000
2001
2002
2003
2004

i tried and finally i got my requirement as

DECLARE
out_val varchar2(20);
begin
<< record_loop >>
for rec in (select start_year, end_year, increment_value from commodity_m_details where commodity_id='C3' )
loop
out_val := rec.start_year;
<< output_loop >>
loop
--pipe row (out_val); -- or
dbms_output.put_line(out_val);
out_val := out_val + rec.increment_value;
exit when out_val > rec.end_year;
end loop output_loop;
end loop record_loop;
end;
/


But the problem is i need to declre the out_value as a parameter of the procedure. But the o/p is a cursor.
I am declared the out_value as cursor as show in below procedure..

create or replace procedure getYear1(commodityid in varchar2,
out_val out resultscur.r)
is
begin
<< record_loop >>
for rec in (select start_year, end_year, increment_value from commodity_m_details where commodity_id=commodityid )
loop
out_val := rec.start_year;
<< output_loop >>
loop
--pipe row (out_val); -- or
dbms_output.put_line(out_val);
out_val := out_val + rec.increment_value;
exit when out_val > rec.end_year;
end loop output_loop;
end loop record_loop;
end;
/
but still i am getting the exception as


SQL> show error
Errors for PROCEDURE GETYEAR1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1 PL/SQL: Statement ignored
8/16 PLS-00382: expression is of wrong type
12/1 PLS-00306: wrong number or types of arguments in call to
'PUT_LINE'

12/1 PL/SQL: Statement ignored
13/1 PL/SQL: Statement ignored
13/12 PLS-00306: wrong number or types of arguments in call to '+'
14/1 PL/SQL: Statement ignored
14/19 PLS-00306: wrong number or types of arguments in call to '>'

Please tell me how to declare the out_val as a procedure parameter
(Bcoz by taking this o/p parameter only in front end they are displaying the o/p)
Please help me on this

thank u
Re: How to declare the parameter as cursor [message #278146 is a reply to message #278136] Fri, 02 November 2007 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

2/
There are so many errors in this code that we can't count them all in one shot.
* what is resultscur.r? Where is it declared?
* if is it a cursor what is the meaning of out_val := out_val + rec.increment_value? Can you add a value to a cursor?

Start by rethinking what you do, rewrite the whole and come back.


Regards
Michel
Re: How to declare the parameter as cursor [message #278147 is a reply to message #278136] Fri, 02 November 2007 01:46 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As you already declared it as a CURSOR, you shall work with it as with cursor; in this case OPEN it for constructed query.
By the way, do you store START_YEAR and END_YEAR as VARCHAR2 (the type of OUT_VAL variable)? Not a good idea really.

Back to topic, choose one of the row generator techniques (note, that some are not available or limited in 9i) and OPEN cursor for the constructed query.
Previous Topic: to get date in weeks
Next Topic: Fragmenting query
Goto Forum:
  


Current Time: Fri Dec 09 03:52:35 CST 2016

Total time taken to generate the page: 0.10671 seconds