title: ORA-00997: illegal use of LONG datatype in INSERT INTO... SELECT statement
body: <p>I am coding for a stored procedure for inserting data from a table to another table. I use statement like:</p>
  
  <pre><code>Procedure ImportData(
    IMPORT_DATE_FROM date
    ,IMPORT_DATE_TO date  
    ,SEQ_ID int
  )
  AS
  INSERT_DAY nvarchar2(10);
  IMPORT_MONTHYEAR nvarchar2(10);
  IMPORT_DATE_FROM_INT NUMBER;
  IMPORT_DATE_TO_INT NUMBER;
  IMPORT_DATE varchar2(20);
  --LOG_ID, BATCH_ID have been of type NUMBER and varchar2
  IN_LOGID mytable.LOG_ID%TYPE; 
  IN_BATCHID mytable.BATCH_ID%TYPE;
  BEGIN
  
  IMPORT_DATE_FROM_INT := to_number(to_char(IMPORT_DATE_FROM, 'j'));
  IMPORT_DATE_TO_INT := to_number(to_char(IMPORT_DATE_TO, 'j'));
  
  --even assigned it to a nubmer then insert throws exception
  IN_LOGID:=10;
  IN_BATCHID:10; 
      for cur_r in IMPORT_DATE_FROM_INT ..IMPORT_DATE_TO_INT loop
      IMPORT_DATE := to_char(to_date(cur_r, 'j'), 'yyyy-MM-dd');
      INSERT_DAY:=SUBSTR(IMPORT_DATE,9,2);
      IMPORT_MONTHYEAR:=SUBSTR(IMPORT_DATE,1,7);
      INSERT INTO mytable
      (LOGID,BATCHID,TEXT1,TEXT2,TEXT3,...)
      SELECT
      (IN_LOGID,IN_BATCHID, table1.field1, table2.field2,....)
      FROM table1 
      INNER JOIN table2
      on table1.dayId= 
      CASE 
      WHEN INSERT_DAY='01' THEN table2.day1
      WHEN INSERT_DAY='02' THEN table2.day2
      ....
      WHEN INSERT_DAY='31' THEN table2.day31
      END
      INNER JOIN table3
      .....
      END LOOP;
  </code></pre>
  
  <p>The problem seems to be at (LOGID&amp;BATCHID) because when I replace "IN_BATCHID"&amp;"IN_LOGID" in the select clause to a hard code 3 (or any number), then the procedure works fine.
  I have searched on the web and get information like. </p>
  
  <blockquote>
    <p>A value of datatype LONG was used in a function or in a DISTINCT,
    WHERE, CONNECT BY, GROUP BY, or ORDER BY clause. A LONG value can only
    be used in a SELECT clause.</p>
  </blockquote>
  
  <p>So I have tried to change my bacthid column from number to varchar2 (and of course the type of the variable IN_BATCHID in the procedure), but the same exception was thrown.</p>
  
  <p>May be it is also useful to mention that for mytable ,TEXT1,TEXT2...are type of VARCHAR2; while table1, table 2 are table of SQL SEVER and of table1.field1 is type of int, table2.field2 is type of varchar.</p>
  
  <p>Any help will be appreciated.</p>