ClariFit From/To Date Picker Plug-in Moved to OraOpenSource

This article is for people looking for help with the ClariFit From/To Date Picker.

I'm currently in the process of updating some of the plugins I created for ClariFit to support APEX 5.0. As part of these changes some additional things are happening, mainly listing the plugins in the new apex.world site and moving the plugins ownership over to OraOpenSource.

The first plugin that has been moved/updated is the ClariFit From/To Date Picker. The plugin is now called OraOpenSource From/To Date Picker and is hosted here. To help with backwards compatibility the plugin's internal name has remained the same. This means that upgrades will be seamless.

Please post all bugs and enhancement requests on the project's issue page.


The next plugin that will be updated is the ClariFit Dialog (to be called OraOpenSource Dialog). It is currently being tested and will be officially released pending any major bugs. If you'd like any other ClariFit plugins updated to APEX 5.0 please leave a comment and I'll look into it.

ODTUG Election

If you were at Oracle Open World a few weeks ago you may have missed that the ODTUG Board of Directors election results were posted.

The newly elected directors are:

• Opal Alapat
• Danny Bryant
• Gary Crisci
• Natalie Delemar
• Martin D'Souza

I first must thank all the people that voted for me again this year. It means a lot that I have the support of the APEX and Oracle communities. 

They're a few board members who will not be returning next year. They all have done a lot for ODTUG and its members.

• Monty Latiolais
• Monty has been the President of ODTUG for the past few years. He's done an amazing job leading the organization. Monty's lasting legacy at ODTUG will be the Leadership Program. He took a risk trying it out and it has been a very big success helping develop leaders within the ODTUG community.
• David Schleis
• David has helped re-energize the traditional developer and DBA communities. Over the past two years he helped merged them into one united "database" community. I look forward to David to continue help out with the database community in the coming years.
• Mia Urman
• Mia has been instrumental with helping foster the ADF community and lead the global outreach program. Both of these areas require a lot of time and effort, all which Mia happy put in. If you're going to be at DOAG in a few weeks look for Mia who be representing ODTUG.

I also need to say a special thank you to the current board for supporting me over the past year. Last year was a very difficult time for me personally and they all stepped up and supported me along the way. This is something I will never forget and will always be thankful for.

As ODTUG has a six year term limit for directors, this will be my last two years. I look forward to making them as good as the first four. If you have any feedback or things you'd like to see happen within the community please let me know.

- Martin

ODTUG Technical Journal - Authors Wanted

If you've ever thought of starting your own blog or wanted to write a detailed article on some cool APEX or Oracle feature but didn't know where to start, now's your big chance! The ODTUG Technical Journal is looking for some new articles and we'd like to see articles from first time contributors.

Signing up is easy. Simply email Karen Cannell (kcannell@odtug.com) and let her know that you're interested in writing an article for the journal. Then you can work out a schedule with her. After that, all you need to do is write the article, Karen will take care of the rest.

This is a great opportunity to get your name out there and also promote what you and your organization have been been working on. I'm looking forward to seeing some great content from the APEX community.

1st Calgary APEX Meetup

We're having our 1st inaugural APEX meetup on Thursday Nov 12th! I'll be giving a presentation about APEX 5 and the new apex.world site.

FAQs I've received about this meetup:

• Does it cost anything? No, it's entirely free 
• What should I bring? Yourself. Some people like to bring their laptop to try things out at the same time. 
• Is this a sales pitch: No, it's by the APEX community, for the APEX community. 
• Will only APEX items be covered? Primarily we'll be talking about APEX but may deviate a bit in the future to cover some SQL, PL/SQL, and web technologies. These topics are all relevant for APEX developers. 
• Where can I get one of the cool APEX stickers I see everyone has on Twitter? At the meetup. I've got a whole stack to give away.

Location details:

Trident Limited Partnership 

1000, 444 - 7 Avenue SW (Barclay building) 

Calgary, Alberta T2P 0X8

10th floor -- Must sign-in at reception desk

Special thanks to Jean Paradis and Trident Limited Partnership for securing the meeting space!

Please RSVP to let us know if you're coming.

APEX Podcast

If you can't get enough APEX there's now an APEX Podcast! This is a great opportunity to know more about the people behind APEX and some key people in the APEX community.

Juergen Schuster and I co-host the show (I only joined on recently) and it has been a lot of fun so far.

You can download each episode on the official site (apex.press/talkshow) or subscribe on iTunes.

Note: In the latest podcast we mentioned that we're looking for sponsors for the show. If you or your organization is interested in the opportunity please email me.

OOW 15 Presentations

If you're attending Oracle OpenWorld 2015 this year here are a few links for presentations I'd recommend:

•  Open Source and Oracle Application Express [UGF4096]
• Sunday Oct 25 8:00am - Moscone South Room 300
• Martin Giffy D'Souza
• If you've ever thought about using open source applications or want to become more involved with open source development for your Oracle and APEX applications this presentation is for you.
• The Objects of My Affection: Deploying Your APEX Applications [UGF10050]
• Sunday Oct 25 10:00am - Moscone South Room 300
• Francis Mignault
• Learn how to deploy your applications for testing, QA, and production. Also learn how supporting objects work, and see the new Oracle Application Express 5 features that will help you package and deploy your applications.
• ODTUG Sunday Sessions
• There's a lot of great presentations. Readers of this blog will most likely be focused on the PL/SQL and APEX sessions.

