Ilmu Indra Ajjah

Send Mail To, CC, BCC

2012-01-27T11:18:00.000+07:00

Transform in SendMail Component :

include "~$AB_HOME/include/mail.dml";

/*Prepare message information*/

message::prepare_message(in) =

begin

let string("\x01") from = read_file("${MAILFROM}");

let string("\x01") reply_to = read_file("${MAILREPLYTO}");

let string("\x01") to_list_file = read_file("${MAILTO}");

let string("\x01") cc_list_file = read_file("${MAILCC}");

let string("\x01") bcc_list_file = read_file("${MAILBCC}");

let string("\x01")[] from_tmp_vect = string_split(from,";");

let string("\x01")[] reply_tmp_vect = string_split(reply_to,";");

let string("\x01")[] to_tmp_vect = string_split(to_list_file,";");

let string("\x01")[] cc_tmp_vect = string_split(cc_list_file,";");

let string("\x01")[] bcc_tmp_vect = string_split(bcc_list_file,";");

let string("\x01")[] tmp_split;

let int i = 0;

let record

string("\x01") readable_name = NULL;

string("\x01") e_mail_address = NULL;

end[length_of(from_tmp_vect)] user_from_mail = allocate();

let record

string("\x01") readable_name = NULL;

string("\x01") e_mail_address = NULL;

end[length_of(reply_tmp_vect)] user_reply_mail = allocate();

let record

string("\x01") readable_name = NULL;

string("\x01") e_mail_address = NULL;

end[length_of(to_tmp_vect)] user_to_mail = allocate();

let record

string("\x01") readable_name = "";

string("\x01") e_mail_address = "";

end[length_of(cc_tmp_vect)] user_cc_mail = allocate();

let record

string("\x01") readable_name = NULL;

string("\x01") e_mail_address = NULL;

end[length_of(bcc_tmp_vect)] user_bcc_mail = allocate();

i = 0;

for (i, i <= length_of(from_tmp_vect)-1)

begin

tmp_split = string_split(from_tmp_vect[i],",");

user_from_mail[i].readable_name = tmp_split[0];

user_from_mail[i].e_mail_address = tmp_split[1];

end;

i = 0;

for (i, i <= length_of(reply_tmp_vect)-1)

begin

tmp_split = string_split(reply_tmp_vect[i],",");

user_reply_mail[i].readable_name = tmp_split[0];

user_reply_mail[i].e_mail_address = tmp_split[1];

end;

i = 0;

for (i, i <= length_of(to_tmp_vect)-1)

begin

tmp_split = string_split(to_tmp_vect[i],",");

user_to_mail[i].readable_name = tmp_split[0];

user_to_mail[i].e_mail_address = tmp_split[1];

end;

i = 0;

for (i, i <= length_of(cc_tmp_vect)-1)

begin

tmp_split = string_split(cc_tmp_vect[i],",");

user_cc_mail[i].readable_name = tmp_split[0];

user_cc_mail[i].e_mail_address = tmp_split[1];

end;

i = 0;

for (i, i <= length_of(bcc_tmp_vect)-1)

begin

tmp_split = string_split(cc_tmp_vect[i],",");

user_bcc_mail[i].readable_name = tmp_split[0];

user_bcc_mail[i].e_mail_address = tmp_split[1];

end;

message::[record

envelope [record

return_path user_from_mail

created_date local_now()

from user_from_mail

sender NULL

reply_to user_reply_mail

subject in.subject

to user_to_mail

cc user_cc_mail

bcc user_bcc_mail

in_reply_to ""

message_id ""]

content [record

header [record

mime_type "text"

mime_subtype "plain"

parameters [vector]

encoding "q"

id ""

description U""

disposition [record

disp_type ""

parameters [vector]]

language [vector]

location ""]

text in.text

parts NULL]]

;

end;

type _message_t=record

envelope_t envelope;

content_t content=NULL;

end; /*Metadata for records containing message information*/

type _server_log_t=string('\0'); /*Metadata for records containing server_log information*/

out :: create_output(in, server_log) =

begin

end;

=======================

TextFile for list distribution:

Nama Lengkap,email_saya@hostname.com;Indra Gunawan,indra.stone@gmail.com

FTP Component

2012-01-27T10:55:00.000+07:00

Write data to file with maximum record (example : 3000record per file)

2011-07-07T17:33:00.000+07:00

Using reformat component

Input port :

record

decimal("\x01") trx_date = NULL("") /*INTEGER*/;

decimal("\x01") id_card = NULL("") /*INTEGER*/;

utf8 string("\x01", maximum_length=40) name = NULL("") /*VARCHAR(20) CHARACTER SET LATIN*/;

utf8 string("\x01", maximum_length=40) ttype = NULL("") /*VARCHAR(20) CHARACTER SET LATIN*/;

utf8 string("\x01", maximum_length=10) expired = NULL("") /*VARCHAR(5) CHARACTER SET LATIN*/;

string(1) newline = "\n";

end

Output Port:

record

utf8 string(unsigned integer(2)) filename;

record

utf8 string("\n") message = "";

end message;

end;

Transform script:

let unsigned integer(2) file_count = 1;

let unsigned integer(4) rec_count = 1;

out :: reformat(in) =

begin

let unsigned integer(2) temp_rec_count = 0;

let utf8 string(unsigned integer(2)) temp_filename ='FileName_';

let record

utf8 string("\n") message = "";

end temp_message = allocate();

//temp_filename = temp_filename + '_' + string_lpad((string(""))(decimal(""))file_count, 3, '0');

temp_filename = temp_filename + '_' + (string (8)) (datetime("yyyymmdd"))now1() +'_' + (string(6))(datetime("HH24MISS"))datetime_add(now1(),0,0,0,file_count);

temp_message.message = (string_lrtrim(((string(30))in.id_card))+";OKE;"+in.name);

rec_count = rec_count + 1;

if (rec_count > 3000)

begin

file_count = file_count + 1;

rec_count = 1;

end;

out.filename:: temp_filename;

out.message :: temp_message;

end;

write to file using Write Multiple Files component

input port : propagate from reformat

Transform script :

type output_type= record

