oracle case insensitive

Searching on column which contains the document name and document name is in Lower case and Upper case. To find the document with document name the query would be:

Select d.document_name from document d where LOWER(d.document_name) like LOWER('%java%')

Select d.document_name from document d where UPPER(d.document_name) like UPPER('%java%')

Converting VARCHAR2 to CLOB and CLOB to VARCHAR2 for ORACLE 10g

1. Converting Varchar2 to Clob

ALTER TABLE TEST ADD (TEMP_DESCRIPTION_TEXT  CLOB);

Add a column named "TEMP_DESCRIPTION_TEXT"to the table whose data type will be CLOB.

UPDATE TEST SET TEMP_DESCRIPTION_TEXT=DESCRIPTION_TEXT;
COMMIT;

Copy the text from existing column "DESCRIPTION_TEXT" to the new column "TEMP_DESCRIPTION_TEXT".

ALTER TABLE TEST DROP COLUMN DESCRIPTION_TEXT;

Drop the old column named "DESCRIPTION_TEXT".

ALTER TABLE TEST RENAME COLUMN TEMP_DESCRIPTION_TEXT TO DESCRIPTION_TEXT;

Rename the new column "TEMP_DESCRIPTION_TEXT" with old name "DESCRIPTION_TEXT".

2. Converting Clob to Varchar2


ALTER TABLE TEST ADD (TEMP_DESCRIPTION_TEXT  VARCHAR2(4000 BYTE));

Add a column named "TEMP_DESCRIPTION_TEXT"to the table whose data type will be VARCHAR2.


UPDATE TEST SET TEMP_DESCRIPTION_TEXT=DBMS_LOB.SUBSTR(DESCRIPTION_TEXT,4000,1);
COMMIT;

Copy the text from existing column "DESCRIPTION_TEXT" to the new column "TEMP_DESCRIPTION_TEXT". 

ALTER TABLE TEST DROP COLUMN DESCRIPTION_TEXT;

Drop the old column named "DESCRIPTION_TEXT".

ALTER TABLE TEST RENAME COLUMN TEMP_DESCRIPTION_TEXT TO DESCRIPTION_TEXT;

Rename the new column "TEMP_DESCRIPTION_TEXT" with old name "DESCRIPTION_TEXT".