Oracle router - Simple select statement fails on certain DB

[Clint C.]

I have simplified a problem we have on site to the following. We have functionality where the users can write their own SQL queries to get a certain result. This result is cached into a local String variable:

- When concatenating too many strings into 1 variable of type String the result is empty
- This only happens on their production database and not on the test database (nor can we simulate it locally)

With the attached test application you can fill in the:

- database
- username (for the database)
- password (for the database)
- query (see later)

With the button "Test the Query" you will get the result (or nothing) of the query in the box below.

Example queries that fails on their production but doesn't fail on their test database:

Code: Select all

select 'a' || 'b' || 'c' || 'd' || 'e' || 'f' || 'g' || 'a' || 'b' || 'c' || 'd' from dual
select 'a' || 'b' || 'c' || 'd' || 'e' || 'f' || 'g' || 'a' || 'b' from dual
select 'a' || 'b' || 'c' || 'd' || 'e' || 'f' || 'g' || 'a' from dual

While using 6 concatenations it does work:

Code: Select all

select 'a' || 'b' || 'c' || 'd' || 'e' || 'f' || 'g' from dual

My question:

Does the Oracle router of TD6.1 use any settings from the database to which the application connects?
We have eliminated all other possibilities for error:

- No application logic in this test app
- We tested on site with the same oracle client and same database version
- All the queries succeed if we use sqltalk, sqltools, sqlplus, ...

The only thing that is left, according to me is:

- The oracle router from TD to the database

See two screenshots taken on site:

ResultQDOC.png

ResultQTEST.png

[H3PO4]

Hi Clint,

just downloaded and tested your testcase, I am sorry but everything works fine.
(see screenshot below)

My Configuration:
TD 6.1 SP3r
Oracle 11g 11.2 64 bit (standard installation, nothing changed)

Do you have special sql.ini entries? Or changes in your Oracle client?

Regards
Henning

[Clint C.]

Thank you for testing the application.

By now we have gotten a reply via the support portal that helped us a bit:

I have a suggestion for your problem.

Can you please force the literals to NLS as shown below and see if the o problem goes away.

Thank you
Kumuthini Ragavan

Code: Select all

select cast(N'a' || N'b' || N'c' || N'd' as NVARCHAR2(100)) from dual

[markkuvento]

Hi,

This is fine also in my environment, but I'm not sorry about it;)
TD 6.1 SP3r
Ora11gr2

BR,
Markku