utf8 string("\n") message;

end; /*Metadata for records written to output files*/

filename :: get_filename(in) =

begin

filename :: string_concat("${FOLDER_NAME}/", in.filename, ".txt");

end;

write :: reformat(in) =

begin

write :: in.message;

end;

Printilan Ab Initio

2011-05-26T13:40:00.001+07:00

dalam insert table ke database teradata mendapatkan error:

**** 13:19:16 Current CLI or RDBMS allows maximum row size: 64K
atau
========= Too many load/unload tasks running: try again later

hal tersebut dikarenakan bug di workload management Teradata

solusi sementara di Ab Inition

isi di parameter komponen input table
SLEEP = 30
TENACITY = 1

========session_error=================

**** 11:28:52 Teradata Database Release: 13.00.00.14
**** 11:28:52 Teradata Database Version: 13.00.00.15
**** 11:28:52 Current CLI or RDBMS allows maximum row size: 64K
**** 11:28:52 Character set for this job: ASCII
**** 11:31:54 CLI Error 301: CLI2: SESSOVER(301): Exceeded max number
of sessions allowed.
**** 11:31:57 Number of FastLoad sessions requested = *
**** 11:31:57 Number of FastLoad sessions connected = 298
**** 11:31:57 FDL4808 LOGON successful

======== session di komponen table usahakan jangan bintang "*" kasih 16 aja atau 1 1 (1 space 1), karena kalau bintang akan meminta banyak session jika server tidak mencukupi maka akan muncul error seperti di atas=========

====penggunaan separator antar karakter===========

record
decimal("\x07",0, maximum_length=15, sign_reserved) subs_no = NULL("") /*DECIMAL(14)*/;
date("YYYY-MM-DD")("\x07") ddate = NULL("") /*DATE*/;
utf8 string("\x07", maximum_length=400) topic = NULL("") /*VARCHAR(200) CHARACTER SET LATIN*/;
utf8 string("\x07", maximum_length=1000) comments = NULL("") /*VARCHAR(500) CHARACTER SET LATIN*/;
utf8 string("\x07", maximum_length=100) region = NULL("") /*VARCHAR(50) CHARACTER SET LATIN*/;
string(1) newline = "\n";
end;

===============pemisah ditandai dengan karakter "\x07" biasanya "\x01", apapun boleh lah yang penting bisa mendapatkan data yg di inginkan====================

====komponen run program====
${SERIAL_DIR} -> is a directory in ab initio
${LANDING_DIR} -> is a directory in ab initio

find ${SERIAL_DIR}${LANDING_DIR} -size +22c -name 'Filename_*.zip' -exec ls {} \;

====getting file_list==========

to run command in abinitio klik F8 and write "echo ${SERIAL_DIR}"

===================================

In this example, string_filter_out compares the input strings "Apt. #2" and ".#,%". The function filters "." and "#" from the first string, returning "Apt 2":
string_filter_out("Apt. #2", ".#,%") "Apt 2"

====================================

Only for Teradata

2011-02-10T17:45:00.002+07:00

code2 printilan buat teradata

--display end of date for current month
select add_months((current_date - extract(day from current_date)+1),1)-1

buat uppercase data
CAST(EMP_NAME AS VARCHAR(200) UPPERCASE) AS NAME

buat row_num, karena di teradata gak ada row num seperti di oracle jadi harus gunain ini
ROW_NUMBER() OVER(ORDER BY EMP_ID ASC) NO_URUT

select 10 rows top
select top 10 * from table_name

SELECT * FROM sys_calendar.calendar b
WHERE CAST(CAST(calendar_date AS FORMAT 'yyyy')AS VARCHAR(4))='2011'

where date
WHERE tanggal BETWEEN (CAST(CAST(CAST((CAST( '20110424' AS DATE FORMAT 'yyyymmdd')-INTERVAL '52' DAY)AS FORMAT 'yyyymmdd')AS VARCHAR(8))AS BIGINT))
AND (CAST(CAST(CAST((CAST( '20110424' AS DATE FORMAT 'yyyymmdd')-INTERVAL '7' DAY)AS FORMAT 'yyyymmdd')AS VARCHAR(8))AS BIGINT))

jika ada yang double datanya maka diambil record yg pertama.
gunakan :
QUALIFY (ROW_NUMBER () OVER (PARTITION BY trx_a.subs_no ORDER BY trx_a.d_day DESC))=1

SELECT DISTINCT trx_a.d_day,trx_a.subs_no,trx_b.pps_amt,
case
    when prev_reg_date is null then 'N'
    else 'Y'
end IS_EXIST
FROM
(SELECT DISTINCT subs_no,subs_key,d_day
FROM table
) trx_a
LEFT JOIN
(SELECT DISTINCT subs_no,d_day
FROM table
) trx_b
ON trx_a.subs_no=trx_b.subs_no
ORDER BY trx_a.subs_key ASC,trx_b.pps_amt DESC
QUALIFY (ROW_NUMBER () OVER (PARTITION BY trx_a.subs_no ORDER BY trx_a.d_day DESC))=1

--------------

OALESCE(ColumnName,0) = return col1 if not NULL, 0 if NULL

--------------

menghitung menit dari 2 date
SELECT SUBS_NO,MIN_DIFF FROM (
SELECT SUBS_NO,(SessionEndTime - SessionStartTime DAY(4) TO MINUTE) AS tsdiff,
   (EXTRACT(DAY FROM tsdiff) * 1440)
   + (EXTRACT(HOUR FROM tsdiff) * 60)
   + EXTRACT(MINUTE FROM tsdiff) AS min_diff
   FROM session_table
   ) SES

------------
running sql from unix

-------------
/usr/bin/bteq << EOF

.LOGON 10.10.10.10/user,pass
.Set Echoreq off
.Set Titledashes off
.Set Separator '|'
.Set Format off
.Export REPORT File = 'home/indra/date.txt'
select cast(calendar_date as integer)+19000000 from sys_calendar.calendar
where calendar_date>=${curr_date}-19000000 and calendar_date-2 <= ${curr_date}-19000000
order by calendar_date;
.Export Reset
.Logoff
.Quit

