Cloud Data Loading gets easier

 For people trying out the database service on http://cloud.oracle.com, data loading got 1 step easier.  Before this, there would would have to be an APEX user created with roles granted.  While this still will work, now you can simple use the cloud user created at signs ups.  This is the administer's login created in the initial welcome emails.

To setup a new user in the cloud that allows data loading  simply launch the IDM console from your service and assign the Database Developer or Admin role to that new user.

APEX Listener PL/SQL Validations

There's a 2 ways to lockdown which applications/URLS are accessible for any APEX installation with the APEX Listener.

First, I showed the new javascript method in the 2nd half of this post.  This is very nice as it stops any database communication at all.

Now let's talk about the PL/SQL validation function and the part that I forgot to document.  The normal  PL/SQL Validation that has been in mod_plsql for a long time is supported.  With that method, you have a function that takes in a varchar and returns a boolean if it's allowed or not.  The input is the last portion of the URI.  That means for Application Express it would be something like "f" or "wwv_flow.show".  This works great if you want to allow or deny an entry point.

function authorize(
    procedure_name in varchar2)
    return boolean;

The bind is assumed in this case and not needed in the configuration.  The config file for this would look like this

<entry key="apex.security.requestValidationFunction">
authorize
</entry>

The new way is similar however you can reference some bind values.  This offer more flexibility on which web server which Application Express applications are allowed on.

Let's take and example of 1 Application Express install where there is a public application ( app id 123 ) and an admin application ( app id 456) .  For security reasons, the admin application is not to be accessible to the internet.  The following function would only allow app id 123 and allow all others be blocked.

create or replace function is_allowed(
    flow_id in varchar2,
    page_id in varchar2)
    return boolean as
begin
   if ( flow_id = 123 ) then
      return true;
   end if;
   return false;
end;
/ 
show errors

The configuration for this would look like:

<entry key="apex.security.requestValidationFunction">
is_allowed(flow_id=>:P_FLOW_ID,page_id=>:P_FLOW_STEP_ID)
</entry>

There Apex Listener looks for the bind and puts them in automatically when referenced.  The full list available is:

 :URL, :PROCNAME, :P_FLOW_ID , :P_FLOW_STEP_ID, :P_INSTANCE, :P_PAGE_SUBMISSION_ID , :P_REQUEST"    

Now that the external webserver is setup and locked down to only our 123 application.  There is another Apex Listener installed on an internal webserver.  This internal install could run with no function at all and that would allow all applications to be accessible.

OTN Labs at RMOUG Training Days 2013

OTN is putting on a set of labs on Monday before the show that are FREE to anyone.  You can join David Peake from the Application Express team, Jeff Smith from the SQL Dev team, Maria Colgan from the Optimizer, and myself for the labs.

There's 2 rooms that will be running with the below agenda.  The first room is performance/optimizer focused.  Starting with Maria and the optimize then showing what tuning features are in SQL Develoer. The second will walk you from beginning to end of building an application. It starts in the SQL Developer Data Modeler then into SQL Developer, and finally building the application in Application Express.

	Room TBD	Room TBD
01:00:00 PM	How to prevent suboptimal execution plans This session shows the process of analyzing and resolving the most common SQL execution performance problems including, poor cardinality estimations, bind peeking issues, selecting the wrong access method and more. You will learn how to identifying and quickly resolving these issues and develop a toolkit of queries and techniques to determine the root cause of any plan regression.	Re-engineering Your Database Using Oracle SQL Developer Data Modeler 3.1 Learn how to use SQL Developer Data Modeler to import your database schema, make changes and generate the modified DDL.
02:00:00 PM		Testing and Debugging Procedures using SQL Developer 3.1 Learn how to run a script to update your database, review database objects in your database, create and debug a PL/SQL procedure and create an run a unit test in SQL Developer.
03:00:00 PM	Tune your SQL with the SQL Tuning Advisor Learn about Optimizer Access Paths using Oracle SQL Developer Working with Tuning Utilities in Oracle SQL Developer This set of 3 tutorials shows you how to work with the tuning utilities in SQL Developer.	Building an Application using Oracle Application Express: Part 1 Learn how to use Application Express to load data into your database, create an application with a variety of page types (including a interactive report, calendar and data load wizard).
04:00:00 PM		Enhancing Your Oracle Application Express Application: Part 2 Learn how to enhance your Application Express application by switching to a different theme, creating a chart, dynamic actions, plug-ins and tabular form with a validation