Tuesday, May 22, 2012

Oracle version compatibility and support matrix

Before you install or upgrade your Database server, clients, JDBC, JDK…etc. first you need to check the version support matrix. Make sure that your target version is supported. Otherwise oracle will not provide support.

Please find the support matrix links/ Metalink Notes

• Oracle Database Server/ Client version compatibility matrix
Client / Server / Interoperability Support Matrix for Different Oracle Versions [ID 207303.1]

• Support Status of each Oracle Server (RDBMS) Release
Oracle Database (RDBMS) Releases Support Status Summary [ID 161818.1]

• JDBC, JDK, and Oracle Database Certification
Starting With Oracle JDBC Drivers [ID 401934.1]
You can also refer JDBC FAQ

• JDBC Driver Support for Oracle Application Server (Fusion Middleware) Note:365120.1

• JDBC Features - classes12.jar , oracle.jdbc.driver, and OracleConnectionCacheImpl [ID 335754.1]

• Export/Import Compatibility between different oracle versions Note:132904.1

• Export/Import Datapump Compatibility between different oracle versions Note:553337.1

• Oracle 11g Release 1 (11.1) Support Status and Alerts [ID 454507.1]

• Oracle 11g Release 2 (11.2) Support Status and Alerts [ID 880782.1]

• Oracle 10g Release 2 (10.2) Support Status and Alerts [ID 316900.1]

• Oracle 10g Release 1 (10.1) Support Status and Alerts [ID 263719.1]

• Oracle9i Release 2 (9.2) Support Status and Alerts [ID 189908.1]

• Oracle9i (9.0.1) Support Status and Alerts [ID 149018.1]

• Oracle8i Release 3 (8.1.7) Support Status and Alerts [ID 120607.1]

• Oracle8i Release 2 (8.1.6) Support Status and Alerts [ID 93849.1]

• Release Schedule of Current Database Releases [ID 742060.1]

• Oracle Lifetime Support Policies [ID 971415.1]

• Oracle Database 11g Editions feature comparison

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

ORA-01427: single-row subquery returns more than one row

Everyday developers, testers and newbie’s face ORA-01427 error and I have seen many users looking for a solution for this error.

First let’s understand why users receive this error.

SQL> select count(*) from employee where employee_id = (select employee_id from salary where gross_sal < 5000 );
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

This error is raised when a sub-query returns more than one row.

Make sure you use multiple row sub query operator instead of single row sub-query operator to resolve the issue.

SQL> select count(*) from employee where employee_id in (select employee_id from salary where gross_sal < 5000 );

Now this query works fine.

Single row sub query operators are = (equal) , < (less than) , <=( less than or equal) , >(greater than) , >= (greater than or equal), <>(not equal ) ,!=(not equal)

Multiple row sub query operators are IN (equal to any member in the list), ANY (compare value to each value returned by the subquery), ALL (compare value to every value returned by the subquery)

Few guide lines for using Sub queries:
1. Use single-row operators with sing row sub queries
2. Use multiple-row operators with multiple row sub queries
3. Make sure you enclose sub queries in parentheses
4. Always keep sub queries on the right side of the comparison operator
5. You can use inline view to avoid ORA-01427 error, please refer http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm#DWHSG08032

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com/