EOF

-------------

another bteq use

-----------

bteq << !!EOF!!

.LOGON 10.24.125.49/xl_optprod,prod
.RUN FILE=runTDsql.sql

.LOGOFF
.exit
!!EOF!!

-----another bteq again-----------

/usr/bin/bteq << EOF
.SET SESSIONS 16
.LOGON 10.24.125.48/xl_optprod,prod

delete;
insert;
select;

.Logoff
.Quit

EOF

Ajax Open page without loading (whatever lah for the subject just see the script)

2011-02-10T16:52:00.001+07:00

note : please dont use "\" in script cause error while write the blog.

Ajax Open page without loading (whatever lah for the subject just see the script)

this code is for get data from server without refreshing or move to other page.

this script is basic for every ajax web application.

---Start script---

<\script type='text/javascript'>
function createRequestObject() {
var ro;
var browser = navigator.appName;
if(browser == "Microsoft Internet Explorer"){
ro = new ActiveXObject("Microsoft.XMLHTTP");
}else{
ro = new XMLHttpRequest();
}
return ro;
}

var xmlhttp = createRequestObject();

function setData(frm,combobox)
{
alert(combobox.value);
return false;
var kode = combobox.value;
var form = frm;
if (!kode) return;
xmlhttp.open('get', form+'.jsp?critID='+kode, true);
xmlhttp.onreadystatechange = function() {
if ((xmlhttp.readyState == 4) && (xmlhttp.status == 200))
document.getElementById("dataMetric").innerHTML = xmlhttp.responseText;
return false;
}
xmlhttp.send(null);
}
<\/script>
---EndScript---

how to use?

<\div id="dataMetric"><\/div>
<\script language="javascript1.2">setData("frm","1");<\/script>

ROW_NUM for paging table

2011-02-10T16:43:00.000+07:00

select * from (

select id,name,ROW_NUMBER() OVER(ORDER BY id) no_urut

from table_master) a

where a.no_urut between 3 and 5

JavaScript Trim Function

2011-02-10T16:40:00.000+07:00

left

// function ltrim(str, chars) {

chars = chars || "\\s";

return str.replace(new RegExp("^[" + chars + "]+", "g"), "");

}

// right

function rtrim(str, chars) {

chars = chars || "\\s";

return str.replace(new RegExp("[" + chars + "]+$", "g"), "");

}

// both

function fTrim(str){

return str.replace(/^\s+|\s+$/g,'');

}

Update Table

2010-12-23T11:23:00.000+07:00

Update Table (Source Data from other table)

UPDATE mu

SET rt_id = (

SELECT rt_id

FROM billing_info

WHERE billing_info.ms = mu.ms )

WHERE EXISTS (

SELECT 1

FROM billing_info

WHERE billing_info.ms = mu.ms )

Insert data from text file (using grep) to Database

2010-11-14T16:59:00.001+07:00

echo "Start grep data transaction "`date`;

ORACLE_HOME=/app/oracle/product/10.2.0/cl_1;

export ORACLE_HOME;

PATH=$PATH:$ORACLE_HOME/bin;

export PATH;

# please don't use "/" for /EOF this used because error at write the blog

DATE=`sqlplus -s user/password@DATABASE_SID << /EOF

set pages 1000

set heading off

SELECT TO_CHAR(SYSDATE-1/24, 'DD_MM_YYYY_HH24') sdate FROM DUAL;

quit;

EOF`

#DATE="10_11_2010_18"

# goto home directory

echo $DATE > IG_DATE.txt;

DATE=`cat IG_DATE.txt`;

rm IG_DATE.txt;

vServer="10.10.10.10"

echo "Grep data on $DATE "`date`;

#echo "Grep data "`date`;

echo "grep Success "`date`;

VSuccess=`cat /logs/LOG_TRANSACTIONS_$DATE* | grep -ic "Success"`

echo "INSERT INTO TBL_TRANS_VOUCHER VALUES ('"$DATE"','"$vServer"','Success','"$VSuccess"',SYSDATE);" >> IG_INSERTED.sql

echo "Insert data to database "`date`;

ORACLE_HOME=/app/oracle/product/10.2.0/cl_1;

export ORACLE_HOME;

PATH=$PATH:$ORACLE_HOME/bin;

export PATH;

# please don't use "/" for /EOF this used because error at write the blog

get_date=`sqlplus -s user/password@DATABASE_SID << /EOF

set pages 1000

set heading off

@IG_INSERTED.sql;

quit;

EOF`

echo "Process finished "`date`;

rm IG_INSERTED.sql

Split data in Oracle

2010-08-08T18:59:00.000+07:00

#first create type data.

CREATE OR REPLACE

type SPLIT_TBL as table of varchar2(32767);

# and Then create function

CREATE OR REPLACE function split

(

p_list varchar2,

p_del varchar2 := ',' -- default delimeter is coma

) return split_tbl pipelined

l_idx pls_integer;

l_list varchar2(32767) := p_list;

l_value varchar2(32767);

begin

loop

l_idx := instr(l_list,p_del);

if l_idx > 0 then

pipe row(substr(l_list,1,l_idx-1));

l_list := substr(l_list,l_idx+length(p_del));

else

pipe row(l_list);

exit;

end if;

end loop;

return;

end split;

#how to use???

select COLUMN_VALUE from table(split('DATA-INDRA-VAS','-'));

#Result

COLUMN_VALUE

-------------------

DATA

INDRA

VAS

script gado-gado biar gak lupa

2010-07-08T18:43:00.001+07:00

script campuran, cari sendiri yah maksud dari scriptnya ^_^

find /export/home -size +10000c -exec ls -ltr {} \;
find /home/ -size 22c -name '*.zip' -exec rm -f {} \;

cat a.txt | awk '{FS=","}{sum +=$6} END {print sum}'

http://www.grymoire.com/Unix/Sed.html

select /**/ column from table

select /*+ index (scott.emp ix_emp) */ scott.name,scott.address from scott.emp

select /*+ index (emp_alias ix_emp) */ ... from scott.emp emp_alias

for i in `ps -eaf | grep box | grep -iv "grep box" | awk '{FS=" "}{print $2}'`; do kill -9 $i; done;

