Calling API's from PL/SQL using UTL_HTTP

As Big data is becoming more and more important, also the data analytics tools get more and more popular and important. Such as Eloqua, Radian6(Salesforce), Omniture and Bit.ly. On the internet, lots of examples can be found how to call the API's, but mostly it concerns only Java, PHP or .Net. In this blogpost I will show you how to call the API's from PL/SQL.

There are several things you should pay attention to. As the API call mostly runs over HTTPS, you need to setup a wallet. Also, if you work on 11g or higher, you should setup an Access Control List (ACL). And if you are working behind a firewall, you should set a proxy in your PL/SQL code.

Set up a wallet

To setup a wallet, you need to do two things: download a certificate and setup the wallet. To download the certificate, you can use Google Chrome. In the url bar, enter the url for the API, for example https://login.eloqua.com.

Right-click on the green lock next to the url. A popup appears.

Click on the connection tab and click on the Certificate information link. Another popup appears.

Click on the middle path (Verisign Class 3 Extended Validation SSL CA) and click on the "View Certificate" button.

Click on the details tab and then click on the "Copy to file" button. Click next.

Select "Base-64 encoded" and click next.

Enter a file location and a filename. Click next, click finish.


Now that you have the certificate, you can setup the wallet. Go to a dos-box or a terminal session and create a directory, for example eloqua_wallet.

Enter the following commands:

orapki wallet create -wallet d:\eloqua_wallet -pwd <your_password> -auto_login

orapki wallet add -wallet d:\eloqua_wallet -trusted_cert -cert "d:\certificates\eloqua.cer" -pwd <your_password>

Where <your_password> is a password that you can make up yourself. This password will later on be used in the PL/SQL code. This way, you tell Oracle that there is a certificate held in a wallet and this certificate ensures that the site is safe to be called by the API. The reference to this wallet in the PL/SQL code:

utl_http.set_wallet('file:d:\eloqua_wallet', '<your_password>');

Set up ACL

When working with Oracle 11g or 12c, you should setup an ACL to tell Oracle that it is safe to connect to the API. With the following code, you could setup an ACL. You should run this script as the SYS user.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'eloqua_req.xml',
    description  => 'Permissions to access http://secure.eloqua.com',
    principal    => '<username>',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'eloqua_req.xml',               
    principal    => '<username>',
    is_grant     => TRUE,
    privilege    => 'connect',
    position     => null);
   COMMIT;
END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'eloqua_req.xml',               
    host         => 'secure.eloqua.com');
   COMMIT;
END;
/

Refer to a proxyserver

If you are behind a firewall, your Oracle database might not have a direct connection to the internet. With utl_http.set_proxy, you can set the proxy that leads the database to the outside world:

utl_http.set_proxy(proxy => '10.0.0.99:8888');

Authentication

The authentication differs from API to API. The Radian6 API requires that you first authenticate. After successful authentication, you will get a token back that you can use in the following API call. Without the token, you cannot call the API. Eloqua expects a Base-64 encoded token that is calculated from the user's site name, concatenated with the userid and password.

The actual call

After the wallet and the proxy, the actual call begins. A call consists of a request to a certain url and some extra required information. This extra information is passed as header info. Examples of header info are the user-agent, user credentials, type of input (json or xml) or length of the header info. The call to the url is done using the url_http.begin_request command:

declare
  l_request utl_http.req;
  l_response utl_http.resp;
  l_value varchar2(2000);
begin
  l_request := utl_http.begin_request('https://secure.eloqua.com/api/bulk/1.0/contact/id','GET','HTTP/1.1');
end;

In this example, a GET request was done. In case of a POST, the post information is required, together with the length of the header information:

utl_http.set_header (r      =>  l_request,

                     name   =>  'Content-Type',  

                     value  =>  'application/json');

--

utl_http.set_header (r      =>  l_request,

                     name   =>  'Content-Length',  

                     value  =>  length(l_rq_body));

utl_http.write_text(l_request,l_rq_body);

Output in JSON or XML

To get the response from the API, you can use utl_http.get_response. If the response is too big, it will be chunked. You have to write a loop to fetch all the chunks into you program:

l_response := UTL_HTTP.GET_RESPONSE(l_request);
loop
    UTL_HTTP.READ_LINE(l_response, l_value, TRUE);
    DBMS_OUTPUT.PUT_LINE(l_value);
end loop;
  UTL_HTTP.END_RESPONSE(l_response);
exception
  when UTL_HTTP.END_OF_BODY
  then
    UTL_HTTP.END_RESPONSE(l_response);


l_value can also be of type CLOB or RAW.

Now that we have all necessary pieces of code, we can make a script to call the API. As an example we call the Eloqua API to get data from a contact with id 1234:

