Solved

Schedule an SQL query to produce a daily report (Oracle 9i)

Posted on 2008-04-03
10
3,610 Views
Last Modified: 2013-12-19
Schedule an SQL query to produce a daily report
I would like to create an oracle job to execute a query daily at specified time except on Sundays.
The sql script (prat_daily.sql) resides in a remote machine.
The results of the query should be produce an EXCEL file in a directory still in the remote machine
 Thanks in advance
0
Comment
Question by:diteps06
  • 7
  • 3
10 Comments
 
LVL 1

Author Comment

by:diteps06
ID: 21270288
If is possible I would like the report to be send to my mailbox
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21273559
Do you have any way for Oracle to reach that machine?  First, to read the sql file (why not put it in a stored procedure) and second to create the file.

You can't create an excel file directly, at least not very easily,  you can however create a csv file that excel can read and import.

What does your sql script look like?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21273654
do you want the file emailed instead of being written to the remote machine, or in addition to?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 21274169
Here's a sample package, simply replace my query from all_objects with your query and adjust the append_text lines for your data...
CREATE OR REPLACE PACKAGE sample_email_report
AS
    PROCEDURE run_report(p_date IN DATE);
END sample_email_report;
/
 
CREATE OR REPLACE PACKAGE BODY sample_email_report
AS
    s_report              CLOB;
    g_boundary   CONSTANT VARCHAR2(256) := '-----7D81A643C843032A747A1CBD';
 
    PROCEDURE append_text(p_string IN VARCHAR2)
    IS
    BEGIN
        IF p_string IS NOT NULL
        THEN
            DBMS_LOB.writeappend(s_report, LENGTH(p_string), p_string);
        END IF;
 
        DBMS_LOB.writeappend(s_report, LENGTH(UTL_TCP.crlf), UTL_TCP.crlf);
    END;
 
    PROCEDURE begin_mail(
        p_conn      IN OUT   UTL_SMTP.connection,
        p_from      IN       VARCHAR2,
        p_to        IN       VARCHAR2,
        p_cc        IN       VARCHAR2,
        p_subject   IN       VARCHAR2
    )
    IS
        v_host       VARCHAR2(9) := NULL;
        v_rcpt_cnt   NUMBER      := 0;
    BEGIN
        p_conn := UTL_SMTP.open_connection('your_smtp_server');
        UTL_SMTP.helo(p_conn, 'yourdomain.com');
        UTL_SMTP.mail(p_conn, p_from);
        UTL_SMTP.rcpt(p_conn, p_to);
 
        IF (p_cc IS NOT NULL)
        THEN
            UTL_SMTP.rcpt(p_conn, p_cc);
        END IF;
 
        UTL_SMTP.open_data(p_conn);
        UTL_SMTP.write_data(p_conn, 'From: ' || p_from || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn, 'To: ' || p_to || UTL_TCP.crlf);
 
        IF p_cc IS NOT NULL
        THEN
            UTL_SMTP.write_data(p_conn, 'Cc: ' || p_cc || UTL_TCP.crlf);
        END IF;
 
        UTL_SMTP.write_data(p_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn,
                               'Content-Type: multipart/mixed; boundary="'
                            || g_boundary
                            || '"'
                            || UTL_TCP.crlf
                           );
        UTL_SMTP.write_data(p_conn, UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn,
                               'This is a multi-part message in MIME format.'
                            || UTL_TCP.crlf
                           );
    END begin_mail;
 
    PROCEDURE begin_mime_block(
        p_conn       IN OUT NOCOPY   UTL_SMTP.connection,
        p_inline     IN              BOOLEAN DEFAULT TRUE,
        p_filename   IN              VARCHAR2 DEFAULT NULL
    )
    IS
    BEGIN
        UTL_SMTP.write_data(p_conn, '--' || g_boundary || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn,
                            'Content-Type: text/plain' || UTL_TCP.crlf
                           );
 
        IF (p_inline)
        THEN
            UTL_SMTP.write_data(p_conn,
                                   'Content-Disposition: '
                                || 'inline; filename=""'
                                || UTL_TCP.crlf
                               );
        ELSE
            UTL_SMTP.write_data(p_conn,
                                   'Content-Disposition: '
                                || 'attachment; filename="'
                                || p_filename
                                || '"'
                                || UTL_TCP.crlf
                               );
        END IF;
 
        UTL_SMTP.write_data(p_conn, UTL_TCP.crlf);
    END begin_mime_block;
 
    PROCEDURE end_mime_block(
        p_conn   IN OUT NOCOPY   UTL_SMTP.connection,
        p_last   IN              BOOLEAN DEFAULT FALSE
    )
    IS
    BEGIN
        UTL_SMTP.write_data(p_conn, UTL_TCP.crlf);
 
        IF (p_last)
        THEN
            UTL_SMTP.write_data(p_conn,
                                '--' || g_boundary || '--' || UTL_TCP.crlf
                               );
        END IF;
    END end_mime_block;
 
    PROCEDURE report_query(p_date IN DATE)
    IS
        CURSOR c_query
        IS
            SELECT   owner, object_name, subobject_name, object_type,
                     TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') created,
                     TO_CHAR(last_ddl_time,
                             'yyyy-mm-dd hh24:mi:ss'
                            ) last_ddl_time,
                     status
                FROM all_objects
               WHERE last_ddl_time >= p_date
            ORDER BY last_ddl_time DESC,
                     owner,
                     object_type,
                     object_name,
                     subobject_name;
    BEGIN
        DBMS_APPLICATION_INFO.set_action('query');
        append_text(   'Owner'
                    || ','
                    || 'Object_Name'
                    || ','
                    || 'SubObject_Name'
                    || ','
                    || 'Object_Type'
                    || ','
                    || 'Created'
                    || ','
                    || 'Last_DDL_Time'
                    || ','
                    || 'Status'
                   );
 
        FOR x IN c_query
        LOOP
            DBMS_APPLICATION_INFO.set_client_info(   'append '
                                             || x.owner
                                             || '.'
                                             || x.object_name
                                            );
            append_text(   x.owner
                        || ','
                        || x.object_name
                        || ','
                        || x.subobject_name
                        || ','
                        || x.object_type
                        || ','
                        || x.created
                        || ','
                        || x.last_ddl_time
                        || ','
                        || x.status
                       );
        END LOOP;
    END;
 
    PROCEDURE run_report(p_date IN DATE)
    IS
        i        NUMBER;
        v_len    NUMBER;
        v_conn   UTL_SMTP.connection;
    BEGIN
        DBMS_APPLICATION_INFO.set_client_info(NULL);
        DBMS_APPLICATION_INFO.set_module(   'Sample Email Report '
                                         || TO_CHAR(p_date,
                                                    'yyyy-mm-dd hh24:mi:ss'
                                                   ),
                                         'initialize'
                                        );
        DBMS_LOB.createtemporary(s_report, TRUE);
        DBMS_LOB.OPEN(s_report, DBMS_LOB.lob_readwrite);
        report_query(p_date);
        begin_mail(v_conn,
                   'fromme@mydomain.com',
                   'toyou@yourdomain.com',
                   'ccthem@theirdomain.com',
                      'Sample Email Report for '
                   || TO_CHAR(p_date, 'YYYY-MM-DD HH24:MI:SS')
                  );
        begin_mime_block(v_conn, TRUE);
        UTL_SMTP.write_data(v_conn, 'Attachment follows' || UTL_TCP.crlf);
        end_mime_block(v_conn, FALSE);
        DBMS_APPLICATION_INFO.set_action('create attachment');
        begin_mime_block(v_conn, FALSE, 'recently_touched_objects.csv');
        v_len := DBMS_LOB.getlength(s_report);
        i := 1;
 
        WHILE i <= v_len
        LOOP
            UTL_SMTP.write_data(v_conn, DBMS_LOB.SUBSTR(s_report, 32767, i));
            i := i + 32767;
        END LOOP;
 
        DBMS_APPLICATION_INFO.set_action('End mail');
        DBMS_LOB.freetemporary(s_report);
        UTL_SMTP.write_data(v_conn, UTL_TCP.crlf);
        end_mime_block(v_conn, TRUE);
        UTL_SMTP.close_data(v_conn);
        UTL_SMTP.quit(v_conn);
        DBMS_APPLICATION_INFO.set_module(NULL, NULL);
        DBMS_APPLICATION_INFO.set_client_info(NULL);
    END;