On Tuesday night there will be the APEX meetup. Don't get their too late since it fills up pretty quickly. We'll be in the basement.

I'll also be filming some Periscope interviews for ODTUG. If you can't attend OOW then you'll want to follow the ODTUG twitter account. This will be the first time ODTUG has ever done this and we'd appreciate your feedback. If there's someone you'd like to see interviewed during the conference please let me know below by leaving a comment on this post.

I look forward to seeing everyone there!

ODTUG 2015 Election - Please Vote

The ODTUG Board of Directors election is now officially open. Paid ODTUG members can cast their ballot between Oct 6th - Oct 27th. If you are a paid member you will get an email with the link, username, and voter id.

I am running for the ODTUG Board of Directors and I am looking for your support to continue to help the ODTUG community for another two years.

Here is my official campaign statement:

Over the past four years, I have had the privilege to represent the Oracle APEX Community as a Director for ODTUG. I have made the decision to run again this year for the ODTUG Board and am looking for your support in doing so.

During my four-year tenure, I have been involved in several committees within ODTUG that have helped bring conferences around the world, improved marketing initiatives, and organized developer contests. 

I have been able to build strong relationships with other ODTUG Board Members, Oracle employees, and many developers in the APEX community. These relationships have allowed me to be a liaison between all three groups and help relay ideas and opportunities to the appropriate parties. I hope to continue to be able to facilitate open communication over the next two years.

I am looking for your vote to continue as an ODTUG Board Member to represent developers within one of the world’s largest Oracle user groups. Thank you in advance for your support.

Thank you in advance,

Martin

APEX Webinar: From the Community, for the Community

ODTUG will be hosting an APEX webinar on Monday, Oct 19th at 12:00pm EDT. The webinar is about something that several key members of the APEX community have been working on that is "from the community, for the community". I strongly recommend that everyone attend this webinar. 

I can't say anything more than the above statement and all will be revealed in the webinar. Registration is free, and you can register using this link.

APEX OOW 2015 Meetup

Each year the Oracle APEX community gets together at Oracle Open World (OOW) to have a few beers and catch up. This is a great informal event and its your chance to network with a lot of people in the APEX community and meet some of the gurus and members of the core APEX development team.

We'll be having the annual APEX OOW Meetup at Johnny Foleys on Tuesday, October 27th at 7:30 onwards. We've been their a few times now and it is a really fun night since they have duelling pianos later on in the evening. Don't show up too late or it may be tough to get in.

243 O'Farrell Street
San Francisco, CA 94102

ODTUG will also be giving away some cool APEX swag!

Looking forward to seeing everyone there!

Martin

Custom Code for Tabular Forms (Part 2)

The previous article covered how to create a Tabular Form with manual code rather than automatic row processing. This article will demonstrate how to change the Tabular Form to modify data from multiple tables using the same Tabular Form.

Modify Tabular Form

Using the example from the previous article, modify the Tabular Form and change the query to:

select
  e.empno,
  e.empno empno_display,
  e.ename,
  e.sal,
  d.dname
from emp e, dept d
where 1=1
  and e.deptno = d.deptno

Edit DNAME

Edit the newly added DNAME column with the following changes:

Modify Page process

Change the page process to use the code snippet below. Note: This isn't the best example, as the below code will update the department name for each modified employee record. It does highlight is that you can reference and modify data from multiple tables.

if :empno is null then
  -- code to insert emp
  null;
else
  update emp
  set
    ename = :ename,
    sal = :sal
  where empno = :empno;

  -- Update dept name
  update dept d
  set dname = :dname
  where 1=1
    and d.deptno = (
      select e.deptno
      from emp e
      where e.empno = :empno)
end if;

If you've ever developed a true manual tabular form using collections, the above approach covered in this article may be a better alternative to manage and maintain.

Custom Code for Tabular Forms (Part 1)

Over a year ago I wrote an article covering how to create a tabular form and then use custom PL/SQL to process the data rather than the automatic Apply MRU and Apply MRD processes. The demo showed how to do this in APEX 4.2. This article will re-introduce the topic but use APEX 5.0 instead.

Create new Tabular Form

• Create page > Page type: Form > Tabular Form
• Select the following options: 

• Note: For simplicity/demo purposes, limiting to just the SAL and ENAME columns.
• Primary Key: Select Primary Key Column(s) > Primary Key Column 1 > EMPNO
• Run through the rest of the wizard.

Remove Automatic DML

Since custom code will be used to process the page, delete the automatic row processing process as shown below.

Create Process

Create a new process with the following settings (the Source is included below the image).

if :empno is null then
  -- code to insert emp
  null;
else
  update emp
  set
    ename = :ename,
    sal = :sal
  where empno = :empno;
end if;

Using the above technique you can now use a tabular form to call custom PL/SQL code rather than the automatic row processing.

The next article will cover how to modify data from multiple tables in the same Tabular Form.

Report with Checkboxes (an update)

Supposed you have a report with checkboxes. Once the user selects all the rows, they can submit the page and the application would process the rows. Sounds pretty simple and straight forward however they're some additional requirements:

- The report is an Interactive Report
- There may be up to 10,000 records in the report
- When the user "scrolls" through the report (i.e. uses pagination), if they checked off a box it should remain checked the entire time (i.e. if they check an row in the 1st 15 rows, then view rows 16~30, then go back to rows 1~15 it should remain checked). Same applies if a user uses the IR filters.

