GarciaPL Notes - Java and Others

If constraint on column STATUS was created without a name during creating a table, Oracle will assign a random name for it. Unfortunately, we cannot modify the constraint directly.

Steps involved of dropping unnamed constraint linked to column STATUS

1. Duplicate STATUS field into a new field STATUS2
2. Define CHECK constraints on STATUS2
3. Migrate data from STATUS into STATUS2
4. Drop STATUS column
5. Rename STATUS2 to STATUS

ALTER TABLE MY_TABLE ADD STATUS2 NVARCHAR2(10) DEFAULT 'OPEN';
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_CHECK_STATUS CHECK (STATUS2 IN ('OPEN', 'CLOSED'));
UPDATE MY_TABLE SET STATUS2 = STATUS;
ALTER TABLE MY_TABLE DROP COLUMN STATUS;
ALTER TABLE MY_TABLE RENAME COLUMN STATUS2 TO STATUS;

I was trying recently to add unique constraint on few fields on Oracle. At the beginning I thought that it might be a easy task to do, but at the end some workaround was needed. So, in case of error you might get below message which indicates that Oracle found some duplicate records in your table, even though you specify NOVALIDATE flag which allows you to do not check existing data against that constraint.

ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUE_CONSTRAINT UNIQUE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE) NOVALIDATE
Error report -
SQL Error: ORA-02299: cannot validate (DB.MY_UNIQUE_CONSTRAINT) - duplicate keys found
02299. 00000 - "cannot validate (%s.%s) - duplicate keys found"
*Cause:    an alter table validating constraint failed because the table has
           duplicate key values.
*Action:   Obvious

CREATE INDEX INDEX_UNIQUE_CONSTRAINT ON MY_TABLE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE);
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUE_CONSTRAINT UNIQUE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE) NOVALIDATE;

Some of you might thinking how to create sequence for table which contains some records. You might always export all records from database, then eventually create sequence and after all import those records once again into database. Nevertheless there is a solution how to create this sequence using PL/SQL.

DECLARE
  seq_value_holder        NUMBER;
BEGIN
  -- create a sequence for YOUR_TABLE
  SELECT NVL(MAX(id), 0) + 1
  INTO seq_value_holder
  FROM YOUR_TABLE;
  EXECUTE immediate 'create sequence SEQ_YOUR_TABLE start with ' || seq_value_holder || ' increment by 1';
END;

If you have difficulties with running SQL Developer (in my case 4.0.2.15.21) from Oracle in console on Linux you can see such stack trace :

# A fatal error has been detected by the Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x00007feb22ed8be0, pid=32116, tid=140649711318784
#
# JRE version: Java(TM) SE Runtime Environment (7.0_65-b17) (build 1.7.0_65-b17)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (24.65-b04 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C  0x00007feb22ed8be0
#
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
#
# An error report file with more information is saved as:
# /home/lukasz/sqldeveloper/sqldeveloper/bin/hs_err_pid32116.log
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.sun.com/bugreport/crash.jsp
#
/home/lukasz/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 1193: 32116 Przerwane               (core dumped) ${JAVA} "${APP_VM_OPTS[@]}" ${APP_ENV_VARS} -classpath ${APP_CLASSPATH} ${APP_MAIN_CLASS} "${APP_APP_OPTS[@]}"

SQLDeveloper.sh

I would like only to announce that I have created a small application in Android called Oracle DB 11g Errors Guide. This application helps you as a developer or administrator of Oracle ® Database 11g Release 1 find solutions of problems which you are faced with during your work. Each error message contains the message statement, an explanation of the probable causes and a recommended action.

More information about Oracle ® Database 11g Release 1 Error Messages you can find under hyperlink - Oracle® Database Error Messages 11g Release 1 (11.1) Documentation

Hyperlink to Google Play Store - Google Play Store GarciaPL

In last few days I had a problem with some SQL queries in Oracle database. I won't give you this particular SQL query which has ended with error which you can find in the header of this post, but i will give solution! :)

Suppose that your SQL queries use data in format for instance YYYY-MM-DD, so before them execute below command which specifies the default date format which can be used in TO_CHAR and TO_DATE functions :

alter session  set NLS_DATE_FORMAT='YYYY-MM-DD';