cursor for loop returns o/p as cursor

[san1014]

Hi
I am new to cursors
I have a table
SQL> select * from commodity_m_details;

COMMODITY_ID COMMODITY_NAME STAR END INTERVAL
-------------------- ------------------------------ ---- ---- ---------------
C1 Commodity1 1996 2006 2
C2 Commodity2 1996 2000 2
C3 Commodity3 1983 1995 4

i need to write a procedure for different years for every commodity
o/p is like
For C3
year
-----------------
1983-1986
1986-1989
1989-1992
1992-1995
i have written like this

create or replace procedure getYear(commodityid in varchar2,
                 results out resultscur.r,
                 status out varchar2)
is
V_data number(10);
cursor c1 is select start_year,end_year,interval_number from commodity_m_details where commodity_id=commodityid;
begin
V_data:=0;
FOR record in c1
LOOP
V_data:=V_data+(record.end_year-record.start_year)/record.interval_number;
V_data:=record.start_year+V_data;
end loop;
DBMS_OUTPUT.PUT_LINE(V_data);
commit;
status:='0';
exception
when others then
status:='7';
end getYear;

/
But it is giving only 1986
please help me

Thank you

[amitpatel66]

Hi
I am new to cursors
I have a table
SQL> select * from commodity_m_details;

COMMODITY_ID COMMODITY_NAME STAR END INTERVAL
-------------------- ------------------------------ ---- ---- ---------------
C1 Commodity1 1996 2006 2
C2 Commodity2 1996 2000 2
C3 Commodity3 1983 1995 4

i need to write a procedure for different years for every commodity
o/p is like
For C3
year
-----------------
1983-1986
1986-1989
1989-1992
1992-1995
i have written like this
create or replace procedure getYear(commodityid in varchar2,
results out resultscur.r,
status out varchar2)
is
V_data number(10);
cursor c1 is select start_year,end_year,interval_number from commodity_m_details where commodity_id=commodityid;
begin
V_data:=0;
FOR record in c1
LOOP
V_data:=V_data+(record.end_year-record.start_year)/record.interval_number;
V_data:=record.start_year+V_data;
end loop;
DBMS_OUTPUT.PUT_LINE(V_data);
commit;
status:='0';
exception
when others then
status:='7';
end getYear;
/
But it is giving only 1986
please help me

Thank you

Yes for C3 it will print only 1986 because:

check below lines:

V_data := 0;
V_data:=V_data+(record.end_year-record.start_year)/record.interval_number;
V_data:=record.start_year+V_data;

Eg:

V_data:= 0 + (1995 - 1983)/4 -- this will give you 3
V_data:= 1983 + 3 -- this will give you 1986
DBMS_OUTPUT.PUT_LINE(V_data);

In order to get 1986,1989,1992,1995, you need to add 3 to the year 4 times.
Eg:

V_data now = 1986
adding 3 to it will givve V_data:= 1989 then 1992 then 1995 as you require

So For each record from cursor, LOOP through "interval no of times" (ie 4 times here), calculate (end_year - start_year)/interval and store it in a variable V_data
Now do the following:

 
test_data VARCHAR2(10) := start_year;
result VARCHAR2(100);
<your cursor LOOP here>
V_data:=  (end_year - start_year)/interval;
For I IN interval LOOP
test_data:= test_data + V_data;
result:= result||' '||test_data;
END LOOP;
DBMS_OUTPUT.PUT_LINE(result);
V_data:= NULL;
result:= NULL;
END LOOP;
 
 

[san1014]

Hi, my table structure is changed(interval as increment)

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

Please give me a query for this

Thank u

[debasisdas]

Kindly post what you have tried so far.

[san1014]

I tried like this

