Home » Other » General » Looking for a Oracle feature that will fail when client is lower version than database
Looking for a Oracle feature that will fail when client is lower version than database [message #658269] Wed, 07 December 2016 14:07 Go to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I seem to recall years ago, maybe MANY years ago when the SQL engine and PL/SQL engine were not in sync, that if I was using a SQL*Plus client at a lower version that the database, if that feature (eg. built-in function for example) wasn't available in the version that the client was, it would not work.

I tested using the row limiting of Top-N queries of 12c using a 10g client and it worked. I was expecting it to fail.

select *
from user_objects
order by OBJECT_NAME
fetch first 5 rows only;

Is it possible it was only DDL and not DML?
Re: Looking for a Oracle feature that will fail when client is lower version than database [message #658271 is a reply to message #658269] Wed, 07 December 2016 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is possible to use any statement if SQL*Plus recognizes the first keyword which is the only thing it checks.
For instance, with a 10g SQL*Plus you can create an edition on a 11g database:
C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Mer. DÚc. 7 21:41:55 2016

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn michel/michel@mikb2
Connected.
SQL> def _SQLPLUS_RELEASE
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
SQL> @v

Oracle version: 11.2.0.4.0

SQL> create edition foo;

Operation 212 succeeded.

SQL> select * from dba_editions;
EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE                                                      YES
TOTO                           ORA$BASE                       YES
FOO                            TOTO                           YES

3 rows selected.

SQL> drop edition foo;

Operation 214 succeeded.

SQL> select * from dba_editions;
EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE                                                      YES
TOTO                           ORA$BASE                       YES

2 rows selected.
As you can see, SQL*Plus 10g does not know editions but is able to create and drop one, the only issue is that it does not recognize the operation codes.

[Updated on: Wed, 07 December 2016 15:44]

Report message to a moderator

Re: Looking for a Oracle feature that will fail when client is lower version than database [message #658273 is a reply to message #658271] Wed, 07 December 2016 15:38 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Thank you Michel. I guess it's not really important what my memory of the issue was.
Re: Looking for a Oracle feature that will fail when client is lower version than database [message #658277 is a reply to message #658269] Thu, 08 December 2016 02:21 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Got one! Following MC's comment, I thought of a new 12.x command. First, SQL*Plus 11 and DB 12:
C:\Users\john>
C:\Users\john>sqlplus sys/oracle@127.0.0.1:1521/orclz as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 8 08:18:01 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

127.0.0.1:1521/orclz> administer key management;
SP2-0734: unknown command beginning "administer..." - rest of line ignored.
127.0.0.1:1521/orclz>
and now with 12 both sides:
C:\Users\john>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 08:14:44 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orclz> administer key management;
administer key management
                        *
ERROR at line 1:
ORA-46600: invalid ADMINISTER KEY MANAGEMENT command


orclz>
Re: Looking for a Oracle feature that will fail when client is lower version than database [message #658278 is a reply to message #658277] Thu, 08 December 2016 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, SQL*Plus only checks the first word, "administer" command is not known in 11g, so 11g SQL*Plus rejects and does not forward it to the database.

Re: Looking for a Oracle feature that will fail when client is lower version than database [message #658281 is a reply to message #658278] Thu, 08 December 2016 03:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
use of WITH in a select would fail in versions before it was introduced since it's the first word when used.
Re: Looking for a Oracle feature that will fail when client is lower version than database [message #658282 is a reply to message #658269] Thu, 08 December 2016 03:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
A really irritating similar problem is that the parser included in 12.x RMAN is not complete. It can understand some SQL*Plus commands, but not others. For example, you can use DESCRIBE and you can even abbreviate it to DESC, but you can't abbreviate SHUTDOWN to SHU, and you can't use COLUMN...FORMAT at all.

Re: Looking for a Oracle feature that will fail when client is lower version than database [message #658308 is a reply to message #658282] Thu, 08 December 2016 08:57 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Great info guys. John, good one, but the administer key management is more of a "system" command. I could have wore there was something more "generic" that I recall a user trying to run that didn't work.

The reason I ask is that we have 12c databases and most developers use SQL Developer, but I don't like GUI, so I use a linux box to connect the database and it has a 10g client. I don't want to come across a situation where I cannot create something that won't work because of the limitation of the client.

Cookie, I don't recall when WITH was first introduced. I have instant client all the way back to 9i and I even have an 8.0.6 Windows client that came with Forms 6i that I can try out.

Re: Looking for a Oracle feature that will fail when client is lower version than database [message #658309 is a reply to message #658308] Thu, 08 December 2016 09:59 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
WITH was introduced in version 9.
Previous Topic: Oracle online documentation
Next Topic: Books
Goto Forum:
  


Current Time: Thu Mar 28 18:13:16 CDT 2024