Posted by: MVk - Monika V. kycka <m.vai...@gmail.com> A weird problem has occurred, 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