declare
V_x number(10);
V_start number(10);
V_end number(10);
V_inc number(10);
V_data number(10);
--V_res number(10);
begin
select start_year,end_year,increment_value into V_start,V_end,V_inc from commodity_m_details where commodity_id='&commodity_id';
DBMS_OUTPUT.PUT_LINE((V_end-V_start)/V_inc);
For rec in 1..((V_end-V_start)/V_inc)
loop
V_data:=0;
V_data:=V_data+(V_start+(2*rec-V_inc));
end loop;
DBMS_OUTPUT.PUT_LINE(V_data);
end;

/

[amitpatel66]

I tried like this

declare
V_x number(10);
V_start number(10);
V_end number(10);
V_inc number(10);
V_data number(10);
--V_res number(10);
begin
select start_year,end_year,increment_value into V_start,V_end,V_inc from commodity_m_details where commodity_id='&commodity_id';
DBMS_OUTPUT.PUT_LINE((V_end-V_start)/V_inc);
For rec in 1..((V_end-V_start)/V_inc)
loop
V_data:=0;
V_data:=V_data+(V_start+(2*rec-V_inc));
end loop;
DBMS_OUTPUT.PUT_LINE(V_data);
end;

/

Hint: Instead of using a single SELECT, make use of CURSOR and loop through CURSOR for each commodity_id

[san1014]

i am already tried using cursor
But i am unable to get the required o/p

Plase help me

[amitpatel66]

i am already tried using cursor
But i am unable to get the required o/p

Plase help me

Try this:

 
DECLARE
CURSOR C1 IS SELECT start_year,end_year,interval FROM table_name;
test_data VARCHAR2(10);
BEGIN
FOR I IN C1 LOOP
IF(C1%FOUND) THEN
test_data:= := I.start_year;
V_data:=  (I.end_year - I.start_year)/I.INTERVAL;
FOR I IN 1..I.INTERVAL LOOP
test_data:= test_data + V_data;
DBMS_OUTPUT.PUT_LINE(test_data);
END LOOP;
V_data:= NULL;
test_data:= NULL;
END LOOP;
END IF;
END;
 

[san1014]

Ya Finally i got my requirement as

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

[debasisdas]

Use ref cursor as the OUT parameter.

[san1014]

ofcourse,
out_val out resultscur.r this is of type refcursor only

And also i changed like

create or replace procedure getYear1(commodityid in varchar2,
out_val out refcursor)
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;
/

Then it is giving exception as

SQL> show error
Errors for PROCEDURE GETYEAR1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/13 PLS-00201: identifier 'REFCURSOR' must be declared

Please help me

[amitpatel66]

ofcourse,
out_val out resultscur.r this is of type refcursor only

And also i changed like

create or replace procedure getYear1(commodityid in varchar2,
out_val out refcursor)
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;
/

Then it is giving exception as

SQL> show error
Errors for PROCEDURE GETYEAR1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/13 PLS-00201: identifier 'REFCURSOR' must be declared

Please help me

say SYS_REFCURSOR and not refcursor

[san1014]

Thats not the matter.
Becaue before that i wrote a package as below

create or replace package resultscur
is
TYPE r IS REF CURSOR;
end resultscur;
/

Any way i tried like what u suggested,

create or replace procedure getYear1(commodityid in varchar2,
out_val out sys_refcursor)
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 its also not working

[amitpatel66]

Thats not the matter.
Becaue before that i wrote a package as below

create or replace package resultscur
is
TYPE r IS REF CURSOR;
end resultscur;
/

Any way i tried like what u suggested,

create or replace procedure getYear1(commodityid in varchar2,
out_val out sys_refcursor)
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 its also not working

Code is erroring out or giving wrong results?
If erroring out, please post the error message else post the output that you are getting?

[san1014]

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 '>'

[amitpatel66]

create or replace procedure getYear1(commodityid in varchar2,
out_val out sys_refcursor)
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;
/

The use of out_value is incorrect in your code.
We cannot use a cursor object in this way.
Why do you want to return a CURSOR here.
You dont need any value to be returned since the procedure itself prints the value using DBMS_OUTPUT package.
Instaed of using out_value in all your manipulations, make use of simple integer variable.