select CAST(2.56 AS INTEGER) dec_to_intgr from dual;

# Kill Process

if [`ps -eaf | grep "ProcessIndra" | grep -v grep|wc -l` -eq 0 ]; then

echo "ProcessIndra Interface Agent is not Running." `date`

else

echo "Stopping the ProcessIndra Interface Agent at " `date`

ps -eaf | grep ProcessIndra | grep -v grep | awk {'print $2'} | xargs kill -9

#===========

------for pearl get yesterday date

perl -e 'use POSIX qw(strftime); $range=3600*24*$ARGV[0]; my $date = strftime("%Y%m%d",localtime(time-$range)); printf "$date\n" ' 1

Package Send Mail

2010-01-21T14:59:00.001+07:00

--- semua ini tinggal copas doang koq, tapi harus tersedia koneksi ke mail server
--- semoga bermanfaat

CREATE OR REPLACE PACKAGE BODY MAIL_ATTACHMENT AS
/******************************************************************************
NAME: MAIL_ATTACHMENT
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 1/21/2010 1. Created this package.
******************************************************************************/

g_crlf CHAR (2) DEFAULT CHR (13) || CHR (10);
g_mail_conn UTL_SMTP.connection;
g_mailhost VARCHAR2 (255) := '10.10.10.10';

FUNCTION address_email (p_string IN VARCHAR2, p_recipients IN ARRAY)
RETURN VARCHAR2
IS
l_recipients LONG;
BEGIN
FOR i IN 1 .. p_recipients.COUNT
LOOP
UTL_SMTP.rcpt (g_mail_conn, p_recipients (i));

IF (l_recipients IS NULL)
THEN
l_recipients := p_string || p_recipients (i);
ELSE
l_recipients := l_recipients || ', ' || p_recipients (i);
END IF;
END LOOP;

RETURN l_recipients;
END;

PROCEDURE SEND_MAIL_ATTACHMENT (
p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN ARRAY DEFAULT ARRAY (),
p_cc IN ARRAY DEFAULT ARRAY (),
p_bcc IN ARRAY DEFAULT ARRAY (),
p_subject IN VARCHAR2,
p_filename IN VARCHAR2,
p_body IN LONG,
p_content IN LONG
)
IS
l_to_list LONG;
l_cc_list LONG;
l_bcc_list LONG;
l_date VARCHAR2 (255) DEFAULT TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss');

BEGIN
g_mail_conn := UTL_SMTP.open_connection (g_mailhost, 25);
UTL_SMTP.helo (g_mail_conn, g_mailhost);
UTL_SMTP.mail (g_mail_conn, p_sender_email);
l_to_list := address_email ('To: ', p_to);
l_cc_list := address_email ('Cc: ', p_cc);
l_bcc_list := address_email ('Bcc: ', p_bcc);
UTL_SMTP.data (g_mail_conn,
'Date: ' || l_date || g_crlf ||
'From: ' || NVL (p_from, p_sender_email) || g_crlf ||
'Subject: '|| NVL (p_subject, '(no subject)') || g_crlf ||
'To: ' || l_to_list || g_crlf ||
'Cc: ' || l_cc_list || g_crlf ||
'Bcc: ' || l_bcc_list || g_crlf ||

'MIME-Version: 1.0' || g_crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;' || g_crlf ||
' boundary="-----SECBOUND"' || g_crlf ||g_crlf ||

'-------SECBOUND' || g_crlf ||
'Content-Type: text/plain;' || g_crlf ||
'Content-Transfer_Encoding: 7bit' || g_crlf || g_crlf ||
p_body || g_crlf || g_crlf ||

'-------SECBOUND' || g_crlf ||
'Content-Type: text/plain;' || g_crlf ||
' name="excel.txt"' || g_crlf ||
'Content-Transfer_Encoding: 8bit' || g_crlf ||
'Content-Disposition: attachment;'|| g_crlf ||
' filename="'||p_filename||'.txt"' || g_crlf || g_crlf ||
p_content || g_crlf || g_crlf || -- Content of attachment

'-------SECBOUND--' -- End MIME mail
);
UTL_SMTP.quit (g_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;

PROCEDURE SEND_MAIL_ (
p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN ARRAY DEFAULT ARRAY (),
p_cc IN ARRAY DEFAULT ARRAY (),
p_bcc IN ARRAY DEFAULT ARRAY (),
p_subject IN VARCHAR2,
p_body IN LONG
)
IS
l_to_list LONG;
l_cc_list LONG;
l_bcc_list LONG;
l_date VARCHAR2 (255) DEFAULT TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss');

PROCEDURE writedata (p_text IN VARCHAR2)
AS
BEGIN
IF (p_text IS NOT NULL)
THEN
UTL_SMTP.write_data (g_mail_conn, p_text || g_crlf);
END IF;
END;
BEGIN
g_mail_conn := UTL_SMTP.open_connection (g_mailhost, 25);
UTL_SMTP.helo (g_mail_conn, g_mailhost);
UTL_SMTP.mail (g_mail_conn, p_sender_email);
l_to_list := address_email ('To: ', p_to);
l_cc_list := address_email ('Cc: ', p_cc);
l_bcc_list := address_email ('Bcc: ', p_bcc);
UTL_SMTP.open_data (g_mail_conn);
writedata ('Date: ' || l_date);
writedata ('From: ' || NVL (p_from, p_sender_email));
writedata ('Subject: ' || NVL (p_subject, '(no subject)'));
writedata (l_to_list);
writedata (l_cc_list);
UTL_SMTP.write_data (g_mail_conn, '' || g_crlf);
UTL_SMTP.write_data (g_mail_conn, p_body);
UTL_SMTP.close_data (g_mail_conn);
UTL_SMTP.quit (g_mail_conn);
END;

PROCEDURE SEND_MAIL_HTML (
p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN ARRAY DEFAULT ARRAY (),
p_cc IN ARRAY DEFAULT ARRAY (),
p_bcc IN ARRAY DEFAULT ARRAY (),
p_subject IN VARCHAR2,
p_body IN LONG DEFAULT NULL
)
IS
l_boundary VARCHAR2 (255) DEFAULT 'a1b2c3d4e3f2g1';
l_body_html CLOB := EMPTY_CLOB; --This LOB will be the email message
l_offset NUMBER;
l_ammount NUMBER;
l_temp VARCHAR2 (32767) DEFAULT NULL;
l_to_list LONG;
l_cc_list LONG;
l_bcc_list LONG;
l_date VARCHAR2 (255) DEFAULT TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss');
BEGIN
g_mail_conn := UTL_SMTP.open_connection (g_mailhost, 25);
UTL_SMTP.helo (g_mail_conn, g_mailhost);
UTL_SMTP.mail (g_mail_conn, p_sender_email);
l_to_list := address_email ('To: ', p_to);
l_cc_list := address_email ('Cc: ', p_cc);
l_bcc_list := address_email ('Bcc: ', p_bcc);
l_temp := l_temp || 'MIME-Version: 1.0' || g_crlf;
l_temp := l_temp || 'Date:' ||l_date|| g_crlf;
l_temp := l_temp || 'From: ' || NVL (p_from, p_sender_email) || g_crlf;
l_temp := l_temp || 'Subject: ' || p_subject || g_crlf;
l_temp := l_temp || 'To: ' || l_to_list || g_crlf;
l_temp := l_temp || 'Cc: ' || l_cc_list || g_crlf;
l_temp := l_temp || 'Bcc: ' || l_bcc_list || g_crlf;
l_temp :=
l_temp
|| 'Content-Type: multipart/alternative; boundary='
|| CHR (34)
|| l_boundary
|| CHR (34)
|| g_crlf;

-- Write the headers
DBMS_LOB.createtemporary (l_body_html, FALSE, 10);
DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), 1, l_temp);

