Oracle

Cancel Queries

In order to cancel queries with Oracle, you need to set Options > Cancel Queries > AQT Cancel Task. This runs a cancel task in a separate thread, which allows an active query or update to be cancelled.

Message ORA-00933 SQL command not properly ended

You can get this message if you are running a join which uses ansi join syntax (Left Outer Join), but your database does not support this syntax for joins.

If you are building your query using the GUI Query Builder, you can avoid this problem with:

click on the Options tab of the query builder
click on Join Syntax
for Outer Join Syntax select the second option Tab1 = Tab2(+)
you may also wish to click on Save as Default Options, so that this option will be used for future queries you build.

Displaying LOBs

If you wish to view LOB columns, you need to use the Oracle ODBC Driver. The Microsoft ODBC for Oracle driver does not support LOB columns.

Insert Data into LOBs

When inserting a LOB value, you can get the following error

ORA-01461: can bind a LONG value only for insert into a LONG column

This happens because you have not enabled LOB write support in the configuration of your Oracle ODBC Datasource. To correct this, change this:

start the ODBC Data Source Administrator (from the AQT signon window > Manage Datasources)
select your database, then click on Configure
go the Oracle tab, and check the Enable LOBs checkbox

Inserting Data into CLOBs

When inserting into CLOBs, you cannot insert a literal which is more than 4000 bytes long. If you try this you may get a message ORA-01704 string literal too long. Some versions of the Oracle ODBC Driver will abend when you try this.

The solution to both these problems is to use the Load LOBs from Files feature.

Inserting Data into LONG columns

The Load LOBs from Files feature does not appear to work for Long columns. This may be a limitation of the Oracle ODBC Driver.

Timestamp and Interval Columns

AQT cannot display timestamp or interval columns, as the Oracle ODBC driver does not yet support these data types. As a workaround you can use the TO_CHAR function. eg.

select TO_CHAR(timestamp_col) from table1

When displaying a table that has timestamp or interval columns, AQT will automatically add the TO_CHAR function to any timestamp/interval columns in the table.

Displaying Package Body Text

In the Database Explorer window, you can only display the Body Text of a Package if you have CREATE ANY PROCEDURE or DEBUG ANY PROCEDURE privileges on that Package. If you do not have this privilege, the Body Text will display as blank.

An alternative is to change the query used (query 63 in the oracle.cfg file) to use DBA_SOURCE rather than ALL_SOURCE.

User-Defined Data Types

AQT cannot display any columns defined with a user-defined data type. This is due to a limitation of the Oracle ODBC Driver. If you try to display such columns, AQT will hang or crash.

You can display a table with these data types only if you exclude these columns from the column list. When you display a table in AQT, AQT will automatically exclude the user-defined columns from the table display and replace them with the literal "(user-defined type, not displayed)".

Displaying Remote Data

If you display data from a remote database by either:

select * from remote_synonym

select * from table@database

then you may get error ORA-02041: client database did not begin a transaction.

This error is due to a bug or oddity with the Oracle ODBC Driver. The problem doesn't happen if you use the Microsoft ODBC Driver for Oracle. In general the Microsoft ODBC Driver is not as good as the Oracle ODBC Driver, however in this case it is able to display remote data without problem.

For more information on setting up a connection using the Microsoft ODBC Driver for Oracle see Connecting to Oracle.

Bug with Displaying Large Numbers

There appears to be a bug with the Oracle ODBC Driver in that large numbers (eg. 17 digits or more) have their last digits replaced with zeros. There are two circumventions to this problem:

use the Microsoft ODBC Driver for Oracle. Normally we don't recommend using this driver, however in this case it works better than the Oracle driver.
use the to_char() function. When this is used, the number is displayed correctly.

No Data when using WITH

If you are using the Microsoft ODBC for Oracle Driver, you will not see any data for a query using the WITH clause.

This is a bug / limitation of that driver.

Advanced Query Tool

https://www.querytool.com