Six years ago(!) I did an Office Hours session where I demonstrated a little routine that would let you generate more efficient INSERTs by converting a standard INSERT-VALUES into a multi-table insert (INSERT ALL) in order to reduce the number of network trips.
Of course that is a moot point with 26ai because we have more options at our disposal with INSERT. For example, you can have multiple rows in a single VALUES clause.
For full details on that, check out this video
But to my knowledge, none of the tools available out there have caught up with the feature advancements in 26ai, so I figured I should spruce up my little demo from 6 years ago so allow you generate 26ai-style INSERTS. Here is the fresh version:
SQL> create or replace
2 function as_insert26(p_query varchar2, p_batch int default 10, p_use_set_clause boolean default false) return sys.odcivarchar2list pipelined as
3 l_theCursor integer default dbms_sql.open_cursor;
4 l_columnValue varchar2(4000);
5 l_status integer;
6 l_descTbl dbms_sql.desc_tab;
7 l_colCnt number;
8 n number := 0;
9 l_pad int := 0;
10
11 l_tname varchar2(2000) := lower(translate(p_query,chr(10)||chr(13)||',',' '));
12 l_collist varchar2(32000);
13 l_colval varchar2(32000);
14 l_dml varchar2(32000);
15
16 l_nls sys.odcivarchar2list := sys.odcivarchar2list();
17
18 function format_col(l_existing varchar2, p_value varchar2, p_colname varchar2) return varchar2 is
19 begin
20 if p_use_set_clause then
21 return ' '||rpad(p_colname,l_pad)||' = '||p_value||',';
22 else
23 return l_existing||p_value||',';
24 end if;
25 end;
26
27 begin
28 l_tname := substr(l_tname,instr(l_tname,' from')+6)||' ';
29 l_tname := substr(l_tname,1,instr(l_tname,' '));
30
31 if l_tname is null then l_tname := '@@TABLE@@'; end if;
32
33 select value
34 bulk collect into l_nls
35 from v$nls_parameters
36 where parameter in (
37 'NLS_DATE_FORMAT',
38 'NLS_TIMESTAMP_FORMAT',
39 'NLS_TIMESTAMP_TZ_FORMAT')
40 order by parameter;
41
42 execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';
43 execute immediate 'alter session set nls_timestamp_format=''yyyy-mm-dd hh24:mi:ssff'' ';
44 execute immediate 'alter session set nls_timestamp_tz_format=''yyyy-mm-dd hh24:mi:ssff tzr'' ';
45
46 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
47 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
48
49 for i in 1 .. l_colCnt loop
50 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
51 l_collist := l_collist || l_descTbl(i).col_name||',';
52 l_pad := greatest(l_pad, length(l_descTbl(i).col_name));
53 end loop;
54 l_collist := 'into '||l_tname||'('||rtrim(l_collist,',')||')';
55 l_pad := l_pad + 2;
56
57 l_status := dbms_sql.execute(l_theCursor);
58
59
60 pipe row('alter session set cursor_sharing = force;');
61 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
62 n := n + 1;
63
64 if p_use_set_clause then
65 pipe row('insert into '||l_tname);
66 pipe row('set');
67 else
68 if mod(n,p_batch) = 1 or p_batch = 1 then
69 if n > 1 then
70 l_colval := rtrim(l_colval,',')||';';
71 pipe row (l_colval);
72 end if;
73 pipe row('insert '||l_collist||' values');
74 else
75 if nvl(length(l_colval),0) > 0 then
76 pipe row (l_colval);
77 end if;
78 end if;
79 l_colval := ' (';
80 end if;
81
82 for i in 1 .. l_colCnt loop
83 dbms_sql.column_value( l_theCursor, i, l_columnValue );
84 if l_columnValue is null then
85 l_colval := format_col(l_colval , 'null',l_descTbl(i).col_name);
86 elsif l_descTbl(i).col_type in (1,8,9,96,112) then
87 l_colval := format_col(l_colval , 'q''{'||l_columnValue ||'}''' ,l_descTbl(i).col_name);
88 elsif l_descTbl(i).col_type in (2,100,101) then
89 l_colval := format_col(l_colval , l_columnValue,l_descTbl(i).col_name);
90 elsif l_descTbl(i).col_type in (12) then
91 l_colval := format_col(l_colval , 'to_date('''||l_columnValue||''',''yyyy-mm-dd hh24:mi:ss'')',l_descTbl(i).col_name);
92 elsif l_descTbl(i).col_type in (180) then
93 l_colval := format_col(l_colval , 'to_timestamp('''||l_columnValue||''',''yyyy-mm-dd hh24:mi:ssff'')',l_descTbl(i).col_name);
94 elsif l_descTbl(i).col_type in (181) then
95 l_colval := format_col(l_colval ,'to_timestamp_tz('''||l_columnValue||''',''yyyy-mm-dd hh24:mi:ssff tzr'')',l_descTbl(i).col_name);
96 elsif l_descTbl(i).col_type in (231) then
97 l_colval := format_col(l_colval , 'to_timestamp('''||l_columnValue||''',''yyyy-mm-dd hh24:mi:ssff'')' ,l_descTbl(i).col_name);
98 elsif l_descTbl(i).col_type in (182) then
99 l_colval := format_col(l_colval , 'to_yminterval('''||l_columnValue||''')' ,l_descTbl(i).col_name);
100 elsif l_descTbl(i).col_type in (183) then
101 l_colval := format_col(l_colval ,'to_dsinterval('''||l_columnValue||''')' ,l_descTbl(i).col_name);
102 end if;
103 if p_use_set_clause then
104 if i < l_colCnt then
105 pipe row (l_colval);
106 else
107 pipe row (rtrim(l_colval,',')||';');
108 end if;
109 end if;
110 end loop;
111 if not p_use_set_clause then
112 l_colval := rtrim(l_colval,',')||'),';
113 end if;
114 end loop;
115 if n = 0 then
116 pipe row( 'No data found ');
117 else
118 if not p_use_set_clause then
119 l_colval := rtrim(l_colval,',')||';';
120 pipe row (l_colval);
121 end if;
122 end if;
123 pipe row('alter session set cursor_sharing = exact;');
124
125 execute immediate 'alter session set nls_date_format='''||l_nls(1)||''' ';
126 execute immediate 'alter session set nls_timestamp_format='''||l_nls(2)||''' ';
127 execute immediate 'alter session set nls_timestamp_tz_format='''||l_nls(3)||''' ';
128 return;
129 end;
130 /
Function created.
Now you can choose the number of rows per VALUE clause, or if network trips is not an issue, why not have beautiful self-documenting INSERTs with the SET clause variant.
SQL> select * from as_insert26('select * from scott.emp',5);
COLUMN_VALUE
---------------------------------------------------------------------------------------------------------------
alter session set cursor_sharing = force;
insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7369,q'{SMITH}',q'{CLERK}',7902,to_date('1980-12-17 00:00:00','yyyy-mm-dd hh24:mi:ss'),800,null,20),
(7499,q'{ALLEN}',q'{SALESMAN}',7698,to_date('1981-02-20 00:00:00','yyyy-mm-dd hh24:mi:ss'),1600,300,30),
(7521,q'{WARD}',q'{SALESMAN}',7698,to_date('1981-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss'),1250,500,30),
(7566,q'{JONES}',q'{MANAGER}',7839,to_date('1981-04-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),2975,null,20),
(7654,q'{MARTIN}',q'{SALESMAN}',7698,to_date('1981-09-28 00:00:00','yyyy-mm-dd hh24:mi:ss'),1250,1400,30);
insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7698,q'{BLAKE}',q'{MANAGER}',7839,to_date('1981-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),2850,null,30),
(7782,q'{CLARK}',q'{MANAGER}',7839,to_date('1981-06-09 00:00:00','yyyy-mm-dd hh24:mi:ss'),2450,null,10),
(7788,q'{SCOTT}',q'{ANALYST}',7566,to_date('1982-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss'),3000,null,20),
(7839,q'{KING}',q'{PRESIDENT}',null,to_date('1981-11-17 00:00:00','yyyy-mm-dd hh24:mi:ss'),5000,null,10),
(7844,q'{TURNER}',q'{SALESMAN}',7698,to_date('1981-09-08 00:00:00','yyyy-mm-dd hh24:mi:ss'),1500,null,30);
insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7876,q'{ADAMS}',q'{CLERK}',7788,to_date('1983-01-12 00:00:00','yyyy-mm-dd hh24:mi:ss'),1100,null,20),
(7900,q'{JAMES}',q'{CLERK}',7698,to_date('1981-12-03 00:00:00','yyyy-mm-dd hh24:mi:ss'),950,null,30),
(7902,q'{FORD}',q'{ANALYST}',7566,to_date('1981-12-03 00:00:00','yyyy-mm-dd hh24:mi:ss'),3000,null,20),
(7934,q'{MILLER}',q'{CLERK}',7782,to_date('1982-01-23 00:00:00','yyyy-mm-dd hh24:mi:ss'),1300,null,10);
alter session set cursor_sharing = exact;
SQL> select * from as_insert26('select * from scott.dept',p_use_set_clause=>true);
COLUMN_VALUE
---------------------------------------------------------------------------------------
alter session set cursor_sharing = force;
insert into scott.dept
set
DEPTNO = 10,
DNAME = q'{ACCOUNTING}',
LOC = q'{NEW YORK}';
insert into scott.dept
set
DEPTNO = 20,
DNAME = q'{RESEARCH}',
LOC = q'{DALLAS}';
insert into scott.dept
set
DEPTNO = 30,
DNAME = q'{SALES}',
LOC = q'{CHICAGO}';
insert into scott.dept
set
DEPTNO = 40,
DNAME = q'{OPERATIONS}',
LOC = q'{BOSTON}';
alter session set cursor_sharing = exact;
Enjoy!
Learning is not a spectator sport 
Got some thoughts? Leave a comment