dbms_output

[qwerty123987]

At the bottom of this code are tow dbms_output statements. The second one always fails with this error message

"The following error has occurred:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_OUTPUT", line 64
ORA-06512: at line 17"

Does anyone know why the second one would fail?

Thanks

Note - mon_dt7 is a column of char(7) that is always in the format YYYY-MM

declare
test char(10);
cursor csr is
select max(mon_dt7) mon_dt7
from table1

lrt csr%ROWTYPE;

begin
open csr;
fetch csr into lrt;
select max(mon_dt7) into test
from table1
dbms_output.put_line(test);
dbms_output.put_line(lrt.mon_dt7);
end;

 

[scotty7998]

The following should correct the error:
dbms_output.put_line(to_char(lrt.mon_dt7,'YYYY-MM'));

 

[qwerty123987]

I had tried that, and it also fails. It gives an error of:
The following error has occurred:

ORA-06550: line 17, column 22:
PLS-00307: too many declarations of 'TO_CHAR' match this call
ORA-06550: line 17, column 1:
PL/SQL: Statement ignored

 

[KarenF]

Hi,

I'm not sure why this problem is occurring, but it appears that the '%rowtype' isn't working very well with the max function. If you fetch into an explicitely declared variable it works:
declare

cursor csr is select max(mon_dt7) max_m from table1;
lrtvar char(7);

begin
open csr;
fetch csr into lrtvar;
dbms_output.put_line(lrtvar);
close csr;

end;

Karen.