declare
  l_site_name    varchar2(50) := '<companysitename>';
  l_username     varchar2(50) := '<youruserid>';
  l_password     varchar2(50) := '<yourpassword>';
  l_request      utl_http.req;
  l_response     utl_http.resp;
  l_value        clob;
  l_basic_base64 varchar2(2000);
begin
  l_basic_base64 := replace(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(l_site_name||'\'||l_username||':'||l_password))),chr(13)||chr(10),'');
  --
  utl_http.set_wallet('file:d:\eloqua_wallet', '<yourwalletpassword>');
  utl_http.set_proxy(proxy => '10.0.0.99:8888');
  l_request := utl_http.begin_request('https://secure.eloqua.com/api/Rest/1.0/data/contact/1234','GET','HTTP/1.1');
  utl_http.set_header(l_request, 'User-Agent', 'Mozilla/4.0');
  utl_http.set_header (r      =>  l_request,
                       name   =>  'Authorization',
                       value  =>  'Basic '||l_basic_base64);
  --
  l_response := utl_http.get_response(l_request);
  begin
    loop
      utl_http.read_text(l_response, l_raw, 2000);
      l_value := l_value || l_raw;
      utl_http.read_text(l_response, l_value, 2000);
      dbms_output.put_line('value: '||l_value);
    end loop;
    utl_http.end_response(l_response);
  exception
    when utl_http.end_of_body then
      utl_http.end_response(l_response);
  end;
end;
/

If you have APEX 4.0 or higher installed, you can also use APEX_WEB_SERVICE:

declare
  l_site_name    varchar2(50) := '<yourcompanysitename>';
  l_username     varchar2(50) := '<youruserid>';
  l_password     varchar2(50) := '<yourpassword>';
  l_basic_base64 varchar2(2000);
  l_clob         clob;
  l_buffer       varchar2(32767);
  l_amount       number;
  l_offset       number;
begin
  l_basic_base64 := replace(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(l_site_name||'\'||l_username||':'||l_password))),chr(13)||chr(10),'');
  --
  apex_web_service.g_request_headers(1).name := 'User-Agent';
  apex_web_service.g_request_headers(1).value := 'Mozilla';
  --
  apex_web_service.g_request_headers(2).name := 'Authorization';
  apex_web_service.g_request_headers(2).value := 'Basic '||l_basic_base64;
  --
  l_clob := apex_web_service.make_rest_request(p_url => 'https://secure.eloqua.com/api/Rest/1.0/data/contact/1234',
                                               p_http_method => 'GET',
                                               p_wallet_path => 'file:d:\eloqua_wallet',
                                               p_wallet_pwd => '<yourwalletpassword>' );
  --
  l_amount := 32000;
  l_offset := 1;
  begin
    loop
      dbms_lob.read( l_clob, l_amount, l_offset, l_buffer );
      dbms_output.put_line(l_buffer);
      l_offset := l_offset + l_amount;
      l_amount := 32000;
    end loop;
  exception
    when no_data_found then
      dbms_output.put_line('Nothing found: '||l_buffer);
  end;
end;
/
Note that the interaction with network services is by default disabled in Oracle 11g. Refer to the "Enable networking services in Oracle database 11g" section in the Oracle Application Express Installation Guide.

Dynamically showing reports side by side

I always see complex requests as a challenge. A recent example is a request to dynamically display reports side by side. Suppose you want to display a number of reports side by side but at runtime you don't know how much reports will be displayed. Could be 2, could be 4, could be anything. You don't know, it depends on the data or the user request. Difficult? Here's how to solve this.

To make it more understandable, I will demonstrate this with the good old EMP and DEPT tables so make sure you have access to these tables. We will make a page with a tabular form based on the EMP table and the dynamic reports beneath.

First, create the tabular form. Just create a page with a form of type tabular form. Select the EMP table as the base table for this form. Select all the columns from the table.

Here are the steps to create the dynamic reports:

- In the application builder go to the page where you just created the tabular form
- In the regions section, click the add region icon (the "+" sign)
- Select "pl/sql dynamic content" and click next
- Enter a title for the region and click next
- In the pl/sql source text area, enter the following:

declare
  cursor c_col is
    select distinct deptno
    from   emp
    order by 1;
  --
  cursor c_emp (b_deptno in number) is
    select deptno
    ,      empno
    ,      ename
    ,      job
    ,      sal
    ,      comm
    from   emp
    where  deptno = b_deptno;
  --
  cursor c_dept (b_deptno in varchar2) is
    select dpt.deptno
    ,      dpt.dname
    ,      dpt.loc
    from   dept dpt
    where  dpt.deptno = b_deptno;
  --
  r_dept  c_dept%rowtype;
  l_query varchar2(4000);
begin
  sys.htp.p('<table>');
  sys.htp.p('<tr>');
  for r_col in c_col
  loop
    open c_dept(r_col.deptno);
    fetch c_dept into r_dept;
    close c_dept;
    --
    sys.htp.p('<td>');
    sys.htp.p('  <h1>Department: '||r_dept.deptno||', '||r_dept.dname||', '||r_dept.loc||'</h1>');
    sys.htp.p('<table class="uReport uReportStandard">');
    sys.htp.p('<thead>');
    sys.htp.p('<tr>');
    sys.htp.p('<th id="EMPNO">Empno</th>');
    sys.htp.p('<th id="ENAME">Ename</th>');
    sys.htp.p('<th id="JOB">Job</th>');
    sys.htp.p('<th id="SAL">Sal</th>');
    sys.htp.p('<th id="COMM">Comm</th>');
    sys.htp.p('</tr>');
    sys.htp.p('</thead>');
    for r_emp in c_emp(r_col.deptno)
    loop
      sys.htp.p('<tr>');
      sys.htp.p('<td>'||r_emp.empno||'</td>');
      sys.htp.p('<td>'||r_emp.ename||'</td>');
      sys.htp.p('<td>'||r_emp.job||'</td>');
      sys.htp.p('<td>'||r_emp.sal||'</td>');
      sys.htp.p('<td>'||r_emp.comm||'</td>');
      sys.htp.p('</tr>');
    end loop;
    sys.htp.p('</table>');
    sys.htp.p('</td>');
  end loop;
  sys.htp.p('</tr>');
  sys.htp.p('</table>');
end;

First, the script runs through all distinct departments. For each deptno, a HTML column (<td>) within a HTML table is created so that each collection is displayed separately. The line with the classes (uReport and uReportStandard) makes sure that the reports look just as the standard reports.

- Click next
- Click create region

The region is ready. Run the page and use the tabular form to update the department for some employees. Especially when you add more departments and move some employees to the new departments, you will see that the number of reports extends to the right side. You could add a scrollbar to the region by entering the following in the region attributes text field in the attributes section of the region:

style="height: 400px; width:1000px; overflow:scroll"

If you have APEX 4.2, use theme 26 and the template for the scrollable content region. Otherwise, if you are using another theme, create a new template as a copy of the scrollable content region template.

This looks quite good. However, the customer could just ask for an extra requirement that the reports should be responsive. So no scrollbar but instead a region with a report for each department. And when resizing the browser, or displaying on a mobile device, the regions are stacked. In that case, use the following pl/sql code instead of the above code:

declare
  cursor c_col is
    select distinct deptno
    from   emp;
  --
  cursor c_emp (b_deptno in number) is
    select deptno
    ,      empno
    ,      ename
    ,      job
    ,      sal
    ,      comm
    from   emp
    where  deptno = b_deptno;
  --
  cursor c_dept (b_deptno in varchar2) is
    select dpt.deptno
    ,      dpt.dname
    ,      dpt.loc
    from   dept dpt
    where  dpt.deptno = b_deptno;
  --
  r_dept  c_dept%rowtype;
  l_query varchar2(4000);
  l_col   number(2);
  l_num_of_cols number(2) := 0;
  l_rowcount number(2) := 1;
begin
  select count(distinct deptno)
  into   l_num_of_cols
  from   emp;
  --
  if l_num_of_cols > 4
  then
    l_col := 2;
  else
    l_col := 12 / l_num_of_cols;
  end if;
  --
  sys.htp.p('<div id="uOneCol:">');
  sys.htp.p('<div class="apex_grid_container">');
  sys.htp.p('<div class="apex_cols apex_span_12">');
  sys.htp.p('<div class="apex_row">');
  for r_col in c_col
  loop
    open c_dept(r_col.deptno);
    fetch c_dept into r_dept;
    close c_dept;
    --
    if l_rowcount = 1
    then
      sys.htp.p('<div class="apex_cols apex_span_'||l_col||' alpha">');
    elsif l_rowcount = l_num_of_cols
    then
      sys.htp.p('<div class="apex_cols apex_span_'||l_col||' omega">');
    else
      sys.htp.p('<div class="apex_cols apex_span_'||l_col||'">');
    end if;
    l_rowcount := l_rowcount + 1;
    sys.htp.p('<section id="" class="uRegion clearfix">');
    sys.htp.p('  <div class="uRegionHeading">');
    sys.htp.p('  <h1>Department: '||r_dept.deptno||', '||r_dept.dname||', '||r_dept.loc||'</h1>');
    sys.htp.p('  </div>');
    sys.htp.p('  <div class="uRegionContent clearfix">');
    sys.htp.p('<table class="uReport uReportStandard">');
    sys.htp.p('<thead>');
    sys.htp.p('<tr>');
    sys.htp.p('<th id="EMPNO">Empno</th>');
    sys.htp.p('<th id="ENAME">Ename</th>');
    sys.htp.p('<th id="JOB">Job</th>');
    sys.htp.p('<th id="SAL">Sal</th>');
    sys.htp.p('<th id="COMM">Comm</th>');
    sys.htp.p('</tr>');
    sys.htp.p('</thead>');
    for r_emp in c_emp(r_col.deptno)
    loop
      sys.htp.p('<tr>');
      sys.htp.p('<td>'||r_emp.empno||'</td>');
      sys.htp.p('<td>'||r_emp.ename||'</td>');
      sys.htp.p('<td>'||r_emp.job||'</td>');
      sys.htp.p('<td>'||r_emp.sal||'</td>');
      sys.htp.p('<td>'||r_emp.comm||'</td>');
      sys.htp.p('</tr>');
    end loop;
    sys.htp.p('</table>');
    sys.htp.p('</div>');
    sys.htp.p('</section>');
    sys.htp.p('</div>');
  end loop;
  sys.htp.p('</div>');
  sys.htp.p('</div>');
  sys.htp.p('</div>');
  sys.htp.p('</div>');
end;


The difference with the first code snippet is that here a number of div's are used to get the responsive behaviour. For the responsiveness, a 12-column layout is used. If two regions are displayed, the two regions get the class apex_span_6 (so 12 divided by 2 columns). If three regions are displayed, the three regions get the class apex_span_4 (so 12 divided by 3 columns). The first region also gets the alpha class, the last region also gets the omega class.

How do I know this? Well, create a new application with theme 25 (the responsive theme) and create a number of reports and display them side by side. Then use the inspect element or view source option from your browser to check what HTML code is generated by APEX.

Run the report. Resize the browser so that you can see that the reports will be stacked when the browser's width is too small to show them all side by side.

Using jQuery selectors in dynamic actions

When you want to control multiple items with your dynamic action, you can make use of jQuery selectors. This can be very handy when you want to enable or disable a number of buttons on your page but want to leave other buttons untouched.

Suppose you have a resume application where you can add or edit a new resume for an employee, and you can edit the skills and the work history of the employee.

In the application it is possible to maintain multiple resumes, for example in two languages. First select an employee in the Employee select list and then select a resume. When no employee and no resume is selected, no button at all will be enabled. When an employee is selected but no resume, only the New Resume button will be enabled. When an employee and a resume are selected, all buttons will be enabled.

To implement this, we will create two dynamic actions: one for the employee select list and one for the resume select list. It is assumed that you already have a page with the items as displayed in the picture. So, two list items, one for the employees and one for the resumes. And four buttons, one for a new resume, one for editing a resume, one for editing skills and one for editing work history.

- In the Buttons section, click on the New Resume button
- In the Attributes section, enter "BT_EM-1" in the static id text field
- Click "Apply Changes"

- In the Buttons section, click on the Edit Resume button
- In the Attributes section, enter "BT_RM-1" in the static id text field
- Click "Apply Changes"
- In the Buttons section, click on the edit skills button
- In the Attributes section, enter "BT_RM-2" in the static id text field
- Click "Apply Changes"
- In the Buttons section, click on the edit work history button
- In the Attributes section, enter "BT_RM-3" in the static id text field
- Click "Apply Changes"

Instead of numbers, you could also use names that cover the button's function. So now we have four buttons with each having an id, which means that they can be found in the Document Object Model (DOM). Dynamic actions can also find the objects in the DOM and there are several ways to do so.


- In the dynamic actions section, click the "Add" icon
- Enter a name, for example "handle_employee_list"
- Click "Next"
- In the event select list, select "Change"
- In the selection type select list, select "Item"
- In the item text field, enter the name of the employee select list, you can use the icon next to the text field to find the exact name
- In the condition select list, select "Is not null"
- Click "Next"
- In the action select list, select "Enable"
- Make sure the fire on page load and the generate opposite false action checkboxes are checked
- Click "Next"
- In the selection type select list, select "jQuery selector"
- In the jQuery selector text field, enter "[id|='BT_EM']"

- In the dynamic actions section, click the "Add" button
- Enter a name, ie "handle_resume_list"
- Click "Next"
- In the event select list, select "Change"
- In the selection type select list, select "Item"
- In the item text field, enter the name of the resume select list, you can use the icon next to the text field to find the exact name
- In the condition select list, select "Is not null"
- Click "Next"
- In the action select list, select "Enable"
- Make sure the fire on page load and the generate opposite false action checkboxes are checked
- Click "Next"
- In the selection type select list, select "jQuery selector"
- In the jQuery selector text field, enter "[id|='BT_RM']"
- Click the create dynamic action button


We have two dynamic actions now. One is to enable/disable the new resume button and the other one is to enable/disable the edit resume, edit skills and edit work history buttons. Run the page and check if everything works. On entering the page for the first time, both select lists are empty and all buttons are disabled. Select an employee and check that the new resume button will be enabled, but the other
buttons not. Select a resume and check that also the other buttons will be enabled.

The big secret is off course the jQuery selector. The syntaxis tells that the selector should be between square brackets and starts with the name of the attribute. In this case we use the "id" attribute. After the pipe comes the matching string. BT_RM means that all objects are selected where the id attribute has the value 'BT_RM' or at least starts with 'BT_RM'. Everything in the id attribute before the hyphen (-) is matching, so BT_RM-1, BT_RM-2 and BT_RM-3 are all matching id's and thus will be affected when the resume select list changes. The SQL equivalent would be "where id like 'BT_RM%' ".

As you may know, there are two types of buttons in APEX. Buttons in region positions and buttons among items in the region. The recipe here is about buttons in region positions. In case you created buttons as items in the region, you don't see the static id text field. In that case you can prefix the name of the button with 'BT_RM-'. For such buttons, the name will appear in the id attribute.

For more information on jQuery selectors, go to http://api.jquery.com/category/selectors

Oracle APEX Cookbook second edition published today!

I'm proud to announce that our second book has been published today. The Oracle APEX Cookbook second edition, published by Packt. I wrote it together with Michel van Zoest. We both wrote also the Oracle APEX 4.0 Cookbook in 2010. We took the original APEX 4.0 Cookbook and added a number of new recipes, especially for APEX 4.2. Creating mobile applications, using HTML5 with CSS3, touch screen events, RESTful webservices are all new items in this book. We also tried to solve a number of issues that were submitted by reader queries.

Writing a book is a tough job. It's almost like a project. First, you start writing the drafts which have to be finished before a certain deadline. Take 1 month per chapter. When the draft is delivered, it will be reviewed by external reviewers. That could be well-known persons from the Oracle APEX community. After the review of the external reviewers, we will get time to address all the comments that are written by the reviewers. And once that is done, a technical editor will do a check whether everything is OK. The draft will also be checked upon spelling, grammar and so on. In the final stage we receive the chapters in PDF format. This is almost how the contents of the book will look like. Changes are only accepted if it will not affect the layout. And finally, once that is done, the book is ready to be published. Last thing to do is to deliver the code bundle, containing all the scripts and examples shown in the book.

Oracle APEX Cookbook second edition is avaible at bookstores, among others Packt and Amazon.

APEX Listener 2.0 with Glassfish 3.1.2 - my journey

Recently I tried to install the APEX Listener 2.0 on a Glassfish 3.1.2 application server. It all runs on Red Hat Enterprise Linux  server 5.9 in a virtual server at my company. I wanted to test whether it was indeed possible to download a report to PDF format. Before APEX Listener 2.0, you needed a separate server like BI Publisher or Apache FOP but now with 2.0, you don't need that anymore. Just install the listener 2.0, login to the internal workspace and select "Oracle APEX Listener" in the print server list box in the instance settings and that's all! Unfortunately I didn't even manage to install the new Listener.

Here's what I did:

- Download APEX Listener 2.0 from otn.oracle.com to my Windows laptop.
- unzip the download
- scp the apex.war file to the virtual machine.
- start putty and logon to the virtual machine
- Execute java -jar apex.war
- Enter the name of the configuration directory
- enter the other settings like host, name of the user (APEX_PUBLIC_USER) and password and so on
- create a image archive: java -jar apex.war static /opt/sw/oracle/apex/images
- login to the Glassfish admin console
- deploy the apex.war and i.war

After the deployment, the Engines column in the applications overview page didn't show "web" for the newly deployed apex.war. And when I tried to launch the application I got an error message.

By the way, there were more APEX listener deployments on that machine. For every OTAP environment, there was a listener installed. All those listeners were version 1.4. Deploying the new listener into an existing 1.4 configuration automatically updates it to 2.0 but I didn't want to touch the other listeners, as I only wanted to test the new listener.

Renaming the apex.war file into apex-2.war and using another name for the deployment in the context root did help me a little bit. I was able to deploy the new listener and I could see that the Engines column showed "web". However, it still didn't work. On launching the deployment, I got an error message "Not yet configured".

So, all kinds of problems, tried everytning, nothing worked. Then I came up with an idea. Since the listener was downloaded on my Windows machine, this might have caused some problems. So I logged in to the virtual machine using TighVNC, started a browser session and I downloaded the listener via otn.oracle.com. Ok, I know it's possible to download from the command line but this was the easiest way for me. I repeated the necessary steps to configure the listener and tadaa, it worked!

Here's my conclusion: downloading files on Windows and deploying the files on Linux might give some problems. If possible, download files on the same machine as where you want to use them.

A Google map and a Streetview panel in your application

Including a Google map in your APEX application is of course not difficult anymore. You can find lots of examples of how to do that on the internet. But what about a Google map and a streetview panel in one page? It is possible, including a marker on the map that corresponds to the picture on the streetview panel. And navigating through the streetview panel also moves the marker on the map. And the coordinates (latitude and longitue) are captured into two text items, together with the yaw, the pitch and the zoom. The yaw is the view movement from left to right and the pitch is the view movement from the ground to the sky.

Here's how to do it:

Note: regarding the items and the button, replace the X by the pagenumber you are working on. And replace <your_key> by your Google Maps API key. You can obtain a key via https://developers.google.com/maps/documentation/javascript/tutorial

- Create the following procedure in the database:

create or replace PROCEDURE show_map (
                      p_map_div IN VARCHAR2 DEFAULT 'map-canvas', p_streetview_div IN VARCHAR2 DEFAULT 'pano', p_lat_item_name IN VARCHAR2
                     ,p_lng_item_name IN VARCHAR2, p_yaw_item_name IN VARCHAR2, p_pitch_item_name IN VARCHAR2
                     ,p_zoom_item_name IN VARCHAR2
                     )
  IS
    l_mapstr       varchar2 (32000);
  begin
    l_mapstr    :=    '
    <script src="https://maps.googleapis.com/maps/api/js?key=<your_key>&sensor=false">
    </script>
    <script type="text/javascript">
    var map;
    var panorama;
    var marker;
    var streetview;
    function initialize(myAddress, myLat, myLng, myPOV) {
      var mapOptions = {zoom      : 12
                       ,mapTypeId : google.maps.MapTypeId.ROADMAP
      }   
      map            = new google.maps.Map(document.getElementById('||''''|| p_map_div ||''''||'),mapOptions);
      panorama       = new google.maps.StreetViewPanorama(document.getElementById('||''''||p_streetview_div||''''||'));
      streetview     = new google.maps.StreetViewService();
      geocoder       = new google.maps.Geocoder();
      geocoder.geocode( { '||''''||'address'||''''||': myAddress}, function(results, status) {
        if (status == google.maps.GeocoderStatus.OK) {
          var markerOptions = {map       : map
                              ,draggable : true
                              ,animation : google.maps.Animation.DROP
                              ,flat      : false
                              ,position  : results[0].geometry.location
          }
          map.setCenter(results[0].geometry.location);
          marker = new google.maps.Marker(markerOptions);
          streetview.getPanoramaByLocation(results[0].geometry.location, 50, showStreetviewData);
          google.maps.event.addListener(marker, '||''''||'dragstart'||''''||', function() {map.closeInfoWindow();} );
          google.maps.event.addListener(marker, '||''''||'dragend'||''''||', function(event) {
            document.getElementById("'||p_lat_item_name||'").value=event.latLng.lat();
            document.getElementById("'||p_lng_item_name||'").value=event.latLng.lng();
            streetview.getPanoramaByLocation(event.latLng, 50, showStreetviewData);
          });
        } else {
          document.getElementById("'||p_map_div||'").innerHTML = "No mapdata found for given address. Did you enter a correct address?";
        }
      });
    }
  
    function showStreetviewData(data, status) {
      if (status == google.maps.StreetViewStatus.OK) {
        panorama.setPano(data.location.pano);
        panorama.setPov({heading : 270
                        ,pitch   : 0
                        ,zoom    : 1
        });
        panorama.setVisible(true);
        google.maps.event.addListener(panorama, '||''''||'position_changed'||''''||', function(event) {
          var panoramaInfo = panorama.getPosition();
          document.getElementById("'||p_lat_item_name||'").value=panoramaInfo.lat();
          document.getElementById("'||p_lng_item_name||'").value=panoramaInfo.lng();
          marker.setPosition(new google.maps.LatLng(panoramaInfo.lat(),panoramaInfo.lng()));
        });
        google.maps.event.addListener(panorama, '||''''||'pov_changed'||''''||', function(event) {
            var panoInfo   = panorama.getPov();
            document.getElementById("'||p_pitch_item_name||'").value=panoInfo['||''''||'pitch'||''''||'];
            document.getElementById("'||p_yaw_item_name||'").value=panoInfo['||''''||'heading'||''''||'];
            document.getElementById("'||p_zoom_item_name||'").value=panoInfo['||''''||'zoom'||''''||'];
        });
      } else {
          document.getElementById("'||p_map_div||'").innerHTML = "No Streetview data for this location?";
        }
    }
    </script>';
    --
    sys.htp.p (l_mapstr);
  EXCEPTION
    WHEN OTHERS
    THEN
      raise_application_error (-20000, 'error in show_map: ' || SQLERRM);
  END show_map;



A little explanation of the code:

This procedure uses a variable that holds Javascript code which is outputted by the sys.htp.p function. The procedure is called from the pl/sql region. The parameters are the name of the two divs where the google map and streetview panel are rendered, and the names of the items that hold the geo details, like latitude, longitude, yaw, pitch and zoom.

The code makes use of the Google api to find the latitude and the longitude for a given address. Furthermore is the code event-driven for the movement of the marker and the navigation through the streetview map. This recipe makes use of the Google maps api v3. Previously, this recipe used v2, which will be deprecated.

In the APEX builder, create an empty page.
Create 4 HTML regions. Call them Address details, Geo details, Google Map and Streetview map.
Click on the Geo details region and select 2 from the Column selectlist in the User Interface section. Click the apply changes button. Do the same for the Streetview map region.
Click on the Google map region and put the following code in the region source text area:

<div align="center" style="width: 100%; height: 300px;">
  <div id="map_canvas" style="width: 500px; height: 300px"></div>

- Click apply changes
- Click on the streetview map region and put the following code in the region source text area:

  <div id="pano" style="width: 500px; height: 300px"></div>
</div>

- Click apply changes
- Create the following items:

Region Address details:

- PX_ADDRESS
- PX_CITY
- PX_COUNTRY

Region Geo details:

- PX_LAT
- PX_LNG
- PX_YAW
- PX_PTCH
- PX_ZM

- In the buttons section, click the add button icon
- Select the address details region
- Select "create button among this region's items"
- Enter a name for the button, as well as a label. Click next
- Click next
- In the action selectlist, select "defined by dynamic action"
- In the execute validations selectlist, select no. click next
- Click create button

- In the dynamic actions section, click the add button
- Select advanced
- Wnter a name for the dynamic action, ie find_address. Click next
- In the event selectlist, select mouse button press
- In the selection type selectlist, select button
- In the button select list, select the button you just created
- Click next
- In the action selectlist, select "execute javascript code"
- In the code text area, enter the following code:

initialize(document.getElementById("PX_ADDRESS" ).value + "," + document.getElementById("PX_CITY" ).value + "," + document.getElementById("PX_COUNTRY" ).value,0,0,{yaw:0,pitch:0,zoom:0});

- Click next, click create
- Create a pl/sql region and enter the following code into the region source text area:

show_map('map_canvas','pano','PX_LAT','PX_LNG','PX_YAW','PX_PTCH','PX_ZM');
sys.htp.p('<script type="text/javascript">');
sys.htp.p('initialize('||''''||'Leidscheplein, Amsterdam,NL'||''''||','||0||','||0||',{yaw:'||0||',pitch:'||0||',zoom:'||0||'});');
sys.htp.p('</script>');

You can of course enter your own favourite address. In the User Interface section, select "No Template"  in the Template select list. Click apply changes.

The page is ready now. The result should look something like this:




Try to move the red marker on the google map. You will see that the latitude and the longitude columns change and that the streetview changes. In the streetview map, click on the arrows on the road to navigate through the map. You will see that the red marker on the google map also moves and that the geo details change.

By the way, you can get the regions straight put next to one another and stacked by putting the following into the Region attributes text field in the Attributes section of the Address details and Geo details regions:

style="height:200px"

Dynamic quick picks

Quick picks are those little red links with predefined values you see under (mostly) listboxes in the APEX builder.

They offer the user a flexibilty because the user can fill an item with a single click.Since APEX 4.0, you can easily add quick picks to your items by selecting "Yes" in the Show quick picks listbox in the Quick picks section of the item.

However, you can only add static data to the quick picks. Sometimes it would be very handy to make the quick picks dynamic. Let's say you have a CV application where you can add language skills to employees and suppose you want to have a language selectlist with quick picks of the 4 most used languages.

In 2008, long before APEX 4.0 was released, Anthony Rayner already wrote a blogpost about creating your own dynamic quick picks. Now, since APEX 4.0, you can create a plug-in for these kind of user-interface enhancements. In this blogpost I will show you how to do that.

Preparations:

We are going to use the following tables:

Table CBR_LANGUAGES:

LNG_ID           NUMBER(5)
LANG_DESCRIPTION VARCHAR2(20)

Table CBR_EMP_LANG:

ELG_ID NUMBER(5)
LNG_ID NUMBER(5)
SPEAK  VARCHAR2(30)
WRITE  VARCHAR2(30)
EMP_ID NUMBER(5)

sequence CBR_ELG_SEQ

Steps:

- Go to your application
- click on shared components
- in the user interface section, click plug-ins
- click the create button
- in the name section, enter a name for the plug-in, ie Dynamic quick pick, in the name text field
- in the internal name text field, enter a unique name, ie com.ciber.dynamic.quick.pick. This will be used internally by APEX
- in the type selectlist, make sure item is selected
- in the source section, enter the following code:

function render_quickpick (p_item                in apex_plugin.t_page_item
                          ,p_plugin              in apex_plugin.t_plugin
                          ,p_value               in varchar2
                          ,p_is_readonly         in boolean
                          ,p_is_printer_friendly in boolean )
return apex_plugin.t_page_item_render_result
is
  TYPE query_type  is REF CURSOR;
  TYPE query_type1 is REF CURSOR;
  c_query_cursor query_type;
  c_topn_query   query_type1;
  l_result       apex_plugin.t_page_item_render_result;
  l_query        apex_application_page_items.attribute_01%type := p_item.attribute_01;
  l_topn_query   apex_application_page_items.attribute_02%type := p_item.attribute_02;
  l_return       varchar2(20);
  l_display      varchar2(20);
  l_name         varchar2(30);
begin
  --
  -- get an APEX input name
  l_name := apex_plugin.get_input_name_for_page_item (p_is_multi_value => true );
  sys.htp.p('<select name="'||l_name||'" id="'||p_item.name||'" size="1" class="selectlist">');
  --
  -- create the selectlist
  open c_query_cursor for l_query;
  loop
    fetch c_query_cursor into l_display, l_return;
    exit when c_query_cursor%notfound;
    if l_return = p_value
    then
      sys.htp.p('<option value="'||l_return||'" selected="selected">'||l_display||'</option>');
    else
      sys.htp.p('<option value="'||l_return||'">'||l_display||'</option>');
    end if;
  end loop;
  sys.htp.p('</select>');
  sys.htp.p('<br/>');
  --
  -- The top-n query is optional, if empty, use the l_query
  if l_topn_query is null
  then
    l_topn_query := l_query;
  end if;
  --
  -- create the quick picks
  open c_topn_query for l_topn_query;
  loop
    fetch c_topn_query into l_display, l_return;
    exit when c_topn_query%notfound;
    sys.htp.p('<a href="javascript:$s('||''''||p_item.name||''''||','||''''||l_return||''''||','||''''||l_display||''''||');" class="itemlink">'||'['||l_display||']'||'</a>');
  end loop;
  --
  return l_result;
end render_quickpick;


- in the callbacks section, enter render_quickpick in the render function name text field
- in the standard attributes check the following attributes:

is visible
has element attributes
session state changeable
has source attributes
has width attributes

- click the create button but stay in the page.
- in the custom attributes section, click the add attribute button
- fill in the items with the following data:

scope: component
attribute: 1
display sequence: 10
Label: query
type: sql query
required: yes
minimum columns: 2, maximum columns:2


- click create and create another
- enter the following data:

scope: component
attribute: 2
display sequence: 20
Label: Top-N query
type: sql query
required: no
minimum columns: 2, maximum columns:2

- click create

The plug-in is ready. Now we will create a page that makes use of the plug-in.

- Go to the application builer and click the create page button.
- select form
- select form on a table or view
- in the table/view name selectlist, select cbr_emp_lang. click next
- click next
- select do not use tabs and click next
- click the radio button "select primary key" and in the primary key column 1 selectlist, select elg_id. click next
- select existing sequence and in the sequence selectlist, select cbr_elg_seq. click next
- transfer all columns to the right and click next
- click next
- enter pagenumber where APEX should navigate to when submitting or cancelling. You can also enter the same pagenumbers you are creating. click next
- click finish
- click edit page

Now we will include the plug-in into the application

- Click on the PXX_LNG_ID item (where XX is the page number)
- In the display as selectlist, select Dynamic quick pick [Plug-in]
- In the query text area (in the settings section), enter the following query:

SELECT LANG_DESCRIPTION D
,      LNG_ID R
FROM   CBR_LANGUAGES

- In the top-N query text area, enter the following query:

SELECT LNG.LANG_DESCRIPTION D
,      TOP_LNG.LNG_ID R
FROM   (SELECT LNG_ID
        ,      COUNT(LNG_ID) ORDERING
        FROM   CBR_EMP_LANG
        GROUP BY LNG_ID
        ORDER BY 2 DESC) TOP_LNG
,      CBR_LANGUAGES LNG
WHERE  TOP_LNG.LNG_ID = LNG.LNG_ID
AND    ROWNUM < 5

- click apply changes.

The page and the plug-in are ready. Click run to see the result.