Re: Problem of using LONG datatype in SQL

From: Douglas Dunnigan <douglas_at_yogananda-srf.org>
Date: 1997/07/17
Message-ID: <01bc92d3$6d249500$d7060b26@systems-douglas>#1/1

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; 

 BEGIN         stmt := 'SELECT TEXT FROM SYS.VIEW$ WHERE OBJ# = 579';

        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); 

END; Received on Thu Jul 17 1997 - 00:00:00 CDT