How to generate insert statements of a table in Oracle?

This code snippet takes one parameter as table name. Then generates insert statements of current data in this table. DBMS_SQL package is used to detect column types and retrieve data from table. You may want to look at "Is it possible to return table from oracle function?" post before you examine this code snippet. This code works on Oracle databases which have version 10g or higher.

PACKAGE PKG_UTIL2 IS
    TYPE record_type IS RECORD (strSql    varchar2(8192));
    TYPE tbl_record_type IS TABLE OF record_type;
    FUNCTION f_get_tbl_insert_dml(p_table_name in varchar2
        ) RETURN tbl_record_type PIPELINED;

END; -- Package spec

PACKAGE BODY PKG_UTIL2 IS
    function f_get_tbl_insert_dml(p_table_name in varchar2
        ) return tbl_record_type pipelined as
 
        out_rec record_type;
        str_sql   varchar2(8192);
        desc_tab    dbms_sql.desc_tab;
        col_cnt     number;
        cur_id      NUMBER;
        namevar  VARCHAR2(32767);
        numvar   NUMBER;
        datevar  DATE;
        opr     varchar2(4000);
        ret_val varchar2(8192);
        tbl_col_cnt number;
     
    begin
        str_sql := 'select * from '||p_table_name;
        cur_id := dbms_sql.open_cursor;
        dbms_sql.parse( c => cur_id, statement => str_sql, language_flag => dbms_sql.native);
  
        dbms_sql.describe_columns( c => cur_id, col_cnt => col_cnt, desc_t  => desc_tab);
  
        FOR i IN 1 .. col_cnt LOOP
            IF desc_tab(i).col_type = 2 THEN
                DBMS_SQL.DEFINE_COLUMN(cur_id, i, numvar);
            ELSIF desc_tab(i).col_type = 12 THEN
                DBMS_SQL.DEFINE_COLUMN(cur_id, i, datevar);
            ELSE
                DBMS_SQL.DEFINE_COLUMN(cur_id, i, namevar, 4000);
            END IF;
        END LOOP;
  
        if DBMS_SQL.execute(cur_id) = 0 then
            while DBMS_SQL.FETCH_ROWS(cur_id) > 0 loop
                ret_val := 'INSERT INTO ' || p_table_name || ' VALUES (';
                for i in 1..col_cnt loop
                    if desc_tab(i).col_type = 2 then  --number
                        DBMS_SQL.COLUMN_VALUE(cur_id, i, numvar);
                        if numvar is null then
                            ret_val := ret_val ||  'null,';
                        else
                            ret_val := ret_val || replace(to_char(numvar),',','.') || ',';
                        end if;
                    elsif desc_tab(i).col_type = 12 then --date
                        DBMS_SQL.COLUMN_VALUE(cur_id, i, datevar);
                        if datevar is null then
                            ret_val := ret_val ||  'null,';
                        else
                            if datevar = trunc(datevar) then
                                ret_val := ret_val || 'to_date('''
                                    || to_char(datevar, 'DD/MM/YYYY') ||''',''DD/MM/YYYY''),';
                            else
                                ret_val := ret_val || 'to_date('''
                                    || to_char(datevar, 'DD/MM/YYYY hh24:mi:ss') ||''',''DD/MM/YYYY hh24:mi:ss''),';
                            end if;
                        end if;
                    else --varchar2, char, others
                        DBMS_SQL.COLUMN_VALUE(cur_id, i, namevar);
                        if namevar is null then
                            ret_val := ret_val ||  'null,';
                        else
                            ret_val := ret_val || '''' || namevar || ''',';
                        end if;
                    end if;
                end loop;
            
                ret_val := substr(ret_val,1,length(ret_val)-1)  || ')';
                out_rec.strSql := ret_val;
                pipe row (out_rec);
            end loop;
        end if;
        DBMS_SQL.CLOSE_CURSOR(cur_id);
  
        return;
    end;
END;

Usage

select * from table(PKG_UTIL2.f_get_tbl_insert_dml('customer'))

select * from customer 

select * from table(PKG_UTIL2.f_get_tbl_insert_dml('customer'))

Is it possible to return table from oracle function?

 
In Oracle 9i and previous versions you could return table from a function by creating a type and a function which returns that type as pipelined function. In Oracle databases which have lower versions than 10g we should create that object on database. And it was a bit hard when you want to change the return structure.
We had to drop that type and recreate it. In Oracle 10g and higher verions we can return table from a function by no creating any type on database. We could do this by declaring a record in package spec. The code below works on Oracle databases which have version is 10g or higher.

PACKAGE PKG_PIPELINE IS
    TYPE record_type IS RECORD (
        string      varchar2(8192),
        nmbr        number,
        dt         date
    );
    TYPE tbl_record_type IS TABLE OF record_type;
    function f_test(cnt in number) RETURN tbl_record_type PIPELINED;
 
END; -- Package spec

PACKAGE BODY PKG_PIPELINE IS
    function f_test(cnt in number) RETURN tbl_record_type PIPELINED as
        PRAGMA AUTONOMOUS_TRANSACTION;
        out_rec     record_type;
    begin
      
        out_rec.string := 'A';
        out_rec.nmbr := 5;
        out_rec.dt := sysdate;
        pipe row (out_rec);
     
        for i in 1..2*cnt loop
            out_rec.string := 'B';
            out_rec.nmbr := i;
            out_rec.dt := sysdate-1;
            pipe row (out_rec);
        end loop;
        return;
    end;
  END;

Usage
select * from table(PKG_PIPELINE.f_test(3))