Monday, 24 December 2012

Using long data type in where clause in oracle.

How to use long data type in where clause in Oracle?

There can be a situation where in you want to use long data type column in the where clause of a sql statement. But long data type have a limitation that they can be used only in the select part of the sql statement. You can not use long columns in where, group by, having, connect by, distinct clause. The  workaround for this is that :-

1) Write a pl/sql block as per your specific requirement.

2) Convert long to clob.

Let's create a test table to demonstrate the first solution.

SQL> create table test ( a long);

Table created.

SQL> insert into test values ('Hello World');

1 row created.

SQL> commit;

Commit complete.

Now we will write an anonymous block, then take the value of long column into a varchar data type and use an instr function to check the existence of 'Wor' in the column. 

SQL> set serveroutput on;
SQL> declare
  2  lv_var varchar2(4000);
  3  begin
  4  select a into lv_var from test;
  5  if (instr(lv_var,'Wor') > 0)  then
  6  dbms_output.put_line('Ok');
  7  else
  8  dbms_output.put_line('Fail');
  9  end if;
 10  end;
 11  /


PL/SQL procedure successfully completed.

This is a simple example to demonstrate how to work with long data type. It may be necessary for you to create a function or procedure using the above logic so as to meet your specific requirement. You can use the select statement with a cursor and loop through entire table to check existence of keyword in a row. 

Another workaround for above problem is that you can convert a long data type to clob and then use that in the where clause. But this has one limitation that once you convert to clob you can not revert back to long data type. Also there may be situations where you need to search for values in long data type columns of data dictionary. In that case also you can not modify the data type of data dictionary tables and need to write the block to get your result.

SQL> alter table test modify(a clob);

Table altered.

SQL> select * from test where a like 'Hell%';

Hello World

SQL>  alter table test modify(a long);
 alter table test modify(a long)
ERROR at line 1:
ORA-22859: invalid modification of columns

No comments:

Post a Comment

back to top