END sample_email_report;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21274392
The following script will schedule your report to run every day except Sunday at 2am
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => 'sample_email_report.run_report(trunc(sysdate)-7);'
     ,next_date => sysdate
     ,interval  => 'trunc(SYSDATE) + DECODE(TO_CHAR(TRUNC(SYSDATE),''Dy'')),''Sat'',2,1)  + 2/24'
     ,no_parse  => TRUE
    );
  DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
  commit;
END;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21283999
need anything else?
0
 
LVL 1

Author Comment

by:diteps06
ID: 21290990
I currently studying the script and how to applied it. Last but not the least I would like the result to be transmitted when it returns atleast a row.
Maybe a boolean variable to check the rows return by the cursor would do it.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 21296865
How about this?
CREATE OR REPLACE PACKAGE BODY sds.sample_email_report
AS
    s_report              CLOB;
    g_boundary   CONSTANT VARCHAR2(256) := '-----7D81A643C843032A747A1CBD';
 
    PROCEDURE append_text(p_string IN VARCHAR2)
    IS
    BEGIN
        IF p_string IS NOT NULL
        THEN
            DBMS_LOB.writeappend(s_report, LENGTH(p_string), p_string);
        END IF;
 
        DBMS_LOB.writeappend(s_report, LENGTH(UTL_TCP.crlf), UTL_TCP.crlf);
    END;
 
    PROCEDURE begin_mail(
        p_conn      IN OUT   UTL_SMTP.connection,
        p_from      IN       VARCHAR2,
        p_to        IN       VARCHAR2,
        p_cc        IN       VARCHAR2,
        p_subject   IN       VARCHAR2
    )
    IS
        v_host       VARCHAR2(9) := NULL;
        v_rcpt_cnt   NUMBER      := 0;
    BEGIN
        p_conn := UTL_SMTP.open_connection('mailmta.aepsc.com');
        UTL_SMTP.helo(p_conn, 'aep.com');
        UTL_SMTP.mail(p_conn, p_from);
        UTL_SMTP.rcpt(p_conn, p_to);
 
        IF (p_cc IS NOT NULL)
        THEN
            UTL_SMTP.rcpt(p_conn, p_cc);
        END IF;
 
        UTL_SMTP.open_data(p_conn);
        UTL_SMTP.write_data(p_conn, 'From: ' || p_from || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn, 'To: ' || p_to || UTL_TCP.crlf);
 
        IF p_cc IS NOT NULL
        THEN
            UTL_SMTP.write_data(p_conn, 'Cc: ' || p_cc || UTL_TCP.crlf);
        END IF;
 
        UTL_SMTP.write_data(p_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn,
                               'Content-Type: multipart/mixed; boundary="'
                            || g_boundary
                            || '"'
                            || UTL_TCP.crlf
                           );
        UTL_SMTP.write_data(p_conn, UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn,
                               'This is a multi-part message in MIME format.'
                            || UTL_TCP.crlf
                           );
    END begin_mail;
 
    PROCEDURE begin_mime_block(
        p_conn       IN OUT NOCOPY   UTL_SMTP.connection,
        p_inline     IN              BOOLEAN DEFAULT TRUE,
        p_filename   IN              VARCHAR2 DEFAULT NULL
    )
    IS
    BEGIN
        UTL_SMTP.write_data(p_conn, '--' || g_boundary || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_conn,
                            'Content-Type: text/plain' || UTL_TCP.crlf
                           );
 
        IF (p_inline)
        THEN
            UTL_SMTP.write_data(p_conn,
                                   'Content-Disposition: '
                                || 'inline; filename=""'
                                || UTL_TCP.crlf
                               );
        ELSE
            UTL_SMTP.write_data(p_conn,
                                   'Content-Disposition: '
                                || 'attachment; filename="'
                                || p_filename
                                || '"'
                                || UTL_TCP.crlf
                               );
        END IF;
 
        UTL_SMTP.write_data(p_conn, UTL_TCP.crlf);
    END begin_mime_block;
 
    PROCEDURE end_mime_block(
        p_conn   IN OUT NOCOPY   UTL_SMTP.connection,
        p_last   IN              BOOLEAN DEFAULT FALSE
    )
    IS
    BEGIN
        UTL_SMTP.write_data(p_conn, UTL_TCP.crlf);
 
        IF (p_last)
        THEN
            UTL_SMTP.write_data(p_conn,
                                '--' || g_boundary || '--' || UTL_TCP.crlf
                               );
        END IF;
    END end_mime_block;
 
    PROCEDURE report_query(p_date IN DATE)
    IS
        CURSOR c_query
        IS
            SELECT   owner, object_name, subobject_name, object_type,
                     TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') created,
                     TO_CHAR(last_ddl_time,
                             'yyyy-mm-dd hh24:mi:ss'
                            ) last_ddl_time,
                     status
                FROM dba_objects
               WHERE last_ddl_time >= p_date
            ORDER BY last_ddl_time DESC,
                     owner,
                     object_type,
                     object_name,
                     subobject_name;
 
        v_data   BOOLEAN := FALSE;
    BEGIN
        DBMS_APPLICATION_INFO.set_action('query');
 
        FOR x IN c_query
        LOOP
            DBMS_APPLICATION_INFO.set_client_info(   'append '
                                                  || x.owner
                                                  || '.'
                                                  || x.object_name
                                                 );
 
            IF NOT v_data
            THEN
                append_text(   'Owner'
                            || ','
                            || 'Object_Name'
                            || ','
                            || 'SubObject_Name'
                            || ','
                            || 'Object_Type'
                            || ','
                            || 'Created'
                            || ','
                            || 'Last_DDL_Time'
                            || ','
                            || 'Status'
                           );
            END IF;
 
            append_text(   x.owner
                        || ','
                        || x.object_name
                        || ','
                        || x.subobject_name
                        || ','
                        || x.object_type
                        || ','
                        || x.created
                        || ','
                        || x.last_ddl_time
                        || ','
                        || x.status
                       );
            v_data := TRUE;
        END LOOP;
    END;
 
    PROCEDURE run_report(p_date IN DATE)
    IS
        i        NUMBER;
        v_len    NUMBER;
        v_conn   UTL_SMTP.connection;
    BEGIN
        DBMS_APPLICATION_INFO.set_client_info(NULL);
        DBMS_APPLICATION_INFO.set_module(   'Sample Email Report '
                                         || TO_CHAR(p_date,
                                                    'yyyy-mm-dd hh24:mi:ss'
                                                   ),
                                         'initialize'
                                        );
        DBMS_LOB.createtemporary(s_report, TRUE);
        DBMS_LOB.OPEN(s_report, DBMS_LOB.lob_readwrite);
        report_query(p_date);
        v_len := DBMS_LOB.getlength(s_report);
 
        IF v_len > 0
        THEN
            begin_mail(v_conn,
                       'sdstuber@aep.com',
                       'sdstuber@aep.com',
                       'dbmullen@aep.com',
                          'Sample Email Report for '
                       || TO_CHAR(p_date, 'YYYY-MM-DD HH24:MI:SS')
                      );
            begin_mime_block(v_conn, TRUE);
            UTL_SMTP.write_data(v_conn, 'Attachment follows' || UTL_TCP.crlf);
            end_mime_block(v_conn, FALSE);
            DBMS_APPLICATION_INFO.set_action('create attachment');
            begin_mime_block(v_conn, FALSE, 'recently_touched_objects.csv');
            i := 1;
 
            WHILE i <= v_len
            LOOP
                UTL_SMTP.write_data(v_conn,
                                    DBMS_LOB.SUBSTR(s_report, 32767, i)
                                   );
                i := i + 32767;
            END LOOP;
 
            DBMS_APPLICATION_INFO.set_action('End mail');
            UTL_SMTP.write_data(v_conn, UTL_TCP.crlf);
            end_mime_block(v_conn, TRUE);
            UTL_SMTP.close_data(v_conn);
            UTL_SMTP.quit(v_conn);
        END IF;
 
        DBMS_LOB.freetemporary(s_report);
        DBMS_APPLICATION_INFO.set_module(NULL, NULL);
        DBMS_APPLICATION_INFO.set_client_info(NULL);
    END;
END sample_email_report;
/

Open in new window

0
 
LVL 1

Author Closing Comment

by:diteps06
ID: 31445499
Thanks!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21342688
why a B?  What more could I have possibly done?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question