CREATE OR REPLACE PROCEDURE geidm.longvarchar_to_blob (
   oldtablename       IN   VARCHAR,
   newtablename       IN   VARCHAR,
   idcolumnname       IN   VARCHAR,
   oldlobcolumnname   IN   VARCHAR,
   newlobcolumnname   IN   VARCHAR
)
AUTHID CURRENT_USER
IS
   TYPE mycurtype IS REF CURSOR;

   my_cursor   mycurtype;
   clob_loc    NCLOB;
   blob_loc    BLOB;
   charbuf     NVARCHAR2 (8000);
   rawbuf      RAW (32000);
   amount      NUMBER;
   offset      NUMBER;
   LENGTH      NUMBER;
   ID          VARCHAR (50);
   counter     NUMBER           := 0;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE geidm.tempnclob (tempnclob NCLOB)';

   OPEN my_cursor FOR 'select ' || idcolumnname || ' FROM ' || newtablename;

   LOOP
      FETCH my_cursor
       INTO ID;

      EXIT WHEN my_cursor%NOTFOUND;

      --First convert the LONG VARCHAR to an NCLOB
      EXECUTE IMMEDIATE    'INSERT INTO geidm.tempnclob select TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename
                        || ' WHERE '
                        || idcolumnname
                        || ' = :1'
                  USING ID;

      --Get the clob_loc
      EXECUTE IMMEDIATE 'select tempnclob FROM geidm.tempnclob'
                   INTO clob_loc;

      --Set the empty_blob
      EXECUTE IMMEDIATE    'update '
                        || newtablename
                        || ' SET '
                        || newlobcolumnname
                        || ' = empty_blob() '
                        || ' WHERE '
                        || idcolumnname
                        || ' = :1'
                  USING ID;

      --Get the blob_loc
      EXECUTE IMMEDIATE    'select '
                        || newlobcolumnname
                        || ' FROM '
                        || newtablename
                        || ' WHERE '
                        || idcolumnname
                        || ' = :1 FOR UPDATE'
                   INTO blob_loc
                  USING ID;

      --convert the nclob to a blob
      DBMS_LOB.OPEN (blob_loc, DBMS_LOB.lob_readwrite);
      DBMS_LOB.OPEN (clob_loc, DBMS_LOB.lob_readonly);
      offset := 1;
      LENGTH := DBMS_LOB.getlength (clob_loc);

      WHILE (offset <= LENGTH)
      LOOP
         amount := 8000;
         DBMS_LOB.READ (clob_loc, amount, offset, charbuf);
         rawbuf := UTL_RAW.cast_to_raw (charbuf);
         DBMS_LOB.writeappend (blob_loc, UTL_RAW.LENGTH (rawbuf), rawbuf);
         offset := offset + amount;
      END LOOP;

      DBMS_LOB.CLOSE (blob_loc);
      DBMS_LOB.CLOSE (clob_loc);

      --delete the temporary nclob
      EXECUTE IMMEDIATE 'delete from geidm.tempnclob';

      --commit every 100 iterations to avoid exhausting rollback segments
      counter := counter + 1;

      IF (MOD (counter, 100) = 0)
      THEN
         COMMIT;
      END IF;
   END LOOP;

   CLOSE my_cursor;

   EXECUTE IMMEDIATE 'DROP TABLE geidm.tempnclob';

   COMMIT;
END;
/

...
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE geidm.tempnclob (id  varchar2(50), tempblob BLOB)';

  EXECUTE IMMEDIATE    'INSERT INTO geidm.tempnclob '||
                       ' select '||idcolumnname||', TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename;

  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newcolumnname||' = (select '||oldcolumnname||
                        'from   '||oldtablename||' o '||
                        'where  n.'||idcolumnname||'= o.'||idcolumnname||')';

  EXECUTE IMMEDIATE 'DROP TABLE geidm.tempnclob';
end;

