A weird problem has occured, maybe someone could give any advice?

Client has 2 Oracle 10g databases - test and real. Gupta application
used with test db works fine, but when connecting to real one, some
listboxes don't fill.Tendency is seen on litboxes, generated like this
one:
-----------------------------------------------------------------------------------------------------------------------------------------
Number: nComboID[*]
String: sComboValue[*]
String: sSqlSelectParm

Set sSqlSelectParm = "
SELECT
DP.ID,
'" || "(" || "' || DP.CODE || '" || ") " || "' || SUBSTRB( DP.NAME,
0, 200 )
FROM
DR_OBJEKTAS.DR_PRODUKTAS_T DP
ORDER BY
DP.CODE"

Set sSqlSelectParm=sSqlSelectParm||' INTO :nComboID[nI], :sComboValue
[nI]'

Call SalListClear( hWndItem )
Call SalArraySetUpperBound( nComboID, 1, -1 )
Call SalArraySetUpperBound( sComboValue, 1, -1 )

If SqlPrepareAndExecute( hSqlClass, sSqlSelectParm )
While SqlFetchNext( hSqlClass, nInd )
Call SalListInsert( hWndItem, nI, sComboValue[nI] )
Set nI=nI+1
Set __nUpperBound=nI
-----------------------------------------------------------------------------------------------------------------------------------------
CODE column type VARCHAR2(50)
NAME column type VARCHAR2(254)

Result should be list box filled with records like:
(code1) some_name_1
(code2) some_name_2
etc.

But application retrievs only first number value leaving string empty.

I've did loads of experiments to test possible reasons:
1. changing "(" and ")" to oracle chr(40) and chr(41) -
doesn't work
2. if selecting only one of the
columns - OK
3. CODE ||
'abc'
- OK
4. NAME ||
'abc'
- doesn't work
5. SUBSTRB( NAME, 0, 200 ) || 'abc'
- doesn't work
6. just SELECT
'abc' -
doesn't work

If adding bound variable String: sTest with same value "abc":
7. CODE ||
sTest
- OK
8. NAME ||
sTest
- doesn't work
9. SUBSTRB( NAME, 0, 200 ) || sTest -
doesn't work
10. just SELECT
sTest - OK
11. tried reducing SUBSTRB length value -
didn't work
12. switching between SUBSTR, SUBSTRB, SUBSTRC - doesn't work
13. tried switching listbox field datatype String<>Long String -
doesn't work
14. tried switching sComboValue to Long String -
application hangs
15. any of selects above work just fine if executed in Pl/Sql
Developer

The only work arround that worked fine was creating view in oracle
with concatenated string and selecting directly from there. But this
is not a good solution as there are many such listboxes and creating
view for each is not the best thing.

Has anyone any ideas on what's going on? Application is same for test
and real databases, run from the same location. I suppose it might be
an oracle issue, but I don't seem to find any information regarding
this and ran out of ideas of what to look for and where...

MVk - Monika V. kycka

MVk - Monika V. kycka schreef:
Actually, I think it IS the best thing. Better than cluttering your code
with '""||() and so on... Views are for free, you don't have to pay for
them...

And instead of testing all kind of modifications to your code, I suggest
you try to find the difference in the databases. It can't be an Oracle
bug, for it does work in one of the databases.

I would perform these queries in SQL Plus and check for the differences
in the result set (null values, lengths, number of records returned)

Shakespeare

MVk - Monika V. kycka schreef:
Actually, I think it IS the best thing. Better than cluttering your code
with '""||() and so on... Views are for free, you don't have to pay for
them...

And instead of testing all kind of modifications to your code, I suggest
you try to find the difference in the databases. It can't be an Oracle
bug, for it does work in one of the databases.

I would perform these queries in SQL Plus and check for the differences
in the result set (null values, lengths, number of records returned)

Shakespeare

MVk - Monika V. kycka schreef:
Actually, I think it IS the best thing. Better than cluttering your code
with '""||() and so on... Views are for free, you don't have to pay for
them...

And instead of testing all kind of modifications to your code, I suggest
you try to find the difference in the databases. It can't be an Oracle
bug, for it does work in one of the databases.

I would perform these queries in SQL Plus and check for the differences
in the result set (null values, lengths, number of records returned)

Shakespeare

MVk - Monika V. kycka schreef:
Actually, I think it IS the best thing. Better than cluttering your code
with '""||() and so on... Views are for free, you don't have to pay for
them...

And instead of testing all kind of modifications to your code, I suggest
you try to find the difference in the databases. It can't be an Oracle
bug, for it does work in one of the databases.

I would perform these queries in SQL Plus and check for the differences
in the result set (null values, lengths, number of records returned)

Shakespeare

