OXIESEC PANEL

Name	Size	Modified	Perms
📁 ..	-	02/03/2023 08:06:37 AM	rwxr-xr-x
📄 config.pl	6.53 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 dataextract.sh	2.51 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 etl.pl	5.46 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 etlproc	11.81 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 func.pl	4.01 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 globvar.sh	330 bytes	02/03/2023 07:34:08 AM	rw-r--r--
📄 initetl.pl	1.88 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 libawk.awk	3.07 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 makeXtab2NormalExcel.pl	5.25 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 process.pl	6.92 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 process_changed.pl	5.69 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 readwrite.pl	4.16 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 rxcsv.pl	7.94 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 rxdb.pl	8.84 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 rxfile.pl	4.72 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 rxsalesforce.pl	3.33 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 rxsalesforce.pl.bak	3.33 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 rxxml.pl	12.66 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 rxxml2.pl	4.18 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 s.log	8.86 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 sendmail.pl	1.06 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 sourcequery.pl	4.17 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 sql.pl	13.89 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 sql_mssql.pl	13.89 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 store.pl	7.6 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 task.pl	30.55 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 task_mssql.pl	30.8 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 tmpxml.pl	1.49 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 userfunc.pl	5.5 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 valid.pl	3.9 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 workflow.pl	3.72 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 xform.pl	2.62 KB	02/03/2023 07:34:08 AM	rw-r--r--

Editing: sourcequery.pl

#!/usr/bin/perl

########################################################
#  ALL QUERY & QUERY TASK RELATED THING IN THIS BLOCK  #
########################################################

sub create_database_source_query{
	my $dbtype = shift;
	my $srctabname = shift;
	my $srccols = shift;
	my $tabtype = shift;

my $qrysep = '';
	my $colstr = '';

if($dbtype =~ m/racle/si){#Looking Oracle
		$qrysep = "||'$G_DBOutDelim'||SLASH_N_CHAR";	
	}
	else{
		$qrysep = ",";	
	}

foreach $srccolname(@$srccols){
		if($srccolname eq 'No_Source'){
			$srccolname = '\'\'';
			}
                  else{
		$colstr = $colstr."$srccolname$qrysep";
                      }       
	}
	if($dbtype =~ m/alesforce/si){
		$colstr=$colstr.$qrysep;
	}
	else{
         $colstr=$colstr."\'endl\'$qrysep";
	}

for($i=0; $i<length($qrysep); $i++){
		chop($colstr);
	}

$colstr =~ s/\s//g;

if($dbtype =~ m/alesforce/si){
			chop($colstr);
	}

if($tabtype eq 'Q'){
		return $colstr;
		}
	else{
		#$source_query = "select $colstr from $srctabname";
		$source_query = "SELECT $colstr FROM $srctabname";
		return $source_query;
		}
}

sub getqcols{
	my $sqry = shift;

my @a = split /\s+/, $sqry;

my $sc = 0;
	my $fc = 0;
	my $cols = '';
	my $rest = '';

foreach my $v(@a){
		next if(($fc==0) && ($v =~ /\bselect\b/gi));
		if(($fc==0) && ($v =~ /\bfrom\b/gi)){
			$fc = 1;
			next;
		}
		if($fc == 0){
			$cols = $cols." ".$v;
		}else{
			$rest = $rest." ".$v;
		}
	}

return ($cols, $rest);
}

sub makeOraPipedQuery{
	my $query = shift;
	my $qstr = shift;
	my $sepch = shift;
	my $sqry = '';
	my $i = 0;

if($query =~ /{/){# Added for complex queries	}
		return make_curlybrace_oraquery($query, $sepch);
	}

if ($query =~ /\bunion\b/gsi){
		my @selqlist = ();

@selqlist = split /\bunion\b/i, $query;
		foreach my $q(@selqlist){
			my ($collist, $aftr_from) = &getqcols($q);
                         $collist=$collist.","."\'endl\'";  
			my $collist = &makeOraPipedQueryBlock($collist, $sepch);
			
			if($i eq 0){
				}
			else{
				$sqry = $sqry . ' union ';
				}
			$sqry = $sqry . 'select ' . $collist. ' from' . $aftr_from;
			$i++;

$collist = '';
			$aftr_from = '';
			$q = '';
			}
		}
	else{
		my ($collist, $aftr_from) = &getqcols($query);
		my $collist = &makeOraPipedQueryBlock($qstr, $sepch);
		$sqry = 'select ' . $collist. ' from' . $aftr_from;
		}

printlog($QUERY, "ORA Query:$sqry");

return $sqry;
}

sub make_curlybrace_oraquery{
	my $inquery = shift;
	my $sepch = shift;
	my $brace = 0;
	my $ix = 0;
	my $nthcollist = -1;
	my @collist = ();
	my @splitqlist = ();

if($inquery =~ /{/)#for indent putting }
	{
		@splitqlist = split /\s/, $inquery;
		foreach my $curword(@splitqlist){
			if($curword =~ /{/){
				$nthcollist++;
				$brace++;
			}
			if($curword =~ /}/){
				$collist[$nthcollist] = $collist[$nthcollist].$curword;
				$splitqlist[$ix] = "{$nthcollist";
				$brace--;
			}
			if($brace > 0){
				$collist[$nthcollist] = $collist[$nthcollist].$curword;
				$splitqlist[$ix] = "{$nthcollist";
			}
			$ix++;
		}
	}
	my $outquery = join(' ', @splitqlist);

$nthcollist = -1;
        my $length= scalar @collist;
	foreach $cols(@collist){
		$nthcollist++;
		$cols =~ s/[\{\}]//g;
                if(($length-1) == $nthcollist){
        printlog($MAX, "MY LIST IS $length"); 
                $cols=$cols.","."\'endl\'";    
                } 
		$cols = makeOraPipedQueryBlock($cols, $sepch);
		$outquery =~ s/(\{$nthcollist )+/$cols /g;
		printlog($MAX, "[$nthcollist][$cols]");
	}
	printlog($MAX, "$outquery");
	return $outquery;
}
	
sub makeOraPipedQueryBlock{
	my $qstr = shift;
	my $sepch = shift;

my @qchr = split //, $qstr;
	my @collist = ();
	my $curcol = '';

my $brc = 0;
	foreach my $ch(@qchr){
		if($ch eq '('){
			$brc++;
		} elsif($ch eq ')'){
			$brc--;
		}

if(($brc eq 0) && ($ch eq ",")){
			push(@collist, $curcol);
			$curcol = '';
		}
		else{
			$curcol = $curcol.$ch;
		}

}

push(@collist, $curcol);

#Now making piped query from collist
	my $pipedqry = '';
	my $i = 0;
	foreach $cv(@collist){
		$cv =~ s/ as .*//g;
		if($i eq 0){
			$pipedqry = $cv; 
			}
		else{
			$pipedqry = $pipedqry.$sepch.$cv;
			}

$i++;
	}

printlog($MAX, "[RETURNING ORACLE COLS $sepch => $pipedqry]");
	return $pipedqry;
}