Update Table

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

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

cd

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

#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

is

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

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

fi

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

------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

--- 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 ---