Passing variable on a for loop statement

[EdRev]

I have a local variable that I need to pass to my for..loop statement:

extract_date Date := sysdate-1;
strMonth char(3):= to_char(extract_date, 'MON');

for rec in (select unit,decode(account,800,strMonth,0) from tbl)
Loop...

end loop;

I'm getting an error message when I pass the strMonth to the statement.

I tried this one:

strSQL = 'select unit,decode(account,800,'||strMonth||',0) from tbl';

for rec in (strSQL)

Loop...

End Loop;

with no luck.

Any help will be greatly appreciated.

 

[AOLEURO]

Hi EdRev


You need to assign a column name to your decode column, like this:

select unit, decoce(account,800,StrMonth,0) as XXX from tbl


Please show ORA-xxx error when you wants help.


What do you have in your FOR..LOOP statement - can't you do SQL statement(s)? Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[EdRev]

Thanks Allan,

My proc will create a fixed-width file in our database server. Here's the complete code.

Create or replace procedure spExtract
AS
--file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
v_line VARCHAR2(255);
v_fname VARCHAR2(20);
extract_date Date;
strMonth varchar2(3);
intMonth char(2);
strYear char(4);
intYear char(2);
begin
extract_date := sysdate-1;
strMonth := to_char(extract_date, 'MON');
intMonth := to_char(extract_date, 'mm');
strYear := to_char(extract_date, 'yyyy');
intYear := substr(to_char(extract_date, 'yy'), 1,2);

-- Open file to write into and get it's file_handle
-- file_handle := UTL_FILE.FOPEN('/IBM/outgoing',v_fname,'W');
FOR rec IN (select distinct unit.mem_name as unit ,sum(decode(unit_base.line_id,80000376,strMonth,0)) as DAYU,sum(decode(unit_base.line_id,80000259,strMonth,0)) as RNTL,sum(decode(unit_base.line_id,80000015,strMonth,0)) as M0992 from unit_base,unit_tree,unit where (unit_base.ver_id = 1 and unit_base.unit_id=unit_tree.mem_id and unit_base.unit_id = unit.mem_id) and (UNIT_TREE.MEM_PID = '80000000' OR UNIT_TREE.MEM_PID = '80000002') group by unit.mem_name)
LOOP
v_line := intYear || intMonth || rec.unit || to_char(rec.DAYU,'9999999') || to_char(rec.RNTL,'9999999') || to_char(rec.M0992,'9999999');
-- Write a line of text out to the file.
UTL_FILE.PUT_LINE(file_handle, v_line);
END LOOP;
-- CLose the file.
UTL_FILE.FCLOSE(file_handle);

EXCEPTION
WHEN OTHERS THEN UTL_FILE.FCLOSE(file_handle);
end;




And here's the error message:

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "SPEXTRACT", line 20
ORA-06512: at line 1

 

[EdRev]

I have been commenting lines out, trying to output to console. The code above might still have the comments.The proc works when I hard-code the actual column name (i.e jan) instead of passing the local var to the statement.

 

[R1972]

try this...

for rec in (select unit,decode(to_char(account),'800',strMonth,'0') from tbl)

Regards,
Raj

 

[jag14612]

I'd say you were having problems here:
sum(decode(unit_base.line_id,80000376,strMonth,0))

strMonth is a char and 0 is a number. The evaluation of the decode happens before the sum, so you try to sum up 0 with a 3 char field. Of course aum only works with numerics.

What I would suggest is taking the sql out of the code and putting into toad or sqlplus. Then substitute 'MAR' for strmonth in the sql. Run it. it is works then its a parameter passing problem.
I'm guessing it won't run so next I'd take the distinct, sum and group by out of the code. You should then run it and get a picture of what your data looks like so you can reconstruct the query.
I can't tell what you are really trying to do but I am guessing you are trying to count products and group by month.

I really hope I helped.
Good luck.