Tuesday, June 24, 2014

My Early Departure From Kscope 14

Unfortunately I had to cut my Kscope 14 trip short. I had hit my head pretty hard right before leaving for Kscope and it turns out I gave myself a "mild" concussion. I'm not sure why they call it mild since it really threw me off.

 If you've ever had a concussion before then you'll know what I'm going through. The only way to recover is to rest in a quite location for as long as it takes. I had hoped for a speedy recovery but that just isn't going to be the case for me.

I want to thank everyone for coming to Kscope 14 and I hope you enjoy your time at the conference. For those looking to attend my presentations, I sincerely apologize for cancelling at the last minute. I will try to do a webinar on Logger when I'm feeling better and time permits.

See everyone at Kscope 15!

- Martin


Thanks to all those that have sent me messages. I need to keep my screen time to a minimal and will reply later on.


Posted by at No comments:

Saturday, June 21, 2014

#TourKscope

In January the ODTUG board had a meeting. During the trip I took some time to explore Seattle, the hotel, and conference centre. Here's a summary of the tweets that I sent out. If you're in Seattle and looking for things to do this should help.


Posted by at No comments:

Monday, June 16, 2014

Presenting at ODTUG Kscope 14

I'll be heading to Seattle soon for ODTUG Kscope 14 which officially kicks off on Sunday, June 22nd. Kscope is the premier Oracle developer conference with experts flying in from around the world to participate in a week lot of excellent talks and great networking opportunities.

I will be giving the following presentations on APEX and PL/SQL / at these events this year.


Lunch with Oracle ACEs

When: Mon Jun 23, 2014, 11:15 - 1:00 pm

It’s your chance to meet the ACE Directors and ACEs up close and personal. They will be sitting at tables designated with topics of their expertise. Feel free to join one of those tables for lunch to ask technical questions or just to meet and talk with these Oracle user legends.

Just for the record, I didn't come up with description ;-). Essentially most of the ACEs (ACE Directors, ACEs, and ACE Associates) will be sitting at different tables in the lunch area and you can sit at their table. If you have questions for a specific person bring them and ask them. If you read this blog then you know I'm good for any APEX, PL/SQL, SQL, and Web questions in general. Of course I'm always open to talk about anything else!


Just Log It.

 When: Mon Jun 23, 2014, Session 2, 1:15 pm - 2:15 pm
Room: 607


Abstract: Code instrumentation is critical in helping developers debug applications. This presentation will cover Logger, which is an open-source PL/SQL logging tool used in many organizations. The latest features of Logger will be discussed, along with integration into APEX and how to take proactive approaches to error handling in your applications.

This talk will cover the open source PL/SQL logging tool: Logger. I'll also be covering some of the new features that are being actively worked on.


APEX & HTML5

Note: this was supposed to be a talk on APEX 5.0 and had to be changed due to some scheduling conflicts. If you were hoping to see the APEX 5.0 talk I encourage you to go to Dietmar Aust's presentation on Tues at 2:00 pm in room 303.

When: Tues Jun 24, 2014, Session 7, 11:15 am - 12:15 pm
Room: 612

Abstract: This presentation will cover some of the new HTML 5 features and how to integrate them in APEX. HTML 5 is the next major version for HTML. It consists of many new features, which will make developer's jobs a lot easier and improve end-user experience. This presentation will highlight some of these new features and demonstrate how to leverage them within an APEX application. New features include (but not limited to) drag & drop, media, storage, and graphics.

I have given several HTML 5 talks at Kscope before and they have all covered new features. This is no different as it will cover some really cool new features in HTML and show how you can add them to your APEX applications.


Oracle APEX Lunch and Learn (sponsored by OTN)

When: Tues Jun 24, 2014, 12:15-1:45pm
Room 619/620

Time to bring your Oracle APEX questions for this session as it's a free for all Q&A with fellow Oracle ACE Directors Scott Spendolini, Dimitri Gielis, Roel Hartman, Peter Raganitsch.
Posted by at No comments:

Thursday, June 12, 2014

#Get2Kscope

Back in January we had an ODTUG board meeting in Seattle at the same location of Kscope 14. Instead of taking a taxi to the hotel, I decided to use the train that brings you 1 block away from the hotel and I'm glad I did! It only cost me $1.75 USD and I avoided all the traffic.

I tweeted my experience on getting from the Airport directly to the Sheraton hotel. For those going to KScope next weekend I hope you find these instructions helpful.














Posted by at No comments:

Saturday, April 12, 2014

NTILE vs WIDTH_BUCKET

Rick Cale recently asked a question on an article I wrote two years ago about Some Interesting Oracle Analytic Functions. His question (see comments on Apr 11, 2014) were about the results from the NTILE function and how the same value could be in two different buckets. It was an excellent question and one that got me digging a bit more into the functionality of NTILE.

They're two ways to handle "bucketing data" in Oracle. In the documentation Oracle describes the two types as either having equiwidth or or equiheight histograms.

 Equiwidth (NTILE): Each bucket will have the same number of items in it with some buckets having at most 1 more item than other buckets. An easy way to think of this concept is to order all the items, then divide the data evenly into groups based on the number of buckets. For example, supposed you have 9 values (AAAABCDEF) and wanted to put them into 3 buckets. the buckets would be B1 = AAA, B2 = ABC, B3 = DEF. You'll notice that the value "A" is in both buckets B1 and B2.

When an uneven amount of objects need to go into the buckets, NTILE will fill the first bucket first, second bucket second, etc. For example, suppose you had 10 values (AAAABCDEFG) and wanted to fill them into 3 buckets. (Note this is similar to the previous example with an additional "G"). The buckets would be B1 = AAAA, B2 = BCD, B3 = EFG.

Equiheight (WIDTH_BUCKET): This will take the min and max range, divided by the number of buckets and place each value in it. For example, the salaries in the EMP table range from 801 ~ 5000. If you set the min/max range from 0~5000 3 buckets will be created. All values from 0~1,666 will go into B1, values from 1,6667~3333 into B2, and values 3334 to 5000 into B3. (Note: for simplicity I took out decimals in this split). 


An easy way to think of these two bucketing methods is that NTILE divides values based on the number of items. WIDTH_BUCKET divides values based on their values.

Here's an example that highlights the difference between the two functions. 
with data as (
  -- using this as data input
  select 3 as num_buckets
  from dual
)
select 
  d.dname, 
  e.ename, 
  e.sal,
  ntile (data.num_buckets) over (order by sal asc) ntile,
  width_bucket(sal, 0, max(sal+1) over (), data.num_buckets) width_bucket
from emp e, dept d, data
where e.deptno = d.deptno
order by sal;


DNAME          ENAME      SAL        NTILE      WIDTH_BUCKET
-------------- ---------- ---------- ---------- ------------
RESEARCH       SMITH         801          1        1
SALES          JAMES         950          1        1
RESEARCH       ADAMS        1100          1        1
SALES          WARD         1250          1        1
SALES          MARTIN       1250          1        1
ACCOUNTING     MILLER       1300          2        1
SALES          TURNER       1500          2        1
SALES          ALLEN        1600          2        1
ACCOUNTING     CLARK        2450          2        2
RESEARCH       JONES        2975          2        2
RESEARCH       SCOTT        3000          3        2
RESEARCH       FORD         3000          3        2
SALES          BLAKE        3850          3        3
ACCOUNTING     KING         5000          3        3
It's important to note that WIDTH_BUCKET is not an analytic function but NTILE is. For more information read the documentation for each function. For WIDTH_BUCKET, the documentation covers what happens with values outside the min/max range (they go into bucket 0 and num_buckets+1).
Posted by at No comments:
Labels: ,

Monday, March 31, 2014

Running Custom Code for Tabular Forms (Part 1)

Note: An updated version of this article is now available which covers how to run custom code for a manual Tabular Form in APEX 5. There is also a new article which covers how to modify data from multiple tables in the same Tabular Form.