On 2 Gruo, 18:32, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
It's not the matter of price. For the fast solution, we created some
views, but they are simply not the best sollution. Our software is
huge and creating dozens of views just to represent simple cumulated
select data (e.g. for a filter listbox) is not practical and time
consuming, especialy for places with already existing code. Writing
queries in Pl/Sql Developer (or SQL Plus - doesn't matter which one)
is no help here because I can't recreate this situation - data is
selected well in both databases.

Comparing db parameters is the only step I see for further checking.
But taking in mind that there are more than 250 db parameters (of
course, not each is of the matter or may differ), but lets say I'll
find 30 parameters that differ and looks potentialy important. How to
know which one(s) is(are) faulting data capture? I hoped someone would
have any idea or give a direction what to look for/base on - charsets,
nls settings, cursor sharing, oracle version difference in dbs, server
setting differences (if different servers), etc? Besides, as I have
mentioned, problem occurres on the real database with real data where
we have limited access and have to make requests to the client for
exact information we need. Well, the burocracy... Ok, I'll try getting
all the parameters from both dbs tomorow, see what differs and write
news here. Hope, then there would be some ideas of what could be
wrong.

Thank's for cooperating.

MVk - Monika V. kycka

ps. Ah.. And I almost forgot! Data is the same in both databases as
there was dump created from real db and imported to test db.
ps2. I'm just a programmer and db or server administration is not my
field. That's why I would be greatfull for any help. :]

On 2 Gruo, 18:32, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
It's not the matter of price. For the fast solution, we created some
views, but they are simply not the best sollution. Our software is
huge and creating dozens of views just to represent simple cumulated
select data (e.g. for a filter listbox) is not practical and time
consuming, especialy for places with already existing code. Writing
queries in Pl/Sql Developer (or SQL Plus - doesn't matter which one)
is no help here because I can't recreate this situation - data is
selected well in both databases.

Comparing db parameters is the only step I see for further checking.
But taking in mind that there are more than 250 db parameters (of
course, not each is of the matter or may differ), but lets say I'll
find 30 parameters that differ and looks potentialy important. How to
know which one(s) is(are) faulting data capture? I hoped someone would
have any idea or give a direction what to look for/base on - charsets,
nls settings, cursor sharing, oracle version difference in dbs, server
setting differences (if different servers), etc? Besides, as I have
mentioned, problem occurres on the real database with real data where
we have limited access and have to make requests to the client for
exact information we need. Well, the burocracy... Ok, I'll try getting
all the parameters from both dbs tomorow, see what differs and write
news here. Hope, then there would be some ideas of what could be
wrong.

Thank's for cooperating.

MVk - Monika V. kycka

ps. Ah.. And I almost forgot! Data is the same in both databases as
there was dump created from real db and imported to test db.
ps2. I'm just a programmer and db or server administration is not my
field. That's why I would be greatfull for any help. :]

On 2 Gruo, 18:32, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
It's not the matter of price. For the fast solution, we created some
views, but they are simply not the best sollution. Our software is
huge and creating dozens of views just to represent simple cumulated
select data (e.g. for a filter listbox) is not practical and time
consuming, especialy for places with already existing code. Writing
queries in Pl/Sql Developer (or SQL Plus - doesn't matter which one)
is no help here because I can't recreate this situation - data is
selected well in both databases.

Comparing db parameters is the only step I see for further checking.
But taking in mind that there are more than 250 db parameters (of
course, not each is of the matter or may differ), but lets say I'll
find 30 parameters that differ and looks potentialy important. How to
know which one(s) is(are) faulting data capture? I hoped someone would
have any idea or give a direction what to look for/base on - charsets,
nls settings, cursor sharing, oracle version difference in dbs, server
setting differences (if different servers), etc? Besides, as I have
mentioned, problem occurres on the real database with real data where
we have limited access and have to make requests to the client for
exact information we need. Well, the burocracy... Ok, I'll try getting
all the parameters from both dbs tomorow, see what differs and write
news here. Hope, then there would be some ideas of what could be
wrong.

Thank's for cooperating.

MVk - Monika V. kycka

ps. Ah.. And I almost forgot! Data is the same in both databases as
there was dump created from real db and imported to test db.
ps2. I'm just a programmer and db or server administration is not my
field. That's why I would be greatfull for any help. :]

On 2 Gruo, 18:32, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
It's not the matter of price. For the fast solution, we created some
views, but they are simply not the best sollution. Our software is
huge and creating dozens of views just to represent simple cumulated
select data (e.g. for a filter listbox) is not practical and time
consuming, especialy for places with already existing code. Writing
queries in Pl/Sql Developer (or SQL Plus - doesn't matter which one)
is no help here because I can't recreate this situation - data is
selected well in both databases.

Comparing db parameters is the only step I see for further checking.
But taking in mind that there are more than 250 db parameters (of
course, not each is of the matter or may differ), but lets say I'll
find 30 parameters that differ and looks potentialy important. How to
know which one(s) is(are) faulting data capture? I hoped someone would
have any idea or give a direction what to look for/base on - charsets,
nls settings, cursor sharing, oracle version difference in dbs, server
setting differences (if different servers), etc? Besides, as I have
mentioned, problem occurres on the real database with real data where
we have limited access and have to make requests to the client for
exact information we need. Well, the burocracy... Ok, I'll try getting
all the parameters from both dbs tomorow, see what differs and write
news here. Hope, then there would be some ideas of what could be
wrong.

Thank's for cooperating.

MVk - Monika V. kycka

ps. Ah.. And I almost forgot! Data is the same in both databases as
there was dump created from real db and imported to test db.
ps2. I'm just a programmer and db or server administration is not my
field. That's why I would be greatfull for any help. :]

On Dec 2, 10:08*am, "MVk - Monika V. kycka" <m.vaitk... (AT) gmail (DOT) com>
wrote:
Don't know a thing about Gupta, but I can guess that if you are using
substrb on NAME and have different results with data that was exported
and imported, you may have a characterset issue. So you would need to
post:

The character set of the prod db.
The character set of the test db.
The NLS values of the database.
The NLS values of any other tier.
The NLS values of any session involved, including the export/import.
The exact (to all findable decimal places - what does sqlplus say when
you start it?) of all parts of this, including database, operating
system, and apps. Could be a Gupta bug or missing synonym or
something like that, too.

I'm guessing you will find some difference there, could be as
fundamental as the db character sets. exp/imp (oh yeah, there are
different tools with similar names, be sure and specify which you
used) will oh-so-helpfully translate character values for you, as can
NLS settings dynamically. If you have middle tier startups for test
and prod, look for differences in the environment.

Aside from that, I doubt there would be init.ora differences to make
this kind of effect.

jg
--
@home.com is bogus.
"Cheech & Chong celebrity roast brought to you by Windows" - yeah, Up
In Smoke for sure.