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/

User Account Control is enabled. Installation may lead to unpredictable results and thus will be stopped

You need to disable User Account Control (UAC) before you install Hyperion Essbase, otherwise you will receive below error.

 User Account Control is enabled. Installation may lead to unpredictable results and thus will be stopped. EPMINS-01001: One or more preinstallation checks failed. Correct the errors before continuing with the installation. Press Enter to exit

You can disable the User access Control (UAC) by following below steps

In case of Windows 2008 Server
1. Start -> Control Panel
2. Click User Accounts in Control Panel
3. In the User Accounts window, click User Accounts
4. Click on Turn User Account Control on or off
5. Uncheck the "Use User Account Control (UAC) to help protect your computer", and then click OK. 6. Restart the computer to apply the changes

In case of Windows7
1. Start -> Control Panel
2. Click User Accounts in Control Panel
3. In the User Accounts window, click User Accounts
4. Click on “Change User Account Control settings”
5. Move the bar to “Never Notify”
6. Restart the computer to apply the changes

Now launch the installTool.cmd to install Hyperion and you should receive below error message.

User Account Control is disabled. EPMINS-01002: All installation prerequisites have been met. Starting EPM System Installer.

If you are running the installation over network share then you will see below error message

EPMINS-01001: One or more preinstallation checks failed. Correct the errors before continuing with the installation. Installer Path check... Failed! Installation from shared folder is not supported, please mount a network drive.

Make sure you map the network drive to local computer and launch the installTool.cmd and you should be able to install successfully.

Regards
Satishbabu Gunukula
http://oracleracexpert.com

Oracle Application Express component is INVALID

I have created a new Database in Oracle 11gR2 and process is smooth without any errors. When I check the status of components, it showing that Oracle application Express is INVALID.

SQL> select comp_name, version, status from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express INVALID 3.2.1.00.10

Possible Cause:-
1. If one or more Application Express objects are invalid
2. When doing export/import privileges might have lost
3. The XDB or Oracle Text components are not installed or de-installed.

To find the cause run the APEX Installation Verification Script apex_verify.sql and this script will generate an output apex_verify_out.html. You can download the verification script from below metalink note.

APEX Installation Verification Script [ID 1254932.1]

From the apex_verify_out.html output I found that WWV_FLOW_HELP object is INVALID.

Owner Object Name Object type Status
APEX_030200 WWV_FLOW_HELP PACKAGE BODY INVALID

You can also check the invalid objects using below query

SQL> select owner,object_name from dba_objects where status='INVALID';
OWNER OBJECT_NAME
------------------------------ --------------------
APEX_030200 WWV_FLOW_HELP

Run utlrp.sql to compile all invalid objects
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

The utlrp.sql output has below error message
FAILED CHECK FOR PACKAGE BODY WWV_FLOW_HELP

To find the errors query DBA_ERRORS table or complete the package.

SQL> alter package APEX_030200.WWV_FLOW_HELP compile body;
Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY APEX_030200.WWV_FLOW_HELP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
188/20 PL/SQL: SQL Statement ignored
189/27 PL/SQL: ORA-00942: table or view does not exist
191/13 PL/SQL: Statement ignored
191/13 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
196/16 PL/SQL: SQL Statement ignored
197/23 PL/SQL: ORA-00942: table or view does not exist
199/9 PL/SQL: Statement ignored
199/9 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
261/5 PL/SQL: Statement ignored
261/5 PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be
Declared

SQL> SELECT NAME,TYPE,TEXT from dba_errors where owner='APEX_030200'

NAME TYPE
------------------------------ ------------
TEXT
--------------------------------------------------------------------------------
WWV_FLOW_HELP PACKAGE BODY
PL/SQL: Statement ignored

WWV_FLOW_HELP PACKAGE BODY
PLS-00201: identifier 'CTX_DOC.FILTER' must be declared

WWV_FLOW_HELP PACKAGE BODY
PL/SQL: Statement ignored

WWV_FLOW_HELP PACKAGE BODY
PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared

It’s clear that APEX_030200 user does not have privilege on CTX_DDL and CTX_DOC, need to grant the execute privileges on CTX_DDL and CTX_DOC to APEX user.

SQL> grant execute on ctx_ddl to APEX_030200;
Grant succeeded.
SQL> grant execute on ctx_doc to APEX_030200;
Grant succeeded.

Now recompile the APEX_030200.WWV_FLOW_HELP package

SQL> alter package APEX_030200.WWV_FLOW_HELP compile;
Package altered.
SQL> alter package APEX_030200.WWV_FLOW_HELP compile body;
Package body altered.

Now you should able to see that Application Express component is invalid.

SQL> select comp_name, version, status from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express VALID 3.2.1.00.10

If there are no INVALID objects and if you still see the component as invalid then run below commands as SYS to reset the DBA_REGISTRY.

SQL> exec validate_apex; (If your APEX version is 2.0 or above)
OR
SQL> exec validate_htmldb; (If your APEX version is prior to 2.0)

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

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

The below are the most common errors, when connecting remotely to MySQL Server from MySQL Client.

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

Cause: The host that you are using to connect MySQL Server does not have privilege

Solution: Grant required privileges
GRANT ON *.* TO ‘user’@'hostname' IDENTIFIED BY ‘password’ ;
For ex:- if you are connecting as root then use below syntax
Mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Now you should able to see a new user in mysql.user table.
Mysql> select host,user from mysql.user;
+------+---------------------------
| User | Host
+------+---------------------------
| root | test-server
+------+---------------------------

If you want to grant privilege to a host on specific database try below syntax.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Error 1130: #HY000 Host ‘IP address’ is not allowed to connect to this MySQL server

Cause: The user host IP address is not allowed to connect to MySQL Server

Solution: Grant the privilege to host IP address
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘user’@’IPADDRESS’ IDENTIFIED BY ‘password’ ;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’x.x.x.x’ IDENTIFIED BY ‘test’ ;

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: NO)

Cause: The root user is password protected and you need to use password parameter in order to connect

Solution: - Try to connect MySQL Server using below command
# mysql -u root -h test-server –p

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: YES)

Cause: The 'root'@hostname' doesn’t have privilege to connect MySQL Server

Solution: - Grant permission using below command.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@hostname' IDENTIFIED BY ‘password’

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

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

I was working on MySQL Server setup and modified the default location of MySQL datadir. After I restart the MySQL, I was unable to connect and receiving below error.

#mysql –u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

Cause:
When we restart mysql server, it creates a mysql.sock file under socket variable path. The default location is /var/lib/mysql/mysql.sock.

I have updated the new location in /etc/my.cnf file.

[mysqld]
datadir=/opt/mysql/data
socket=/opt/mysql/mysql.sock

I can see the new file under /opt/mysql/mysql.sock, but I still receive the same error and not able to connect to MySQL.

I have modified the socket path under [mysqld], but the parameter was missing under [client] section. When I was trying to connect using MySQL client, it could not able to find mysql.sock file.

Solution:
Add [client] section in /etc/my.cnf

[client]
socket=/opt/mysql/mysql.sock

I have restarted MySQL in order to take effect of new changes in my.cnf

# service mysql stop
# service mysql start

MySQL Client able to find mysql.sock file and I was able to connect.

[root@iwebsql etc]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If you don’t want to restart mysql then use below command to tell client to use /opt/mysql/mysql.sock file

#mysql -u root –socket=/opt/mysql/mysql.sock

You may receive the same error, if mysqld didn't start. You can try below solution.

# /etc/init.d/mysqld start
# mysql –u root

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