Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-04-03
10
Medium Priority
?
3,671 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 750 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

885 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