469,356 Members | 1,978 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

cursor for loop returns o/p as cursor

37
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
Expand|Select|Wrap|Line Numbers
  1. create or replace procedure getYear(commodityid in varchar2,
  2.                  results out resultscur.r,
  3.                  status out varchar2)
  4. is
  5. V_data number(10);
  6. cursor c1 is select start_year,end_year,interval_number from commodity_m_details where commodity_id=commodityid;
  7. begin
  8. V_data:=0;
  9. FOR record in c1
  10. LOOP
  11. V_data:=V_data+(record.end_year-record.start_year)/record.interval_number;
  12. V_data:=record.start_year+V_data;
  13. end loop;
  14. DBMS_OUTPUT.PUT_LINE(V_data);
  15. commit;
  16. status:='0';
  17. exception
  18. when others then
  19. status:='7';
  20. end getYear;
/
But it is giving only 1986
please help me

Thank you
Oct 30 '07 #1
15 3843
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. test_data VARCHAR2(10) := start_year;
  3. result VARCHAR2(100);
  4. <your cursor LOOP here>
  5. V_data:=  (end_year - start_year)/interval;
  6. For I IN interval LOOP
  7. test_data:= test_data + V_data;
  8. result:= result||' '||test_data;
  9. END LOOP;
  10. DBMS_OUTPUT.PUT_LINE(result);
  11. V_data:= NULL;
  12. result:= NULL;
  13. END LOOP;
  14.  
  15.  
Oct 30 '07 #2
san1014
37
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
Nov 1 '07 #3
debasisdas
8,127 Expert 4TB
Kindly post what you have tried so far.
Nov 1 '07 #4
san1014
37
I tried like this
Expand|Select|Wrap|Line Numbers
  1. declare
  2. V_x number(10);
  3. V_start number(10);
  4. V_end number(10);
  5. V_inc number(10);
  6. V_data number(10);
  7. --V_res number(10);
  8. begin
  9. select start_year,end_year,increment_value into V_start,V_end,V_inc from commodity_m_details where commodity_id='&commodity_id';
  10. DBMS_OUTPUT.PUT_LINE((V_end-V_start)/V_inc);
  11. For rec in 1..((V_end-V_start)/V_inc)
  12. loop
  13. V_data:=0;
  14. V_data:=V_data+(V_start+(2*rec-V_inc));
  15. end loop;
  16. DBMS_OUTPUT.PUT_LINE(V_data);
  17. end;
/
Nov 1 '07 #5
amitpatel66
2,367 Expert 2GB
I tried like this
Expand|Select|Wrap|Line Numbers
  1. declare
  2. V_x number(10);
  3. V_start number(10);
  4. V_end number(10);
  5. V_inc number(10);
  6. V_data number(10);
  7. --V_res number(10);
  8. begin
  9. select start_year,end_year,increment_value into V_start,V_end,V_inc from commodity_m_details where commodity_id='&commodity_id';
  10. DBMS_OUTPUT.PUT_LINE((V_end-V_start)/V_inc);
  11. For rec in 1..((V_end-V_start)/V_inc)
  12. loop
  13. V_data:=0;
  14. V_data:=V_data+(V_start+(2*rec-V_inc));
  15. end loop;
  16. DBMS_OUTPUT.PUT_LINE(V_data);
  17. end;
/
Hint: Instead of using a single SELECT, make use of CURSOR and loop through CURSOR for each commodity_id
Nov 1 '07 #6
san1014
37
i am already tried using cursor
But i am unable to get the required o/p

Plase help me
Nov 1 '07 #7
amitpatel66
2,367 Expert 2GB
i am already tried using cursor
But i am unable to get the required o/p

Plase help me
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. CURSOR C1 IS SELECT start_year,end_year,interval FROM table_name;
  4. test_data VARCHAR2(10);
  5. BEGIN
  6. FOR I IN C1 LOOP
  7. IF(C1%FOUND) THEN
  8. test_data:= := I.start_year;
  9. V_data:=  (I.end_year - I.start_year)/I.INTERVAL;
  10. FOR I IN 1..I.INTERVAL LOOP
  11. test_data:= test_data + V_data;
  12. DBMS_OUTPUT.PUT_LINE(test_data);
  13. END LOOP;
  14. V_data:= NULL;
  15. test_data:= NULL;
  16. END LOOP;
  17. END IF;
  18. END;
  19.  
Nov 1 '07 #8
san1014
37
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
Nov 2 '07 #9
debasisdas
8,127 Expert 4TB
Use ref cursor as the OUT parameter.
Nov 2 '07 #10
san1014
37
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
Nov 2 '07 #11
amitpatel66
2,367 Expert 2GB
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
Nov 2 '07 #12
san1014
37
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
Nov 2 '07 #13
amitpatel66
2,367 Expert 2GB
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?
Nov 2 '07 #14
san1014
37
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 '>'
Nov 2 '07 #15
amitpatel66
2,367 Expert 2GB
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.
Nov 2 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dmitry Duginov | last post: by
reply views Thread by Anthony Robinson | last post: by
1 post views Thread by Søren Larsen | last post: by
1 post views Thread by traceable1 | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.