বুধবার, ১৪ ফেব্রুয়ারী, ২০১৮

Send Mail From Oracle Database

BEGIN
  send_mail(p_to        => 'mdalamin@lantaburgroup.com',
            p_from      => 'mdalamin@lantaburgroup.com',
            p_message   => 'This is a test message.',
            p_smtp_host => 'mail.lantabur.com');
END;

BEGIN
  send_mail(p_to        => 'mdalamin@lantaburgroup.com',
            p_from      => 'mdalamin@lantaburgroup.com',
            p_subject   => 'Test Message',
            p_message   => 'This is a test message.',
            p_smtp_host => 'mail.lantabur.com');
END;

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);
 
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);
 
  UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;

begin
  utl_mail.send(sender     => 'mdalamin@lantaburgroup.com',
                recipients => 'mdalamin@lantaburgroup.com',
        cc         => 'mdalamin@lantabur.com',  
                subject    => 'Test mail from database',
                message    => 'Welcome to new horizon ... Alhamdulillah');
end;

UTL_MAIL.SEND (
   sender      IN    VARCHAR2 CHARACTER SET ANY_CS,
   recipients  IN    VARCHAR2 CHARACTER SET ANY_CS,
   cc          IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   bcc         IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   subject     IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   message     IN    VARCHAR2 CHARACTER SET ANY_CS,
   mime_type   IN    VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
   priority    IN    PLS_INTEGER DEFAULT NULL);
================================================================

begin
UTL_MAIL.SEND_ATTACH_RAW (sender     => 'mdalamin@lantaburgroup.com',
                recipients => 'mdalamin@lantaburgroup.com',
        cc         => 'mdalamin@lantaburgroup.com',  
                subject    => 'Test mail from database',
                message    => 'Welcome to new horizon ... Alhamdulillah'
   att_inline   =>    BOOLEAN DEFAULT TRUE,
   att_mime_type    =>    VARCHAR2 CHARACTER SET ANY_CS DEFAULT
                          'application/octet',
   att_filename     =>    VARCHAR2 DEFAULT NULL);
end;

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

BEGIN
  UTL_MAIL.send_attach_raw (
    sender       => 'mdalamin@lantaburgroup.com',
    recipients   => 'mdalamin@lantaburgroup.com',
    cc           => 'mdalamin@lantaburgroup.com',
    bcc          => 'mdalamin@lantaburgroup.com',
    subject      => 'UTL_MAIL Test',
    message      => 'If you get this message it worked!',
    attachment   => 'The is the contents of the attachment.',
    att_filename => 'my_attachment.txt'   
  );
END;

========================Successfull=================================
BEGIN
    UTL_MAIL.send_attach_varchar2(sender        => 'mdalamin@lantaburgroup.com',
                                  recipients    => 'mdalamin@lantaburgroup.com',
                                  subject       => 'Test msg',
                                  attachment    => 'Test text 1' || UTL_TCP.crlf || 'Test text 2',
                                  att_inline    => FALSE,
                                  att_filename  => 'test.txt'
                                 );
END;


BEGIN
    UTL_MAIL.send_attach_varchar2(sender        => 'mdalamin@lantaburgroup.com',
                                  recipients    => 'mdalamin@lantaburgroup.com',
                                  subject       => 'Test msg',
                                  attachment    => 'emailmsg',
                                  att_filename  => 'emailmsg.txt'
                                 );
END;

========================successfull second=================================

BEGIN
    UTL_MAIL.send_attach_varchar2(sender        => 'mdalamin@lantaburgroup.com',
                                  recipients    => 'mdalamin@lantaburgroup.com',
                                  subject       => 'Test msg',
                                  attachment    => '<html><body><H3>This is test mail</H3></body></html>',
                                  att_filename  => 'emailmsg.html'
                                 );
END;
/


===========================successfull second==========================
Begin
   send_email.send(
   ToList=>             'mdalamin@lantaburgroup.com',
   Subject=>            'Test email with attachments',
   Body=>               'Here is the body',
   FromEmail=>          'mdalamin@lantaburgroup.com',
   FromHost=>           'mdalamin@lantaburgroup.com',
   SMTPServer=>         'localhost',
   AttachList=>         'test.log',
   Directory=>          'mkdir');
End;
/


DECLARE

    vInHandle       utl_file.file_type;
    l_sender        varchar2(100)   := 'mdalamin@lantaburgroup.com';
    l_recipients    varchar2(100)   := 'mdalamin@lantaburgroup.com';
    l_subject       varchar2(100)   := 'Employee Roster Report';
    l_message       varchar2(100)   := 'Hello';
    l_attachment    raw;
    l_directory     varchar2(100)   := 'mkdir';
    fname           varchar2(100)   := 'test.log';


