Blog do Marcio Portes

A versão do Client do SQL*Plus pode influenciar a sentença SQL, mesmo que o banco esteja na versão que aceita o comando?

Sim! O SQL*Plus na sua construção já pré-interpreta alguns comandos. Veja abaixo um script simples usando feature do 9i executadas em duas versões do SQL*Plus (8i e 9i) contra uma mesma base - Oracle9i Enterprise Edition Release 9.2.0.2.1.

Script:

set echo on

with
   a as ( select ename, sal, rank() over (order by sal) rank
            from emp )
select *
  from a
 where rank <= 3 
/  
set echo off 

O Teste:

c:\migracao\loads>plus /

SQL*Plus: Release 9.2.0.2.0 - Production on Mon Nov 24 11:09:33 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

ops$t_mp00@MRP9I1> @teste_sql+
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> with
 2     a as ( select ename, sal, rank() over (order by sal) rank
 3              from emp )
 4  select *
 5    from a
 6   where rank <= 3     
 7  /    

ENAME             SAL       RANK  
---------- ---------- ----------  
SMITH             800          1  
JAMES             950          2  
ADAMS            1100          3    

3 rows selected.    

ops$t_mp00@MRP9I1> set echo off

Agora conectando com sql*plus 8.1.7.

C:\migracao\loads>sqlplus /@mrp9i1

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Nov 24 11:07:48 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

ops$t_mp00@MRP9I1> @teste_sql+
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> with
SP2-0042: unknown command "with" - rest of line ignored.
ops$t_mp00@MRP9I1>    a as ( select ename, sal, rank() over (order by sal) rank
ops$t_mp00@MRP9I1>             from emp )
SP2-0042: unknown command "from emp )" - rest of line ignored.
ops$t_mp00@MRP9I1> select *
 2    from a
 3   where rank <= 3     
 4  /    
from a        * ERROR at line 2: ORA-00942: table or view does not exist

Portanto tente alinhar as versões tanto do client quanto a do server para obter um melhor aproveitamento das new features.