Solved

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

Posted on 2008-04-03
10
3,620 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 recover a database from a user managed backup

751 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