-- Write the text boundary
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
l_temp := '--' || l_boundary || g_crlf;
l_temp :=
l_temp || 'content-type: text/plain; charset=us-ascii' || g_crlf
|| g_crlf;
DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);

-- Write the plain text portion of the email
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.WRITE (l_body_html, LENGTH (p_body), l_offset, p_body);

-- Write the HTML boundary
l_temp := g_crlf || g_crlf || '--' || l_boundary || g_crlf;
l_temp := l_temp || 'content-type: text/html;' || g_crlf || g_crlf;
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);

-- Write the HTML portion of the message
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.WRITE (l_body_html, LENGTH (p_body), l_offset, p_body);

-- Write the final html boundary
l_temp := g_crlf || '--' || l_boundary || '--' || CHR (13);
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);

-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
UTL_SMTP.open_data (g_mail_conn);

WHILE l_offset < DBMS_LOB.getlength (l_body_html)
LOOP
UTL_SMTP.write_data (g_mail_conn,
DBMS_LOB.SUBSTR (l_body_html, l_ammount, l_offset)
);
l_offset := l_offset + l_ammount;
l_ammount := LEAST (1900, DBMS_LOB.getlength (l_body_html) - l_ammount);
END LOOP;

UTL_SMTP.close_data (g_mail_conn);
UTL_SMTP.quit (g_mail_conn);
DBMS_LOB.freetemporary (l_body_html);
END;
END MAIL_ATTACHMENT;
/

--- Cara Menggunakannya
--- Sebagai contoh ajah yang menggunakan attachment, disini juga disertai tanpa attachment, dan body HTML
--- untuk multi file attach belum bisa, bis lum dapet contekannyah

exec MAIL_ATTACHMENT.SEND_MAIL_ATTACHMENT('Pengirim', --SENDER
'pengirim@mail.com', --FROM
MAIL_ATTACHMENT.ARRAY ('aku@mail.com', 'saya@mail.com'), --TO
MAIL_ATTACHMENT.ARRAY ('dia@mail.com'), --CC
MAIL_ATTACHMENT.ARRAY (NULL), --BCC
'Tes From Vasm', --SUBJECT
'Filename' , --Nama File Attachment
'Body Email', --Body Email
'Isi dari file yang akan di attach' --Isi dari file yang akan di attach
);

--- selamat mencoba ---

Sample

2009-09-25T14:20:00.000+07:00

# Baca list data dari file, untuk digunakan sebagai master data dalam pencarian detail data

# Set Tanggal Kemarin
date
echo "Mulai Pemecahan file..."
VCURDATE=`TZ=y24 date +%c %Y`
tgl=`echo $VCURDATE | cut -c9-10`
bln=`echo $VCURDATE | cut -c5-7`
thn=`echo $VCURDATE | cut -c21-24`
if [ $tgl -lt 10 ]
then
tgl="0"$tgl
fi

tanggal=$tgl"-"$bln"-"$thn
#tanggal="11-Nov-2007"

ls -ltr /data/backup/daily_trans_$tanggal.txt

cd /export/home/

cat $1 | while read x; do
echo "ambil code dealer dan branch dari tld id "$x"..."
branch=`cat /data/backup/daily_trans_$tanggal.txt | grep -i "|"$x"|" | head -1 | awk '{FS="|"}{print $17}'`
dealer=`cat /data/backup/daily_trans_$tanggal.txt | grep -i "|"$x"|" | head -1 | awk '{FS="|"}{print $18}'`
code=`echo $dealer | cut -c1-3`
if [ "$code" == "" ]
then
echo "tld id "$x" tidak ditemukan..."
else
nmfile="/data/backup/daily_trans_"$code"_"$branch"_"$tanggal".txt"
echo "generate file dealer "$code" untuk branch "$branch"..."
cat /data/backup/daily_trans_$tanggal.txt | grep -i "|"$x"|" | awk '{FS="|"}{print $1"|"$2"|"$3}' > $nmfile
fi
done
echo "Selesai..."
date

# Cara menggunakannya
# sh script.sh list.txt

#list.txt
0001
0002
0003
dst...

Query with specific partition table

2009-01-30T14:24:00.000+07:00

this query only for table with partition

=========

SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF

-- Dates
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';

COLUMN S NEW_VALUE start_date
SELECT trunc(sysdate-1) S FROM dual;

