du

SET serveroutput ON SIZE 100000
SET feedback off

DECLARE
vtablename varchar2(30) := 'EMP'; -- Your Tablename
vcolumnlist varchar2(2000);
vinsertlist varchar2(2000);
vrefcurcolumns varchar2(4000);
vrefcurquery varchar2(2000);
vrefcuroutput varchar2(2000);
vcolumnname varchar2(2000);
cursor c1 IS SELECT columnname, datatype FROM usertabcolumns WHERE tablename = vtablename ORDER BY columnid;
refcur sysrefcursor;
BEGIN
FOR i IN c1 loop
vcolumnlist := vcolumnlist||','||i.columnname;
IF i.datatype = 'NUMBER' THEN
vcolumnname := i.columnname;
elsif i.datatype = 'DATE' THEN
vcolumnname := chr(39)||'todate('||chr(39)||'||chr(39)'||'||tochar('||i.columnname||','||chr(39)||'dd/mm/yyyy hh:mi:ss'||chr(39)||')||chr(39)||'||chr(39)||', '||chr(39)||'||chr(39)||'||chr(39)||'dd/mm/rrrr hh:mi:ss'||chr(39)||'||chr(39)||'||chr(39)||')'||chr(39);
elsif i.datatype = 'VARCHAR2' THEN
vcolumnname := 'chr(39)||'||i.columnname||'||chr(39)';
END IF;
vrefcurcolumns := vrefcurcolumns||'||'||chr(39)||','||chr(39)||'||'||vcolumnname;
END loop;
vcolumnlist := ltrim(vcolumnlist,',');
vrefcurcolumns := substr(vrefcur_columns,8);

vinsertlist := 'INSERT INTO '||vtablename||' ('||vcolumnlist||') VALUES ';
vrefcurquery := 'SELECT '||vrefcurcolumns||' FROM '||vtablename;

OPEN refcur FOR vrefcurquery;
loop
fetch refcur INTO vrefcuroutput;
exit WHEN refcur%notfound;
vrefcuroutput := '('||vrefcuroutput||');';
vrefcuroutput := REPLACE(vrefcuroutput,',,',',null,');
vrefcuroutput := REPLACE(vrefcuroutput,'(,','(null,');
vrefcuroutput := REPLACE(vrefcuroutput,',,)',',null)');
vrefcuroutput := REPLACE(vrefcuroutput,'null,)','null,null)');
vrefcuroutput := vinsertlist||vrefcuroutput;
dbmsoutput.putline (vrefcur_output);
END loop;
END;
/`