Oracle router - Simple select statement fails on certain DB

General discussion forum about all databases other than SqlBase.
Clint C.

Oracle router - Simple select statement fails on certain DB

Post by Clint C. » 13 Aug 2013, 14:05

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
You do not have the required permissions to view the files attached to this post.

User avatar
H3PO4
Germany
Posts: 241
Joined: 10 Mar 2017, 07:53
Location: Rohlstorf, Germany

Re: Oracle router - Simple select statement fails on certain DB

Post by H3PO4 » 16 Aug 2013, 15:34

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
You do not have the required permissions to view the files attached to this post.

Clint C.

Re: Oracle router - Simple select statement fails on certain DB

Post by Clint C. » 19 Aug 2013, 07:54

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

Re: Oracle router - Simple select statement fails on certain DB

Post by markkuvento » 23 Aug 2013, 09:14

Hi,

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

BR,
Markku

Return to “General Discussion”

Who is online

Users browsing this forum: No registered users and 0 guests