%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%                Reload Data from Tablelist               %%
%%                 copyright  1998  by LDU                   %%
%%   Creates SQL file for reloading Data from Tablelist    %%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



parameters tablelist_file,output_file,everytable_commit,order_data;












set option ON_ERROR='PROMPT';
set option AUTO_COMMIT='OFF';
set option HEADINGS='OFF';
set option OUTPUT_FORMAT='TEXT';

create variable outname varchar( 3001 );
create variable x_commit varchar( 3001 );
create variable x_order varchar( 3001 );

set outname='{output_file}';

set x_commit=TRIM(UPPER('{everytable_commit}'));
IF (x_commit='1')or(x_commit='YES')or(x_commit='TRUE') THEN 
	SET x_commit='\\ncommit;\\n';
ELSE
	SET x_commit='';
END IF;

set x_order='{order_data}';

select '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%' from dummy>#tmp\rldata.tmp;
select '%%              Temporary file               %%' from dummy>>#tmp\rldata.tmp;
select '%%  automaticaly generated using RlData.sql  %%' from dummy>>#tmp\rldata.tmp;
select '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%\n\n' from dummy>>#tmp\rldata.tmp;
select 'set option HEADINGS=''OFF'';' from dummy >>#tmp\rldata.tmp;
select 'set option OUTPUT_FORMAT=''TEXT'';' from dummy >>#tmp\rldata.tmp;



select 'select ''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'' from dummy>#'+outname+';' from dummy>>#tmp\rldata.tmp;
select 'select ''%%  Automaticaly generated using RlData.sql  %%'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;
select 'select ''%%           ''+CONVERT (VARCHAR(12), now(*),111 )+'' ''+CONVERT (VARCHAR(12), now(*),108 )+''             %%'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;
select 'select ''%%          copyright 1998 by LDU            %%'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;
select 'select ''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%\\n\\n'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;



DECLARE LOCAL TEMPORARY TABLE tmp_listoftables4unload_ldu
(
	id			integer	default autoincrement,
	tn			long varchar not null,
    ob			varchar( 3001 )	null,
    primary key(id)
);

INPUT INTO tmp_listoftables4unload_ldu
	FROM {tablelist_file} 
	FORMAT ASCII
    (tn); 

IF (x_order='1')or(x_order='YES')or(x_order='TRUE') THEN 
	update tmp_listoftables4unload_ldu a
    set ob=(select list(distinct b.column_id) from
	  		SYS.SYSCOLUMN b,SYS.SYSTABLE c,SYS.SYSUSERPERM d
            where b.table_id=c.table_id and c.creator=d.user_id
			  	and a.tn=d.user_name+'.'+table_name
                and b.pkey='Y');
	update tmp_listoftables4unload_ldu set ob=' order by '+ob where ob<>'';
END IF;
update tmp_listoftables4unload_ldu set ob='' where ob is null;



select	'select ''\\n%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'' from dummy>>#'+outname+';\n'+
		'select ''%% TABLE '+tn+''' from dummy>>#'+outname+';\n'+
		'select ''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'' from dummy>>#'+outname+';\n'+
		'select ''TRUNCATE TABLE '+tn+';'' from dummy>>#'+outname+';\n'+
		'set option OUTPUT_FORMAT=''SQL'';\n'+
 		'select * from '+tn+ob+'>>#'+outname+';\n'+
        'set option OUTPUT_FORMAT=''TEXT'';\n'+
        'select '''+x_commit+''' from dummy>>#'+outname+';'
  from	tmp_listoftables4unload_ldu a
  order by id>>#tmp\rldata.tmp;

select 'set option OUTPUT_FORMAT=''TEXT'';' from dummy >>#tmp\rldata.tmp;
select 'select ''%%%%%%%%%%%%%%%%%%%%% END %%%%%%%%%%%%%%%%%%%%%'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;
select 'select ''%%  Automaticaly generated using RlData.sql  %%'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;
select 'select ''%%           ''+CONVERT (VARCHAR(12), now(*),111 )+'' ''+CONVERT (VARCHAR(12), now(*),108 )+''             %%'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;
select 'select ''%%          copyright 1998 by LDU            %%'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;
select 'select ''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%\\n\\n'' from dummy>>#'+outname+';' from dummy>>#tmp\rldata.tmp;
commit;

read tmp\rldata.tmp;