If you follow this blog and the above sounds familiar, that's because it is. I wrote about this problem a very long time ago. A lot has changed since 2009 and an update on the original post is long overdue. Here's the updated (and simplified) solution.

Create Item to Hold List of IDs

Create a hidden APEX item which will contain a comma delimited list of all the IDs that are to be checked off (in this example it will be P1_EMPNO_LIST). Be sure to modify the Value Protected attribute to No. This is critical as this item will be updated via AJAX and can not have any hashing/security applied to it.

If you are loading this from a cross reference table you can use the following query in a Before Header process. This query will load all the employees from the Accounting department.

select listagg(e.empno, ',') within group (order by e.empno)
from dept d, emp e
where 1=1
  and e.deptno = d.deptno
  and d.dname = 'ACCOUNTING'

Create IR Report with Checkboxes

Create an IR with the query below. Note the p_attributes value. This is critical as we need to identify the checkboxes that should be monitored.

select 
  apex_item.checkbox2(
    p_idx => 1,
    p_value => e.empno ,
    p_attributes => 'class="empno"',
    p_checked_values => :p1_empno_list,
    p_checked_values_delimiter => ',') checkbox,
  e.ename, 
  e.job
from emp e

In the report attributes set the Page Items to Submit to P1_EMPNO_LIST. Each time the report is refreshed (pagination, filters, sorting, etc) the active list of selected values will be submitted.

Create Dynamic Action (DA)

Create a new DA with the attributes in the below image. This DA will append/remove the comma delimited list of IDs in P1_EMPNO_LIST. Note the jQuery Selector value must match what was used in the IR query above.

Configure a True action as shown below (JS code follows).

var
  //Checkbox that was changed
  $checkBox = $(this.triggeringElement),
  //DOM object for APEX Item that holds list.
  apexItemIDList = apex.item(this.affectedElements.get(0)),
  //Convert comma list into an array or blank array
  //Note: Not sure about the "?" syntax see: http://www.talkapex.com/2009/07/javascript-if-else.html
  ids = apexItemIDList.getValue().length === 0 ? [] : apexItemIDList.getValue().split(','),
  //Index of current ID. If it's not in array, value will be -1
  idIndex = ids.indexOf($checkBox.val())
;

//If box is checked and it doesn't already exist in list
if ($checkBox.is(':checked') && idIndex < 0) {
  ids.push($checkBox.val());
}
//If box is unchecked and it exists in list
else if (!$checkBox.is(':checked') && idIndex >= 0){
  ids.splice(idIndex, 1);
}

//Convert array back to comma delimited list
apexItemIDList.setValue(ids.join(','));

Demo

That's all that's required. Now each time a checkbox is checked/unchecked P1_EMPNO_LIST will be updated to reflect these changes. The checkboxes will persist each time the report is refreshed. You can see the checkbox implantation in this demo.

Considerations

This solution is fairly simple to create an manage however it does have one small caveat. If the list of checked items is very large (more than 4000 characters) you may run into some varchar2 issues. In most cases this shouldn't be an issue but if it is you should test first.

To process the list of comma delimited list you can use the apex_util.string_to_table function and loop over the table values. If you want to use the comma delimited list in a query the following example should work (again it does have varchar2 size limitations).

select regexp_substr(:p1_empno_list,'[^,]+', 1, level) empno
from dual
connect by regexp_substr(:p1_empno_list, '[^,]+', 1, level) is not null

Oracle Developer Choice Awards - 1 Week Left to Nominate

Oracle has launched its inaugural Developer Choice Awards. The deadline to nominate someone has been extended to August 21st so take advantage of this extension and nominate someone you think deserves recognition for the work they do with Oracle!

A description of the competition is below and you can find out more by going to the main site.

The Oracle Database Developer Choice Awards celebrate and recognize technical expertise and contributions in the Oracle Database community. As longtime and new users of Oracle Database move to the Cloud and take advantage of this exciting new architecture, community experts will play a critical role in helping them succeed.

For 2015, awards will be given out in the following technology areas:

• SQL
• PL/SQL
• Oracle REST Data Services
• Oracle Application Express
• Database Design

We encourage you to nominate a developer you believe is deserving of this award. We are accepting nominations through August 21. A panel of judges, composed of Oracle ACEs and Oracle employees, will then choose a set of finalists.

But who chooses the winners? You do! The worldwide Oracle technologist community votes on the finalists from September 15 through October 15.

The winners of the Oracle Database Developer Choice Awards will be announced at the YesSQL! Celebration during Oracle OpenWorld 2015.

This is your chance to shine and earn highly deserved recognition for yourself or your respected peers.

APEX Meetup - Montreal this Thursday!

Note: The location for the Meetup has moved. Please check the meetup page for the new location.

For those in Montreal, I'll be presenting at the local #orclapex meetup this Thursday, August 13, evening from 5-7. All the information can be found on the meetup page.


Top 10 APEX API

APEX has a lot of excellent APIs. These APIs, for both PL/SQL and JavaScript, can help extend your application and improve your development. This presentation will cover some of the most useful APIs (both documented and undocumented) that are available.


The presentation will be in English however I can take questions in both French and English.

I look forward to meeting the Montreal APEX community!

- Martin

APEX and the Order Items are Submitted

