You Asked

Hi ,

I have faced one issue while creating a table from view.

I have created a view with some tables and it has around 900 million records and in that one of tble col have LONG data type. When create table from that view, I got some inconsistent data type like but I solved by using to_lob(col), but my question is now ...when I try to create a table using bulk collection am getting one error.

I wrote code like.

CREATE TABLE temp AS
SELECT *
FROM temp_view 
WHERE 1=2;
 
DECLARE
 CURSOR s_cur IS
 SELECT *
 FROM temp_view;
 
 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
 s_array fetch_array;
BEGIN
  OPEN s_cur;
  LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
 
    FORALL i IN 1..s_array.COUNT
    INSERT INTO temp VALUES s_array(i);
 
    EXIT WHEN s_cur%NOTFOUND;
  END LOOP;
  CLOSE s_cur;
  COMMIT;
END;
/

Finally am getting an error like s_cur incomplete or malformed.

Please can you give me right solution.

Note :- Even I have used in cursor select statement like cursor s_cur is select col, col2, Col3, to_lob(col4)as col4 from temp_view, to avoid that data type issue but still am getting an error.