One of my biggest pet peeves with Tabular Forms in APEX is that it would only run basic (Insert, Update, Delete) DML functions against a table. This works really well for basic situations but more often than not data must be processed by a procedure to handle all the business logic. For this reason, I've avoided Tabular Forms for a very long time.

Last week at OGh APEX World, Dimitri Gielis showed how you can run your own procedure against a tabular form. Here's how to do it:

First, create a Tabular Form using the standard wizard. This will create the standard validations and processes for the page. Their will be two Page Processing processes as shown below.

These processes will automatically handle any of the data changes that a user makes in the Tabular Form. For now you can go ahead and remove each process as we'll use a custom procedure to process the data instead.

Next, create a new process. The important part comes when creating the process; be sure to select the Tabular Form option (as shown below).

In your PL/SQL block, you can now reference each of the columns using their column names (example :SAL and :ENAME). What's even better is that APEX will only run the code against rows that have changed which can save a lot of processing time. For example, if only two rows in a 15 row table were changed the code will be executed twice.

In the next article I'll show how to expand this functionality beyond a base table and use this new technique to modify any data set.
Posted by at 2 comments:
Labels:

Sunday, March 16, 2014

oracleapex.com (part 2)

Back in December I blogged about the new domain: oracleapex.com. I used the domain to host shortcuts to common links for APEX. One example that I use on a regular basis is api.oracleapex.com which points to the latest API APEX documentation.

Initially I used the blog post to host the list of available shortcuts. This wasn't the best solution but it allowed me to quickly inform everyone of the URLs. I have since created an application (built using APEX of course) to list all the redirect shortcuts on oracleapex.com.

Check it out and let me know what you think. Of course if you'd like a specific URL please let me know either by leaving a comment or tweeting me at @martindsouza
Posted by at No comments:

Thursday, March 13, 2014

APEX 5.0 and KScope 14

A few weeks ago the Oracle APEX team released APEX 5.0 EA1 (Early Adopter). You can try the new version of APEX at ea.oracleapex.com.

I expect that the APEX team will release an EA2 and possible EA3 version before officially launching APEX 5.0 with the final release coming around June. Just to be clear, these are my personal estimates not any inside information.

Releasing APEX 5.0 in June would be perfect timing as it coincides with Kscope 14. The conference has a lot of talks specifically focused on APEX 5.0 from both the Oracle development team and some of the worlds leading experts on all things APEX. I think that this conference will be a great opportunity for people (both developers and managers) to learn about all the new features and get hands on experience with this latest version.

One of the unique aspects of Kscope is that you'll be able to easily talk to the Oracle APEX team, Oracle ACE and ACE Directors, and other experts in the community. These are the people that write the blogs, read the books, and do the webinars that you may have read/seen. You can sit down with them and get their advice on how to integrate 5.0 and leverage it to enhance your current applications and increase ROI.

I'll be giving a talk at Kscope 14 called "APEX 5.0 - New Features Highlights". It will cover some of the new aspects of APEX that will help both developers and the business. You can see all the Kscope 14 APEX abstracts here. If you're thinking of coming to the conference you should register soon as Early Bird rates end on March 25th.

Hope to see you at Kscope 14!
Posted by at No comments:
Labels: ,

Wednesday, March 5, 2014

Referencing USER in APEX


It’s not uncommon to reference the current user as USER in your pl/sql code. A simple use case may be to determine the client or environment that you’re running in (ex: dev, test, prod).