This is the last post in a multi-part series on how APEX submits and processes input elements from your browser to the server. The goal is to understand the effects of moving elements around the page. It is important to read these articles in the following order.

- Back to Basics
- APEX and the HTML Form
- APEX and the Order Items are Submitted
- Why does APEX do this? (by John Snyders)

The goal of this article is to highlight the effects of moving items and regions around on a page. This article will be broken into four sections, a high level recap from the previous two articles, moving an APEX item, moving an APEX item along with its corresponding p_arg_names element, and finally a conclusion. It will reference and build upon the example from the previous article.

Recap

I can't stress enough how important it is to read the previous two articles. To recap, when APEX submits the page it'll use the following values to map the HTML elements to their corresponding APEX items.

APEX Item ID    |  APEX Item      |  Element Name
p_arg_names[1]  |  P1_FIRST_NAME  |  p_t01
p_arg_names[2]  |  P1_LAST_NAME   |  p_t02

Moving APEX items

They're some times where it is required to move APEX items around the page after it has rendered. This example will highlight what happens when an item (and only the item) is moved.

Using the example from the previous article suppose you move P1_LAST_NAME before P1_FIRST_NAME using jQuery. 

$('#P1_LAST_NAME').insertBefore('#P1_FIRST_NAME');

The page will look like:

When the page is submitted, everything still works as expected. P1_FIRST_NAME = Martin and P2_LAST_NAME = D'Souza. In this case, it was safe to the move the item around the page since only the p_t02 element was moved. The order of p_arg_names (which APEX uses to map to apex_application_page_items.item_id an p_txx) was not changed.

Moving an APEX item along with its corresponding p_arg_names element.

Using the previous example, suppose the items were split up into two regions as shown below. To help out, each region has been assigned a static id of region-one and region-two respectively.

Using the following code, Region Two is moved above Region One:

$('#region-two').insertBefore('#region-one');

The screen then looks like:

Referring to the previous article not only has the input element for P1_LAST_NAME moved, but it's corresponding p_arg_names hidden element was moved and its overall order has changed. Now when the page is submitted the following is sent to the server (note the order):

p_arg_names[1] = 32629863123858907 - Maps to APEX item P1_LAST_NAME
p_arg_names[2] = 32629789701858906 - Maps to APEX item P1_FIRST_NAME

p_t01 = Martin
p_t02 = D'Souza

Just to recap and highlight what is about to happen:

