Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem of using LONG datatype in SQL
Maoz Mussel <mmussel_at_iil.intel.com> wrote in article
<33C9D43C.6DD0_at_iil.intel.com>...
> I want to run queries based on a LONG field in the where clause, but
> since this datatype could not be used in the where statement, I cannot
> run such a query:
> SELECT * FROM table_name WHERE description LIKE '%string%';
> where description is a LONG datatype.
>
> Other problem is when I'm trying to run such a statement:
> INSERT INTO tmp_table SELECT * FROM table_name;
> Once again, when one of table_name fields is a LONG datatype, I got the
> following error message:
> ORA-00997: illegal use of LONG datatype
>
> I guess I'm not the first one to fase this problem, so if you know of a
> solution, or at least a workaround, please let me know. I cannot replace
> this field to a VARCHAR2 type, since it should hold a large amount of
> charachters, but any other idea will be great.
You might be able to use a PL/SQL function that includes calls to the
DBMS_SQL package.
Here is an anonymous PL/SQL block that uses that package to select the LONG
field from SYS.VIEW$ and display it with dbms_output. You could adapt the
techniques of reading a LONG field in sections and processing them in
PL/SQL.
set serveroutput on
DECLARE
cur_id pls_integer; buff varchar2(32767); pNewLine pls_integer; pStartLine pls_integer; pEndline pls_integer; offset pls_integer; v_length pls_integer; stmt varchar2(500); ret pls_integer;
cur_id := dbms_sql.open_cursor; dbms_sql.parse(cur_id, stmt, dbms_sql.NATIVE); dbms_sql.define_column_long(cur_id, 1); ret := dbms_sql.execute(cur_id); IF (dbms_sql.fetch_rows(cur_id) > 0 ) THEN offset := 0; LOOP dbms_sql.column_value_long(cur_id, 1, 32767, offset, buff, v_length); EXIT WHEN v_length = 0; pNewLine := 0; pStartLine := 1; LOOP pNewLine := instr(buff, chr(10), pNewLine + 1); IF pNewLine = 0 THEN -- through end of buffer pEndline := length(buff) - pStartLine; ELSE pEndline := pNewLine - 1; END IF; WHILE pEndline - pStartLine > 255 LOOP -- 255 limit for dbms_output.put_line dbms_output.put_line(substr(buff, pStartLine, 255)); dbms_output.put('(continuation)>'); pStartLine := pStartLine + 255; END LOOP; dbms_output.put_line(substr(buff, pStartLine, pEndline - pStartLine + 1)); pStartLine := pNewLine + 1; EXIT WHEN pNewLine = 0; END LOOP; offset := offset + v_length; END LOOP; END IF; dbms_sql.close_cursor(cur_id);