We're upgrading one of our applications from an Oracle database to a DB2 database, and I'm trying to modify one of my PL/SQL report scripts so that it will continue to work in DB2, but I'm running into problems.
Here's the watered-down code I'm using:
set serveroutput on size 1000000
--set verify off
set feedback off
set pagesize 2000
set linesize 300
--set trimspool on
set escape on
cursor received_files (FI_ROUTER_MASK varchar) is
select substr(jpstats.ah_archive1,instr(jpstats.ah_archive1,'- ')+2),
(jpstats.ah_timestamp1 + current timezone), 'MM/DD/YYYY HH24:MI:SS'),
when jpstats.ah_status between 0 and 1799 then 'Active'
when jpstats.ah_status between 1800 and 1899 then 'Issue encountered'
else 'FILE RECEIVED'
decode (callopstats.ah_name,'ZERO_BYTE_FILE_ALERT','Empty file',
'EB_DMZ_TO_ARCHIVE','File not fully uploaded',
'Other ('||callopstats.ah_archive1||')' )) note
from ah jpstats,
(select * from ah where ah_otype='CALL') callopstats
where jpstats.ah_name like 'EB_ARCHIVE_ROUTER%JP'
and jpstats.ah_archive1 like FI_ROUTER_MASK
and date(jpstats.ah_timestamp1 + current timezone )
order by 1,3;
procedure show_received_files(FI_ROUTER_MASK varchar) is
fetch received_files into filename,
EXIT WHEN received_files%NOTFOUND;
TZ := '&1';
JP_MASK := '&2'||'_%_JP';
ROUTING_TABLE := '&3';
FI_ROUTER_MASK := '&4 -%';
When I execute this, I'm getting no rows being returned from the cursor...or at least, nothing printed out from the fetch loop. However, I get several rows back when I run the cursor's query directly, replacing "FI_ROUTER_MASK" with an actual value (e.g., "'FI.TEST -%'"). I'm stumped.
I can't figure out if it's a problem (1) with the cursor definition, (2) the cursor's FI_ROUTER_MASK parameter, (3) a problem with the loop in the show_received_files procedure, or (4) a problem with how I'm building the FI_ROUTER_MASK variable.
Is anything jumping out at anyone here that's obviously wrong?
Thanks in advance!
Pinned topic problem with cursor code? (porting from Oracle to DB2)
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-03-14T14:22:37Z at 2013-03-14T14:22:37Z by nivanov1
nivanov1 100000H9H7231 Posts
Re: problem with cursor code? (porting from Oracle to DB2)2013-03-13T15:34:19ZThis is the accepted answer. This is the accepted answer.I would verify, if I were you, that the value of the parameter FI_ROUTER_MASK in the procedure is what you expect it to be.
SystemAdmin 110000D4XK17917 Posts
Re: problem with cursor code? (porting from Oracle to DB2)2013-03-13T21:02:50ZThis is the accepted answer. This is the accepted answer.
- nivanov1 100000H9H7
nivanov1 100000H9H7231 Posts
Re: problem with cursor code? (porting from Oracle to DB2)2013-03-14T14:22:37ZThis is the accepted answer. This is the accepted answer.
- SystemAdmin 110000D4XK
I don't see how that tells you what the value is in the procedure. I thought along the lines of using dbms_output.put_line()...