Cross Connection Query Producing Different

[gibran.braithwaite]

I am using Toad Data Point 3.5.0.2936 Professional Edition. I usually work with Oracle connections and have used the Cross Connection Query Editor from time to time when I need to query across multiple connection type. I've noticed that I get different results with the Cross Connection Query Editor compared to when I use regular Oracle connection.

Below I've executed the same query in both editors:

Both queries are run from the same table at roughly the same time. Why am I getting thousands less rows from Cross Connection Query? Both queries are simple select statements with no joins.

[Debbie_Peabody]

This warrants some investigation. We will need to gather some data. Please go to your desktop shortcut and add " /log=all" to the command line.

IE: “C:\PF\Toad.exe” /log=all

Restart TDP and execute one of the cross-query. Then go to the help menu and generate a supportbundle. Post the ziped file in this thread.

I would also like you to save each of the editors as a *.tef file. Use file save as and change the extension to Toad Editor File. This will save the result set with the query. Please post these two files as i want to do a comparison to see what types of rows are being omitted.

If the data is sensitive, you can send by email to debbie.peabody@quest.com

[Stewart]

Do you get the same result if you do a count(*) from both editors?

[gibran.braithwaite]

Strange enough, I do get the same result when I do a count(*) in both editors.

I counted each field in my original query and they all consistenly gave me 16,695 (which is currently the correct anwser for that table). I pulled in the columns I originally pulled and the cross query drops 4,000 lines from the results set.

[Stewart]

Could you now try selecting just one column. If that returns the correct number of rows add another column. Repeat this until the row count drops, which should hopefully identify the problem column.

[gibran.braithwaite]

i went in a did as you suggested and tried some more testing. The results were once again interesting.

There should currently be 16,698 lines on this tab. I queried each individual column that I used in my original query. All columns produced the correct number of lines except the ADDR1 column, which reported 11,499 lines.

Here is where it gets more interesting. I then went and added each field one by one in the order they appear and the line count changes depending on the columns being pulled.

SELECT RQL.COMPANY,RQL.REQ_LOCATION, RQL.FROM_LOCATION, RQL.ADDR1

FROMLAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.RQLOC RQL

No of Lines = 12199

SELECT RQL.COMPANY,RQL.REQ_LOCATION, RQL.FROM_LOCATION, RQL.ADDR1, RQL.ADDR2

FROM LAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.RQLOC RQL

No of Lines = 11999

SELECT RQL.COMPANY,RQL.REQ_LOCATION, RQL.FROM_LOCATION, RQL.ADDR1, RQL.ADDR2, RQL.ADDR3

FROM LAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.RQLOC RQL

No of Lines = 12299

SELECT RQL.COMPANY,RQL.REQ_LOCATION, RQL.FROM_LOCATION, RQL.ADDR1, RQL.ADDR2, RQL.ADDR3, RQL.CITY_ADDR5, RQL.STATE_PROV

FROMLAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.RQLOC RQL

No of Lines = 12199

SELECT RQL.COMPANY,RQL.REQ_LOCATION, RQL.FROM_LOCATION, RQL.ADDR1, RQL.ADDR2, RQL.ADDR3, RQL.CITY_ADDR5, RQL.STATE_PROV, RQL.COUNTY

FROM LAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.RQLOC RQL

No of Lines = 12399

[Stewart]

On further investigation we have found these ODBC errors in the logs from your support bundle.

ODBC data source returned an error:ORA-01406: fetched column value was truncated

We would like to eliminate the Cross Query execution engine (data hub) as the source of the issue by having you to define in Data Point an ODBC connection to the Oracle instance. Execute the same query and let us know the results.

[Debbie_Peabody]

To set up an ODBC connection so you can follow Stewart's instructions, please do the following:

1. Make sure you have full client or Oracle ODBC drivers installed. You must have OCI client and ODBC dirvers using the same OCI Client.

2. In Toad Data Point make a new connection of the ODBC type.

