Procedure error

[jcasas23]

What is wrong with this sentence?

The following error has occurred:

ORA-06550: line 5, column 20:
PLS-00905: object DEVCBS.RCURSOR2 is invalid
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

This is the sentence that I am running. in PL-SQL

CREATE OR REPLACE FUNCTION rcursor2
(p_cia number, p_month number, p_year number)
return number is
l_query long;
l_tipo long;
l_label long;
nun number;
p_start number;
begin
l_query := '(select bpl.line_id, bl.description ';
for j in 1 .. 4 loop
if j = 1 then
l_tipo := ', bpl.prod_ptge';
l_label := 'P';
end if;
if j = 2 then
l_tipo := ', bpl.qual_ptge';
l_label := 'Q';
end if;
if j = 3 then
l_tipo := ', bpl.qual_record';
l_label := 'R';
end if;
if j = 4 then
l_tipo := ', bpl.supp_ptge';
l_label := 'S';
end if;
select count(*), min(bp.period) into nun, p_start
from devcbs.b_periods bp
where bp.cia = p_cia
and bp.cmonth = p_month
and bp.year = p_year;
for i in 1 .. nun loop
l_query := l_query || ', sum( decode( bpl.period, ' ||
to_char(p_start+i-1) || l_tipo || ', 0 )) "' || l_label ||
to_char(i) || '"';
end loop;
end loop;
l_query := l_query || ' from devcbs.b_ptge_line bpl, devcbs.b_lines bl '
|| 'where bpl.line_id = bl.line_id group by bpl.line_id, bl.description )';
delete devcbs.b_temp_rep;
for ctemp in l_query loop
if n = 4 then
insert into devcbs.b_temp_rep values(ctemp.line_id, ctemp.description,
ctemp.p1, ctemp.p2, ctemp.p3, ctemp.p4, 0,
ctemp.q1, ctemp.q2, ctemp.q3, ctemp.q4, 0,
ctemp.r1, ctemp.r2, ctemp.r3, ctemp.r4, 0,
ctemp.s1, ctemp.s2, ctemp.s3, ctemp.s4, 0);
end if;
if n = 5 then
insert into devcbs.b_temp_rep values(ctemp.line_id, ctemp.description,
ctemp.p1, ctemp.p2, ctemp.p3, ctemp.p4, ctemp.p5,
ctemp.q1, ctemp.q2, ctemp.q3, ctemp.q4, ctemp.q5,
ctemp.r1, ctemp.r2, ctemp.r3, ctemp.r4, ctemp.r5,
ctemp.s1, ctemp.s2, ctemp.s3, ctemp.s4, ctemp.s5);
end if;
end loop;
return (1);
end;
/

Thanks in advance

 

[SantaMufasa]

K'zaz,

That error message just means that some token upon which your procedure, "rcursor2", depends upon has changed and as a result, the status of "rcursor2" has become "INVALID". to resolve the problem (barring any other syntax errors in your procedure code), just issue the following command:

ALTER PROCEDURE rcursor2 COMPILE;

If the result is "Procedure altered.", then you should be good to go.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:59 (13Oct04) UTC (aka "GMT" and "Zulu"), 16:59 (13Oct04) Mountain Time)