---- + Debug
DEFINE ptab_td = 'p_tab_trans_data_&start_date'
DEFINE ptab_tr = 'p_tab_trans_&start_date'
---- - Debug

-- Spool File
DEFINE spool_file = /home/sql/trans_&start_date..txt

SET PAGESIZE 0
SET LINESIZE 100
SET SPACE 1

CLEAR COLUMNS

SPOOL &spool_file

select
to_char(tr.ID)||'|'||to_char(tr.CREATED,'YYYYMMDDHH24MISS')||'|'||'62'||substr(tr.INITIATOR,2)||'|'||
(select (case when substr(td.value,1,1) = 0 then concat(62,substr(td.value,2)) else 62 ||td.value end) from vtudadm.trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'target_msisdn')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'amount')||'|'||
to_char(vtudadm.get_tld(tr.INITIATOR))||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'origcmd')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'lacid')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'cellid') LINEREC
from trans partition (&ptab_tr) tr
where tr.created between to_timestamp('&start_date'||'000000.000','YYYYMMDDHH24MISS.FF3')
and to_timestamp('&start_date'||'235959.999','YYYYMMDDHH24MISS.FF3')
and tr.type_ = 'LOAD'
and tr.state = 2
and tr.result = '0';

SPOOL OFF

CLEAR COLUMNS
CLEAR BREAKS
SET TERMOUT ON
SET VERIFY ON
SET FEEDBACK ON

Read one line from a file

2008-07-19T07:32:00.000+07:00

# i got this script from other website
# hope can help anyone who read this
# this script is for get one line from the file
# save it as read.sh

PN=`basename "$0"` # Program name
VER='1.2'

usage () {
echo >&2 "$PN - get one line of text, $VER (hs '96)
usage: $PN [line] [file ...]

If no line is given, the default line is 1."
exit 1
}

msg () {
for i
do echo "$PN: $i" >&2
done
}

fatal () { msg "$@"; exit 1; }

# Check arguments before setting them
set -- `getopt h "$@"`
while [ $# -gt 0 ]
do
case "$1" in
--) shift; break;;
-h) usage;;
-*) usage;;
*) break;; # First file name
esac
shift
done

