Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-04-03
10
Medium Priority
?
3,654 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

719 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