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

APEX Listener 2.0 !

The Oracle APEX Listener 2.0 is officially out. There's a TON of changes in this release. The first how you get it up and running. For the standalone version, it's very simple to be up and running quickly. Simply type "java -jar apex.war" and follow the prompts. This will get you up and running.  The biggest changes are in the REST services and I'll blog more on that later.

The command line has a options now.

$ java -jar apex.war help
java -jar apex.war  [Options] [Arguments]

The following commands are available:

               configdir         Set the value of the web.xml
                                 config.dir property

               help              Describe the usage of this
                                 program or its commands

               map-url           Map a URL pattern to the
                                 named database connection

               migrate           Migrate a 1.x configuration
                                 to 2.x format

               setup             Configure database connection

               standalone        Launch Oracle Application
                                 Express Listener in
                                 standalone mode

               static            Generate a Web Application
                                 Archive (WAR) to serve Oracle
                                 Application Express static
                                 resources

               user              Create or update credentials
                                 for a user

To see instructions on how to use each of these commands, type help
followed by the command name, for example

 java -jar apex.war help configdir

If no command is provided the Listener is started in standalone mode

The important one to not using the command line is the "user" command. You can use sqldeveloper 3.2.2 to now administer the APEX Listener. The user command lets you setup the authentication that sqldev will use to connect and administer in standalone mode.

java -jar apex.war user klrice "Listener Administrator"

SQL Developer 3.2.2 is the current and required version to administer the listener.  Use the View -> APEX Listener menu and change anything. Once the settings are done, use the test button in the tool bar to validate setting and finalize with the Upload Setting button. Alternatively, you can save the setting to a zip file and manually put them in place.

Java Script Validations?

In the new listener there's an option for the normal PL/SQL Validations.  Nothing has changed there however if you change the select box to javascript, you can now validate the request before hitting the database at all.  This has the obvious advantage of no database hit, no connection borrowed from the pool, no extra load on the database to simply refuse some requests.  The following are all available in javascript as variables.

HOST
PORT
REFERER
USER_AGENT
URI
QUERY_STRING
REMOTE_ADDR
REQUEST_METHOD
REQUEST_PROTOCOL
REQUEST_SCHEME
SCRIPT_NAME
SERVER_NAME
SERVER_PORT
SERVER_PROTOCOL

Also all querystring params and headers are put in as variables with their names. 

The only requirement is that there be a function named "isValid" that returns the string "true" or "false".  

function isValid(){ 
 // no more ie6 
    if ( USER_AGENT.indexOf("MSIE 6") > 0 ) {
        return "false";
     }
 // a procedure in the klrice schema named HI has been performing poorly so shut it off
   if ( SCRIPT_NAME == 'klrice.hi' ) { 
       return "FALSE";
   }
  return "true";
}

SQL Developer and Cloud.oracle.com

Cloud accounts are trickling out so figured I'd do a quick post showing how to connect SQL Developer to the Database Service. Check out http://cloud.oracle.com for details on the service offerings and to sign up for a trial.

Once your service is activated, connecting SQL Developer is pretty easy. You have to go into the Application Express and create new user. To get to the screens, simply choose Create user off the Administration menu.

The key is that the User Groups listed at the bottom of the page have to all be granted. This is to allow SQL Developer an OAuth2 client to connect to the RESTful calls we have created. I'll get into that in another blog post.

Now in SQL Developer, enter the username just created and the URL of the apex service into the URL field. Also the SFTP credential can be entered for doing data deployment ( also in another blog post coming soon ).

Then you simply use the tree widget as normal. Expand the tree, expand the tables and see the ones we seeded in for initial tinkering.

These are the clouds you are looking for.