রবিবার, ২৯ জুলাই, ২০১৮

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 .
 
@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….
 
 
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)

বৃহস্পতিবার, ২৮ জুন, ২০১৮

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

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

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)
 
 
select * from AC_VOUCHERDTL as of timestamp sysdate-interval '30' minute
minus
select * from AC_VOUCHERDTL