Solved

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

Posted on 2008-04-03
10
3,609 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
Independent Software Vendors: 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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle- set role and grant privileges 6 50
ODBC in excel 2016 in Windows 10 via VBA 16 236
oracle numeric condition check 4 34
Pivoting oracle table 9 72
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

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