p_arg_names[1] (P1_LAST_NAME) maps to p_t01 (Martin)
p_arg_names[2] (P1_FIRST_NAME) maps to p_t02 (D'Souza)

You can see the mis-match occur below once the page has been submitted. Martin is stored in P1_LAST_NAME and D'Souza is stored in P1_FIRST_NAME.

Conclusion

Be very careful when moving APEX items around the page. As a guideline, it's usually safe to move individual items, provided you're not moving any p_arg_names hidden elements. If you're moving a region and/or any p_arg_names elements you may get invalid data assignments if the order of p_arg_names is changed.

This issue was first discussed a very long time ago between myself and Dan McGhan on the Oracle Forums: https://community.oracle.com/message/3182532

APEX and the HTML Form

This is the second post in a multi-part series on how APEX submits and processes input elements from your browser to the server. The goal is to understand the effects of moving elements around the page. It is important to read these articles in the following order.

- Back to Basics
- APEX and the HTML Form
- APEX and the Order Items are Submitted
- Why does APEX do this? (by John Snyders)

The goal of this article is to highlight how APEX actually processes the data that is sent data from the browser to the server when a submit button is pressed.

Suppose you have a simple page with two elements: P1_FIRST_NAME and P1_LAST_NAME as shown below:

Stripping out a lot code, the underlying HTML code for this page looks like:

You'll notice that despite their being four input elements they're really only three unique sets of names that are used: p_arg_names, p_t01, and p_t02.  When the page is submitted the web server (APEX) will get/processes the following elements:

p_arg_names[1] = 32629789701858906
p_arg_names[2] = 32629863123858907

p_t01 = Martin 
p_t02 = D'Souza

None of the data sent back to the server make reference of P1_FIRST_NAME or P2_LAST_NAME. As well, p_arg_names is stored in a top down order of how they are in the page.

p_arg_names values are actually the IDs for each of the of the page items as highlighted in the following query:

select item_id, item_name
from apex_application_page_items
where 1=1
  and application_id = 118
  and page_id = 1;

ITEM_ID           ITEM_NAME
----------------- -------------
32629789701858906 P1_FIRST_NAME
32629863123858907 P1_LAST_NAME

APEX is able to map the data back to their corresponding APEX items by first mapping the values in p_arg_names to the apex_application_page_items view and then using the values in the p_txx to retrieve the data that was submitted for each item.

The order that the values are submitted in for p_arg_names must match the p_txx values for APEX to correctly map the values to their appropriate APEX item. I.e. p_args_names[1] will link to p_t01 and p_arg_names[2] will link to p_t02 etc.

Back to Basics: The HTML Form

This is the first post in a multi-part series on how APEX submits and processes input elements from your browser to the server. The goal is to understand the effects of moving elements around the page. It is important to read these articles in the following order.

- Back to Basics
- APEX and the HTML Form
- APEX and the Order Items are Submitted
- Why does APEX do this? (by John Snyders)

As the above note suggests, the next few articles will cover how APEX submits and processes input elements in a page. This article is specifically focused on the basics: HTML forms. It's important to understand how the HTML form tag works and posts input elements. They're other articles that cover this in much more detail and this article will only cover the high level concepts.

Any web page that submits data to it has the following structure:

First name: 
  Last Name: 
  

This will produce a page that looks like:

When the page is submitted the following is sent to the server:

firstname = Martin
lastname = D'Souza

Despite what most developers think, the element's IDs (in this case foo and bar) are not submitted to the server. The server never sees/knows about them. Instead it must use the element's name attribute.

HTML form behaviour has another nice feature that allows for the same name to be used multiple times. Modifying the previous example, the next example will use the name person for both the first and last name elements:

First name: 
Last Name: 

Now when submitting the page the following is sent to the server:

person[1] = Martin
person[2] = D'Souza

The server processes the person attribute as an array of data. Using pseudo code, this is how a web server scripting language may process the data:

...
firstName = htmlForm.person[1];
lastName = htmlForm.person[2];
...

The two key items to take away from this article are:

- When elements are submitted, only the name attribute is sent to the web server and is used to reference the element.
- A form can contain multiple elements with the same name. In this case the web server will view them as an array of data.

A New Chapter

I'm please to announce that starting in July I will be joining Insum as a Senior Technical Consultant! I'm really excited to be joining their team which specializes in Oracle and APEX solutions.

A bit about Insum:

"Insum is a highly creative solution center specialized in Oracle Database, developing innovative applications for clients and partners in North America. Founded in 2002, Insum has been assisting customers realize the full benefits of Oracle Database with Application Express since 2005. In fact, Insum is a pioneer with APEX and is today the largest consultancy specialized in Oracle APEX developments in North America. Privately held with offices in Montreal, Canada and South Burlington, USA, Insum has the most complete and experienced staff to serve the needs for complex application development and deployments in a wide range of industries. Come discover our unique APEX center of excellence"

- Martin

APEX Meetups - Calgary

One of the popular things these days in the APEX community is to have localized APEX Meetups. These are usually monthly meetings where one, or a few, people give a presentation about APEX. These are a lot more informal than traditional conferences and help the community grow in a given area.

I recently launched the Calgary APEX Meetup group: ORCLAPEX-YYC. If you live in or around Calgary and are interested in getting together once a month to hear more about APEX join this group! The plan is to host our first Meetup in early June and are currently looking for a venue to host us. If you can acquire some space please leave a comment on the discussion board.

We're looking to keep these events free and are always looking for sponsors to help fund some of the small costs. If you or your organization is interested please leave a comment on the discussion board.

SQLcl and login.sql

In SQL*Plus you can configure it to automatically run login scripts, typically used to configure your session. These are stored in the files glogin.sql and login.sql. Some examples that you'd find in these files are statements like: set serveroutput on.  You can read more about login scripts here.

SQLcl also handles login scripts, however it may require some additional configuration. The easiest way to leverage this is to create a file called login.sql in your current directory, then call SQLcl from that directory.

If you're like me and launch SQLcl from many directories that approach won't work. Instead, you can create one login.sql file and have it automatically referenced. To do this create login.sql in a folder (in my case it was ~/Documents/Oracle) and then add the following to ~/.bash_profile:   export SQLPATH=~/Documents/Oracle/

Now when you run SQLcl anywhere it will reference your login.sql file.

Thanks to Kris Rice and Jeff Smith for helping with this.

Help Promote Logger

Here's a snippet from a Twitter conversation today between Steven Feuerstein and I around a user that created their own PL/SQL logging platform:

Shameless self-promotion, I know. But it feels so good....I really like to help people. http://t.co/WW91NDOnN9 pic.twitter.com/EuigB0cAIO

— Steven Feuerstein (@sfonplsql) May 15, 2015

@sfonplsql Any reason they’re not using Logger? https://t.co/Q2BViP914Q

— Martin Giffy D'Souza (@martindsouza) May 15, 2015

@martindsouza You haven't done a good enough job promoting it? They don't trust outside code? I don't use it?

— Steven Feuerstein (@sfonplsql) May 15, 2015

Steven is absolutely correct when he says "You haven't done a good enough job promoting it" ("it" being Logger). Despite my best efforts, promoting Logger is something I've struggled with and need some help.

Based on past emails and conversations, I know they're a lot of developers and organizations using Logger. If you're using Logger, either personally or within your organization, I need your help. Please blog about it (and post the link in the comments section below), email me a quote that I can use (martin@clarifit.com), Tweet about it (use #orcllogger), or mention it at the next conference you go to.

To those that help promote it, thank you in advance.

- Martin

APEX Docs for iBooks

I'm not sure if this is new news or not, but you can download the APEX documentation in ePub format which can be loaded into iBooks.

To download the ebook format go to docs.oracleapex.com then click the download button for the appropriate doc and select ePub as the format.

Here's a screen shot of the APEX API docs in iBooks. I still prefer the online / HTML format but having the docs in iBooks helps when you may not have a internet connection.

APEX Request: BRANCH_TO_PAGE_ACCEPT

In APEX, there's a special request type called BRANCH_TO_PAGE_ACCEPT. This can be used in the REQUEST portion of the APEX URL. The syntax for the REQUEST attribute is "BRANCH_TO_PAGE_ACCEPT|".

For example, suppose you are on Page 1 (P1) and want a button which will:
  - Go Page 2 (P2)
  - Set P2_X to some_value
  - Automatically submit the page, simulating the SAVE button on P2

Create a button on P1 that redirects to the following URL: f?p=&APP_ID.:2:&APP_SESSION.:BRANCH_TO_PAGE_ACCEPT|SAVE:&DEBUG.:2:P2_X:some_value

The current documentation for BRANCH_TO_PAGE_ACCEPT states that "Using BRANCH_TO_PAGE_ACCEPT is the same as navigating to page 1, entering a value into the item P1_DATA, and clicking a button that submits the page with a SAVE request." You may interpret this as:

  - Run Page 2
    - Therefor load/run the page rendering regions and processes
  - Simulate clicking the Save button on P2
  - Run the Page Processing of P2

This is not entirely true as the following occurs when a user goes to the above URL:

  - Set P2_X to some_value (this is regular APEX URL behaviour)
  - Execute the Page Processing portion of P2.
    - This includes Validations, Processes, and Page branching on P2 as shown below.

What does not occur is the Page Rendering potion of P2. This is important to note because if you had some Pre-Rendering page processes (i.e. on page load) they will not be run. This is slightly contrary to what the documentation states that it is "... the same as navigating to Page 2 ...".

If everything runs smoothly on the P2 submit page processing (i.e no errors or validations fail) then you will branch to the defined page branch on P2. If something does fail then you will be shown P2 along with the corresponding error message.

It is important to note that currently the page item built-in validations are not executed when using BRANCH_TO_PAGE_ACCEPT. For example, suppose that you set Value Required attribute to Yes for P2_X.

If you run P2 normally, set P2_X to an empty value, then submit the page, an error will appear stating that "X must have some value". This is done automatically for you due to the Value Required attribute. If you use the URL f?p=&APP_ID.:2:&APP_SESSION.:BRANCH_TO_PAGE_ACCEPT|SAVE:&DEBUG.:2:P2_X: it will set P2_X to null, no error will occur as the page item's build in validations aren't executed.

APEX_JSON.PARSE Issue with CLOBs and 11g

Update: this issue is documented on the APEX 5 Known Issues page (bug 20974582) and has a patch for users with access to MOS (bug 20931298).

APEX 5.0 comes with some new APIs including APEX_JSON which manages JSON files.

When using APEX_JSON to consume a JSON file, the process is fairly straight forward:
- Parse: This parses the JSON and stores it in a record type.
- Read values in the JSON object by providing the path to each name/value pair.

The APEX_JSON documentation has some great examples which covers the above process in more details.

There is one "bug" that I found with APEX_JSON.PARSE when passing in a CLOB that has more than 8191 characters.  The reason is APEX_JSON.PARSE uses DBMS_LOB.SUBSTR behind the scenes which has a known bug in 11g R1, 11g R2, and 11g XE. In 12c onwards, this is not an issue. An example of the error that is raised is:

ORA-20987: Error at line 626, col19: Expected ":", seeing "<varchar2>"

ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 292
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 560
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 756
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 774
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 624
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 719
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 615
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 758
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 774
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 811
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 887
ORA-06512: at line 24

The APEX_JSON.PARSE procedure currently has two supported overloaded methods. One which takes in a varchar2, and the other which takes in a CLOB. Thankfully there's a third (undocumented) method which takes in a table of varchar2. Note: in future versions of APEX, this may be a supported procedure.

Here's a sample snippet on how to convert your CLOB to a varchar2 table and then call APEX_JSON.PARSE:

declare
  l_clob clob;
  l_json apex_json.t_values;

  l_temp varchar2(1000);

  l_clob_tab apex_application_global.vc_arr2;

begin

  l_clob := 'load json data here';

  -- Convert clobtotable
  declare
    c_max_vc2_size pls_integer := 8100; -- Bug with dbms_lob.substr 8191

    l_offset pls_integer := 1;
    l_clob_length pls_integer;
  begin
    l_clob_length := dbms_lob.getlength(l_clob);

    while l_offset <= l_clob_length loop
      l_clob_tab(l_clob_tab.count + 1) :=
        dbms_lob.substr (
         lob_loc => l_clob,
         amount => least(c_max_vc2_size, l_clob_length - l_offset +1 ),
         offset => l_offset);

      l_offset := l_offset + c_max_vc2_size;
    end loop;
  end;


  -- Parse clob as JSON
  apex_json.parse(
    p_values => l_json,
    p_source => l_clob_tab,
    p_strict => true);


  -- You can now use the JSON object for l_json
end;

Note: the code to convert the CLOB to the table will be available soon as part of the new OraOpenSource OOS_UTILS project.

Thanks to Christian Neumüller for helping resolve this issue.

OOS Utilities Package

This is a duplicate post originating from OraOpenSource. Normally I try not to duplicate content but in this case I really wanted to get the message out.

Just like any other programming language they're some common tools/features that are not offered natively within the Oracle PL/SQL APIs. As such, most developers start compiling a list of these common tools and create their own utility packages or code snippets.

We're looking to create a master utility package(s), currently called OOS Utils. Before we can do that we need your help! If you have a common snippet of code, procedure, or function that you think will be useful to the entire community, please go to: https://github.com/OraOpenSource/oos-utils/issues and submit a new issue with your suggestion. If you just have an idea but don't have the source code, please submit it anyways and we'll try to add it in.

Hint: If you want to format your code nicely when submitting a recommendation use the following template. 

```sql
your_code_here
```

Note that those are not apostrophes, rather they are backticks (key in the top left of your keyboard).

Once we have enough entries, we'll look at grouping all the suggestions into logical packages and start the development process.

We look forward to your recommendations and can't wait to launch this when it's ready! As always if you want to stay up to date with everything that's going on, please subscribe to our email list.

APEX Backup Script

A long time ago (almost 3 years ago to be exact), I blogged about command line backups for APEX. The script was originally for DOS and required configurations to be stored directly in the script.

Since making the script available I've had numerous requests to update it to support Linux and Mac users. I've done a major overhaul of the backup script which is now available on Github here.

The main changes are that all configurations can be stored in custom.properties and you won't lose your changes when you update the scripts from the repository.

If you have any comments, issues, or feedback please submit them in the project's issues page.

15 Minute Timer on Mac

I need to take a small break every 15 minutes when using my computer. I looked at various timers for OS X to help notify me when 15 minutes were up. I wasn't overly impressed with any of the options that I found.

Then I stumbled upon a neat feature in OS X that will announce the time every hour, half hour, or quarter hour. To enable it, go into System Preferences, then Date & Time. Select the Announce the time check box and the desired interval.

You can also change the voice for the time announcement by clicking on the Customize Voice button.

Though this solution isn't perfect it seems to be working well for me. The only catch is that in noisy environments or when I have the audio off I can't hear the notification.

APEX 5: Creating Sub-Menus

APEX 5 has gotten rid of the old APEX tabs concept and instead replaced it with the APEX Navigation Menu. This is a very positive change and, tied in with the new Universal Theme it, certainly makes life easier.

One thing that I really like is the simplicity to create sub-menus. They're various ways to create sub-menus. This post will cover creating sub-menus from both the page creation wizard and via the Shared Components > Navigation Menu options.  There will be three pages in this example: parent, child, and grand child.

To start, I'll assume that you have one page and one Navigation Menu item called Parent. Next, create a second page called Child using the wizard. In the wizard, configure the Navigation Menu options as shown below:

Finish the page creation wizard. When you run your app you'll now see the Parent/Child menu and sub-menu.

Create a third page, called Grand Child. This time when you get the the Navigation Menu wizard option you can't select the Child page as it's parent. It is shown in the list, but greyed out. To get around this issue, just select "No Parent Selected" and finish the wizard. 

To make the Grand Child page a sub-menu of Child:

• Go to Shared Components > Navigation Menu. 
• Edit the Grant Child list entry.
• Select Child for the Parent List Entry and click Apply Changes.

If you rerun the application you should now see the updated sub-menu structure.

The above example show the default Navigation Menu with the new Universal Theme, which is a side menu. To make it into a top navigation menu with a drop down sub-menus:

• Go to Shared Components > User Interface Attributes.
• Edit the Desktop theme.
• Click on the Navigation Menu tab, and make the following changes:

If you refresh the page the Navigation Menu will now be at the top of the page and has the drop down menu/sub-menu.

ODTUG Oracle Blogs - Now on Twitter

A while ago, ODTUG put a heavy emphasis on communities. As a result, 6 main communities were created: APEX, BI, EPM, Development, DBA, and ADF. Each of these communities have their own site dedicated to them along with a blog aggregator (click on each of their titles for the appropriate page).

The blogs contain an amazing amount of content from the entire Oracle community. The authors include Oracle ACE (Associates, ACEs, and Directors), Oracle employees, speakers from all the major conferences (including Kscope), book authors, etc.

Up until last week the only ways to take advantage of the blog aggregator were to go to the appropriate ODTUG community site or subscribe to the respective RSS feed. Starting today you can subscribe to the following Twitter accounts to get a message in your feed each time a new article is posted for your community.

• APEX: @orclapexblogs
• BI: @orclbiblogs
• EPM: @orclepmblogs
• Development: @orcldevblogs
• DBA: @orcldbablogs
• ADF: @orcladfblogs

Each tweet will also contain the community's appropriate hashtag. ODTUG has the complete hashtag listing here: http://www.odtug.com/hashtag If you're new to Twitter, following your appropriate hashtag can be a quick way to find some excellent and helpful content.

Some tweets will contain the author's Twitter handle (via a @mention). We're currently in the process of updating all the twitter handles for the authors in each community. If you write an article and don't see your Twitter handle show up, just reply to the appropriate Twitter account and we'll fix it.

As always, if you want to add your blog to the list, please email lauren@odtug.com.

Thanks to Adrian Png and Patrick Wolf for their ideas and patience getting this setup!

Installing SQLcl

SQLcl is the new command line tool from Oracle, more specifically from the SQL Developer team. It is currently an Early Adopter (EA) release and you can download it from: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-41ea-2372780.html

After testing it for a while I was hooked and plan on using it as a full time replacement for SQL*Plus (which I think is the intent of the product).

The only difficulty I had was where to store it on OS X so it was accessible everywhere. Here's how I "installed" it and hopefully this will be useful for others:

cd ~/Downloads
#Note: version/filename of file will change for each release
unzip sqlcl-4.1.0.15.067.0446-no-jre.zip

#Assuming you already have an /oracle directory (I had one for the instant client)
cp -r sqlcl /oracle

#Give sqlcl execute permission
cd /oracle/sqlcl/bin
chmod 755 sql

#rename to sqlcl so no confusion (optional)
mv sql sqlcl


#Add directory to path so can run anywhere in the command line:

#Temporary access:
PATH=$PATH:/oracle/sqlcl/bin

#Permanent access:
#Note: See Barry's comment below about storing in /etc/paths.d on Mac
vi ~/.bash_profile
#Add just above the export PATH line
PATH=$PATH:/oracle/sqlcl/bin

In the above example I installed SQLcl in the /oracle directory. You could also put it anywhere you want such as /usr/local/oracle etc. Just make sure that you reference the location in the PATH environment variable.

If you're looking for more info on SQLcl as well as some excellent examples check out Kris Rice's blog and Jeff Smith's blog.

Update: The following article covers how to create login scripts for SQLcl.

JS Console Wrapper Moved to GitHub

I recently received an email from Google Code stating that they will be shutting down their site early next year. Seeing as an JS Console Wrapper was hosted on there I have had to move it to GitHub: https://github.com/martindsouza/js-console-wrapper

They're some nice features in Console wrapper that integrate well with APEX however it was primarily created to get around the lack of support for Console in IE. Since the newer versions of IE now support Console, Console Wrapper may no longer be necessary.

If you do use Console Wrapper and would like to see it be improve please create an issue on the project page.

Logger 3.0.0 EA1

In case you missed yesterday's announcement from OraOpenSource, Logger 3.0.0 EA1 was released yesterday. If you're using Logger you definitely want to read the article and learn about all the major updates to Logger. If you're not using Logger, now may be a good time to take a look and see how it can help your organization.

Logger is Moving

I'm pleased to announce that Logger has found a new home and will be maintained by OraOpenSource. A very big thank you goes out to Tyler Muth for starting this project many years ago and handing over the reins to OraOpenSource.

Here's a brief FAQ regarding the move:

What is Logger?

Logger is a (free) open source logging tool for Oracle PL/SQL. It is used in many instances ranging from small applications to very large applications in major corporations.

What will happen with links to the old project page?

They'll still work. The project was forked and a notice was put up on the old Github site letting people know its new home.

Will Logger still remain open source / free? 

Yes. We'll update the license slightly so that it will be easier to understand and use within your code. This will not affect any existing implementations.

When is the next release?

I did a lot of work last year, then was sidelined with my concussion. It turns out that picking up a project after not touching it for 8 months takes some time.

Since I'm working on this project as part of my rehab I can not give a definite time frame. As soon as the beta version is ready I'll make it available.

What is in the next release?

Lots of great stuff! Going forward you can monitor what will be in each subsequent release by tracking the upcoming milestones on the project's issue page.

Will you still post info on Logger on this site?

Over the years I have written a lot about Logger and posted it on this blog. To maintain a consistent branding and marketing effort all future posts regarding Logger will be on OraOpenSource's blog.

What can I do to help?

Right now the best thing you can do is sign up for the email list (link on oraopensource.com/logger). We'll be announcing the beta version and next major release when it's available. You can also follow OraOpenSource on twitter: @oraopensource.

Once the next release is available we'll be looking for help from the community to continue to develop and maintain Logger.

APEXposed 2015 - Montreal May 6th

It's back! APEXposed is coming back to Montreal this year for a 1 day event. This conference is lined with some amazing speakers and will be your chance to learn lots on APEX 5, ORDS, and so much more.

They're a few things different with this year's conference when compared with previous years. The first, and biggest is that it's free, yes FREE/GRATUIT, for ODTUG members. For non members it's $150. If you're not an ODTUG member and are looking to save a few bucks it's only $99 for a membership so it's definitely worthwhile to get a membership and then attend the event for free. So sign up now if you don't have a membership.

The second big change is that it's only one day instead of two. We think that this will make it easier for people in and around the Montreal area to commit to. The conference will still cover a lot of great content, it's just packed into one day this time.

Aussi, si vous êtes plus à l'aise avec la langue française, il y aura deux présentations en français.

For more information please refer to the conference page: http://www.odtug.com/apexposed2015

Introducing OraOpenSource

As some of you know,  I have been out since last June recovering from a concussion. It has been a very long and lengthy process that has really pushed my limits. The good news is that I'm getting better and have been able to increase my computer time.

Which leads to the topic of this post, OraOpenSource. One of the things that has been very difficult for me to do is use a computer so as part of my recovery/rehab I had to find a small project to work on. The goal was to help me get used to longer screen times and also get back into coding, reading smaller fonts, etc. Thus I decided to start a major push towards open source development for Oracle.

What is OraOpenSource? As the name suggest, it's a site dedicated to open source software for Oracle. They're currently two active projects listed on the site, with more to come. The first major project (which was announced via Twitter last week) is called Oracle XE & APEX VM. It is a build script to quickly build an entire instance. I think it will be very useful for developers who want to quickly build a machine on the cloud and for new people to Oracle.

So what can you do to help out? First, check out the new site oraopensource.com You can also follow us on Twitter: @oraopensource We also have a Github site where all of the projects will be kept: https://github.com/OraOpenSource Check it out and help out on any of the projects that interest you.

We'll be launching more products and projects this year so be sure to keep an eye out on Twitter and read the blog!

- Martin


On a personal side note regarding my continuing recovery, I'm still not 100% yet and it will take some more time to get there. I'm starting to get back onto Twitter etc. I don't have an ETA for full recovery as each brain injury is different and the recover period widely varies for each person. I appreciate all the support I've been getting from everyone!