if [ $# -gt 0 ] && expr "$1" + 0 >/dev/null 2>&1
then # Seems to be a line number
Line=$1
shift
fi

exec sed -n "${Line:-1}{p;q;}" "$@"
# end of script

# for example you can create a file
# save it as selamat.txt
Selamat pagi
Selamat siang
Selamat malam

# how to use this script
bash-2.05$sh read.sh 1 selamat.txt
result : Selamat pagi
bash-2.05$sh read.sh 3 selamat.txt
result : Selamat malam

Store Procedure

2008-06-04T15:38:00.000+07:00

CREATE OR REPLACE PROCEDURE TAMPUNGAN.GET_ALL_TRANS (P_TRANS_DT VARCHAR2) IS
CURSOR LOADRec (P_TRANS_DATE VARCHAR2) IS
select tr.ID, tr.DESCRIPTION, tr.CREATED
from trans tr
where tr.CREATED between to_timestamp(P_TRANS_DATE||'000000.000','YYYYMMDDHH24MISS.FF3')
and to_timestamp(P_TRANS_DATE||'235959.999','YYYYMMDDHH24MISS.FF3')
and tr.DESCRIPTION<>'Commission';
-- and rownum <= 1000;

CURSOR GETTransData (P_TRANS_ID NUMBER) IS
select td.td_key, trim(td.value) value
from trans_data td
where td.transid=P_TRANS_ID
and td.td_key in ('target','amount');

CURSOR GETTransExt (P_TRANS_ID NUMBER) IS
select txt.type_, txt.result
from trans_ext_trans txt
where txt.transid=P_TRANS_ID;

v_trans_id number;
v_description varchar2(40);
v_created date;
v_result varchar2(20);
v_target varchar2(255);
v_amount varchar2(255);

v_loop_count integer;
v_trans_dt varchar2(10);

BEGIN
v_loop_count := 0;

-- for default value use GET_ALL_TRANS('YYYYMMDD');
-- is mean get data of yesterday data
if P_TRANS_DT = 'YYYYMMDD' then
v_trans_dt := to_char(sysdate-1,'YYYYMMDD');
else
v_trans_dt := P_TRANS_DT;
end if;

for CurrRLDRec in LOADRec (v_trans_dt)
loop
begin
v_trans_id := CurrRLDRec.id;
v_description := CurrRLDRec.description;
v_created := CurrRLDRec.created;

v_result := null;
v_target := null;
v_amount := null;

for CurrTERec in GETTransExt (v_trans_id)
loop
v_result := CurrTERec.result;
end loop;

for CurrTDRec in GETTransData (v_trans_id)
loop
if CurrTDRec.td_key = 'target' then
if substr(CurrTDRec.value,1,1) <> '0' then
v_target := '0'||CurrTDRec.value;
else
v_target := CurrTDRec.value;
end if;
else
if CurrTDRec.td_key = 'amount' then
v_amount := CurrTDRec.value;
end if;
end if;
end loop;

-- Insert data to table
insert into tamp_daily
(TRANSID, DESCRIPTION, CREATED, RESULT, TARGET, AMOUNT)
values
(v_trans_id, v_description, v_created, v_result, v_target, v_amount);

v_loop_count := v_loop_count + 1;

-- commit if v_loop_count is more than those value
if v_loop_count >= 10000 then
commit;
v_loop_count := 0;
end if;

exception
when NO_DATA_FOUND then null;
when others then rollback;
end;
end loop;

commit;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END GET_ALL_TRANS;

tampung ke file dari hasil select

2008-02-23T09:30:00.000+07:00

cara ini gw lakuin di unix.

biar lebih mudah gw pisah-pisah filenya sesuai dengan fungsinya.

pertama buat file execute buat jalanin file sql, isinya sbb :
#/bin/sh

ORACLE_HOME=/app/oracle/product/10.2.0/Db_1
export ORACLE_HOME
#jika memang belum diset path oracle homenya
ORACLE_SID=ORACLE_SID
export ORACLE_SID
#sama juga seperti oracle_home
#jika pathnya sudah diset di os-nya gak perlu lagi jalanin perintah diatas

date

cd /export/home/
#supaya posisi direktori berada di file yg mau gw jalanin

$ORACLE_HOME/bin/sqlplus user/password @allSelect.sql

date
#biar gw tau mulai jam berapa dan kelar jam berapa ini query.

#End of File---

kedua buat file sql yang berfungsi untuk menampung file-file sql lain yang akan di jalankan
--Start file

@@dailySql1.sql
@@dailySql2.sql

exit
--End of file

nah yg ketiga ini adalah query yg diperlukan, tapi dikasih contoh yg dailySql1.sql aja :
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF

-- Dates, changes format date
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';

--COLUMN S NEW_VALUE start_date
SELECT trunc(sysdate-1) S FROM dual;

---- + Debug, digunakan kalau melakukan query dengan input date
--DEFINE start_date = &1
---- - Debug, Read from command line

-- Spool File
DEFINE spool_file = dailySql_&start_date..txt

SET PAGESIZE 0
SET LINESIZE 220
-- Linesize adalah jumlah maksimal karakter yg akan ditampung
SET SPACE 1

BREAK ON REPORT

SPOOL &spool_file

select id||"|"||nama||"|"||alamat||"|"||no_telp from nama_table
where tanggal between trunc(sysdate-1) and trunc(sysdate);

SPOOL OFF

CLEAR BREAKS
SET TERMOUT ON
SET VERIFY ON
SET FEEDBACK ON

SQL Loader

2008-02-23T09:22:00.000+07:00

cara menjalankan sql loader :
sqlldr userid=user/password control=/export/home/loader.ctl log=/export/home/loader.log bad=/export/home/loader.bad

loader.log dan loader.bad akan terbuat dengan sendirinya setelah menjalankan perintah load

isi dari loader.ctl :
load data infile '/export/home/dataloader.txt' into table nama_table fields terminated by "|" TRAILING NULLCOLS (ID,NAMA,ALAMAT,NO_TLP)

TRAILING NULLCOLS dimaksudkan jika ada salah satu field yg kosong maka diabaikan, dan akan melanjutkan loader kedata selanjutnya.

FTP

2007-12-11T09:58:00.000+07:00

#!/bin/bash

HOST='192.168.31.75'
USER='indra'
PASSWD='indra'
FILE='file_transaksi_*'

# please don't use "\" for \END_SCRIPT this used because error at write the blog
ftp -n $HOST <<\END_SCRIPT
quote USER $USER
quote PASS $PASSWD
# bin mode
bin
# answer yes for any question
prompt
lcd /home/indra/trans
cd /home/data/trans/backup
mget $FILE
quit
END_SCRIPT
exit 0

About Shell

2007-12-11T09:48:00.000+07:00

this letter from other site, but i am forgot exactly where is it?
i hope this letter can help anyone who needed to learn shell script included me.

1. Tests

Tests (for ifs and loops) are done with [ ] or with the test command.
1.1. Checking files
-r file Check if file is readable.
-w file Check if file is writable.
-x file Check if we have execute access to file.
-f file Check if file is an ordinary file (as opposed to a directory, a device special file, etc.)
-s file Check if file has size greater than 0.
-d file Check if file is a directory.
-e file Check if file exists. Is true even if file is a directory.

Example:

if [ -s file ]
then
such and such
fi

1.2. Checking strings

s1 = s2 Check if s1 equals s2. s1 != s2 Check if s1 is not equal to s2. -z s1 Check if s1 has size 0. -n s1 Check if s2 has nonzero size. s1 Check if s1 is not the empty string.

Example:

if [ $myvar = "hello" ]
then
echo "We have a match"
fi

1.3. Checking numbers

Note that a shell variable could contain a string that represents a number. If you want to check the numerical value use one of the following:

n1 -eq n2 Check to see if n1 equals n2.
n1 -ne n2 Check to see if n1 is not equal to n2.
n1 -lt n2 Check to see if n1 <>
n1 -le n2 Check to see if n1 <= n2.
n1 -gt n2 Check to see if n1 > n2.
n1 -ge n2 Check to see if n1 >= n2.

Example:

if [ $# -gt 1 ]
then
echo "ERROR: should have 0 or 1 command-line parameters"
fi

1.4. Boolean operators

! not
-a and
-o or

Example:

if [ $num -lt 10 -o $num -gt 100 ]
then
echo "Number $num is out of range"
elif [ ! -w $filename ]
then
echo "Cannot write to $filename"
fi

Note that ifs can be nested. For example:

if [ $myvar = "y" ]
then
echo "Enter count of number of items"
read num
if [ $num -le 0 ]
then
echo "Invalid count of $num was given"
else
... do whatever ...
fi
fi

The above example also illustrates the use of read to read a string from the keyboard and place it into a shell variable. Also note that most UNIX commands return a true (nonzero) or false (0) in the shell variable status to indicate whether they succeeded or not. This return value can be checked. At the command line echo $status. In a shell script use something like this:

if grep -q shell bshellref
then
echo "true"
else
echo "false"
fi

Note that -q is the quiet version of grep. It just checks whether it is true that the string shell occurs in the file bshellref. It does not print the matching lines like grep would otherwise do.
2. I/O Redirection

pgm > file Output of pgm is redirected to file.
pgm <>
pgm >> file Output of pgm is appended to file.
pgm1 | pgm2 Output of pgm1 is piped into pgm2 as the input to pgm2.
n > file Output from stream with descriptor n redirected to file.
n >> file Output from stream with descriptor n appended to file.
n >& m Merge output from stream n with stream m.
n <& m Merge input from stream n with stream m.
<<>

Note that file descriptor 0 is normally standard input, 1 is standard output, and 2 is standard error output.
3. Shell Built-in Variables

$0 Name of this shell script itself.
$1 Value of first command line parameter (similarly $2, $3, etc)
$# In a shell script, the number of command line parameters.
$* All of the command line parameters.
$- Options given to the shell.
$? Return the exit status of the last command.
$$ Process id of script (really id of the shell running the script)

4. Pattern Matching

* Matches 0 or more characters.
? Matches 1 character.
[AaBbCc] Example: matches any 1 char from the list.
[^RGB] Example: matches any 1 char not in the list.
[a-g] Example: matches any 1 char from this range.

5. Quoting

\c Take character c literally.
`cmd` Run cmd and replace it in the line of code with its output.
"whatever" Take whatever literally, after first interpreting $, `...`, \
'whatever' Take whatever absolutely literally.

Example:
match=`ls *.bak` #Puts names of .bak files into shell variable match.
echo \* #Echos * to screen, not all filename as in: echo *
echo '$1$2hello' #Writes literally $1$2hello on screen.
echo "$1$2hello" #Writes value of parameters 1 and 2 and string hello.

6. Grouping

Parentheses may be used for grouping, but must be preceded by backslashes since parentheses normally have a different meaning to the shell (namely to run a command or commands in a subshell). For example, you might use:

if test $ -r $file1 -a -r $file2 $ -o $ -r $1 -a -r $2 $
then
do whatever
fi

7. Case statement

Here is an example that looks for a match with one of the characters a, b, c. If $1 fails to match these, it always matches the * case. A case statement can also use more advanced pattern matching.

case "$1" in
a) cmd1 ;;
b) cmd2 ;;
c) cmd3 ;;
*) cmd4 ;;
esac

