SQL*PLUS - no rows returned on one laptop, 67k+ rows returned on anoth

[OMJ]

Hi,

I'm having an issue where a user has been using SQL*PLUS to query a SID on an Oracle Database Server 11g Release 1.

They have a read-only SQL account to run the query, which returns 67,000+ rows when they run it on their old laptop, but 0 rows when they run it on their current laptop.

They are using the same version of SQL*PLUS on both laptops (SQL*PLUS: Release 8.1.7.0.0, connecting to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production).

I used SQL Developer to connect and run the query using the user's username/pwd and it worked for me returning the query as expected.

Can anyone recommend what sort of settings and environment variables I need to check? Things in the registry, SQL*PLUS settings seem to match up too, as does the TNS_ADMIN environment variable.

I've attached the script:

set arraysize 1
set echo off
set verify off
set pages 6000
set lines 600

spool c:\personaldata\jbquery.txt 

col pdname heading 'Period' format A6
col pdyear heading 'Year' format 9999
col pdnum heading 'Pd Num' format 9999
col bu heading 'BU' format A6
col costc heading 'Cost Centre' format A12
col acc heading 'Account Code' format A12
col brs heading 'BRS' format A12
col prj heading 'Project Code' format A12
col Drbal heading 'Dr Bal' format 9999999999.99
col Crbal heading 'Cr Bal' format 9999999999.99
col Pdbal heading 'Net Pd Bal' format 9999999999.99


SELECT  
C.SEGMENT1 bu,
C.SEGMENT2 costc, 
C.SEGMENT3 acc, 
C.SEGMENT5 prj, 
L.PERIOD_NET_DR - L.PERIOD_NET_CR Pdbal
FROM APPS.GL_CODE_COMBINATIONS_V C,
APPS.GL_BALANCES L,
apps.fnd_flex_values_vl g 
WHERE C.CODE_COMBINATION_ID = L.CODE_COMBINATION_ID
And g.flex_value_set_id=1002626 
And c.segment5 = g.flex_value
and l.currency_code = 'GBP'
and l.actual_flag = 'A'
and l.period_year = '2011'
and l.period_num >= '04'
and l.period_num <= '04'
and c.segment3 >= '000000' 
and c.segment3 <= '795000'
and L.PERIOD_NET_DR - L.PERIOD_NET_CR <> 0

/
spool off

set lines 100
set pages 24
set verify on
set echo on

 

[sem]

Most such issues are "implicit type conversion" - related (character to number, character to date and vice versa). You may expect similar results in similar NLS environments (don't forget about collating sequence) but IMHO avoiding implicit conversions is a better way to get not just similar but rather correct results.

Settings to check: NLS_LANGUAGE, NLS_SORT

You may also inpect NLS_DATE_FORMAT, NLS_NUMERIC_CHARACTERS


Regards, Dima

http://seminihin.narod.ru

 

[OMJ]

How / where do I check those 4 variables? Where would the values be stored?

 

[carp]

Since the issue seems to be client machine-specific, you might want to look for files called login.sql or glogin.sql on each machine, and compare them for differences. They should be in $ORACLE_HOME/sqlplus/admin.

 

[sem]

In case NLS_LANGUAGE and NLS_SORT are not specified explicitly (in registry or as environment variables or in scripts mentioned by carp), they're derived from NLS_LANG.

BTW is this

...
and l.period_num >= '04'
and l.period_num <= '04'
...

not the same as

...
l.period_num = '04'
...

???

P.S. Actually you may meet an issue described below:

SQL> select * from (
  2  select 'A' from dual 
  3  union
  4  select '0' from dual)
  5  order by 1;

'
-
0
A

SQL> select 1 from dual where 'A'>='0';

         1
----------
         1

SQL> select * from nls_session_parameters where parameter in ('NLS_SORT','NLS_COMP');

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_SORT
BINARY

NLS_COMP
BINARY

So as you may see both 'A'>='0' and 'A'<='0' :-D

Regards, Dima

http://seminihin.narod.ru