Referencing USER will have some slight side effects when running the code in APEX as the current USER is actually APEX_PUBLIC_USER (or what ever user you configured). This can cause issues in your application. To resolve it, simply reference sys_context('userenv','current_schema’) instead.

Example: 
-- Via SQL*Plus
select user, sys_context('userenv','current_schema')
from dual;

USER        SYS_CONTEXT('USERENV
---------- --------------------
GIFFY        GIFFY
If you run the same query in APEX the output is shown below. You'll notice that referencing USER it does not display my current schema (GIFFY in this case).
The same applies to compiled code executed from APEX. For example if you have a procedure that references USER and that procedure is run from APEX then USER will be APEX_PUBLIC_USER.

This can be really tough to detect in automated tests as when testing via SQL*Plus, USER will return the current schema name.
Posted by at 2 comments:

Tuesday, February 25, 2014

Oracle APEX Training

I'm pleased to announce that I'll be doing some online Oracle Application Express (APEX) training! After giving talks around the world, writing several books, and teaching at a local Institute of Technology I think it's only natural to expand into the online world.

The first course I'm teaching is called APEX Web Components covering CSS, JavaScript, and jQuery. The course go over each of the technologies and how to integrate and enhance your APEX applications.

This course will run from April 14th to April 16th from 12-4pm EDT each day. For more information and to register go to: http://clarifit-orclapexweb.eventbrite.com/?aff=talkapex As an added bonus as a reader of this blog use discount code TALKAPEX for an additional $100 off registration. 

If you'd like to keep informed of future classes please sign up for our email list.


Posted by at No comments:

Monday, February 24, 2014

APEX Shortcuts


The other day I was dabbling around in APEX and noticed a link for Shortcuts in the Shared Components section.



I’ve never used Shortcuts before (let along knew about them) so I tried it out. To start here’s how Shortcuts are described (as copied from APEX screen):

Shortcuts are a repository of shared static or dynamic HTML. Shortcuts are substitution strings that are expanded using the syntax: "SHORTCUT_NAME". Shortcuts are used in the following locations:

  • Region Source for regions of type HTML_WITH_SHORTCUTS
  • Region Templates, Region Headers & Footers
  • Item Labels
  • Item Default Value
  • Item Post Element Text
  • Item Help Text
  • HTML Header of a page

Creating shortcuts on page item labels and page item post element text attributes can include the following substitution strings: 
#CURRENT_FORM_ELEMENT# 
#CURRENT_ITEM_ID# 
#CURRENT_ITEM_NAME# 
#CURRENT_ITEM_HELP_TEXT#

To reference Shortcuts you need to use the “shortcutname” syntax (quotes included). Since they are wrapped in quotes and could conflict with regular text I strong recommend using a naming scheme such as SC_NAME.

Note: I previously wrote an article about the different ways to reference APEX variables. I have updated it to include Shortcuts. The article is available here.

Shortcuts can either be statically defined or reference a PL/SQL function. It’s important to note that if you do call a PL/SQL function it will execute the code each time the Shortcut is referenced. For example, if you have the same Shortcut in three different regions on a page it’ll call the function three times. This may be a good or bad thing depending on how you use it.

Though I haven’t found an immediate need for Shortcuts I think there could be some situations where it can come in handy for labels and templates especially since it allows you to reference a function which can dynamically generate content.

Posted by at 4 comments:
Labels:

Saturday, February 8, 2014

APEX Theme Competition

In case you haven't heard, ODTUG is hosting an APEX Theme Competition. This is your chance to show off what you can do and also win some amazing prizes including a Kscope 15 pass!

For more information go to http://competition.odtug.com/
Posted by at No comments:
Labels:

Wednesday, January 15, 2014

Changing Default Item Required Error Message in APEX

In APEX, if a page item is a required value it is no longer necessary to create an explicit validation. Setting the item's option "Value Required" to Yes will make it a required field as shown below.


This is much quicker then manually creating Not Null validations for each required item as required in older versions of APEX.

If the page is submitted and the item is null, then the page will be reloaded with a validation error message stating that "" The default message can me changed by doing the following:

  • Go to Shared Components > Text Messages (under Globalization)
  • Create Text Message
    • Name: APEX.PAGE_ITEM_IS_REQUIRED
    • Text: #LABEL# can not be null
      • Use what ever message you'd like to see.
      • Note that the substitution string #LABEL# is used for the item's label.
When the page is now submitted and the item is null, the page will be reloaded with a validation error message stating that "". 

The above technique changes the default message for all items that have their Value Required option set to Yes (i.e. it's an application level setting).
Posted by at 3 comments:
Labels:

Monday, January 13, 2014

Stopping Dynamic Actions using apex.da.cancelEvent

Update: Nick Buytaert wrote a follow-up article on the apex.da.resume function. I suggest reading it along with this article in case you need to resume a Dynamic Action (which is especially useful for plugins).

They're a few times when you may need to stop a Dynamic Action part way through the executing all its actions. Since individual actions don't have conditions associated with them there's no supported way to do this. Thankfully there's an undocumented JavaScript (JS) function called apex.da.cancelEvent. Here's an example of how to use it:

Suppose you have a page with two items, P1_X and P1_Y, and a button on P1. When the user clicks the button the Dynamic Action (DA) needs to set P1_X = X and P1_Y = Y. The DA setup will look like the following:


When you click the button P1_X and P1_Y are set to X and Y respectively. 

Now suppose that after P1_X is set you wanted to stop all other actions in the DA (in this case setting P1_Y). There's no way to declaratively do this. Instead you must use the apex.da.cancelEvent function. 

Add a new JavaScript "True Action" to the existing DA with sequence 15 (so its between the two other actions). In the code section use: apex.da.cancelEvent.call(this);  Be sure to uncheck the "Fire On Page Load" option. The updated DA will look like image below (the new action is highlighted).


If you refresh P1 and click the button associated to this DA only P1_X will be set. The third action in this DA will not be executed.

The above example probably isn't the best use case for leveraging apex.da.cancelEvent however it does highlight its functionality. It is useful if you create your own custom confirm notice or some other process that determines if the rest of the actions in a DA can occur.

Note: You'll notice that I used the additional call() function when calling apex.da.cancelEvent in the action. Calling it defines the value of this in the function call as it is required in the cancelEvent function. For more information please read the JS call() documentation.

Posted by at 4 comments:
Labels: ,

Wednesday, January 8, 2014

Parsing Imperfect JSON

In JavaScript, each JSON attribute name should be wrapped in quotes for it to be parsed correctly. Sometimes you may receive a JSON string that isn't properly formed because of the missing quotes. You can still parse it using the technique described below.

To start, here's an example of malformed JSON using JSON.parse and jQuery's $.parseJSON. You'll notice that it doesn't parse the JSON string since it doesn't have its names wrapped with quotes.
//Using native JS: JSON.parse
//Similar result for jQuery $.parseJSON
var jsonStr = '{ename : "martin", sal: 100}';
var jsonObj = JSON.parse(jsonStr);

//Raises error: 
SyntaxError: JSON.parse: expected property name or '}'


//Correct JSON object (notice attribute names are quoted)
var jsonStr = '{"ename" : "martin", "sal": 100}';
var jsonObj = JSON.parse(jsonStr);
console.log (jsonObj);

//Returns JSON object
Object { ename="martin", sal=100}
To get around this issue you can use JavaSript's eval method. It's not pretty but it does the job and will probably save you a lot of time trying to correct your JSON object.
var jsonStr = '{ename : "martin", sal: 100}';
eval('jsonObj = ' + jsonStr + ';'); 
console.log(jsonObj);

//Returns JSON object
Object { ename="martin", sal=100}
I've found this technique really helpful when passing back JSON strings from APEX AJAX calls (via a Dynamic Action, Plugin, or custom AJAX).

Update: Please read John's comment below as he makes a good point that the data should be data that you control otherwise perfect area for a JS injection attack.
Posted by at 3 comments:
Labels:

Monday, January 6, 2014

Derived Columns in APEX Standard Reports

A while back I wrote an article about a feature in APEX's standard reports called Derived Columns. Despite using APEX for a very long time I had never really noticed it or used it before. Someone asked for some more information on derived columns, so this is a follow up article with a bit more detail.

Derived columns are place holders for an additional column in the report. The column can reference any of the query's columns but has some limitations such as not declaratively supporting links or being about to compute a value. It's most useful when you need an additional column to be displayed in a specific HTML format.

In the past when I wanted an additional column for display purposes I would just add it in the query as "..., null my_additional_column ... " and then modify the column's HTML Expression value. Now I can just a derived column and modify its HTML Expression.

To add a derived column, go to the Report Attributes then click Add Derived Column on the right hand side as shown below:


After clicking the above link a new column will appear in the list of columns.



You'll need to edit the newly added derived column and add something in the Column Formatting > HTML Expression field for values to appear. Tip: to reference columns in the HTML Expression field use the #COLUMN_NAME# notation. 
Posted by at No comments:
Labels:

Thursday, January 2, 2014

2014 Speaking Engagements

Wow, 2013 went by in a blur. Lots of exciting things happened last year both personally and professionally and I really look forward to 2014! Speaking of which, I have finally confirmed talks for the first half of the year which I've included below:


Kickoff to Kscope - Oracle ACE Directors Panel
Seattle, Washington
Jan 9th 4-6pm - Sheraton Seattle Hotel - Jefferson Room

Bring your Oracle questions (in pretty much any category) and discuss them with myself and fellow panelists (and ACE Directors) Kellyn Pot'Vin, Tim Gorman, Tim Tow, and Cameron Lackpour. This session will be moderated by ODTUG president Monty Latiolais.

Following the panel there will be a Happy Hour networking event. Both the panel and happy our are free!


Oregon and Southern Washington Oracle User Group (OSWOUG)
Title: APEX and HTML 5
Seattle, Washington
Feb 19th 4:30pm - Oracle Office (411 108th Ave NE #900, Bellevue, WA, 98004)

As the title suggests, I'll be talking about some really cool HTML 5 features and how to integrate them in your APEX applications. Of course I'll stick around after to answer any of questions.

Note: The OSWOUG website will be updated soon with my complete abstract.


OGh APEX World
Title: TBC (waiting for final abstracts to be accepted)
Zeist, Netherlands
March 25th (exact location TBC)

This will be my first talk in Europe! The speaker list is already very impressive with several ACE Directors and people from the APEX development team.

I'm not able to comment on the talk that I'll be giving yet as I sent in several abstracts to the content committee and will find out which one is selected soon. Once it's finalized I'll update this post with the exact details.

The above link is to the Call for Papers site which is still open until January 10th. If you're in Europe and interested in presenting at APEX World, it's still not too late.

I also hope to go speed skating there as the Netherlands is a mecca for long track speed skating. Trying to talk Roel Hartman into a race jj ;-)


Calgary Oracle User Group (COUG)
Title: Instrumenting your PL/SQL and APEX Applications
Calgary, Alberta
April 17 8:30am - Suncor Energy Centre (West Tower). 150 6th Avenue SW. 17th floor – Room 17L

This talk will cover Logger, an open source logging tool that I've been actively working on, and how to integrate it with APEX. I'll be covering a lot of PL/SQL integration so if you're not an active APEX developer it'll still be relevant.


ODTUG Kscope 14
Title: Just Log It.
Title: APEX 5 (TBC)
Seattle, Washington
June 22-26 - Sheraton Seattle Hotel

 It goes without saying the Kscope 14 is the premier Oracle and APEX conference to attend. Besides having excellent content there's a lot of great opportunities to network and meet the experts from around the world. If you've read an Oracle book before, odds are the author(s) will be at Kscope. Best part is everyone is very friendly and approachable.

I'll be giving two presentations. The first is on Logger, an open source logging tool that I've been actively working on, and how to integrate it with APEX. The other presentation will be on APEX 5. Seeing as it's not out yet I don't have a specific topic but I'll make sure it's interesting.


That covers the first half of 2014. I'm really looking forward to all the events and most importantly seeing and meeting everyone!

- Martin
Posted by at 2 comments:
Subscribe to: Posts (Atom)