3. You will see an icon that launches the windows ODBC Manager. NOTE: Make sure and launch from within TDP as this is the 32-bit ODBC Manager. If you launch from Control Panel and you have 64-bit system it will launch the 64-bit ODBC Manager. We do not want that one.

4. Define a System type DSN and test the connection.

5. In TDP, point to that DSN and enter user id and connect data.

6. Execute the Query and see if the results are correct.

[gibran.braithwaite]

I went in and created a new connection to the datasource using the ODBC Generic connection and ran my query. I get over 16,000 rows which should be accurate.

[Stewart]

Well that should eliminate the ODBC driver as the cause. Lets see if we can get more detailed logging information to further explore the ODBC data source returned an error:ORA-01406: fetched column value was truncated error.

Please open the X-Query editor and run

Call hub_information_schema.hub_enable_log(3);

Next execute the query and create a support bundle. Please email it to stewart.dobrzynski@quest.com

[Stewart]

Also, what version of the ORACLE ODBC driver is in use?

[Debbie_Peabody]

So looks you they can upgrade the Oracle Client to 11g or 12c and it should fix this issue.

Bug 4546618 : ORA-1406 WHEN SELECTING CALCULATED NUMBER WITH LARGE PRECISION FROM VIEW

To Bottom

Bug Attributes

---

Type

B - Defect

Fixed in Product Version

11.0.0.0

Severity

2 - Severe Loss of Service

Product Version

10.1.0.3.1A

Status

80 - Development to QA/Fix Delivered Internal

Platform

207 - z*OBSOLETE: Microsoft Windows XP

Created

10-Aug-2005

Platform Version

NT5.1SP2

Updated

19-Jun-2014

Base Bug

N/A

Database Version

9.2.0.6.0

Affects Platforms

Port-Specific

Product Source

Oracle

Knowledge, Patches and Bugs related to this bug

Related Products

---

Line

Oracle Database Products

Family

Oracle Database Suite

Area

Platform specific utilities

Product

579 - Oracle ODBC Driver

Hdr: 4546618 9.2.0.6.0 ODBC 10.1.0.3.1A PRODID-579 PORTID-207 ORA-1406
Abstract: ORA-1406 WHEN SELECTING CALCULATED NUMBER WITH LARGE PRECISION FROM VIEW

*** 08/10/05 06:27 am ***

PROBLEM:

1. Using ODBC 10.1.0.31 against DB 9.2.0.X or lower and performing a
   “SELECT *” on a VIEW which contains a NUMERIC CALCULATED COLUMN
   with 19 or significant digits gives

   SQLState: S1000
   Native Error code: 1406
   Driver Message: [Oracle][ODBC][Ora]ORA-1406:
   fetched column value was truncated

   (may followed by crash depending on rows selected)

   depending on the number of rows contained in the table the view is
   based on

2. No MTS/OPS/distributed/etc

3. Everytime

4. see 1.

5. customer cannot use views containg NUMERIC CALCULATED COLUMNS
   when data volume / rows is exceeding some sort of upper limit

6. we filed bug 4493398 for the same issue
   and we got a fix (odbc 10.1.0.3.1a) that partially fix the problem

   the problem still reproduce against the following vise

=========================
TESTCASE:

=========================

STEPS TO REPRODUCE

1. Run on Oracle 9.2.0.X as SYS:

   CREATE USER TEST IDENTIFIED BY TEST;
   GRANT CONNECT TO TEST;
   GRANT RESOURCE TO TEST;

2. Perform Import with “fa.exp”

   imp system/manager file=fa.exp fromuser=wltuser touser=test

3. run “t1.sql”

4. create or replace view ttxv_1406_case2 as select 1/3 as testvalue from
   ttx_1406_nocrash;

5. Create a DSN with Client 10.1.0.4.0 & ODBC 10.1.0.31
   to the 9.2.0.X DB as user “test”

6. use “ODBCT32.EXE” and perform
   select * from ttxv_1406_case2;

REPRODUCIBILITY:

Cient ODBC RDBMS REP ?

10.1.0.4 10.1.0.3.1a 9.2.0.6 ORA-1406

WORKAROUND

unknown