add_months() to timestamps in Oracle

Oracle has two classes of date/time types, "date" and "timestamp". The "date" type is very mature and has a variety of useful manipulation functions. Some of these functions are lacking in the more precise "timestamp" data types.

Today I'm going to provide one way of implementing the "date" type workhourse "add_months" for timestamps.

Why would you need this? Well, in my case, I'm writing a global application where users can request data for the next N months. All dates and times are implemented as timestamps with local time zone to present dates/times in a user's timezone. This is done so the user doesn't have to try and translate times from halfway around the world.

My problem is that Oracle's add_months() will work on a timestamp, but it doesn't reliably save and return the timezone data. I want to be sure timezone data is preserved, so I wrote my own add_months_tz().

My function performs the following basic steps:

• Extract seconds (and fractions of a second) and time zone data from incoming timestamp
• Alter the session time zone to match the incoming timestamp's time zone
• Use Oracle's built-in add_months (which returns a date)
• Use conversion functions to convert the date into a timestamp using the saved seconds
• Restore the original session time zone
• Return the newly built timestamp

In testing  I've noticed that sometimes (and with no visible consistency) I got the following error:

ORA-01862: the numeric value does not match the length of the format item

To prevent this from breaking my application, I wrapped the timestamp creation logic in a block and used the exception handler to create the timestamp without the fractions of a second.

Now that you have the theory and reasoning, here is the code:

create or replace function add_months_tz ( p_timestamp in timestamp with time zone, p_added_months in number) return timestamp with time zone is
--Created by Matthew Cox
--Please feel free to copy, use or alter as you see fit
--I'd appreciate it if you give me credit in your application comments
v_location_i number := 100; o_secs number; o_region varchar2(128); t_date date; r_timestamp timestamp with local time zone; v_session_timezone varchar2(128); begin --save session timezone so we can put it back v_session_timezone := sessiontimezone; v_location_i := 1000; --Extract the seconds and timezone region from the incoming timestamp o_secs := extract(second from p_timestamp); o_region := extract (timezone_region from p_timestamp); --If there is no region name specified, use the offsets if (o_region = 'UNKNOWN') then o_region := extract(timezone_hour from p_timestamp)||':'||extract(timezone_minute from p_timestamp); end if; --Alter the session timezone to ensure proper translation in our cast functions execute immediate 'alter session set time_zone = '''||o_region||''''; v_location_i := 3000; --We convert the timestamp to a date to do the month math t_date := trunc(p_timestamp, 'MI'); --Debug output --dbms_output.put_line('Extracted seconds: '||o_secs); --dbms_output.put_line('Extracted region: '||o_region); --dbms_output.put_line('converted date: '||t_date); v_location_i := 4000; t_date := add_months(t_date, p_added_months); --dbms_output.put_line('after add_months date: '||t_date); v_location_i := 5000; --Now we build a new timestamp with our manipulated date and extracted seconds begin r_timestamp := to_timestamp(to_char(t_date, 'DD-MON-YYYY HH24:MI')||o_secs, 'DD-MON-YYYY HH24:MI:SS.FF'); exception when others then v_location_i := 5100; --Use the simple date to the second r_timestamp := to_timestamp(to_char(t_date,'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS'); end; --Debugging output --dbms_output.put_line('final timestamp: '||r_timestamp); --dbms_output.put_line('final timestamp region name: '||extract(timezone_region from r_timestamp)); --dbms_output.put_line('final timestamp region offset: '||extract(timezone_hour from r_timestamp)||':'||extract(timezone_minute from r_timestamp)); --Put the session time zone back execute immediate 'alter session set time_zone = '''||v_session_timezone||''''; return r_timestamp; exception when others then dbms_output.put_line('Error at '||v_location_i||': '||sqlerrm); return null; end add_months_tz; /

Feel free to use this code as you see fit, but please be courteous and give me credit (even if only in your pl/sql comments).

---

Apex Reports to Email - Part 1: Mechanics

Oracle Application Express has built in subscription services for its interactive report type. This allows authenticated users to receive daily, weekly or monthly updates delivered to a single email address. This killer feature allows for users to consume data from a site without having to log-in. Unfortunately it is only available for Interactive Report types.

There are many reasons why a site might not implement reports as interactive. I will not go into them here. My goal is to give you the technique needed to send any APEX page or region to users via email from the back-end database.

There are 3 basic steps:

1. Setup database prerequisites
2. Simulate user's APEX login and Request the page using Oracle's utl_http package
3. Reformat and send html to user as email using the apex_mail package

Step 1 - Setup Database Prerequisites

Before we develop the actual code to fetch and send the APEX page, we need to ensure the database is properly configured. You will likely need to get assistance from your DBA to setup some of these constructs as they require super-user access to the database.

Granting object permissions

First we need to be sure the application owner has execute permission on the packages we'll be using. The following grants and synonym creation are needed to ensure we can easily call the necessary Oracle code from our application schema. Note that this example is using APEX version 4.0.2.00.07.

grant select on apex_040000.wwv_flow_sessions$ to <application_schema>;
create or replace synonym <application_schema>.wwv_flow_sessions$ for apex_040000.wwv_flow_sessions$;

--The following should be granted as part of the APEX install/configuration process
--I've included this for completeness

grant execute on htp to <application_schema>;
grant execute on owa to <application_schema>;
grant execute on apex_040000.wwv_flow_api to <application_schema>;
grant execute on apex_040000.wwv_flow_custom_auth_std to <application_schema>;
grant execute on apex_040000.wwv_flow to <application_schema>;
grant execute on apex_040000.apex_custom_auth to <application_schema>;
grant execute on utl_http to <application_schema>;

11g - Setup Access Control List

Oracle 11g introduces fine-grained network access controls. In order to access network resources, such as an application server, you need to create and setup an Access Control List, or ACL. This requires super-user permissions, so you'll likely need DBA help here too. The following code sets up a fully open ACL where the application_schema has full rights to all network access. This step is unnecessary for versions prior to 11gR1.

BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl
          ( acl         => 'http_access_acl.xml',
            description => 'ACL allowing for targeted access for emailing pages',
            principal   => 'SERVICE_DESK',
            is_grant    => TRUE,
            privilege   => 'connect',
            start_date  => SYSTIMESTAMP,
            end_date    => NULL);
  --web server
  DBMS_NETWORK_ACL_ADMIN.assign_acl
          ( acl        => 'http_access_acl.xml',
            host       => '<webserver IP or hostname>', --Wildcard * allowed
            lower_port => 1,                            --All ports open for testing
            upper_port => 9999);                        --Restrict in production to
                                                        --a single port
END;
/

WARNING: The above ACL allows for full network access. For security purposes, you will most likely want to restrict access to only your apex application server and the standard http and/or https ports (defaults of 80 and 443).


Accessing SSL (https) sites

If your application is secured using SSL certificates, you will have to setup an Oracle Wallet on your database server to store the site's SSL certificate and allow access. If you do not, the code will error out when trying to fetch the page. This requires command-line/gui access to the Oracle user on the database server, so again, work with your DBA to set this up.

I will not go into detail in this post as there are numerous excellent write-ups of this process out there. One such article is Using UTL_HTTP and an Oracle Wallet to Establish a Secure URL Connection (SSL) by Jeff Hunter at idevelopment.info. Jeff goes into great detail and uses screen-shots to walk you through the entire process.

One thing to note is that OWM is a gui tool. This requires you to use X11 forwarding if accessing the database server through SSH. To do this in Putty, you can follow these steps:

1. Load your DB server profile and select SSH->X11 from the menu
2. Check the "Enable X11 forwarding box"
3. Add 127.0.0.1:0 in the "X display Location" field
4. If you need to "su" into the Oracle users, you will likely need to setup your authorization keys
1. From your initial login session enter "xauth list" at the command prompt. You'll get something like this:
   server.domain.com/unix:10  MIT-MAGIC-COOKIE-1  37206ef562651c1aede9cb4b8891cfe7
2. Next su into oracle and add the xauth key like this:
   > xauth add server.domain.com/unix:10 MIT-MAGIC-COOKIE-1  37206ef562651c1aede9cb4b8891cfe7
5. run "xclock". If it worked you should see the gui clock on your desktop.

Step 2 - Authenticate and Request Page

With the prerequisites out of the way, we can focus on the heart of the problem. Apex was designed to render pages through a browser. We want to eliminate the browser and render pages directly to email, from the database.

Oracle has kept tight wraps on the rendering engine in Apex, as it is the key to the product. We will not try to circumvent that closely guarded secret. Instead we will masquerade as a browser ourselves in order to render the page.

In this step we will be creating a procedure that will login to the Apex environment as a user, and then submit a request to the application server to render and return the page. This way we get the page html without having to alter or even care about the Apex rendering engine.

Authenticating as an Apex user

The first step toward our goal is to setup a user session in Apex. This section of code will setup the environment and log us into apex as the "EMAIL_TEST" user:

Create or replace 
  FUNCTION  get_apex_page(p_username in varchar2,
                          p_page_url in varchar2,
                          p_request  in varchar2 default null,
                          p_param_names in varchar2 default null,
                          p_param_values in varchar2 default null) return clob is

    v_procedure_c constant varchar2(30) := 'get_apex_page';
    v_location_i integer;  -- This variable tracks our location within this procedure.
    v_parms_c    varchar2(4000);  -- This variable records the calling parameters for
                                  -- debugging and error recording operations.
    
    v_app_id_c       constant number         := 106;    /* change this to your application ID */
    v_public_page_c  constant number         := 101;    /*Change this to a public apex page in your site*/
    
    -- fixed constants
    V_NO_PROXY_FOR_C  constant varchar2(4000) := 'localhost';
    V_WALLET_C        constant varchar2(4000) := 'file:/u00/app/oracle/product/11.2.0/owm/wallets/oracle/';
    V_WALLET_PASS_C   constant varchar2(4000) := 'EmailRpt33';

    -- Proxy settings that can be made arguments in the WebBrowser() call
    v_proxyServer     varchar2(20);
    -- not all proxy servers use authentication, but many corporate proxies do, in
    -- which case you need to specify your auth details here
    -- (make it nulls if not applicable)
    v_proxyUser       varchar2(50) := '<proxy username goes here>';
    v_proxyPass       varchar2(50) := '<proxy password goes here>';

    -- our local variables
    v_proxyURL        varchar2(4000);
    v_request         UTL_HTTP.req;
    v_response        UTL_HTTP.resp;
    v_buffer          varchar2(30000);
    v_endLoop         boolean;    
    
    v_cookie_name     varchar2(256);
    v_cookie_value    varchar2(1024);
    v_cookie_path     varchar2(1024);
    v_cookie_domain   varchar2(256);
    v_cookie_secure   boolean;
    
    v_cgi_name owa.vc_arr;
    v_cgi_val owa.vc_arr;
    v_workspace_id number;
    v_captured_html clob;
    v_output_buffer number := 250;
    v_output_length number;
    v_output_idx number := 1;
    v_page_url varchar2(2000);

    v_return          clob;
    
    
    
  begin



    --
    -- Step 1 - Need to simulate apex login
    --

    -- set up cgi environment - Needed to ensure we can call apex rendering functions
    htp.init;
    v_cgi_name(1) := 'REQUEST_PROTOCOL';
    v_cgi_val(1) := 'HTTP';
    owa.init_cgi_env(num_params => 1,
                     param_name => v_cgi_name,
                     param_val  => v_cgi_val);
 
 
    v_location_i := 2000;
 
    -- find the workspace ID of application
    for c1 in (select workspace_id
                 from apex_applications
                where application_id = v_app_id_c) loop
      v_workspace_id := c1.workspace_id;
    end loop;


    -- set security group ID, session ID, app ID globals
    wwv_flow_api.set_security_group_id(v_workspace_id);
    apex_application.g_instance := wwv_flow_custom_auth.get_next_session_id;
    apex_application.g_flow_id := v_app_id_c;

    v_location_i := 3000;

    --
    -- create authenticated session for v_username
    --
    wwv_flow_custom_auth_std.post_login(
      p_uname => upper(p_username),
      p_session_id => null,
      p_flow_page => apex_application.g_flow_id||':'||v_public_page_c);
 

    dbms_output.put_line('workspace:'||v('WORKSPACE_ID')||', session:'||apex_application.g_instance||', user:'||apex_application.g_user||' '||'APP_USER_ID = ' || apex_util.get_session_state('APP_USER_ID'));

    --
    -- We must call wwv_flow.show to trick Apex into fully initializing the session
    --
    wwv_flow.show (
      p_flow_id => apex_application.g_flow_id,
      p_instance => apex_application.g_instance,
      p_request => 'FSP_SHOW_POPUPLOV'
    );

    --
    -- We need to reset the security group ID because wwv_flow.show resets it
    --
    wwv_flow_api.set_security_group_id(v_workspace_id);


    v_location_i := 4000;

    --
    --Perform user post-authentication initialization steps here
    --


--<Continued in next code-block>

Note that the above code will get you an authenticated session. It will not perform any post-authentication code defined in the Apex authentication scheme. If you need session variables based on the user setup, you must populate them yourself using the apex_util.set_session_state procedure at location 4000 above.



Requesting the Apex page

Now that we have an authenticated session, we need to build and send the HTTP request to the app server. As you may or may not know, the web is built on HTTP requests and responses and APEX is no different.

One distinction APEX has over say the Yahoo home page is that it requires authentication. The APEX rendering engine expects a browser to report your authenticated session ID in two ways. First, and most obvious is in the URL itself as you can see in the below sample URL:

https://server.domain.com/pls/f?p=101:1:1815393136712746::NO::

The second place APEX expects your session id is in your session cookie. This cookie contains a hashed version of your APEX session ID, which must also match a record in the APEX sessions table. We are going to retrieve this hashed value from the wwv_flow_sessions$ table and build a cookie for our request.

The following block of code will show you how to create the utl_http request, create and pass the cookie and retrieve the response (our html source!). It is based upon code by Billy Verreynne shared in this OTN forum thread.

--<continued from above code-block>

    --
    -- Step 2 - Build and submit the request
    --
    v_location_i := 5000;  
    --Now setup the "browser" including session cookie, etc
    dbms_output.put_line( 'Setting browser configuration' );
    UTL_HTTP.set_response_error_check( TRUE );
    UTL_HTTP.set_detailed_excp_support( TRUE );
    UTL_HTTP.set_cookie_support( TRUE );
    UTL_HTTP.set_transfer_timeout( 30 );
    UTL_HTTP.set_follow_redirect( 3 );
    UTL_HTTP.set_persistent_conn_support( TRUE );
    utl_http.clear_cookies;
 
    --Setup URL
    --f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:PrinterFriendly
    v_page_url := rtrim(p_page_url, ': ')||':'||apex_application.g_instance||':'||p_request||':::'||p_param_names||':'||p_param_values;
 
 
    dbms_output.put_line('cookie count: '||utl_http.get_cookie_count);


    v_location_i := 6000;
    -- set wallet for HTTPS access
    dbms_output.put_line( 'Wallet set to '||V_WALLET_C );
    UTL_HTTP.set_wallet( V_WALLET_C, V_WALLET_PASS_C );

    -- Configure for proxy access
    if v_proxyServer is not NULL then
       dbms_output.put_line( 'Proxy Server is '||v_proxyServer );
       v_proxyURL := 'http://'||v_proxyServer;

       if (v_proxyUser is not NULL and v_proxyPass is not NULL) then
          v_proxyURL := REPLACE( v_proxyURL, 'http://',  'http://'||v_proxyUser||':'||v_proxyPass||'@' );
          dbms_output.put_line( 'Proxy URL modified to include proxy user name and password' );
       end if;

       dbms_output.put_line( 'Proxy URL is '|| REPLACE(v_proxyURL,v_proxyPass,'*****') );
       UTL_HTTP.set_proxy( v_proxyURL, V_NO_PROXY_FOR_C );
    end if;
 
    v_location_i := 7000;
 
 
    --Setup user session cookie
    for rec in (select session_id_hashed
                  from WWV_FLOW_SESSIONS$
                 where id = apex_application.g_instance) loop
      v_cookie_value :=  rec.session_id_hashed;        
    end loop;
 
    v_location_i := 8000;  
 
 
    APEX_CUSTOM_AUTH.GET_COOKIE_PROPS(p_app_id        => v_app_id_c,
                                      p_cookie_name   => v_cookie_name,
                                      p_cookie_path   => v_cookie_path,
                                      p_cookie_domain => v_cookie_domain,
                                      p_secure        => v_cookie_secure);
   
    --
    -- Step 3 - Send Request and receive response
    --
    dbms_output.put_line('Fetching page: '||v_page_url);

    v_location_i := 10000;
 
    v_request := UTL_HTTP.begin_request( v_page_url, 'GET', UTL_HTTP.HTTP_VERSION_1_1 );

    -- set HTTP header for the GET
    UTL_HTTP.set_header( v_request, 'User-Agent', 'Mozilla/4.0' );
 
    --
    -- Send the user session cookie. This is key to ensuring you get the actual
    --   page requested, and not the login page
    --
    dbms_output.put_line('Cookie: '||v_cookie_name||'='||v_cookie_value);
    UTL_HTTP.set_header(v_request, 'Cookie', v_cookie_name||'='||v_cookie_value||';'); --LOGIN_USERNAME_COOKIE=coxm; TUSC_SERVICE_DESK=CA901BBB0C24F907

    v_location_i := 11000;
     
    -- get response to the GET from web server
    v_response := UTL_HTTP.get_response( v_request );
     
    v_location_i := 12000;
     
    --Capture incoming buffer to v_captured_html
    v_endLoop := false;
    loop
      exit when v_endLoop;

      begin
        UTL_HTTP.read_line( v_response, v_buffer, TRUE );
 
        if (v_buffer is not null and length(v_buffer)>0) then
           v_captured_html := v_captured_html || v_buffer;
        end if;
      exception
        when UTL_HTTP.END_OF_BODY then
          v_endLoop := true;
      end;

    end loop;
    UTL_HTTP.end_response( v_response );
 
    v_location_i := 13000;
 
 
    dbms_output.put_line('Captured html count: '||length(v_captured_html));

    /*
    --
    -- Output for testing - Note you will likely get buffer overflow errors
    --
    dbms_output.put_line('captured html:');

    v_output_length := length(v_captured_html);

    while (v_output_idx <= v_output_length) loop
      dbms_output.put_line(substr(v_captured_html, v_output_idx, v_output_buffer));
      v_output_idx := v_output_idx + v_output_buffer;
    end loop;
    */

    return v_captured_html;


EXCEPTION
    when OTHERS then
      dbms_output.put_line(v_procedure_c ||' Error found at '||v_location_i||'.'||sqlerrm);
      return null;
END get_apex_page;
/

Step 3 - Reformat and Send as Email to User

Now that we have a procedure to fetch an APEX page, we simply need to write code to call the page and send the email. Of course this is where the most variance will occur from user to user. Each of us has specific reasons for wanting to send an APEX page by email. Some may want an "Email this page" button or link to make collaboration easier. Some may want to setup a schedule of periodic emails for auditing or site use analysis. I will be using this functionality to allow my users to send performance and trending reports to themselves on a periodic basis. Some may not want to use this technique for email at all, but will send the page to some other post-processing routine.

Since I cannot possibly cover the wide variety of needs or uses of this technique, I simply give you a basic procedure to email a given page to a given email account. From there you can expand, schedule via dbms_scheduler, add code to watch a table of subscriptions, and so forth.

Before implementing the email procedure, you need to ensure your APEX environment is setup to send email. Your DBA and/or APEX administrator can assist you with this using the Oracle documentation

create or replace
Procedure  email_apex_page (p_username in varchar2,
                              p_page_url in varchar2,
                              p_target_email in varchar2,
                              p_request  in varchar2 default null,
                              p_param_names in varchar2 default null,
                              p_param_values in varchar2 default null) is

  -- Name: email_apex_page
  --
  -- Description: This procedure will package and email a report to the subscribing user
  --
  -- Parameters:  p_username     - Apex username of user to fetch page as
  --              p_page_url     - Url for accessing report page, up to and including page number
  --                                 https://site.com/testdad/f?p=106:620
  --              p_request      - Apex request value (can use this to turn off non-emailable regions/items
  --              p_param_names  - comma delimited list of page items to set
  --              p_param_values - comma delimited list of page item values (matches param names list)
 

    v_procedure_c constant varchar2(30) := 'email_apex_page';
    v_location_i integer;  -- This variable tracks our location within this procedure.
    v_parms_c    varchar2(4000);  -- This variable records the calling parameters for
                                  -- debugging and error recording operations.
 
    --Constants
    /*Change as required for your environment */
    v_sender_email_c constant varchar2(256)  := 'apex@company.com'; --Your desired "From" Address
    v_plain_body_c   constant clob           := 'APEX Page sent via email is in HTML format. PLease view HTML portion';
    v_subject_c      constant varchar2(256)  := 'Emailing Apex Page'; --Your desired "Subject"
 
 
    --Variables
    v_apex_html clob;

 
  begin
    v_location_i := 100;
 
    --
    -- Step 1 - Get the Page
    --
    v_apex_html := get_apex_page(p_username     => p_username,
                                 p_page_url     => p_page_url,
                                 p_request      => p_request,
                                 p_param_names  => p_param_names,
                                 p_param_values => p_param_values);

    --
    -- Step 2 - Send page via email
    --
    APEX_MAIL.SEND(
      p_to        => p_target_email ,
      p_from      => v_sender_email_c,
      p_body      => v_plain_body_c,
      p_body_html => v_apex_html,
      p_subj      => v_subject_c);

  EXCEPTION
    when OTHERS then
      dbms_output.put_line(v_procedure_c ||' Error found at '||v_location_i||'.'||sqlerrm);
      raise;
  end email_apex_page;
/

Tips and Traps

In writing this code, I've come across several tips and traps. Most are covered above, but some of the APEX specific ones are listed here in brief:

• By default CSS and Javascript files use relative paths. 
• You must convert to absolute paths if you want the emailed HTML to have access to them
• Remember that flash doesn't work in email for security reasons, so trim out any flash based charts
• Many people do not allow javascript in email for security reasons, so be sure to accommodate them especially if using javascript based widgets
• It is probably easier to use APEX conditional display to trim your HTML than to try and do it from the database. You can set this up with one of these three methods:
• Use the printer friendly templates to trim out your header, navigation, etc
• Use the "request" parameter of the URL and the corresponding "request not equal to" condition to hide unnecessary page elements from the email
• Use a page or application variable in lieu of "request" if request is already in use

---