Query Works in SQLPLUS but not TOAD

Toad for Oracle
1

The following query works just fine in SQLPLUS but returns an ORA-00942 (table or view does not exist) in TOAD (v.14.0.75.662):

with my_thing as
     (select max(install_id) "MAXID"
        from sys.dba_registry_sqlpatch)
select p.*
  from sys.dba_registry_sqlpatch p, my_thing
 where p.install_id = my_thing.maxid;

The mischief seems to be the p.*. When I replace that with specific columns, TOAD's error report goes away and the query runs. What is strange is that TOAD highlights 'max' in the with clause as the offending 'table'. SQLPLUS executes this query exactly as shown above without error.

Cheers,
Russ

2

If there is an XMLTYPE in there, that is the cause of this. Cast it to clob or leave it out. This is a known issue but I was actually planning of looking at it tomorrow.

Edit: Oh, listing all columns works too. Even better. I'll take a look at this tomorrow.

3

THere is a column of XMLTYPE as part of this view (Patch_Descriptor).

4

When I specify all columns except patch_descriptor, the query works in TOAD. Once I add patch_descriptor, sadness.

Cheers,
Russ

5

XMLTYPE in its binary form causes Toad to throw errors, so internally we're trying to work around it. If you use GetClobVal() as below then Toad doesn't have to make any changes.

with my_thing as
     (select max(install_id) "MAXID"
        from sys.dba_registry_sqlpatch)
select p.INSTALL_ID, p.PATCH_ID, p.PATCH_UID, p.PATCH_TYPE, p.ACTION, 
       p.STATUS, p.ACTION_TIME, p.DESCRIPTION, p.LOGFILE, p.RU_LOGFILE, 
       p.FLAGS, p.PATCH_DESCRIPTOR.GetClobVal() as PATCH_DESCRIPTOR, 
       p.PATCH_DIRECTORY, p.SOURCE_VERSION, 
       p.SOURCE_BUILD_DESCRIPTION, p.SOURCE_BUILD_TIMESTAMP, 
       p.TARGET_VERSION, p.TARGET_BUILD_DESCRIPTION, p.TARGET_BUILD_TIMESTAMP
  from sys.dba_registry_sqlpatch p, my_thing
 where p.install_id = my_thing.maxid;
6

It's too late to include this fix in 14.1, but it will be fixed in 14.2 when we start betas for that version in about a month.