Hi Chris,
The database is the third party production database so changing of field to blob is out of scope.
However, I managed to solve the problem with your help and using the codes in
https://community.toadworld.com/platforms/oracle/w/wiki/3324.dbms-sql-column-value-long Thanks again
Code for reference purpose
DECLARE
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk PLS_INTEGER;
TYPE long_rectype IS RECORD (piece_len PLS_INTEGER, pos_in_long PLS_INTEGER, one_piece VARCHAR2(256), one_piece_len PLS_INTEGER);
rec long_rectype;
pieces DBMS_SQL.VARCHAR2S;
v_clob CLOB:=null;
BEGIN
FOR R IN (SELECT IMAGE_ID , REMARKS
FROM IMAGE T WHERE IMAGE_LENGTH>32768)
LOOP
BEGIN
v_clob:=NULL;
DBMS_SQL.PARSE (cur,'SELECT IMAGE_FIELD CLOBVALUE FROM IMAGE WHERE IMAGE_LENGTH>32768 AND ID='''||R.IMAGE_ID||'''',DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN_LONG (cur, 1);
fdbk := DBMS_SQL.EXECUTE (cur);
fdbk := DBMS_SQL.FETCH_ROWS (cur);
IF fdbk > 0
THEN
rec.piece_len := 256;
rec.pos_in_long := 0;
LOOP
DBMS_SQL.COLUMN_VALUE_LONG (cur,1, rec.piece_len, rec.pos_in_long, rec.one_piece, rec.one_piece_len);
EXIT WHEN rec.one_piece_len = 0;
pieces (NVL (pieces.LAST, 0) + 1) := rec.one_piece;
rec.pos_in_long := rec.pos_in_long + rec.one_piece_len;
END LOOP;
FOR longint IN 1 ..pieces.COUNT
LOOP
v_clob := v_clob ||pieces(longint);
END LOOP;
//use v_clob as required
DBMS_OUTPUT.PUT_LINE(R.REMARKS);
END IF;
END;
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;