Oracle Turkish Character Support;

echo "

set echo off

set verify off

set termout off

set trimout on

set heading off

set feedback off

set colsep ';'

set pagesize 0

set trimspool on

set headsep off

set linesize 5000

set serveroutput off

spool $tmpdir/${fname1}.txt

alter session set nls_language='TURKISH';

SELECT AIRPORT, a.ISTNO,${perbas}||'-'||${person},sum(VALUE) from TABLE1 a, TABLE2 where a.ICAO=b.ICAO and b.SENSOR_NAME IN('RAININST','PR_INS','RAIN_TDZ2','RAIN_TDZ1') and SENSOR_TIME between to_date('$perbas','YYYYMMDDHH24Mi') and to_date('$person','YYYYMMDDHH24Mi') group by AIRPORT,a.ISTNO order by 2 ;

spool off

exit " > $sql

sqlplus -S $ouser/$opass@$oserver @$sql

Change Spesific Columns Value According to a condition

This changes 4th columns value to "none" if it is zero.

awk 'BEGIN{FS=OFS=";"} $4 == "0" ;{$4 = "none"; } {print $0}' $filename 

Data Select From  SAP-IQ And Insert into Windows MS-SQL

#######################################################

function iq_select

{

echo "SELECT * ISTNO, YIL, AY, GUN, SAAT, DAKIKA, KAYIT_TURU, ORT_RUZGAR_YONU, ORT_RUZGAR_HIZI, MAKS_RUZGAR_YONU, MAKS_RUZGAR_HIZI, TOPLAM_YAGIS, SICAKLIK, NEM, convert(varchar,TARIH,20) FROM AA WHERE YIL=2022 and ay>1 and ISTNO between 17100 and 17102 " > $sql

 echo "go" >>$sql

/usr/local/bin/bsqldb -U user -S dbserver -P password -i $sql -o aa.txt -t "|" -q

sed -i 's/NULL//g' aa.txt

}

#######################################################

function sql_load 

{

freebcp table1 in aa.txt -S Mssqlsrv -U user -P password -c -b 1000 -t '|'

}

#######################################################

iq_select

sql_load

Split a row between pattern1 and pattern2

cat file |sed 's/^M//g' | sed -n "/pattern1/,/pattern2/p" > newfile

Türkçe diline ayarlanmamış Oracle veritabanına "Load Data" ile komut satırından veri yüklerken Türkçe karakterleri girmek için önce bunları İngilizce karşılığına çevirmek gerekiyor. Bunun için;

sed -e 's/Ş/s/g' -e 's/ş/s/g' -e 's/ğ/g/g' -e 's/Ğ/g/g' -e 's/ı/i/g' -e 's/İ/I/g' -e 's/ü/u/g' -e 's/Ü/U/g' -e 's/ö/o/g' -e 's/Ö/O/g' -e 's/ç/c/g' -e 's/Ç/c/g' f.txt > ff2.txt

kullanılabilir.

Sonrasında;

datafile=ff2.txt 

errors=`wc -l $datafile|awk '{print $1}'`

echo "Satir Sayisi: $errors"

echo "load data

infile '$datafile'

append

into table "AAA".TABLE1  

fields terminated by \"|\"

TRAILING NULLCOLS

(C1,C2,C3,C4)

" > table1.ctl

sqlldr user/pass@orcl1 control=table1.ctl bad=table1.bad log=table1.log errors=$errors date_cache=$errors SILENT=feedback

ile veri yüklenebilir. 

 Merge Two Files According To Common Columns

FileA:

17015;202101060930;2021;1;6;9;30;3;0.5;5;0.6;0.00;12.3;81.0;8.8;11.4;1021.7;1022.9

17015;202101060940;2021;1;6;9;40;0;0.7;1;0.8;0.00;12.3;82.0;9.0;11.5;1021.6;1022.8

17015;202101060950;2021;1;6;9;50;345;0.5;353;0.6;0.00;12.7;82.0;9.0;11.5;1021.5;1022.7

17015;202101061000;2021;1;6;10;0;319;0.4;8;0.6;0.00;12.7;83.0;9.2;11.6;1021.4;1022.6

17015;202101061010;2021;1;6;10;10;0;0.0;10;0.0;0.00;12.6;80.0;8.7;11.2;1021.3;1022.5

FileB:

17015;AKÇAKOCA;41.0895;31.1374;10

17018;DEVREK;41.1813;31.7985;1112

Command;

join <(sed 's/;/ /g' omgi_ondakikalik.txt|sort) <(sed 's/;/ /g' istbilgi.csv|sort) |sed 's/ /;/g' > omgi_10dk.txt

Result:

17015;202101060930;2021;1;6;9;30;3;0.5;5;0.6;0.00;12.3;81.0;8.8;11.4;1021.7;1022.9;AKÇAKOCA;41.0895;31.1374;10

17015;202101060940;2021;1;6;9;40;0;0.7;1;0.8;0.00;12.3;82.0;9.0;11.5;1021.6;1022.8;AKÇAKOCA;41.0895;31.1374;10

17015;202101060950;2021;1;6;9;50;345;0.5;353;0.6;0.00;12.7;82.0;9.0;11.5;1021.5;1022.7;AKÇAKOCA;41.0895;31.1374;10

17015;202101061000;2021;1;6;10;0;319;0.4;8;0.6;0.00;12.7;83.0;9.2;11.6;1021.4;1022.6;AKÇAKOCA;41.0895;31.1374;10

17015;202101061010;2021;1;6;10;10;0;0.0;10;0.0;0.00;12.6;80.0;8.7;11.2;1021.3;1022.5;AKÇAKOCA;41.0895;31.1374;10

 Another Date Loop

#start="2020-01-01 00:00"

for i in $(seq 0 1 670000); do  

   date -d "2020-01-01 0000 +$i minutes"  +%Y%m%d%H%M; 

done