CREATE OR REPLACE FUNCTION nclob_to_blob (p_nclob  in nclob) return blob as
  v_return   blob := empty_blob();
  v_length   pls_integer;
  v_offset   pls_integer := 0;
  v_amount   pls_integer;
  v_raw      raw(32000);
  v_vc       varchar2(32000);
begin
  v_LENGTH := DBMS_LOB.getlength(p_nclob);

  WHILE (v_offset <= LENGTH) LOOP
    v_amount := 8000;
    DBMS_LOB.READ (p_nclob, v_amount, v_offset, v_vc2);
    rawbuf := UTL_RAW.cast_to_raw (v_vc2);
    DBMS_LOB.writeappend (v_return, UTL_RAW.LENGTH (v_raw), v_raw);
    v_offset := v_offset + v_amount;
  END LOOP;

  return v_return;
end;

SQL> sho err
Errors for FUNCTION NCLOB_TO_BLOB:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/3     PL/SQL: Statement ignored
11/22    PLS-00306: wrong number or types of arguments in call to 'LENGTH'
SQL>

SQL> sho err
Errors for FUNCTION NCLOB_TO_BLOB:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/5     PL/SQL: Statement ignored
12/49    PLS-00201: identifier 'V_VC2' must be declared
13/5     PL/SQL: Statement ignored
13/5     PLS-00201: identifier 'RAWBUF' must be declared
SQL>

CREATE OR REPLACE PROCEDURE longvarchar_to_blob( oldTableName IN VARCHAR,
                                                         newTableName IN VARCHAR,
                                                         idColumnName IN VARCHAR,
                                                         oldLobColumnName IN VARCHAR,
                                                         newLobColumnName IN VARCHAR)
AUTHID CURRENT_USER
IS 
   /*TYPE MyCurType IS REF CURSOR;  
   my_cursor MyCurType;
   clob_loc NCLOB;
   blob_loc BLOB;
   charbuf NVARCHAR2(8000);
   rawbuf RAW(32000);
   amount number;
   offset number;
   length number;
   id VARCHAR(50);*/
   counter number := 0;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE tempnclob (id  varchar2(50), tempnclob NCLOB)';

  EXECUTE IMMEDIATE    'INSERT INTO tempnclob '||
                       ' select '||idcolumnname||', TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename;
                        
commit;

  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select empty_blob()  from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';


execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select nclob_to_blob(tempnclob)  from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';
     
     
   EXECUTE IMMEDIATE 'DROP TABLE tempnclob ';
   
   commit;

end;
/



CREATE OR REPLACE FUNCTION nclob_to_blob (p_nclob  in nclob) return blob as
  v_return   blob := empty_blob();
  v_length   pls_integer;
  v_offset   pls_integer := 1;
  v_amount   pls_integer;
  v_raw      raw(32000);
  v_vc       nvarchar2(32000);
   
begin

  v_LENGTH := DBMS_LOB.getlength(p_nclob);
  
  DBMS_LOB.createtemporary(v_return,true);

  WHILE (v_offset <= v_LENGTH) LOOP
    v_amount := 8000;
    DBMS_LOB.READ (p_nclob, v_amount, v_offset, v_vc);
    v_raw := UTL_RAW.cast_to_raw (v_vc);
    DBMS_LOB.writeappend (v_return, UTL_RAW.LENGTH (v_raw), v_raw);
    v_offset := v_offset + v_amount;
  END LOOP;

  return v_return;
end;
/

--create source table
CREATE TABLE OBJECT
( ID   VARCHAR2(50 BYTE)  NOT NULL,  XML LONG NOT NULL);
--create target table
CREATE TABLE task
(ID   VARCHAR2(50 BYTE)  NOT NULL, XML  BLOB);
--create temp table
CREATE TABLE tempnclob (id  varchar2(50), tempnclob NCLOB);

