SQL Query in oracle pulling data from AS400, after 50 rows, oracle starts removing 1 character per column

What 'tool' are you using to query the data?
Does it do any sort of paging?

assuming you are using the odbc gateway, then check to see if the gateway is the problem or if the odbc driver is the problem.

use some other odbc tool, use the same DSN that the gateway uses and execute the query.

if you get the same results then the data or the odbc driver is the problem.
if you get the correct results then the data and odbc are fine and the gateway is messing up

or possibly it's how you are viewing the data and the results themselves are correct
to test this,  put the query in a pl/sql block to loop through and use dbms_output to display the results

i have ran the query through

Apex and sqldeveloper

when i query that same data from access it comes correct as it sits on the as400, when i query the data from isql, it comes correct as well

when you query through access and isql,  are you using the same dsn as the oracle gateway?

when i query from isql, i am querying from the same machine (linux) that oracle sits on using the same odbc drivers

not just the same drivers

is it the same dsn?

yes

if you established the data and dsn are correct, just keep going through the list I posted above.


it's either the gateway or the displaying method.

apex and sql*developer work reliably for me, but test them.

put the query in a pl/sql block, loop through it and use dbms_output.put_line to display the column value you're worried about.

if the output is correct then it's display method.  if the output is not correct then the gateway isn't returning the correct results.

in either case you'll have to go to oracle for support as apex, sql*developer and the odbc gateway are all from Oracle, but it'll be better to do the diagnostic before hand, otherwise you'll just have an extra layer of support to have to muddle through when you file the SR.

i do not have oracle SR. i will keep trying to trouble shoot, hopefully someone else will have some ideas

anyone else have any ideas, i have exhausted what i thought might be an issue

have you tried everything suggested above?  if not, please do.  if so,  please post results of each i.e. don't simply say "yes",  tell us what happened for each trial

i installed and enabled the dbms_output package but am affraid i do not know how to proceed in relation to this query, i am researching to see if i can figure it out without having to open up another thread

>>> i installed and enabled the dbms_output package

you shouldn't have needed to install that.  It should have already been in place but anyway, now that you have it.  What where the results of that trial?

There is no reason to open another thread as long as your pursuing the the suggestions introduced within this one.

well, i am not sure what it is that you are asking me to do by running the query in an pl/sql block and looping through it with dbms_output.put_line.

I do not know the correct syntax to achieve this and get the results you are asking for

begin
   for x in (select .....           --- put your query here
    ) loop
         dbms_output.put_line(x.column_name);  
   end loop;
end;


change "column_name" to be the column you think has the wrong results, leave the "x." in place

using

begin
   for x in (select 
  CM_PRIMRY_ROUTNO AS ROUTE, 
  CM_CUSTNO AS CUSTOMER_NUMBER,
  CM_CUSTMR_NAME AS NAME,
  CM_CUSTMR_ADDR_1 AS ADDRESS,
  CM_TKTMSG AS TICKET_MSG,
  ID_DESCRP AS DESCRIPTION,
  CM_PRIMRY_DLVSEQ_1,
  CM_PRIMRY_DLVSEQ_2,
  CM_PRIMRY_DLVSEQ_3,
  CM_PRIMRY_DLVSEQ_4,
  CM_PRIMRY_DLVSEQ_5,
  CM_PRIMRY_DLVSEQ_6,
  CM_NOWDUE AS BLNC, 
	CM_SLSMAN AS SALES_REP
FROM RAP003003.CUSMAS@"orcl.mycompany.com" INNER JOIN RAPFILES.IDCODEQRY@"orcl.mycompany.com" ON CUSMAS.CM_TKTMSG = IDCODEQRY.ID_FLDNUM  
WHERE CUSMAS.CM_PRIMRY_ROUTNO Not In (74,999) 
AND RAP003003.CUSMAS.CM_TKTMSG <> 4 
AND RAP003003.CUSMAS.CM_NOWDUE > =1000
AND RAPFILES.IDCODEQRY.ID_FLDLBL = 'TKTMSG'
AND RAP003003.CUSMAS.CM_BILCOD= 2
ORDER BY CUSMAS.CM_NOWDUE DESC
    ) loop
         dbms_output.put_line(x.ROUTE);   
   end loop;
end;

Select allOpen in new window

the results were

anonymous block completed

where is the dbms_output?

849
702
557
289
514
286
839
605
101
827
221
620
778
101
101
760
221
255
108
837
606
735
816
101
822
850
769
103
849
684
701
622
522
825
572
689
805
705
289
617
815
807
827
850
108
514
682
234
689
237
10
33
51
47
27
34
9
5
11
81
82
60
82
19
7
45
35
44
89
23
67
60
35
86
37
84
88
5
17
2
5
23
44
1
20
41
69
35
11
4
55
60
86
5
14
33
5
89
16

Select allOpen in new window

routes

10
33
51
47
27
34

should really be

810
633
651
647
827
234

this is happening with all columns that have digits on that row, other columns depend on these columns to return results.. those columns are blank because there is no route 10, if it were 810 as its suppose to be, i suspect that it would display the proper address and customer name

some other off-the-wall suggestions:

If we're in the realm of bugs, then "logical" solutions may not work, but we might be able to trick the gateway into letting the data come through.

  rearrange the columns

or fetch the route column twice

select CM_PRIMRY_ROUTNO AS dummy,   -- fetch it, but don't use it
 CM_PRIMRY_ROUTNO AS ROUTE,

or add a dummy column  

   select 'xxxxx' as dummy,



these, like the fetch idea above, aren't "fixes" they are merely attempts to "dodge" the bug by guessing at the symptoms that might cause it to surface.  While they might suffice as workarounds, you should document them heavily if you use them and be prepared to try other things as there is no guarantee they will be reliable in all circumstances.

HS_FDS_FETCH_ROWS = 1

is now returning the correct data, though it is not a practical solution it is getting the data issued correctly,

i will start to troubleshoot what is the ideal configuration parameter for the future