FreeTDS Development Group

[Зверев Виталий Борисович <VZverev AT genesis.spb.ru>]

Hi Ian.
This is "well known" 9i problem (not FreeTDS exactly). The tab2 definitely 
has LONG column.
In two words solution described as: Then you wish to build product between 
local and remote tables and remote table has LONG column, instead of ANSI 
join use (+) join.
Sample:
create public database link test ...
----OK-----
select vl.view_name  
from   dba_views vl,
       dba_views@test vr
where  vr.view_name = vl.view_name(+);
----OK-----
select vl.view_name  
from   dba_views vl,
       dba_views@test vr
where  vr.view_name(+) = vl.view_name ;
----OK-----

*BUT*
select vl.view_name  
from   dba_views vl
Full outer join dba_views@test vr on vr.view_name = vl.view_name;

ORA-00997: illegal use of LONG datatype

select vl.view_name  
from   dba_views vl
Left outer join dba_views@test vr on vr.view_name = vl.view_name;


ORA-00997: illegal use of LONG datatype

select vl.view_name  
from   dba_views vl
Right outer join dba_views@test vr on vr.view_name = vl.view_name;


ORA-00997: illegal use of LONG datatype

If you have metalink access you could see detailed explation in Note:272371.1.


Sincerely yours. 
-------------------------------------------------------
Vitaly Zverev, OCP DBA 8&8i
Oracle E-Business Suite &
Infiniband Linux Cluster Administrator
Genesis Ltd., St.Petersburg, Russia

 

> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org 
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Ian Robinson
> Sent: Wednesday, January 18, 2006 7:36 PM
> To: freetds AT lists.ibiblio.org
> Subject: [freetds] LEFT OUTER JOIN fails on Oracle database
> 
> Hi,
> 
>  
> 
> FreeTds Version = 0.63 (tds version = 8.0)
> 
> SQL Server version = 2000
> 
> Client = Solaris 2.9 / Oracle 9iR2
> 
>  
> 
> Running the following SQL from the Oracle database connecting 
> to SQL Server 2000 fails with ORA-00997: illegal use of LONG datatype.
> 
>  
> 
> Select c.ref1, c.col
> 
> >From tab1 c
> 
> Left outer join tab2@databaselink e on e.ref1 = c.ref1;
> 
>  
> 
> The column ref1 is of type INT on both tab1 and tab2 tables. 
> By selecting the data_type from dba_tab_columns on oracle, 
> the data type is confirmed to be NUMBER - therefore the data 
> type conversion appears to work fine.
> 
>  
> 
> Additionally, I am able to complete a normal join between the 
> two tables successfully, e.g.
> 
>  
> 
>  Select c.ref1, c.col
> 
> >From tab1 c,tab2 e
> 
> where e.ref1 = c.ref1;
> 
>  
> 
> I believe SQL Server supports the ANSI left outer join syntax 
> (I have tried the Sybase *=, but Oracle can not interpret).
> 
>  
> 
> Does FreeTds support the left outer join operation? Has 
> anyone experience of this particular error?
> 
>  
> 
> Regards,
> 
> Ian.
> 
>  
> 
> 
> Visit our website at http://www.hoaresbank.co.uk
> 
> C Hoare & Co is authorised and regulated by the Financial 
> Services Authority Registered in England no. 240822:
> Registered office 37 Fleet St, London, EC4P 4DQ
> 
> **************************************************************
> *****************
> Disclaimer:
> 
> Confidentiality: 
> This message and attachments are, and may be, confidential 
> and are sent for the personal attention of the addressee(s).
> If you are not the intended addressee, any use, disclosure or 
> copying of this document is unauthorised. Information 
> transmitted by email may be intercepted, lost, destroyed, 
> corrupted or delayed and as a result, C Hoare & Co do not 
> accept responsibility for any errors or omissions in the 
> contents of this message. If you would like to confirm the 
> contents of this email, please request a hard copy version.
> 
> Monitoring/Viruses:
> C Hoare & Co may monitor all incoming and outgoing emails in 
> line with current legislation. Although emails are screened 
> for viruses, C Hoare & Co cannot guarantee that any 
> transmissions will be virus free.
> **************************************************************
> *****************
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>