INSERT INTO OBJECT values (1,'zzzzzz');
INSERT INTO OBJECT values (2,'ksiutgoihoasr');
INSERT INTO OBJECT values (3,'ksiutgoihoasr');
INSERT INTO OBJECT values (4,'jkadgfuqhgoihoigfhiodghoidhgoahgd');

--new table task should have the id column matching with old
INSERT INTO task(id) (select id from object);
commit;

CREATE OR REPLACE PROCEDURE longvarchar_to_blob( oldTableName IN VARCHAR,
                                                         newTableName IN VARCHAR,
                                                         idColumnName IN VARCHAR,
                                                         oldLobColumnName IN VARCHAR,
                                                         newLobColumnName IN VARCHAR)
AUTHID CURRENT_USER
IS 
BEGIN
  --Insert all the long values into the temp table
  EXECUTE IMMEDIATE    'INSERT INTO tempnclob '||
                       ' select '||idcolumnname||', TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename;
commit;
--Update the CLOB column in the new table with an empty_blob()
  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select empty_blob()  from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';
--Update the new table using data from the temp table by calling function  nclob_to_blob
execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select nclob_to_blob(tempnclob)  from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';
   commit;
end;
/

CREATE OR REPLACE FUNCTION nclob_to_blob (p_nclob  in nclob) return blob as
  v_return   blob := empty_blob();
  v_length   pls_integer;
  v_offset   pls_integer := 1;
  v_amount   pls_integer;
  v_raw      raw(32000);
  v_vc       nvarchar2(32000);
   
begin
  v_LENGTH := DBMS_LOB.getlength(p_nclob);
  
  DBMS_LOB.createtemporary(v_return,true);

  WHILE (v_offset <= v_LENGTH) LOOP
    v_amount := 8000;
    DBMS_LOB.READ (p_nclob, v_amount, v_offset, v_vc);
    v_raw := UTL_RAW.cast_to_raw (v_vc);
    DBMS_LOB.writeappend (v_return, UTL_RAW.LENGTH (v_raw), v_raw);
    v_offset := v_offset + v_amount;
  END LOOP;
  return v_return;
end;
/

--before execution 
 1* select id,DBMS_LOB.getlength(xml) from task
SQL> /

ID                                                 DBMS_LOB.GETLENGTH(XML)
-------------------------------------------------- -----------------------
1                                                                      
2                                                                      
6                                                                      
4   

 1  begin
  2  longvarchar_to_blob('object','task','id','xml','xml');
  3* end;
SQL> /

PL/SQL procedure successfully completed.

 1* select id,DBMS_LOB.getlength(xml) from task
SQL> /

ID                                                 DBMS_LOB.GETLENGTH(XML)
-------------------------------------------------- -----------------------
1                                                                       12
2                                                                       26
6                                                                       26
4                                                                       66

CREATE OR REPLACE PROCEDURE longvarchar_to_blob( oldTableName IN VARCHAR,
                                                         newTableName IN VARCHAR,
                                                         idColumnName IN VARCHAR,
                                                         oldLobColumnName IN VARCHAR,
                                                         newLobColumnName IN VARCHAR)
AUTHID CURRENT_USER IS 
BEGIN
  --Insert all the long values into the temp table
  EXECUTE IMMEDIATE    'INSERT INTO tempnclob '||
                       ' select '||idcolumnname||', TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename;
--Update the new table using data from the temp table 
  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select tempnclob from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';
   commit;
end;
/

CREATE OR REPLACE PROCEDURE longvarchar_to_clob( oldTableName IN VARCHAR,
                                                         newTableName IN VARCHAR,
                                                         idColumnName IN VARCHAR,
                                                         oldLobColumnName IN VARCHAR,
                                                         newLobColumnName IN VARCHAR)
AUTHID CURRENT_USER IS 
BEGIN
--Update the new table using data from the oldtable. Implement the function to_lob() 
  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select TO_LOB(o.'|| oldlobcolumnname|| ') from '|| oldtablename ||'  o  where  n.'||idcolumnname||'= o.'||idcolumnname||')';
   commit;
end;
/