Re: Search in Long Type Field

From: Frank <franjoe_at_frisurf.no>
Date: Mon, 19 Mar 2001 20:57:32 +0100
Message-ID: <mBtt6.1416$GG4.38425@news1.oke.nextra.no>

Hi!
Is it a situation like this you are describing?

SQL> select * from test where long_col like '%AAA%'; select * from test where long_col like '%AAA%'

                         *

ERROR at line 1:
ORA-00932: inconsistent datatypes

The LONG type is hard to deal with in Oracle because very few operators work on it,
consider VARCHAR2 (max 2000 in Oracle 7 and max 4000 char from some Oracle 8 version) instead if you can.
It is possible to do what you whish in Oracle to but you must turn to PLSQL and put the LONG column into
a plsql variable and traverse it with INSTR.If the data is longer that look up CLOB/BLOB, but Im not familiar with it.

A few examples supplied below

Frank

SQLPlus:

CREATE TABLE test (long_col LONG,

                        text VARCHAR2(2000));

INSERT INTO test (long_col, text)
VALUES ('sdkajdlhsafFranklkajdlkajdlkajdslkajsdlka98', 'dhdhdhdhdhdhddhFrankiuwiwuiwuiwuiw');
commit;

SELECT *
FROM test
WHERE long_col LIKE '%Frank%';

SELECT *
FROM test
WHERE INSTR(long_col, 'Frank', 1) =12;

SELECT long_col||'Does this work'
FROM test;

/*
A hard way
*/

set serveroutput on
DECLARE
  sThe_Long VARCHAR2(30000);
  nPos NUMBER;
BEGIN
  SELECT long_col
  INTO sThe_Long
  FROM test;

  nPos := INSTR(sThe_Long, 'Frank', 1);
  DBMS_OUTPUT.PUT_LINE('Frank found at pos :'||TO_CHAR(nPos));

END;
/

/* The VARCHAR2 column is easier.
*/

SELECT *
FROM test
WHERE text LIKE '%Frank%';

SELECT *
FROM test
WHERE INSTR(text, 'Frank', 1) =16;

SELECT text||'Does this work'
FROM test; Received on Mon Mar 19 2001 - 13:57:32 CST