8. Shell Arithmetic

In the original Bourne shell arithmetic is done using the expr command as in: result=`expr $1 + 2` result2=`expr $2 + $1 / 2` result=`expr $2 \* 5` (note the \ on the * symbol)

With bash, an expression is normally enclosed using [ ] and can use the following operators, in order of precedence: * / % (times, divide, remainder)

1. - (add, subtract) < > <= >= (the obvious comparison operators) == != (equal to, not equal to) && (logical and)

(logical or)

= (assignment) Arithmetic is done using long integers.

Example:
result=$[$1 + 3]

In this example we take the value of the first parameter, add 3, and place the sum into result.
9. Order of Interpretation

The bash shell carries out its various types of interpretation for each line in the following order:

brace expansion (see a reference book)
~ expansion (for login ids)
parameters (such as $1)
variables (such as $var)
command substitution (Example: match=`grep DNS *` )
arithmetic (from left to right)
word splitting
pathname expansion (using *, ?, and [abc] )

10. Looping

# to see by row in the file
filename="/export/home/indra/listfile.lst"
cat $filename | while read x; do
echo $x
done

11. Other Shell Features

$var Value of shell variable var.
${var}abc Example: value of shell variable var with string abc appended.
# At start of line, indicates a comment.
var=value Assign the string value to shell variable var.
cmd1 && cmd2 Run cmd1, then if cmd1 successful run cmd2, otherwise skip.
cmd1 || cmd2 Run cmd1, then if cmd1 not successful run cmd2, otherwise skip.
cmd1; cmd2 Do cmd1 and then cmd2.
cmd1 & cmd2 Do cmd1, start cmd2 without waiting for cmd1 to finish.
(cmds) Run cmds (commands) in a subshell.

12. sed
bash-2.05$ more BandarLampung.txt
510014030735812|89620130000281606063|85840839128|2166|3107
510014030735813|89620130000281606071|85840839129|2166|3107

bash-2.05$ cat BandarLampung.txt | sed 's/|3107/|3000/1' | head
510014030735812|89620130000281606063|85840839128|2166|3000
510014030735813|89620130000281606071|85840839129|2166|3000

13. for loop file
#!/bin/sh

for i in `ls -l /app/prdtrt12/batch/SIMBOX/archive/*.out | awk '{FS=" "}{if ($5==0) print $9}'`
do
echo "deleteing file $i ..."
rm $i
done

How I can get Yesterday Date?

2007-12-11T09:43:00.000+07:00

#!/bin/sh
# File-name: yesterday.sh
#-----------------------------------------------
# Returns date 1 day ago from the specified date
# Current date is taken if no date is specified
#-----------------------------------------------
# Input: Default:
# $1 - dd Current day
# $2 - mm Current month
# $3 - yyyy Current year
#-----------------------------------------------

#This is how a function is defined in a
#UNIX shell scripts

get_one_day_before_specified_date()
{
#get the command line input(date month & year)
day=$1
month=$2
year=$3

# if it is the first day of the month
if [ $day -eq 01 ]
then
# if it is the first month of the year
if [ $month -eq 01 ]
then
# make the month as 12
month=12

# deduct the year by one
year=`expr $year - 1`
else
# deduct the month by one
month=`expr $month - 1`
fi

# use cal command, discard blank lines,
# take last field of last line,
# first awk command is used to get the
# last useful line of the calendar cmd,
# second awk command is used to get the
# last field of this last useful line,
# NF is no. of fields,
# $NF is value of last field

day=`cal $month $year | awk 'NF != 0{ last = $0 }; END{ print last }' | awk '{ print $NF }'`
else
# deduct the day by one
day=`expr $day - 1`

fi

lengthd=`echo "$day" | wc -c | cut -c1-8`
lengthd=`expr $lengthd - 1`
lengthm=`echo "$month" | wc -c | cut -c1-8`
lengthm=`expr $lengthm - 1`

if [ $lengthd -eq 1 ]
then
day="0"$day
fi

if [ $lengthm -eq 1 ]
then
month="0"$month
fi

echo $year-$month-$day
}

#!/bin/ksh
if [ $# -ne 3 ]
then
d=`date +%d`
m=`date +%m`
y=`date +%Y`
else
d=$1
m=$2
y=$3
fi

#Cmd line arguments are captured in a shell script
#through $1 $2 $3, ......., $9,${10} (not $10)

# This is how we call unix user-defined functions,
# notice it is not junk123( $1, $2, $3 ) format
get_one_day_before_specified_date $d $m $y
# end of script

or you can try this command in other script
bash-2.05$ TZ=y17 date +%c %Y
result : Fri Jul 18 07:24:35 2008

VCURDATE=`TZ=y17 date +%c %Y`
tgl=`echo $VCURDATE | cut -c9-10`
bln=`echo $VCURDATE | cut -c5-7`
thn=`echo $VCURDATE | cut -c21-24`