Create fast insert scripts
PL/SQL Export Utilities are now able to export table data as insert scripts
A few weeks ago I released a new version of my PLEX project. I improved the ability to export table data as insert scripts. Previously, PLEX was only able to export CSV data. The reason was that I originally developed PLEX to speed up the implementation of version control in Oracle DB projects, and the ability to export CSV data was intended to track changes in master data tables.
It turned out that a colleague of mine wanted PLEX to export insert scripts for all data in all tables for the initial deployment of a new application. I implemented an initial version, but was not happy with the runtime of the insert scripts. I now hear some people arguing that there are better tools than insert scripts for importing larger amounts of data - but sometimes this is the only accepted way because only SQL*Plus is available to run the deployment scripts….
Then I stumbled across an older post by Connor McDonald on creating fast insert scripts. I have integrated his performance hacks into PLEX.
You can use them as follows (see last parameter):
DECLARE
l_zip_file BLOB;
BEGIN
l_zip_file := plex.to_zip(plex.backapp(
p_app_id => 100, -- parameter only available when APEX is installed
p_include_ords_modules => true, -- parameter only available when ORDS is installed
p_include_object_ddl => true,
p_include_data => true,
p_data_as_of_minutes_ago => 0, -- Read consistent data with the resulting timestamp (SCN). Defaults to 0.
p_data_max_rows => 10000, -- Maximum number of rows per table. Defaults to 1000.
p_data_table_name_like => 'CAT\_%,USERS,ROLES,RIGHTS',
p_data_table_name_not_like => '%\_HIST,LOGGER%',
p_data_format => 'insert:10'
));
-- Do something with the zip file.
-- Your code here...
END;
/
The p_data_format parameter is new and can contain a comma-separated list of formats - currently the CSV and INSERT formats are supported. An example: csv,insert exports for each table a CSV file and a SQL file with insert statements. For insert you can also specify the number of rows per insert all statement (default value is 20) - example: csv,insert:10 or insert:5.
As you can see from the p_data_table_name_not_like => '%\_HIST,LOGGER%' parameter, PLEX also accepts lists with like filters. In our example %\_HIST,LOGGER% is translated to:
where ...
and ( table_name not like '%\_HIST' escape '\'
and table_name not like 'LOGGER%' escape '\'
and ...
)
For the parameter p_data_table_name_like => 'CAT\_%,USERS,ROLES,RIGHTS' the tables are filtered as follows:
where ...
and ( table_name like 'CAT\_%' escape '\'
or table_name like 'USERS' escape '\'
or ...
)
There is a good reason why the default p_data_max_rows parameter is set to 1000 - if you don’t specify table name filters, all tables will be exported…
The resulting insert scripts look like this:
-- Script generated by PLEX version 2.4.2 - more infos here: https://github.com/ogobrecht/plex
-- Performance Hacks by Connor McDonald: https://connor-mcdonald.com/2019/05/17/hacking-together-faster-inserts/
-- For strange line end replacements a big thank to SQL*Plus: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2377701.1 (SQL Failed With ORA-1756 In Sqlplus But Works In SQL Developer)
prompt - insert 847 rows into USERS (exported 2021-01-31 14:24:30.703234000 +00:00)
set define off feedback off sqlblanklines on
alter session set cursor_sharing = force;
alter session set nls_numeric_characters = '.,';
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ssxff';
alter session set nls_timestamp_tz_format = 'yyyy-mm-dd hh24:mi:ssxff tzr';
begin
insert all
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (1,'Ylcmsajbil','Fojkjryntnixzfh','qvspjgvwmtbi@ghovilkddx.mly')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (2,'Axify','Cofjlkwzxytdih','ajgttnqlds@minokpyfo.gu')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (3,'Ckyqqvuqrkuktb','Igacqwp','qpygabuhbrs@nsjxpgjlle.ze')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (4,'Noythhl','Gausfu','ngmgsbr@duyxqzn.hmyo')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (5,'Kpewbrecnfzsi','Nwbsnjh','xwlhcfaxko@uhqsibdojjp.hsm')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (6,'Qlzyizkl','Gwnaojlvyud','kzndqj@nsosenf.fm')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (7,'Xpepttcemrd','Ktaqqdnqyfvc','uhbnzezvz@buiptt.lkrm')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (8,'Ybyinr','Vngairocujhy','igvfzoegbh@hsepkqiwbst.evs')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (9,'Ctjixltj','Yvsiei','ozpspssyw@vooiyfuf.xeh')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (10,'Lttzwsavnozxu','Kcyjalvzrl','yvwowaqrpku@dyapdumb.fvi')
select * from dual;
/* S N I P */
insert all
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (841,'Gpkabykoveq','Gljhlrijqop','imnhrheyr@ypccyiu.ah')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (842,'Mwrfazphbvmekpw','Kxirzfth','fxoatt@frlbwbn.tf')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (843,'Hzxomvkliaxl','Mstdrrmgfmsy','gpeidglzwfa@hwyumsansy.fet')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (844,'Vwvwezfsd','Xtfouojiymtlu','zgsdtowsvt@ywfngnijgts.ozd')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (845,'Eljntjfkxx','Sifgii','gksggat@ubfmmdopqy.ly')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (846,'Ewvyjeudjb','Anihlpdgeg','gietuk@ezciwejuedy.nuf')
into USERS(U_ID,U_FIRST_NAME,U_LAST_NAME,U_EMAIL) values (847,'Wscdamsgssmouf','Omtaofvlrjs','jrwyzftmbmo@gjylnuez.esq')
select * from dual;
end;
/
commit;
alter session set cursor_sharing = exact;
set define on
PLEX saves the NLS settings of the current session and makes sure that the insert scripts run with the same settings - this means that you should not get any number or date conversion errors. The performance hacks from Connor are alter session set cursor_sharing = force; and insert all - thanks for pointing that out Connor :-)
PLEX has many parameters to configure the export - the focus of PLEX is to quickly introduce version control to existing projects (see also this article about PLEX in general). If you want to use PLEX only for exporting table data, then you should create a wrapper function for it that suits your personal needs - here is an example:
create or replace function to_insert (
p_table_name varchar2,
p_max_rows integer default 1000,
p_batch_size integer default 20
) return clob is
l_file_collection plex.tab_export_files;
begin
l_file_collection := plex.backapp(
p_include_data => true,
p_data_max_rows => p_max_rows,
p_data_table_name_like => p_table_name,
p_data_format => 'insert:' || to_char(p_batch_size),
--overwrite defaults to save work for plex:
p_include_templates => false,
p_include_runtime_log => false,
p_include_error_log => false
);
return l_file_collection(1).contents;
end;
/
Now you can always do a quick export like this: select to_insert('USERS', 10000) from dual;
Happy exporting and scripting :-)
Ottmar