BEGIN

    /* how put RAW data into l_attachment here? */
    --vInHandle := utl_file.fopen(l_directory, fname, 'R'); -- If i'm not mistaken, this reads the File from the specified directory
    --utl_file.get_raw();                                   -- not sure what parameters i should pass
    --utl_file.fclose(vInHandle);                           -- ?

    UTL_MAIL.SEND_ATTACH_RAW
    (
        sender       => l_sender
    ,  recipients   => l_recipients
    ,  subject      => l_subject
    ,  message      => l_message
    ,  attachment   => l_attachment
    ,  att_filename => 'test.log'
    );

EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
END;

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

BEGIN
  testmail(fromm        => 'mdalamin@lantaburgroup.com',
            too      => 'mdalamin@lantaburgroup.com',
            sub   => 'This is a test message.',
            body => 'mail.lantabur.com',
            port => '25');
END;
/

alter system set smtp_out_server = 'mail.lantabur.com:25' scope=both;

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'smtp-gate-permissions.xml',
host => 'mail.lantabur.com',
lower_port => 25,
upper_port => null);
COMMIT;
END;
/

execute testmail ('mdalamin@lantaburgroup.com','mdalamin@lantaburgroup.com','test Subject','Test Body Text.',25);


BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'smtp-gate-permissions.xml',
description => 'Permissions for smtp gate',
principal => 'SYS',
is_grant => TRUE,
privilege => 'connect'
);
COMMIT;
END;
/


exec dbms_network_acl_admin.drop_acl('/sys/acls/smtp-gate-permissions.xml');


telnet mail.lantabur.com:25




CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'mdalamin@lantaburgroup.com';
mailhost VARCHAR2(30) := 'mail.lantabur.com'; -- local database host

BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);

utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;
/

BEGIN
  send_mail(p_mail_host => 'mail.lantabur.com',
            p_from      => 'mdalamin@lantaburgroup.com',
            p_to        => 'mdalamin@lantaburgroup.com',
            p_subject   => 'Test SEND_MAIL Procedure',
            p_message   => 'If you are reading this it workedkhgkhhkkhfkfgggggg.');
END;
/


begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_mail.xml',
    description => 'Allow mail to be send',
    principal   => 'sys',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;


begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'SYS',
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;



CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;


begin
send_mail('mdalamin@lantaburgroup.com','mdalamin@lantaburgroup.com','Testmail from oracle','mail.lantabur.com',25);
end;
/

মঙ্গলবার, ৩০ জানুয়ারী, ২০১৮

Getting timezone in oracle

SELECT DISTINCT tzname, TZ_OFFSET (tzname)
FROM V$TIMEZONE_NAMES
ORDER BY tzname;


ALTER SESSION SET time_zone = '-04:00';
 
SELECT TO_CHAR (CURRENT_TIMESTAMP, 'dd-mon-yyyy HH:mi:ss PM') c_time
FROM DUAL; 

মঙ্গলবার, ৯ জানুয়ারী, ২০১৮

Previous Data Query in Oracle

Data of Five minutes past:

select * from employees as of timestamp(sysdate-interval '5' minute)

insert into emplyess
select * from employees as of timestamp(sysdate-interval '5' minute)

মঙ্গলবার, ২৮ নভেম্বর, ২০১৭

Java Web start

<jnlp spec="1.0+" codebase="http://desktop-hnjk47a:8889/forms/java">
  <information>
    <title>Webstart Test Oracle Forms 10g</title>
    <vendor>develishdevelopment.wordpress.com</vendor>
    <homepage href="null"/>
    <description>Webstart Test Oracle Forms 10g</description>
  </information>
  <security>
    <all-permissions/>
  </security>
  <update check="timeout" policy="always"/>
  <resources>
    <java version="1.6.0"/>
    <jar href="frmall.jar" download="eager" main="true"/>
  </resources>
  <applet-desc name="Webstart Test Oracle Forms 10g" main-class="oracle.forms.engine.Main" width="990" height="660">
    <param name="background" value="no"/>
    <param name="logo" value="no"/>
    <param name="serverApp" value="default"/>
    <param name="lookAndFeel" value="oracle"/>
    <param name="allow_debug" value="true"/>
    <param name="separateFrame" value="false"/>
    <param name="em_mode" value="1"/>
    <param name="latencyCheck" value="true"/>
    <param name="networkRetries" value="240"/>
    <param name="serverArgs" value="module=E:\LIBAS_ERP\LOGIN_MAIN.fmx useSDI=yes"/>
    <param name="serverURL" value="http://desktop-hnjk47a:8889/forms/lservlet?libas=http://desktop-hnjk47a:8889/forms/frmservlet?config=jpi"/>
    <param name="colorScheme" value="teal"/>
    <param name="splashScreen" value="no"/>
    <param name="dontTruncateTabs" value="true"/>
  </applet-desc>
</jnlp>

বুধবার, ২০ সেপ্টেম্বর, ২০১৭

রবিবার, ১৭ সেপ্টেম্বর, ২০১৭

বৃহস্পতিবার, ১৭ আগস্ট, ২০১৭