Sunday, 9 December 2012

Tabular Form Validations And Processes Beyond APEX 4.0

Before APEX 4.1, if you wanted to add more complex validation using PL/SQL, you would have to do a loop through the apex_application.g_f0x array and act accordingly - as per: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_app.htm#autoId2

Aside from validations, you may want some process to do something with values before deleting/updating/creating.

Apex 4.1 introduces some new substitution strings:

http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/concept_sub.htm#BEIIBAJD

APEX$ROW_NUM - the row number being processed
APEX$ROW_SELECTOR - If a check has been marked in the checkbox (value will be 'X')
APEX$ROW_STATUS - C if created; D if deleted; U if updated

I wasn't really sure how to work with these, and nor did I really investigate, when I first saw them in the builder guide. A recent thread in the forums, and it all makes sense!

With these changes, you no longer need to deal with the array, but instead can just refer to the above substitution strings in combination with bind syntax to access specific columns. The important steps are that when you first create the validation or process, you specify the tabular form it is associated with. According to the help text of execute condition, you must also specify the execute condition as For Each Row, if referencing any tabular form columns in the process - However, in my tests, I didn't experience this, Once or For Each Row didn't make any difference.

Side note: from what I can see, this doesn't work with Manual Tabular forms, so in that case, you will still need to use the g_f0x array.

For example, lets check the id column matches the row number (not a good example, as sorting will mess this up, but just to give you the idea)

Add a new validation, and specify the type as function returning boolean

return :APEX$ROW_NUM = :ID;

(Referencing the column name with bind syntax)

Similarly, if you had some client side function to re-order rows, you could then have a page process:

update tabular_test
set num_col = :APEX$ROW_NUM
where ID = :ID;

One more example might be that you want to delete child records before performing the multi row delete. Another process:

begin

 if :apex$row_Selector = 'X' then

  delete from child_table where some_id = :id;

 end if;

end;

Alas; no need to deal with the apex_application.g_f0x array (for the most part). Just associate a validation/process to a tabular form and make use of these new variables and reference tabular form columns using bind syntax.

Monday, 5 November 2012

Accessing Google Data

First I want to point out, the code posted here is in my no means complete, but I believe it provides a good foundation to extend on. To point out some of what is lacking - I am not fetching the refresh token to easily get a new access token without user intervention. I am not handling when the access token is no longer valid. Etc. Also, since a lot of the responses result in JSON, I used the PL/JSON package throughout. I also unfortunately cannot set up a demo on apex.oracle.com due to the obvious limitation of the wallet, and using utl_http.

You may know, google has a series of API's that allow you to access your data programatically, to create third party apps. Originally when I looked at the docs, it was using OAuth 1, and I never ended up mastering it. Oleg made a post about the differences here: http://dbswh.webhop.net/htmldb/f?p=BLOG:READ:0::::ARTICLE:889800346602035

The google docs are here, and they are quite detailed with what to do: https://developers.google.com/accounts/docs/OAuth2. There are a number of techniques you can follow, and previous presentation slides I have seen/posts have suggested following the type as a desktop application. This relies on some intervention where you need to copy the token and push it back to the application in some way, so I didn't go this route. Rather, I went down the web server application path.

The first thing you need is set up an API project, and enable the API you want access to. This is done through the Google API Console.




After clicking the button, you select what API's you would like to use with google. A common request I see, and the one I am most interested in is the Calendar API, so I'll turn that on, the go to the API Access tab to create an OAuth 2 client ID. Then specify the type as Web Application and your site's hostname. Now, since I am working locally, I am going to leave it as example.com - it doesn't seem to work with an IP address. So it is necessary to also update your hosts file to point example.com at APEX - or just use your actual apex environment URL.



This will give you two bits of crucial information that you need to authorize - client ID and client secret. The next step is to set the redirct URI. When you want to authorize, the user is redirected to google - then google will either redirect you straight away back to the callback, or you will need to grant access to the app. 

The redirect URI is the first challenge, since you cannot re-direct back to an APEX page. The reason for this is that every apex page is the result of the f procedure. When google re-directs, it passes one of a few query string parameters back - all of which the f procedure doesn't recognise. 

