Monday, March 9, 2009

Oracle Errors while coding of PL/SQL Blocks

ORA-00001 Unique constraint violated. (Invalid data has been rejected)
Solution: Check PL/SQL Code.You are inserting data that already exists in database.

ORA-00600 Internal error (contact support)
Solution : Check the connection with Oracle Database.

ORA-03113 End-of-file on communication channel (Network connection lost)
Solution : Reconnect to Oracle

ORA-03114 Not connected to ORACLE
Solution : Reconnect to Oracle

ORA-00942 Table or view does not exist
Solution : Table or view in which you are entering or fetching data from table/view is not exists in database.

ORA-01017 Invalid User name/Password
Solution : Your are trying to connect with database with wrong password or wrong username.

ORA-01031 Insufficient privileges
Solution : You are currently accessing data from which table or view or any object not granted to the user through which you are currently logged in.

ORA-01034 Oracle not available (the database is down)
Solution : Database connection is not available. Please restart the database or database is in Mount Stage.

ORA-01403 No data found
Solution : While fetching data, No data found from the written Query.

Oracle Date Conversion function - TO_DATE()

TO_DATE in Oracle pl/sql is to convert char data type to a value of DATE data type.

TO_DATE( String, format, nls_param )

String : is the string to convert to date
format : is the format that will be used to convert string to a date.
nls_param : is the nls_languageused to convert string to a date.

Examples:
SQL> select to_date('APR/03/2009' ,'MON-DD-YYYY HH24:MI:SS') DATE from dual;
SQL> select to_date('20080312','YYYYMMDD') from dual;
SQL> select to_date('20080312','YYYYDDMM') from dual;
SQL> select to_date('2006/11/14 18', 'yyyy/mm/dd hh24') from dual;