Code:
$
$ cat -n statements.sql
1 Insert into table_name (col1,col2,col3,col4,col5,DATE1,DATE2,col6,col7,col8,col9,col10,col11) values (6752,14932156,24,'ALL','Staff',to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS'),'aso 4','device',148,null,null,null);
2 Insert into emp (empno,ename,job,sal,hiredate,mgr,deptno) values (21,'Adrian','Salesman',1000,to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),1,42);
3 Insert into tname (col1,col2,col3,col4) values (to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('02/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('03/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('04/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'));
4 Insert into temp (col1,col2,col3,col4) values (null,null,null,null);
$
$
$ cat -n generate_qry.pl
1 #!/usr/bin/perl -w
2 use strict;
3 # Accept the file name as first argument and open it
4 my $file = $ARGV[0];
5
6 open(FH, "<", $file) or die "Can't open $file: $!";
7 while (<FH>) {
8 # Parse the line and set the table name, column-list and value-list
9 my ($tab, $cols, $vals) = m/into\s+(\S+)\s+\((.*)\)\s+values\s+\((.*)\)/i;
10
11 # Split the column-list and value-list into arrays. The "to_date" token will
12 # be split into two elements and will be joined during loop iteration.
13 my @c = split(/,/,$cols);
14 my @v = split(/,/,$vals);
15 my $qry = sprintf("select * from %s", $tab);
16 my ($left, $right, $j);
17
18 foreach my $i (0..$#c) {
19 # Each predicate is of the form "left-string right-string". Here,
20 # "left-string" is "where column" and "right-string" is "= value".
21 if ($i eq 0) {
22 $left = sprintf("where %s", $c[$i]);
23 $j = $i;
24 } else {
25 $left = sprintf("and %s", $c[$i]);
26 }
27 $right = $v[$j];
28
29 # Check the right-string and adjust it according to its value. If
30 # it has "to_date" function, then increment the right counter and append
31 # the next element.
32 if ($right =~ m/null/i) {
33 $right = sprintf(" is %s", $right);
34 } elsif ($right =~ m/to_date/i) {
35 $j++;
36 $right = sprintf(" = %s,%s", $right, $v[$j]);
37 } else {
38 $right = sprintf(" = %s", $right);
39 }
40 $j++;
41 $qry .= sprintf("\n%s%s", $left, $right);
42 }
43
44 # We're done. Print the query and move on to the next line from file.
45 $qry .= ";\n";
46 printf("\n%s", $qry);
47 }
48 close(FH) or die "Can't close $file: $!";
49
$
$ perl generate_qry.pl statements.sql
select * from table_name
where col1 = 6752
and col2 = 14932156
and col3 = 24
and col4 = 'ALL'
and col5 = 'Staff'
and DATE1 = to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and DATE2 = to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS')
and col6 = 'aso 4'
and col7 = 'device'
and col8 = 148
and col9 is null
and col10 is null
and col11 is null;
select * from emp
where empno = 21
and ename = 'Adrian'
and job = 'Salesman'
and sal = 1000
and hiredate = to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and mgr = 1
and deptno = 42;
select * from tname
where col1 = to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and col2 = to_date('02/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and col3 = to_date('03/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and col4 = to_date('04/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS');
select * from temp
where col1 is null
and col2 is null
and col3 is null
and col4 is null;
$
$
05-24-2017
