বুধবার, ২৭ মার্চ, ২০১৯
রবিবার, ২৯ জুলাই, ২০১৮
Apex Install
Please flow flowing step to install apex 5.2 :
** Apex 5.2 installation you must need db 11g . Other version (lower from 5.2) support 10g or less.
1.Apex software folder keep desired drive (c,d,e,f) where u want to install apex software
I install my apex folder in “c” drive for your advantage & my apex folder name is “apex”.
Open cmd –>
cd C:\apex [drive name:\apex software folder name]
Your screen show as like after read desired folder bellow:
c:\apex [this is valid format for read apex folder]
connect to sqlplus as like –> sqlplus /nolog
Connect sqlplus as sys dba –> conn / as sysdba
create a table-space for your apex user
SQL> CREATE TABLESPACE APEX_DATA datafile
‘C:\apex\APEX_data.DBF’
SIZE 1g;
SQL> @apexins APEX_DATA APEX_DATA TEMP /i/
20/25 min needs for installation.
SQL> @apxldimg.sql [after this command show bellow message for give location image folder]
enter vavue 1: C:\ [if install “C” drive] or D:\apex or D:\apex\images [if install except “C” drive]
Without any error image directory created then run flowing script:
SQL> @apxconf.sql [if install “C” drive] or @\apex\apxconf.sql [if install except “C” drive]
8 . You need to create apex user [admin user show as default] and password
[password must be contain Upper case,Special character ,lower character & number as like “Test!1”
Default port generated “8080” [if you don’t change port then enter for next step .
** Apex 5.2 installation you must need db 11g . Other version (lower from 5.2) support 10g or less.
1.Apex software folder keep desired drive (c,d,e,f) where u want to install apex software
I install my apex folder in “c” drive for your advantage & my apex folder name is “apex”.
Open cmd –>
cd C:\apex [drive name:\apex software folder name]
Your screen show as like after read desired folder bellow:
c:\apex [this is valid format for read apex folder]
connect to sqlplus as like –> sqlplus /nolog
Connect sqlplus as sys dba –> conn / as sysdba
create a table-space for your apex user
SQL> CREATE TABLESPACE APEX_DATA datafile
‘C:\apex\APEX_data.DBF’
SIZE 1g;
SQL> @apexins APEX_DATA APEX_DATA TEMP /i/
20/25 min needs for installation.
SQL> @apxldimg.sql [after this command show bellow message for give location image folder]
enter vavue 1: C:\ [if install “C” drive] or D:\apex or D:\apex\images [if install except “C” drive]
Without any error image directory created then run flowing script:
SQL> @apxconf.sql [if install “C” drive] or @\apex\apxconf.sql [if install except “C” drive]
8 . You need to create apex user [admin user show as default] and password
[password must be contain Upper case,Special character ,lower character & number as like “Test!1”
Default port generated “8080” [if you don’t change port then enter for next step .
@apxchpwd.sql change password
After this step you need to some alter statement:
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
User altered.
SQL> ALTER USER XDB ACCOUNT UNLOCK;
User altered.
SQL> ALTER USER APEX_040200 ACCOUNT UNLOCK; –version of release
User altered.
SQL> ALTER USER FLOWS_FILES ACCOUNT UNLOCK;
User altered.
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
User altere
sql> exit
N.B : All sql statement must be connect sysdba connection with sql.
Your Apex software install completed.
For enjoying finally test your connection go to your web browser & write this port for apex test :
localhost:8080/apex
11.There are issues with the configuration of the Static Files in your environment.
Please consult the “Configuring Static File Support” section in the Application Express Installation Guide
N.B :If u face flowing above error then —–>
conn / as sysdba —> Run apex_epg_config.sql. For example:
sql> @apex_epg_config.sql
—>enter value 1: C:\ [if install “C” drive] or D:\apex or D:\apex\images [if install except “C” drive]
If you want to uninstall apex software connect with sysdba and run this script:
–> SQL> @apxremov.sql
Please tuning with me for next tutorial on apex….
After this step you need to some alter statement:
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
User altered.
SQL> ALTER USER XDB ACCOUNT UNLOCK;
User altered.
SQL> ALTER USER APEX_040200 ACCOUNT UNLOCK; –version of release
User altered.
SQL> ALTER USER FLOWS_FILES ACCOUNT UNLOCK;
User altered.
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
User altere
sql> exit
N.B : All sql statement must be connect sysdba connection with sql.
Your Apex software install completed.
For enjoying finally test your connection go to your web browser & write this port for apex test :
localhost:8080/apex
11.There are issues with the configuration of the Static Files in your environment.
Please consult the “Configuring Static File Support” section in the Application Express Installation Guide
N.B :If u face flowing above error then —–>
conn / as sysdba —> Run apex_epg_config.sql. For example:
sql> @apex_epg_config.sql
—>enter value 1: C:\ [if install “C” drive] or D:\apex or D:\apex\images [if install except “C” drive]
If you want to uninstall apex software connect with sysdba and run this script:
–> SQL> @apxremov.sql
Please tuning with me for next tutorial on apex….
exec dbms_xdb.sethttpport('8081');commit; Duplicate row delete
DELETE FROM
pr_taxable_amount_final_bk A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
pr_taxable_amount_final_bk B
WHERE
A.salary_date = B.salary_date
AND
A.uid_no = B.uid_no)
pr_taxable_amount_final_bk A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
pr_taxable_amount_final_bk B
WHERE
A.salary_date = B.salary_date
AND
A.uid_no = B.uid_no)
বৃহস্পতিবার, ২৮ জুন, ২০১৮
External Table
create directory test as 'D:/';
grant read,write on directory test to public;
create table test
(id varchar2(3),
last_name varchar2(50),
salary number
)
organization external
( default directory test
access parameters
(
fields terminated by ','
)
location ('c.txt')
)
grant read,write on directory test to public;
create table test
(id varchar2(3),
last_name varchar2(50),
salary number
)
organization external
( default directory test
access parameters
(
fields terminated by ','
)
location ('c.txt')
)
বুধবার, ১৪ ফেব্রুয়ারি, ২০১৮
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;
/
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;
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)
select * from employees as of timestamp(sysdate-interval '5' minute)
insert into emplyess
select * from employees as of timestamp(sysdate-interval '5' minute)
select * from AC_VOUCHERDTL as of timestamp sysdate-interval '30' minute
minus
select * from AC_VOUCHERDTL
minus
select * from AC_VOUCHERDTL
FLASHBACK TABLE employees TO TIMESTAMP
TO_TIMESTAMP('2025-06-29 14:00:00', 'YYYY-MM-DD HH24:MI:SS');
TO_TIMESTAMP('2025-06-29 14:00:00', 'YYYY-MM-DD HH24:MI:SS');
এতে সদস্যতা:
পোস্টগুলি (Atom)