So I made a package called google_utl, with a procedure called authorization_call_back. Then, in the API console, I updated the redirect URI to point to this procedure (during testing, I just pointed it to http://localhost, so that it was easy to see what is being passed back etc):

http://example.com:8888/apex/google.google_utl.authorization_code_callback.

Before I post the actual code, I should also point out that I am storing the user token in the users table (I called mine app_user):

CREATE TABLE "GOOGLE"."APP_USER" 
   ( "USERNAME" VARCHAR2(200 BYTE), 
 "ACCESS_TOKEN" VARCHAR2(400 BYTE), 
 "ID_TOKEN" VARCHAR2(20 BYTE), 
 "ORIGINATING_URL" VARCHAR2(400 BYTE), 
 "CALENDAR_ID" VARCHAR2(50 BYTE)
   )

ACCESS_TOKEN - Where I store the authorization token to submit with requests
ID_TOKEN - A unique token I use - I pass this in the state parameter, so when it returns I can redirect to their last active page (stored in ORIGINATING_URL)
CALENDAR_ID - Each authorized request needs the calendar ID so you know which calendar data to use

As an additional step, you also need to set up an ACL, and a wallet since the requests are over http, to the googleapis domain. To set up the wallet, you need to export the certificate chain and import it into your wallet. 


So, on the page with the button to authorize, I have a button with the action defined by a dynamic action, with 2 true actions:

1. Generate a random token, and return the value into a hidden page item:

declare
    l_id_token app_user.id_token%type;
begin
    l_id_token := dbms_random.string('A', 20);
    update app_user
    set id_token = l_id_token
   where upper(username) = :APP_USER;
:P3_ID_TOKEN := l_id_token;
end;

2. Execute Javascript Code:

$.ajax({
    url: 'GOOGLE.GOOGLE_UTL.GET_AUTHORIZATION_URL',
    data: {
        p_state: escape($v('P3_ID_TOKEN')),
        p_url: escape(window.location.href)
    },
    success: function(data){
        window.location.href=data;
    }
});

And the code of get_authorization_url is:

  --Refer to docs: https://developers.google.com/accounts/docs/OAuth2WebServer
  procedure get_authorization_url(
    p_state in varchar2
  , p_url in varchar2)
  as
    l_url_params varchar2(400);
    l_state app_user.id_token%type;
    l_url app_user.originating_url%type;
  BEGIN
    l_state := utl_url.unescape(p_state);
    l_url := utl_url.unescape(p_url);
    
    update app_user
    set originating_url = l_url
    where id_token = l_State;
    
    l_url_params := 
      'response_type=#RESPONSE_TYPE#&client_id=#CLIENT_ID#&redirect_uri=#REDIRECT_URI#&scope=#SCOPE#&state=#STATE#';
      
    l_url_params := replace(l_url_params, '#RESPONSE_TYPE#', 'code');  
    l_url_params := replace(l_url_params, '#CLIENT_ID#', g_client_id);
    l_url_params := replace(l_url_params, '#REDIRECT_URI#', g_redirect_uri);
    l_url_params := replace(l_url_params, '#SCOPE#', 'https://www.googleapis.com/auth/calendar');
    l_url_params := replace(l_url_params, '#STATE#', p_state);
  
    htp.p(g_auth_url || '?' || l_url_params);
  END get_authorization_url;

Basically, this is a procedure where you could store any user related data before redirecting to google for authorization. It returns the URL that the user needs to go to be authorized, then the javascript re-directs the user to that web address.

Once they grant access (or cancel), it redirects to the callback procedure, which has the code as follows:

 procedure authorization_code_callback(
    code in varchar2 default NULL, 
    error in varchar2 default NULL,
    state in varchar2)
  as
  
    l_token_req utl_http.req;
    l_token_res utl_http.resp;
    
    l_token_req_payload varchar2(4000) := 
      'code=#CODE#&client_id=#CLIENT_ID#&client_secret=#CLIENT_SECRET#&redirect_uri=#REDIRECT_URI#&grant_type=#GRANT_TYPE#';
    
    l_response CLOB;
    l_response_tmp varchar2(1024);
    l_response_json JSON;
    
    l_unescaped_state APP_USER.ID_TOKEN%type;
    l_endpoint_url APP_USER.ORIGINATING_URL%type;
    
  begin
    
    l_unescaped_state := utl_url.unescape(state);
    
    select originating_url into l_endpoint_url
    from app_user
    where id_token = l_unescaped_state;
    
    --code was returned, get the token
    if code is not null then
      l_token_req_payload := replace(l_token_req_payload, '#CODE#', code);
      l_token_req_payload := replace(l_token_req_payload, '#CLIENT_ID#', g_client_id);
      l_token_req_payload := replace(l_token_req_payload, '#CLIENT_SECRET#', g_client_secret);
      l_token_req_payload := replace(l_token_req_payload, '#REDIRECT_URI#', g_redirect_uri);
      l_token_req_payload := replace(l_token_req_payload, '#GRANT_TYPE#', g_token_grant_type);

      utl_http.set_wallet(
        path => 'file:/home/oracle/app/oracle/product/11.2.0/dbhome_2/owm/wallets/oracle'
      , password => 'helloworld9');
      
      l_token_req := utl_http.begin_request(
        url => g_token_url
      , method => 'POST');
  
      utl_http.set_header(
        r => l_token_req
      , name => 'Content-length'
      , value => length(l_token_req_payload));
  
      
      utl_http.set_header(
        r => l_token_req
      , name => 'Content-Type'
      , value => 'application/x-www-form-urlencoded');
  
      utl_http.write_text(
        r => l_token_req
      , data => utl_url.escape(l_token_req_payload));
  
      
      l_token_res := utl_http.get_response(
        r => l_token_req);
  
      BEGIN
  
        LOOP
          
          utl_http.read_line(
            r => l_token_res
          , data => l_response_tmp
          , remove_crlf => FALSE);
          l_response := l_response || l_response_tmp;
          
        END LOOP;
      
      EXCEPTION
        WHEN
          UTL_HTTP.END_OF_BODY
            THEN
            
              utl_http.end_response(
                r => l_token_res);
      END;
     
     l_response_json := JSON(l_response);
     update app_user
     set access_token = json_ext.get_string(
                        l_response_json
                      , 'access_token')
                                    
      where id_token = l_unescaped_state;                  
      
    end if;
    
    update app_user
    set id_token = NULL,
    originating_url = NULL
    where id_token = l_unescaped_state;
    
    
     owa_util.redirect_url(
      curl => l_endpoint_url);
    exception
      when others
        then
          dbms_output.put_line(utl_http.get_detailed_sqlerrm);
          raise;
          
  
  end authorization_code_callback;

Obviously, I should be handling if an error comes back. The code that returns here is not the access code, it is a preliminary code that you need to send in another request to get the actual access code. Once the access code is returned, it updates the app_user table to clear the unused info and redirects the user to where they were to begin from (I haven't implemented any way to notify the user that the request was successful or not).

I mentioned earlier on about not implementing getting a refresh token. This would be advised, as it would avoid un-necessary user intervention to get a new code. In the initial redirect, you just need to pass another parameter access_type with a value of offline (default is online), and you will get a refresh token. Then you can easily get a new aaccess token by passing the refresh token accross. For more information, see this: https://developers.google.com/accounts/docs/OAuth2WebServer#offline 

For all API calls, I wrote the following wrapper function:

function authorized_request(
      p_access_token in app_user.access_token%type
    , p_url in varchar2
    , p_method in varchar2 default 'GET'
    , p_content_type in varchar2 default 'application/json'
    , p_payload in varchar2 default NULL
    , p_wallet_path in varchar2 default NULL
    , p_wallet_password in varchar2 default ''
    )
  return CLOB
  AS
    l_req utl_http.req;
    l_res utl_http.resp;
    l_return CLOB;
    l_response_tmp varchar2(1024);
  BEGIN
  
    if p_wallet_path IS NULL THEN
      utl_http.set_Wallet(
        path => g_wallet_path
      , password => g_wallet_password);
    
    else
      utl_http.set_Wallet(
        path => p_wallet_path
      , password => p_wallet_password);
      
    END IF;
    
    
    l_req := utl_http.begin_request(
      url => p_url
    , method => p_method);
    
    utl_http.set_header(
      r => l_req
    , name => 'Content-Type'
    , value => p_content_type);
    
    utl_http.set_header(
      r => l_req
    , name => 'Authorization'
    , value => 'Bearer ' || p_access_token);
    
    if p_payload is not null then
    
      utl_http.set_header(
        r => l_req
      , name => 'Content-Length'
      , value => length(p_payload));  
      
      utl_http.write_text(
        r => l_req
      , data => p_payload);
    
    end if;
    
    l_res := utl_http.get_response(
      r => l_req);
    
    BEGIN
      LOOP
        utl_http.read_line(
          r => l_res
        , data => l_response_tmp
        , remove_crlf => FALSE);
        l_return := l_return || l_response_tmp;
      END LOOP;
    EXCEPTION
      WHEN
        UTL_HTTP.END_OF_BODY
          THEN
            utl_http.end_response(
              r => l_res);
    END;

    return l_return;

  END authorized_request;

Before performing any requests on your google data, you need to know what calendar ID to pass with each request. As mentioned, I set up a column in the app_user table for this purpose. The actual API call this relates to is documented here: https://developers.google.com/google-apps/calendar/v3/reference/calendarList/list

I have implemented with the following function(s):

 --Refer to: https://developers.google.com/google-apps/calendar/v3/reference/calendarList/list
  function get_calendar_list(
    p_username in app_user.username%type)
  return varchar2
  as
    lc_request_url CONSTANT varchar2(200) := 'https://www.googleapis.com/calendar/v3/users/me/calendarList';
    l_calender_list_req utl_http.req;
    l_calender_list_res utl_http.resp;
    l_response CLOB;
    l_response_tmp varchar2(1024);
    
    l_access_token app_user.access_token%type;
  BEGIN

    l_response := authorized_request(
                      p_access_token => get_access_token(p_username)
                    , p_url => lc_request_url
                    );
    
    return l_response;
    
  END get_calendar_list;

So that I can easily query the data with: select column_value from table(pipe_calendar_ids(:APP_USER)), create a pipelined function:  

 function pipe_calendar_ids(p_username in app_user.username%type) return t_varchar2 pipelined
  as
    l_json JSON;
    l_json_temp JSON;
    l_calendars JSON_LIST;
  begin
    
    l_json := JSON(get_calendar_list(p_username));
    
    if l_json.exist('items') then
     
      l_calendars := JSON_LIST(l_json.get('items'));
      for i in 1..l_calendars.COUNT LOOP
    
        l_json_temp := JSON(l_calendars.get(i));
        pipe row(json_ext.get_string(l_json_temp, 'id'));
    
      END LOOP;

    else
    
      pipe row('No calendars found. Try re-authorizing');
      
    end if;

  end pipe_calendar_ids;

Then, I wanted to set up to main functions for a start:

  • Listing Events
  • Adding Events
First the functions:

  --Refer to: https://developers.google.com/google-apps/calendar/v3/reference/events/list
  function list_calendar_events(
    p_username in app_user.username%type)
  return CLOB
  as
    l_request_url varchar2(200) := 'https://www.googleapis.com/calendar/v3/calendars/#ID#/events';
    l_calendar_id app_user.calendar_id%type;
    l_response CLOB;
    
  BEGIN
      
    l_calendar_id := get_calendar_id(p_username);  
    l_request_url := replace(l_request_url, '#ID#', l_calendar_id);
    
    l_response := authorized_request(
                  p_access_token => get_access_token(p_username)
                , p_url => l_request_url
                );
      
    return l_response;
    
  END list_calendar_events;

Then, to actually render the returned data, I figured jQuery fullCalendar would be the best bet, so I made a procedure to output the events in its expected format:

procedure fullcalendar_events(
    p_username in app_user.username%type
    --fullCalendar fields. Could use these to improve performance, only fetching
    --events betwween start and end
  , "start" in varchar2 default null
  , "end" in varchar2 default null)
  AS
    l_response CLOB;
    l_return varchar2(32767);
    l_response_json JSON;
    l_events JSON_LIST;
    l_event JSON;
    l_event_title varchar2(255);
    l_start JSON;
    l_start_value varchar2(30);
    l_end JSON;
    l_end_value varchar2(30);
    lc_date_time_name varchar2(20) := 'dateTime';
    lc_date_name varchar2(20) := 'date';
    l_newline varchar2(2) := chr(10);
  BEGIN
  
    l_response := list_calendar_events(p_username);
    
    l_response_json := JSON(l_response);
    
    if l_response_json.exist('items') then
    
      l_events := json_list(l_response_json.get('items'));
      l_return := '[';
      for i in 1..l_Events.COUNT LOOP
        l_event := JSON(l_events.get(i));
        l_event_title := json_ext.get_string(l_event, 'summary');
        -- Some of my events have weird encoding - remove new line and extra space
        -- not sure if pl/json has a good way to handle json encoding - need to investigate
        l_event_title := regexp_replace(l_event_title, '[[:space:]]  *', ' ');
        l_start := json_ext.get_json(l_event, 'start');
        --assume not all day
        l_start_value := json_ext.get_string(l_start, lc_date_time_name);
        if l_start_value is null then
          l_start_value := json_ext.get_string(l_start, lc_date_name);
        end if;
        l_end := json_ext.get_json(l_event, 'end');
        
        l_return := l_return || '{ ';
        l_return := l_return || '"title" : "' || l_event_title || '", ' || l_newline;
        l_return := l_return || '"start" : "' ||l_start_value || '" ' || l_newline;
        l_return := l_return || ' }, ';
        
      END LOOP;
      
      l_return := rtrim(l_return, ', ');
      l_return := l_return || ']';
    end if;
    
    htp.p( l_return );
  
  END fullcalendar_Events;


I then uploaded the necessary files to the Application, and created a HTML region with:

<div id="cal"></div>
<link rel='stylesheet' type='text/css' href='#APP_IMAGES#fullcalendar.css' />
<script type='text/javascript' src='#APP_IMAGES#fullcalendar.min.js'></script>
<script type='text/javascript' src='#APP_IMAGES#gcal.js'></script>
<script type="text/javascript">
$(document).ready(function(){

    region = $('#cal');
        region.fullCalendar({
        editable: false,
        events: "#OWNER#.google_utl.fullcalendar_events?p_username=&APP_USER."
    });

});
</script>

It does take a little while to fetch all the data, so it would be worthwhile filtering the data for the datarange rendered on the current page, since it does a new call with each view change.

I verified it worked by created a couple of bogus events:


The final bit to add was adding an event. I haven't gone all out and implemented time based events, only date ranges.

 procedure add_event(
    p_username in app_user.username%type
  , p_start_date in DATE
  , p_end_date in DATE
  , p_summary in varchar2)
  AS
    l_request_url varchar2(200) := 'https://www.googleapis.com/calendar/v3/calendars/#ID#/events?sendNotifications=false';
    l_calendar_id app_user.calendar_id%type;
    l_response CLOB;
    l_payload JSON;
    l_json_temp JSON;
    
  BEGIN
    l_json_temp := JSON;
    l_payload := JSON;
    l_payload.put('summary', p_summary);
    l_json_temp.put('date', to_char(p_start_date, 'yyyy-mm-dd'));
    l_payload.put('start', l_json_temp);
    l_json_temp := JSON;
    l_json_temp.put('date', to_char(p_end_date, 'yyyy-mm-dd'));
    l_payload.put('end', l_json_Temp);
    dbms_output.put_line(l_payload.to_char);
    
    l_calendar_id := get_calendar_id(p_username);  
    l_request_url := replace(l_request_url, '#ID#', l_calendar_id);
    
    l_response := authorized_request(
      p_access_token => get_access_token(p_username)
    , p_url => l_request_url
    , p_method => 'POST'
    , p_payload => l_payload.to_char
    );
    
  END add_event;

Then in the page, I just have the following process:

google_utl.add_event(:APP_USER, :P4_START_DATE, :P4_END_DATE, :P4_SUMMARY);


Well, that's basically my implementation. There is the obvious security issue of only passing in the username, but there are surely ways to get around that. 

Anyway, here is the full source in its glory, of the package. You would obviously need to update the values:

  • g_client_id 
  • g_client_secret 
  • g_redirect_uri 

create or replace PACKAGE "GOOGLE_UTL" 
as

  type t_varchar2 is table of varchar2(50);

  procedure get_authorization_url(
    p_state in varchar2
  , p_url in varchar2);


  procedure authorization_code_callback(
    code in varchar2 default NULL, 
    error in varchar2 default NULL,
    state in varchar2 default NULL); 
    
  function pipe_calendar_ids(p_username in app_user.username%type)
  return t_varchar2 pipelined;
    
  function get_calendar_list(
    p_username in app_user.username%type)
  return varchar2;  
  
  function list_calendar_events(
    p_username in app_user.username%type)
  return CLOB;  
  
  procedure fullcalendar_events(
    p_username in app_user.username%type
  , "start" in varchar2 default null
  , "end" in varchar2 default null);
  
  procedure add_event(
    p_username in app_user.username%type
  , p_start_date in DATE
  , p_end_date in DATE
  , p_summary in varchar2);

end google_utl;
/

create or replace PACKAGE BODY "GOOGLE_UTL" 
as

  g_auth_url varchar2(400) := 'https://accounts.google.com/o/oauth2/auth';
  g_token_url varchar2(400) := 'https://accounts.google.com/o/oauth2/token';
  
  g_wallet_path varchar2(400) := 'file:/home/oracle/app/oracle/product/11.2.0/dbhome_2/owm/wallets/oracle';
  g_wallet_password varchar2(400) := '';
  
  g_redirect_uri varchar2(400) := 'http://example.com:8888/apex/google.google_utl.authorization_code_callback';
  
  g_client_id varchar2(400) := '';
  g_client_secret varchar2(400) := '';
  
  g_token_grant_type varchar2(20) := 'authorization_code';
  
  function authorized_request(
      p_access_token in app_user.access_token%type
    , p_url in varchar2
    , p_method in varchar2 default 'GET'
    , p_content_type in varchar2 default 'application/json'
    , p_payload in varchar2 default NULL
    , p_wallet_path in varchar2 default NULL
    , p_wallet_password in varchar2 default ''
    )
  return CLOB
  AS
    l_req utl_http.req;
    l_res utl_http.resp;
    l_return CLOB;
    l_response_tmp varchar2(1024);
  BEGIN
  
    if p_wallet_path IS NULL THEN
      utl_http.set_Wallet(
        path => g_wallet_path
      , password => g_wallet_password);
    
    else
      utl_http.set_Wallet(
        path => p_wallet_path
      , password => p_wallet_password);
      
    END IF;
    
    
    l_req := utl_http.begin_request(
      url => p_url
    , method => p_method);
    
    utl_http.set_header(
      r => l_req
    , name => 'Content-Type'
    , value => p_content_type);
    
    utl_http.set_header(
      r => l_req
    , name => 'Authorization'
    , value => 'Bearer ' || p_access_token);
    
    if p_payload is not null then
    
      utl_http.set_header(
        r => l_req
      , name => 'Content-Length'
      , value => length(p_payload));  
      
      utl_http.write_text(
        r => l_req
      , data => p_payload);
    
    end if;
    
    l_res := utl_http.get_response(
      r => l_req);
    
    BEGIN
      LOOP
        utl_http.read_line(
          r => l_res
        , data => l_response_tmp
        , remove_crlf => FALSE);
        l_return := l_return || l_response_tmp;
      END LOOP;
    EXCEPTION
      WHEN
        UTL_HTTP.END_OF_BODY
          THEN
            utl_http.end_response(
              r => l_res);
    END;

    return l_return;
  END authorized_request;
  
  FUNCTION get_Access_token(
    p_username in app_user.username%type)
  return app_user.access_token%type
  as
    l_access_token app_user.access_token%type;
  BEGIN
  
    select access_token into l_Access_token
    from app_user
    where username = p_username;
    
    return l_access_token;
  
  end get_Access_token;
  
  function get_calendar_id(
    p_username in app_user.username%type)
  return app_user.calendar_id%type
  AS
    l_calendar_id app_user.calendar_id%type;
  BEGIN
  
    select calendar_id into l_calendar_id
    from app_user
    where username = p_username;
    
    return l_calendar_id;
  
  END get_calendar_id;

  --Refer to docs: https://developers.google.com/accounts/docs/OAuth2WebServer
  procedure get_authorization_url(
    p_state in varchar2
  , p_url in varchar2)
  as
    l_url_params varchar2(400);
    l_state app_user.id_token%type;
    l_url app_user.originating_url%type;
  BEGIN
    l_state := utl_url.unescape(p_state);
    l_url := utl_url.unescape(p_url);
    
    update app_user
    set originating_url = l_url
    where id_token = l_State;
    
    l_url_params := 
      'response_type=#RESPONSE_TYPE#&client_id=#CLIENT_ID#&redirect_uri=#REDIRECT_URI#&scope=#SCOPE#&state=#STATE#';
      
    l_url_params := replace(l_url_params, '#RESPONSE_TYPE#', 'code');  
    l_url_params := replace(l_url_params, '#CLIENT_ID#', g_client_id);
    l_url_params := replace(l_url_params, '#REDIRECT_URI#', g_redirect_uri);
    l_url_params := replace(l_url_params, '#SCOPE#', 'https://www.googleapis.com/auth/calendar');
    l_url_params := replace(l_url_params, '#STATE#', p_state);
  
    htp.p(g_auth_url || '?' || l_url_params);
  END get_authorization_url;
  
  procedure authorization_code_callback(
    code in varchar2 default NULL, 
    error in varchar2 default NULL,
    state in varchar2)
  as
  
    l_token_req utl_http.req;
    l_token_res utl_http.resp;
    
    l_token_req_payload varchar2(4000) := 
      'code=#CODE#&client_id=#CLIENT_ID#&client_secret=#CLIENT_SECRET#&redirect_uri=#REDIRECT_URI#&grant_type=#GRANT_TYPE#';
    
    l_response CLOB;
    l_response_tmp varchar2(1024);
    l_response_json JSON;
    
    l_unescaped_state APP_USER.ID_TOKEN%type;
    l_endpoint_url APP_USER.ORIGINATING_URL%type;
    
  begin
    
    l_unescaped_state := utl_url.unescape(state);
    
    select originating_url into l_endpoint_url
    from app_user
    where id_token = l_unescaped_state;
    
    --code was returned, get the token
    if code is not null then
      l_token_req_payload := replace(l_token_req_payload, '#CODE#', code);
      l_token_req_payload := replace(l_token_req_payload, '#CLIENT_ID#', g_client_id);
      l_token_req_payload := replace(l_token_req_payload, '#CLIENT_SECRET#', g_client_secret);
      l_token_req_payload := replace(l_token_req_payload, '#REDIRECT_URI#', g_redirect_uri);
      l_token_req_payload := replace(l_token_req_payload, '#GRANT_TYPE#', g_token_grant_type);

      utl_http.set_wallet(
        path => 'file:/home/oracle/app/oracle/product/11.2.0/dbhome_2/owm/wallets/oracle'
      , password => 'helloworld9');
      
      l_token_req := utl_http.begin_request(
        url => g_token_url
      , method => 'POST');
  
      utl_http.set_header(
        r => l_token_req
      , name => 'Content-length'
      , value => length(l_token_req_payload));
  
      
      utl_http.set_header(
        r => l_token_req
      , name => 'Content-Type'
      , value => 'application/x-www-form-urlencoded');
  
      utl_http.write_text(
        r => l_token_req
      , data => utl_url.escape(l_token_req_payload));
  
      
      l_token_res := utl_http.get_response(
        r => l_token_req);
  
      BEGIN
  
        LOOP
          
          utl_http.read_line(
            r => l_token_res
          , data => l_response_tmp
          , remove_crlf => FALSE);
          l_response := l_response || l_response_tmp;
          
        END LOOP;
      
      EXCEPTION
        WHEN
          UTL_HTTP.END_OF_BODY
            THEN
            
              utl_http.end_response(
                r => l_token_res);
      END;
     
     l_response_json := JSON(l_response);
     update app_user
     set access_token = json_ext.get_string(
                        l_response_json
                      , 'access_token')
                                    
      where id_token = l_unescaped_state;                  
      
    end if;
    
    update app_user
    set id_token = NULL,
    originating_url = NULL
    where id_token = l_unescaped_state;
    
    
     owa_util.redirect_url(
      curl => l_endpoint_url);
    exception
      when others
        then
          dbms_output.put_line(utl_http.get_detailed_sqlerrm);
          raise;
          
  
  end authorization_code_callback;
  
  --Refer to: https://developers.google.com/google-apps/calendar/v3/reference/calendarList/list
  function get_calendar_list(
    p_username in app_user.username%type)
  return varchar2
  as
    lc_request_url CONSTANT varchar2(200) := 'https://www.googleapis.com/calendar/v3/users/me/calendarList';
    l_calender_list_req utl_http.req;
    l_calender_list_res utl_http.resp;
    l_response CLOB;
    l_response_tmp varchar2(1024);
    
    l_access_token app_user.access_token%type;
  BEGIN

    l_response := authorized_request(
                      p_access_token => get_access_token(p_username)
                    , p_url => lc_request_url
                    );
    
    return l_response;
    
  END get_calendar_list;
  
  function pipe_calendar_ids(p_username in app_user.username%type) return t_varchar2 pipelined
  as
    l_json JSON;
    l_json_temp JSON;
    l_calendars JSON_LIST;
  begin
    
    l_json := JSON(get_calendar_list(p_username));
    
    if l_json.exist('items') then
     
      l_calendars := JSON_LIST(l_json.get('items'));
      for i in 1..l_calendars.COUNT LOOP
    
        l_json_temp := JSON(l_calendars.get(i));
        pipe row(json_ext.get_string(l_json_temp, 'id'));
    
      END LOOP;

    else
    
      pipe row('No calendars found. Try re-authorizing');
      
    end if;

  end pipe_calendar_ids;
  
  --Refer to: https://developers.google.com/google-apps/calendar/v3/reference/events/list
  function list_calendar_events(
    p_username in app_user.username%type)
  return CLOB
  as
    l_request_url varchar2(200) := 'https://www.googleapis.com/calendar/v3/calendars/#ID#/events';
    l_calendar_id app_user.calendar_id%type;
    l_response CLOB;
    
  BEGIN
      
    l_calendar_id := get_calendar_id(p_username);  
    l_request_url := replace(l_request_url, '#ID#', l_calendar_id);
    
    l_response := authorized_request(
                  p_access_token => get_access_token(p_username)
                , p_url => l_request_url
                );
      
    return l_response;
    
  END list_calendar_events;
  
  procedure fullcalendar_events(
    p_username in app_user.username%type
    --fullCalendar fields. Could use these to improve performance, only fetching
    --events betwween start and end
  , "start" in varchar2 default null
  , "end" in varchar2 default null)
  AS
    l_response CLOB;
    l_return varchar2(32767);
    l_response_json JSON;
    l_events JSON_LIST;
    l_event JSON;
    l_event_title varchar2(255);
    l_start JSON;
    l_start_value varchar2(30);
    l_end JSON;
    l_end_value varchar2(30);
    lc_date_time_name varchar2(20) := 'dateTime';
    lc_date_name varchar2(20) := 'date';
    l_newline varchar2(2) := chr(10);
  BEGIN
  
    l_response := list_calendar_events(p_username);
    
    l_response_json := JSON(l_response);
    
    if l_response_json.exist('items') then
    
      l_events := json_list(l_response_json.get('items'));
      l_return := '[';
      for i in 1..l_Events.COUNT LOOP
        l_event := JSON(l_events.get(i));
        l_event_title := json_ext.get_string(l_event, 'summary');
        -- Some of my events have weird encoding - remove new line and extra space
        -- not sure if pl/json has a good way to handle json encoding - need to investigate
        l_event_title := regexp_replace(l_event_title, '[[:space:]]  *', ' ');
        l_start := json_ext.get_json(l_event, 'start');
        --assume not all day
        l_start_value := json_ext.get_string(l_start, lc_date_time_name);
        if l_start_value is null then
          l_start_value := json_ext.get_string(l_start, lc_date_name);
        end if;
        l_end := json_ext.get_json(l_event, 'end');
        
        l_return := l_return || '{ ';
        l_return := l_return || '"title" : "' || l_event_title || '", ' || l_newline;
        l_return := l_return || '"start" : "' ||l_start_value || '" ' || l_newline;
        l_return := l_return || ' }, ';
        
      END LOOP;
      
      l_return := rtrim(l_return, ', ');
      l_return := l_return || ']';
    end if;
    
    htp.p( l_return );
  
  END fullcalendar_Events;
  
  procedure add_event(
    p_username in app_user.username%type
  , p_start_date in DATE
  , p_end_date in DATE
  , p_summary in varchar2)
  AS
    l_request_url varchar2(200) := 'https://www.googleapis.com/calendar/v3/calendars/#ID#/events?sendNotifications=false';
    l_calendar_id app_user.calendar_id%type;
    l_response CLOB;
    l_payload JSON;
    l_json_temp JSON;
    
  BEGIN
    l_json_temp := JSON;
    l_payload := JSON;
    l_payload.put('summary', p_summary);
    l_json_temp.put('date', to_char(p_start_date, 'yyyy-mm-dd'));
    l_payload.put('start', l_json_temp);
    l_json_temp := JSON;
    l_json_temp.put('date', to_char(p_end_date, 'yyyy-mm-dd'));
    l_payload.put('end', l_json_Temp);
    dbms_output.put_line(l_payload.to_char);
    
    l_calendar_id := get_calendar_id(p_username);  
    l_request_url := replace(l_request_url, '#ID#', l_calendar_id);
    
    l_response := authorized_request(
      p_access_token => get_access_token(p_username)
    , p_url => l_request_url
    , p_method => 'POST'
    , p_payload => l_payload.to_char
    );
    
  END add_event;

end google_utl;
/


Monday, 3 September 2012

Redirect to tab from LOV

For this basic example, let me use it to re-direct to one of my tabs in my example application. I have Plugins, Google, Comments. Their submit values are T_PLUGINS, T_GOOGLE, T_COMMENTS respectively. Of course, this example was designed with the fact of apex set up with the tabs infrastructure and not lists - and to get to a specific tab, you submit the page with the tab name.

Normally, I would use a LOV from an SQL query, however for this basic example, let me just create a static LOV query.

Create the LOV in the shared components - you can find out more information about this in the builders guide - see: http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldapp_lov.htm#HTMDB25450

Specify name as PAGE_TABS and type as Static.

From here we want to set the display value as something that the user will see, and the return value as something we can use to. Se specify the display value as the tab display value, and the return value as the tab name.



Once done, on your page where you want to the redirect to take place, add a page item of type select list.





Then specify the List of Values as the name defined in the earlier step.



Now, to re-direct the tab, we'll use a dynamic action. On your page definition, right click on the item name, and specify Create Dynamic Action.



Specify a meaningful name. Leave the event as Change, Selection type as Item, and Item(s) as the select list that will re-direct when changed.

Clear the checkbox Fire on page load, and specify the action as Execute JavaScript code and then specify the code as:

apex.submit(this.triggeringElement.value)

Then create the dynamic action. Test your work and enjoy!

See: http://apex.oracle.com/pls/apex/f?p=45448:22


Saturday, 23 June 2012

Two Apex 4.2 Noteworthy APIs

Well, yesterday oracle released the first early adopter of application express, having had a chance to have a little play around, these are the ones that caught my attention:

APEX_IR

See: http://apex.oracle.com/pls/apex/f?p=38997:1:0::NO:RP,1:P1_MARQUEE_FEATURE:Interactive%20Report%20Enhancements

There are currently some interactive reports utility functions/procedures as part of the apex_util package. These will now be deprecated, and functionality will be added to a package named APEX_IR.

The most exciting addition is the ability to get the derived IR query (from added filters/sorts). Without any published API docs, I can only assume this is through the function get_report. Further, I set up an example on page 2 of the sample database application. Created a new popup page with a dynamic region with the following source:

declare

 l_report apex_ir.t_report;

begin

    l_report := apex_ir.get_report(
        p_region_id => 6506762112827941367,
        p_page_id => 2);

    htp.p(l_report.sql_query);
    htp.p('<br />');htp.p('<br />');
    htp.p('<p><strong>BIND VARIABLES:</strong>');
    htp.p('<br />');
    htp.p('<br />');
    for i in 1..l_report.binds.COUNT LOOP
        htp.p('name: ' || l_report.binds(i).name || '<br />');
        htp.p('value: ' || l_report.binds(i).value || '<br />');
        htp.p('<br/>');
    END LOOP;

    htp.p('</p>');

end;

Where I got the p_region_id by querying the APEX_APPLICATION_PAGE_REGIONS data dictionary.

Then I added a button to page 2, to the right of the interactive report search "Show Source" pointing to that new page.

See: https://apexea.oracle.com/pls/apex/f?p=502:2

AJAX API

See: http://apex.oracle.com/pls/apex/f?p=38997:1:0::NO:RP,1:P1_MARQUEE_FEATURE:JavaScript%20%2F%20API%20Enhancements

Well, any experienced apex developer will no doubt have used the undocumented htmldb_Get object to perform some form of AJAX request. I know I often use it to submit values of items to session state. With 4.2 comes some additions to the javascript API.

The new objects include:

apex.server.plugin
apex.server.pluginUrl
apex.server.process
apex.widget.util.cascadingLov

The one I am most interested in is the apex.server.process. So how do we use it? Typing in the function name into the javascript console, reveals the following:

function ( pName, pData, pOptions ) {
    return _call( "APPLICATION_PROCESS=" + pName, pData, pOptions );
}

Obviously, pName is the name of the process if you want to submit, pData would be session items you would like to set, and can include the x0n variables you may be used to.

If you dig into the source a bit deeper, you will see that to set page items can be set by passing in an array of page item names with the property name: pageItems. Alternatively, if you want some other derived value you would pass respective values to p_arg_names and p_arg_values (arrays). Also you can set the x0n variables, which are referred to in the app process by wwv_flow.g_x0n. These are all passed into the pData object (2nd parameter).

Then there is the pOptions object. These are the properties you would expect to see in the jQuery AJAX API. Some cases I can imagine you would like to specify is the success/error callback(s), and specifying the dataType in case you would like to return text that is not JSON (as JSON is the default dataType specified).

So, some examples:

Submitting a page item, with the current value:

apex.server.process('ProcessName', {pageItems: ['P2_TEST'], x01: 'temp value'});

If you are returning text from the server, that is not json, i would suggest changing the type in the pOptions.

var respObj = apex.server.process('ProcessName', {pageItems: ['P2_TEST'], x01: 'temp value'}, {dataType: 'text'});
console.log(respObj.responseText);

Otherwise, you will get a Parse Error / Syntax Error (as it is not in valid JSON).

Submitting session state to both a current page item, and an item not on the page (e.g. app item).

var respObj = apex.server.process('ProcessName', {p_arg_names: ['TEST_ITEM'], p_arg_values: ['Some value'], pageItems: ['P2_TEST'], x01: 'temp value'}, {dataType: 'text'});
console.log(respObj.responseText);

On demand process used for testing:

begin

    htp.p('App Item: ' || :TEST_ITEM || ' Page Item: ' || :P2_TEST || ' x Item: ' || wwv_flow.g_x01 );

end;

Anyways, seems good. This is not based on any docs, so there may be some better recommendations come out in time. Happy coding!

Thursday, 14 June 2012

Enhancing gedit for PL/SQL Development

Most text editors allow customisation of syntax highlighting and the like, and having recently moved to using gedit, thought i'd enhance it a bit. In a fresh installation, it already has reasonable support for oracle based keywords, but could do with a bit of an update.

The object that does the syntax highlighting is gtksourceview - depending on the version of gedit, either version 2 or 3. 

By going to the project page on gnome, you will see the howto for adding a new language: https://live.gnome.org/Gedit/NewLanguage. (Linux only) Since package source code is typically in files with the extension pks and pkb, it is first necessary to update the system to recognise that those files are of the mime type text/x-sql (or a custom one if you really want). So, create an xml file with the following:

<mime-info xmlns="http://www.freedesktop.org/standards/shared-mime-info">
  <mime-type type="text/x-sql">
    <comment>SQL Source including PL/SQL</comment>
    <glob pattern="*.sql"></glob>
    <glob pattern="*.pks"></glob>
    <glob pattern="*.pkb"></glob>
  </mime-type>
</mime-info>

This can be saved in two locations. Either:

  • /usr/share/mime/packages
  • ~/.local/share/mime/packages
I saved mine into the former path. Once done, you need to update the mime database:

sudo update-mime-database /usr/share/mime

Now, when you open those two new file types in gedit, it will recognise them as text/x-sql files, and apply the correct language.

The next thing you'll notice, is that there are a few keywords that aren't having syntax highlighting applied. We can update this word list so particular words are highlighted accordingly. Make a backup of the sql.lang file and edit the sql.lang file to add new words:

sudo cp /usr/share/gtksourceview-3.0/language-specs/sql.lang /usr/share/gtksourceview-3.0/language-specs/sql.lang.backup1
sudo gedit /usr/share/gtksourceview-3.0/language-specs/sql.lang

The first section of the file lists the metadata for which files this configuration should apply to. Update the globs property to the file pattern as specified in the mime type configuration file above, so it should read:

<metadata>
    <property name="mimetypes">text/x-sql</property>
    <property name="globs">*.sql;*.pks;*.pkb</property>
    <property name="line-comment-start">--</property>
</metadata>

Then, we need a new context section with the list of words that should get highlighted. To do this, I ran the following query:

select
  '<keyword>' || keyword|| '</keyword>' keyword
from
  v$reserved_words
where
  length(keyword) > 2
order by
  keyword

I then exported that data, enclosed in the xml property:

<context id="oracle-updated-keywords" style-ref="keyword">

Added it to the file we already have opened (sql.lang), and updated the context with the same id as the language, so it has the entry within the include property (at the bottom of the config file). So it should be looking like:

<context id="sql" class="no-spell-check">
      <include>
        <context ref="oracle-built-in-datatypes"/>
        <!-- Other contexts removed for readability of this article -->
        <context ref="oracle-updated-keywords"/>
      </include>
</context>

Restart gedit and open a file with one of those extensions (or manually set the language) and it should now have those keywords highlighted.

More than likely, that list of keywords have been specified earlier in the language file - from what I can tell, gtksourceview takes the first style for a particular keyword it finds. This could obviously be extended further to include packages, procedures and functions.

It's worth noting, a custom language file could also be placed into: ~/.local/share/gtksourceview-3.0/language-specs.

I've chucked the files onto github: https://github.com/trent-/gedit-sql

Other language files:

https://live.gnome.org/Gedit/HighlighterAndBundles
https://live.gnome.org/GtkSourceView/LanguageDefinitions

Monday, 4 June 2012

Saving Files Client Side

A couple of html5 interfaces that allow you to download files from the client side are:
These haven't quite made it into the browsers, but there are projects on GitHub for both of these interfaces, whilst we wait for them to become main stream. The basic examples on the FileSaver project README are saving both a text file based on an input string, and saving a canvas as an image file (you can already redirect the user to the data url of the canvas object, but this would actually download the file). See: https://github.com/eligrey/FileSaver.js and https://github.com/eligrey/BlobBuilder.js. Anyways, i'll give a little focus on saving a file.

 I have a basic page with a text field to be used as the file name (:P20_FILE_NAME) and a text area with the data that I want to save to a file (:P20_EXPORT_CONTENT). First I need to add a reference to the two interfaces (I just added these into the page header - obviously better to import these files into the app):

<script src="https://raw.github.com/eligrey/FileSaver.js/master/FileSaver.min.js" type="text/javascript">
</script>
<script src="https://raw.github.com/eligrey/BlobBuilder.js/master/BlobBuilder.min.js" type="text/javascript">
</script>

Then we write a function to save that will save the data:

var exportData = function(){
    //Set to download.txt if nothing was specified
    var fileName = $v("P20_FILE_NAME") || "Download.txt";
    var exportContents = $('#P20_EXPORT_DATA').val();
    //notepad.exe doesnt like printing new lines. http://stackoverflow.com/a/863806, http://stackoverflow.com/a/5558032
    exportContents = exportContents.replace(/\n\r?/g, '\r\n');
    var bb = new BlobBuilder();
    bb.append(exportContents);
    saveAs(bb.getBlob("plain/text;charset=UTF-8"), fileName);
}

Along with a button (Save) pointing to the function to initiate the download. See an example: http://apex.oracle.com/pls/apex/f?p=45448:20. It's probably worth mentioning, because the scripts are loaded directly from github in this example. it won't work properly in IE(9). See  http://blogs.msdn.com/b/ieinternals/archive/2010/09/27/ie9-beta-google-image-search-javascript-content-type-and-nosniff.aspx 

Friday, 1 June 2012

Error Handling API

Originally heard about this from Patrick's blog months back. See http://www.inside-oracle-apex.com/apex-4-1-error-handling-improvements-part-1/ and http://www.inside-oracle-apex.com/apex-4-1-error-handling-improvements-part-2/. Then a couple of weeks back I was looking at the new features on the Apex builder guide, where they mention about the enhanced error handling. See http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/what_new.htm#CEGIEDHE. So I thought I may as well set up a basic example to see how it is.

The only sections in the documentation that really talk about it are:


Which is basically about setting the error handling (PL/SQL) function. The main source of information is on the API docs for apex_error. This page talks about data types, and available functions/procedures you can use with this package, which will allow customising the displayed error message.

Whilst the API docs do provide a good example, I just wanted to have a play on my own to see it in action. First set up a table with a unique constraint:


CREATE TABLE  "UNIQUE_PEOPLE" 
   ( "ID" NUMBER, 
 "NAME" VARCHAR2(200), 
  CONSTRAINT "UNIQUE_PEOPLE_PK" PRIMARY KEY ("ID") ENABLE, 
  CONSTRAINT "UNIQUE_PEOPLE_UK1" UNIQUE ("NAME") ENABLE
   )
/

CREATE SEQUENCE UNIQUE_PEOPLE_SEQ START WITH 1 INCREMENT BY 1;
/

CREATE OR REPLACE TRIGGER  "BI_UNIQUE_PEOPLE" 
  before insert on "UNIQUE_PEOPLE"               
  for each row  
begin   
  if :NEW."ID" is null then 
    select "UNIQUE_PEOPLE_SEQ".nextval into :NEW."ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_UNIQUE_PEOPLE" ENABLE
/

Then a function to check this validation:


create or replace function unique_person_check(
    p_error in apex_error.t_error )
    return apex_error.t_error_result
is
    l_result          apex_error.t_error_result;
    l_reference_id    number;
    l_constraint_name varchar2(255);
begin
    l_result := apex_error.init_error_result (
                    p_error => p_error );
 
    IF p_error.ora_sqlcode = -1 THEN --unique constraint
    
        if apex_error.extract_constraint_name(p_error) = 'UNIQUE_PEOPLE_UK1' THEN
        
            l_result.message := 'Person already exists. Please choose another';

        END IF;
    
    END IF;
    
    --fallback incase our testcase doesn't match anything
    
    if p_error.ora_sqlcode is not null and l_result.message = p_error.message then --no new message yet assigned. Must mean we haven't met the conditions above
    l_result.message := apex_error.get_first_ora_error_text(
            p_error => p_error);
    
    end if;
 
    return l_result;
end unique_person_check;​
/

Then set up a page with a tabular form for easy testing. Edit the page attributes and specify the error function as: unique_person_check. If this function was more of a global set up, you would just set this up in the application definition.

I set up a demo: http://apex.oracle.com/pls/apex/f?p=45448:19 - try and specify a name that is already in the list, and you should receive the error specified above. The example on the docs also does some logging for internal errors that aren't access denied errors, but I couldn't think of any use cases to test that, so left that alone for now. :-).


Thursday, 31 May 2012

Oracle HTTPS Requests: Set up the Wallet

In addition to setting up the ACL, if you wish to make HTTPS requests you will need to set up a wallet - and more than likely, import trusted certificates.

On the oracle server, open the wallet manager. In linux this is with the command: owm.

Assuming no current wallet exists, create a new one by going to File --> New. At this point you will need to give the wallet a password. Save the wallet and take note of the directory it installed to (this is what you need to pass to utl_http, and no single file). The default directory this is saved to is generally: $ORACLE_HOME/owm/wallets/oracle.

The next step is to import trusted certificates into the wallet. The process varies between OS/Browser. First, go to the website you are intending on making requests to and view the certificate information which is normally done by clicking a button in the address bar.

Linux Chrome:


  1. Click the button the left of the URL
  2. Click certificate information
  3. Click Details
  4. Click Export
  5. Specify the type as PKCS #7, certificate chain
  6. Save to a convenient location. I find it best to actually save it into the same folder as the oracle wallet, so you have a future reference
Windows Chrome:

  1. Click the button to the left of the URL
  2. Click certificate information
  3. Click Details
  4. Click Copy to File...
  5. Click Next
  6. Click Cryptographic Message Syntax Standard - PKCS #7 Certificates (.P7B)
  7. Click Next
  8. Save to a convenient location. I find it best to actually save it into the same folder as the oracle wallet, so you have a future reference
(The same dialogue can be got to in Internet Explorer by going to File --> Properties --> Certificates)

It is important to export the certificates in a type that allows you to export the full certificate chain, otherwise it is highly likely, and HTTPS requests will still not work.

To import the certificates into the wallet, from the wallet manager:

Right click on the tree node that reads Trusted Certificates, and select the option Import Trusted Certificate... 
Select the option Select a file that contains the certificates
Locate the file and click OK

If all went well, you should see at least 1 new certificate added to the node Trusted Certificates.

In any PL/SQL code, you then just need to pass in the wallet parameters - wallet directory, and wallet password (the wallet path parameter is used with the syntax "file:path", where path is the actual path the wallet directory). I normally do this with the set_wallet procedure, but the two properties are parameters on the functions/procedures to execute http requests (e.g. utl_http.begin_request).

Confirm the wallet is working with the following:

declare
 req utl_http.req;
 resp utl_http.resp;
 rw varchar2(32767);
begin

 utl_http.set_wallet('file:<path-to-wallet-directory>', '<wallet-password>');

 req := utl_http.begin_request( '<secure-url>');
 resp := utl_http.get_response(req);

 loop
  begin 
   rw := null; 
   utl_http.read_line(resp, rw, TRUE); 
   --do something with rw if you like
   exception when others 
    then 
     exit;
  end;
 end loop; 
 utl_http.end_response(resp);

end;

Wednesday, 30 May 2012

Oracle HTTP Requests: Set up the ACL

In order to use utl_http requests from Oracle 11g onward (or any network services for that matter), you need to set up an ACL. Two generic permissions to grant are connect and resolve, where * can be used as a wild card.

A good template I commonly use, just replacing the file name and schema name. This should be executed as a user with dba privileges.



DECLARE

 l_filename varchar2(200) := 'file_test_http.xml';
 l_schema varchar2(200) := 'FILE_TEST';

BEGIN

        BEGIN
            DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
                acl => l_filename
            );

  EXCEPTION WHEN OTHERS THEN
      NULL; -- ACL does not exist yet
 END;

        DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
              acl           => l_filename
            , description   => 'All requests to test utl_http'
            , principal     => l_schema -- schema name
            , is_grant      => TRUE
            , privilege     => 'connect'
        );

        DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
              acl       => l_filename
            , principal => l_schema -- schema name
            , is_grant  => TRUE
            , privilege => 'resolve'
        );

        DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
              acl   =>l_filename
            , host  => '*'
        );
END;
/
commit;

End User's Guide

Just noticed this on the documentation index.

Apex now has an end users guide: http://docs.oracle.com/cd/E23903_01/doc/doc.41/e26811/toc.htm

I'm not yet certain it's a good idea linking users to this documentation in any user guides you create for your apps, given Oracle seems to like changing the structure of their links every couple of years, leaving documentation links that no longer work. Even so, it's worth knowing :-)

Current sections include: