Wednesday, December 19, 2012

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

               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.

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";

Friday, October 12, 2012

SQL Developer and

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 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.

Friday, August 03, 2012

APEX Listener 2.0 EA1 is out !

The new APEX Listener that will be powering the Oracle Cloud is out for an Early Access.  There have been huge changes so be sure to check the documentation.  The first change is that there's a more robust command line now.

Give it a try with this:
java –jar apex.war help

This new version supports multiple databases.  Be sure to check out "Configuring Multiple Databases" there's lots of flexibility on how to route which requests to which database.  Here's a jump start to getting it up and going.

To setup the database configuration information:
 java -jar apex.war setup --database sales_db

Then the how to route what to where has to be done.  There's 2 choices for this entire base URL or just the base path.

1) Base Path :  This will route anything /apex/sales/* to the sales_db

  java -jar apex.war map-url --type base-path --workspace-id sales_rest /sales sales_db

2) Base URL :  This will ONLY route exact matches of the enture url to the sales_db.  This include http vs. https.  This example would give a 404 for any https request.

java -jar apex.war map-url --type base-url --workspace-id sales_rest sales_db

Coming soon....

Here's a screenshot of the upcoming configuration screens in sqldev to give an idea of some of the changes in the listener.  Everything is still driven off xml config files so everything in this screenshot is there today to try out.

Friday, July 06, 2012

KScope DB Symposium Slides / Thirsty Games

Here's all the slides from ODTUG's Kscope12 DB Symposium which took place on Sunday.  The flow of the day was meant to be from a blank or reversed diagram in the modeler and the through deploying and tuning.

If you missed it, be sure to check out the slides it was a great set of speakers.  Be sure to check out the video referenced in Tom Kyte's talk.  Anyone that is using a connection pool where the knee jerk reaction to performance is to increase the size of the pool will find it useful.

SQL Developer Data Modeler  
    - Kris Rice, Oracle Corporation
SQL Developer  
    - Jeff Smith, Oracle Corporation
Continuous Integration with Database Projects 
    - Kris Rice, Oracle Corporation
How to "Tune" a Query 
    - Tom Kyte, Oracle Corporation
    - Referenced YouTube video from the talk
SQLDeveloper with Enterprise Manager: Integrating Changes from Developer to DBA 
    - Jagan Athreya, Oracle Corporation
Oracle Optimizer - Top Tips to get Optimal SQL Execution All the Time 
    - Maria Colgan, Oracle Corporation

Can't talk about how great kscope was without mentioning the Thirsty Games.  Team Dev Tools represented us very well.

Here's @thatjeffsmith v.  @martindsouza 

And here's Ashley Chen v. Barbara Morris

Wednesday, May 16, 2012

New OBE series

I'm sure most people know by now that we have a VM we build and run the OTN Developer Day events based on those VMs.  The key to running the events is the Oracle By Example labs.  They are a great way to learn the tools and software in the VM.

What everyone may not know is all the OBEs are online as well.  The are runnable anywhere not just in the VM we build.  The Oracle Learning Library has TONS of great labs to learn.  The labs are tagged to make it very easy to find what you are looking for.

The latest addition is something we kicked around and everyone liked.  That's a series where the labs build upon the previous lab to learn more than 1 topic for 1 hour then switch to another topic.

What we came up with is the Application Developer Days Workshop Series .  This series starts in the SQL Developer Data modeler, then writing plsql in SQL Developer, then creating an app in APEX.  So a soup-to-nuts idea.  Starting from a clean slate and progressing into a functioning application.

Give it a try!

Monday, May 14, 2012

Apex Listener jdbc/security setup

In talking to a few customers, I realized lots of people run with the default settings. So, I thought I should point out a few things that should be conscious decisions vs. accept the defaults.  The defaults are some limits that are safe and will run a modest size app just fine.  When the app gets a few long running spots or  high concurrency it's time to look at changing those defaults.

1.  There are fewer and fewer reasons to use the OCI jdbc driver over thin but it is something that if there's a reason you can swap out.

<entry key="apex.jdbc.DriverType">thin</entry>

2. The pool of connections has a few things that can be adjusted.
a)  The initial size of the pool.  This is when you first start up the listener how many connections to establish so that user #1,2,3 will not be waiting on a new connection to be established.
<entry key="apex.jdbc.InitialLimit">3</entry>

b) The min is how low the pool is allowed.  If things like inactivity is set to idle down the connection this many will stay available.
<entry key="apex.jdbc.MinLimit">1</entry>

c) The max is what it sounds like the most connections that will be allowed.  For a system with high concurrent or some long running pages this will need to be increased.  The effect if not is that user #11 ( when set to 10 ) will be watching a browser spin.  Then the training we all have that the web is fast kicks in, the user cancels the page, and requests again thinking something was wrong and surly it'll be faster the next time.  That causes the next request.  Pretty soon there's a lot of queue requests waiting.  That all turns into phone calls/ emails to the sys admin , dba , and developers that the app is slow or broken.
    Talking to one user, he upped this to 100 and it made a huge difference no more queuing.  Another had to go well over 100 due to a combination of slow queries and high concurrency.  There's not 1 setting that covers everything.

<entry key="apex.jdbc.MaxLimit">10</entry>

d) InactivityTimeout will idle the connection pool back down to the minLimit over time as the connections are idle for the value provided.
<entry key="apex.jdbc.InactivityTimeout">1800</entry>

e) This one covers in case the connection gets lost due to anything at all.  If the connection pool doesn't get the connection back for some reason for this amount of time and is idle for this time, it will be reclaimed automatically
<entry key="apex.jdbc.AbandonedConnectionTimeout">900</entry>

f) Lastly for jdbc, this is how many request a connection will service before it is closed and new one opened.
<entry key="apex.jdbc.MaxConnectionReuseCount">50000</entry>

3) For an APEX install, the security function should always be set. This blocks non-apex procedures.  These could be procedures that happen to be granted to public by accident or intentionally.

<entry key="">wwv_flow_epg_include_modules.authorize</entry>

Saturday, May 12, 2012

SQL Developer shared setup from any machine

It's been on the forums a few times how to get SQL Developer to put it's config setup on to a flash drive.  The same thing works with Dropbox/Google Drive/...

Here's all it takes.
1) edit sqldeveloper/bin/sqldeveloper.con
   - add the path to the Dropbox or where ever location
    AddVMOption -Duser.home=/Users/klrice/Dropbox/sqldev

2) Start it up.

The to check just look in that location and you'll find a .sqldeveloper directory where everything will be stored.

You'll notice bunches of directories where all the settings are being stored.

Then just go to any machine with your drop box installed and you'll have the exact same setup everywhere.

Thursday, May 03, 2012

ODTUG Kscope12 Sunday DB Symposium Agenda

I always enjoy attending and presenting at ODTUG.  So much that last year,  I volunteered to help the track leaders, David Schleis , Eddie Awad , and Chet Justice organize the DB Symposium on Sunday.   It will be a great day that is starting with a joint keynote with Mike Hichwa on tools generally.  Then we split into the DB track where it starts the progression of  blank slate , developing, testing , building , deploying and wrapping up with tuning the deployed code. 

8:45 - 9:45 Keynote  - Oracle Database Tools  Mike Hichwa 

Mike Hichwa, vice president of database tools, will provide an insight into the latest development of database tools and the Oracle Database Cloud Service. Everybody who is currently utilizing Oracle database tools should attend this session.

9:45-10:30 -SQL Developer Data Modeler Kris Rice
Blog ( you are reading it! )  , Twitter 
SQL Developer Data Modeler allows for easy modification of existing database objects. This session will cover how to reverse engineer, modify, and create a script of the changes. Collaboration is an important part of any development team, with built in subversion support the modeler makes collaboration possible.

10:30 - 11:00 BREAK

11:00 - 12:00 - SQL Developer  -Jeff Smith
SQL Developer provides the framework for your complete PL/SQL development process. It begins with developers working together to quickly and efficiently build new applications. Versioning your code, merging in changes from other developers, pushing out the latest and greatest to your QA teams or the Cloud is all a natural part of the tool.
Of course before you publish anything to anyone, you're testing your code, right? For the folks out there that feel guilty about having their users testing their code for them, you can actually build your own test suites and identify regressions.

12:00 - 1:00 LUNCH

1:00 - 1:45 - Continuous Integration with Database projects - Kris Rice
Blog ( you are reading it! )  , Twitter 
Continuous Integration facilitates frequent integrations of a project. This can help reduce turn around times for broken builds by alerting the development team sooner. Hudson along with SQL Developer's built in source code versioning integration can make a highly coupled solution for any database project.

1:45-2:45 - How to "tune" a query  - Tom Kyte
Blog , Ask Tom 
I am often asked how I tune a query, or more generally - how does one "tune" a query. In this session - we'll look at how query tuning is performed. You might be surprised at the answer as it often does not involve rewriting a single query.

2:45 - 3:15 BREAK 

3:15-4:00 - SQLDeveloper with Enterprise Manager: integrating changes from developer to DBA  - Jagan Athreya
Developers and DBAs have usually faced conflicting changes, delayed application upgrades and production downtime due to lack of integrated tools to manage the database change lifecycle. Come to this session to see and learn about the integration between SQL*Developer and Enterprise Manager Cloud Control 12c that enables developers to define changes in SQLDeveloper and allows DBAs to promote these changes to Test and Production seamlessly via Enterprise Manager thereby reducing errors, accelerating productivity and eliminating unplanned downtime.

4:00-4:45 Oracle Optimizer - Top Tips to get Optimal SQL Execution all the Time  - Maria Colgan
Blog,  Twitter
Simple system changes such as gathering optimizer statistics or changing a parameter value can affect SQL execution plans and therefore the performance and stability of a system. Managing such changes is an ongoing challenge for many customers. 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. With clear how-to examples you will learn how to identifying and quickly resolving these issues. 

Saturday, April 28, 2012

Better Fonts in SQL Developer

@narciblog asked about better font support in SQL Developer.  It appears I never noticed that Anti-Aliased is not the default.  Here's a screen shot of the default that installs today.

Now, add 2 lines to the sqldeveloper.conf file

AddVMOption -Dawt.useSystemAAFontSettings=on
AddVMOption -Dswing.aatext=true

Then you get a much nicer looking font.

This is probably the quickest and easiest tip I've given so far. 

Wednesday, February 08, 2012

SQL Developer 3.1 and Obfuscation

SQL Developer 3.1 , SQL Developer Datamodeler 3.1, and the OTN DB VM were all released yesterday.  Now it's time to blog about some of the new features included in these releases.  Be sure to check out the various blogs the team has.  There's a listing on OTN here.

First up from me is PL/SQL Obfuscation.  The idea is to simply make the code unreadable.  This is done in various language mostly for protection of the intelectual property.  The website, shows this examplefrom a contest that used to run for Perl.

@P=split//,".URRUU\c8R";@d=split//,"\nrekcah xinU / lreP rehtona tsuJ";sub p{
($p{$_})&6];$p{$_}=/ ^$P/ix?$P:close$_}keys%p}p;p;p;p;p;map{$p{$_}=~/^[P.]/&&
close$_}%p;wait until$?;map{/^r/&&<$_>}%p;$_=$d[$q];sleep rand(2)if/\S/;print

The PL/SQL one SQL Developer now has isn't to this extreme but does make the code quite difficult to read.  If someone obfuscated and then wrapped the code, that would be 2 hurdles in the way to assist licensing and other agreements to protect the IP invested.

Here's the steps to try it out.

1) Open a PL/SQL Object.  This is a package I have that mirror DBMS_OUTPUT but send to a pipe instead.  It allows me to basically do a tail -f on my code's log messages and watch it as it processes.  If anyone is interested in that I can write it up another time.

2) Right click and choose Obfuscate...

3) SQL Developer will prompt to save the non-obfuscated code so there is a readable copy.

4) Check out the sources.  It can be undone by a determined person quite easy with a lot of search and replace but at least it puts a hurdle in the way.

create or replace
PACKAGE BODY pipe_output IS
PROCEDURE set_pipeoutput_on IS
-- Opens the communication pipe
   pv_pipe_on_bln := TRUE;
END set_pipeoutput_on;

PROCEDURE put_line (p_pipe_name VARCHAR2,p_message_txt VARCHAR2) IS
-- Sends the username of the executing user and a message to the pipe
  "CpaX6WrU0YmLxF5r4E46dw==" PLS_INTEGER;
 "Y1QFM4yVoF5S9b7fNyG/YQ==" varchar2(4000);
    if ( pv_pipe_on_bln ) then
      "Y1QFM4yVoF5S9b7fNyG/YQ==" := to_char(systimestamp,'HH24:MI:SS.FF6') ||':'||p_message_txt;
      "CpaX6WrU0YmLxF5r4E46dw==" := DBMS_PIPE.SEND_MESSAGE(p_pipe_name,0.0001);
    end if;
END put_line;

PROCEDURE put_line (p_message_txt VARCHAR2) IS
-- Sends the username of the executing user and a message to the pipe
  "CpaX6WrU0YmLxF5r4E46dw==" PLS_INTEGER;
   IF (pv_pipe_on_bln) THEN
      "CpaX6WrU0YmLxF5r4E46dw==" := DBMS_PIPE.SEND_MESSAGE('OUTPUT');
   END IF;
END put_line;

PROCEDURE get_line (p_waittime_num NUMBER := 1) IS
-- Monitors the pipe based on a specified wait time, reading and
-- displaying the username and messages as they are sent from the
-- executing process.
   "CpaX6WrU0YmLxF5r4E46dw=="            PLS_INTEGER;
   "cy5MsI3IbOrf1eySVcYcZQ=="          VARCHAR2(30);
   "9C7AbyOB7jrbTAPf3Xpm7w=="           VARCHAR2(2000);
   "=C7AbyOB7joas2oSfUfb6NF431LXcf" BOOLEAN := FALSE;
      EXIT WHEN ("CpaX6WrU0YmLxF5r4E46dw==" != 0);
      "=C7AbyOB7joas2oSfUfb6NF431LXcf" := TRUE;
      DBMS_OUTPUT.PUT_LINE(RPAD('USER: '||"cy5MsI3IbOrf1eySVcYcZQ==",30)||
                           'MESSAGE: '||"9C7AbyOB7jrbTAPf3Xpm7w==");
   IF NOT "=C7AbyOB7joas2oSfUfb6NF431LXcf" THEN
      DBMS_OUTPUT.PUT_LINE('No output in pipe.');
   END IF;
END get_line;

   PROCEDURE get_line (p_pipe_name VARCHAR2,p_waittime_num NUMBER := 1) IS
-- Monitors the pipe based on a specified wait time, reading and
-- displaying the username and messages as they are sent from the
-- executing process.
   "CpaX6WrU0YmLxF5r4E46dw=="            PLS_INTEGER;
   "cy5MsI3IbOrf1eySVcYcZQ=="          VARCHAR2(30);
   "9C7AbyOB7jrbTAPf3Xpm7w=="           VARCHAR2(2000);
   "=C7AbyOB7joas2oSfUfb6NF431LXcf" BOOLEAN := FALSE;
      "CpaX6WrU0YmLxF5r4E46dw==" := DBMS_PIPE.RECEIVE_MESSAGE(p_pipe_name,
      EXIT WHEN ("CpaX6WrU0YmLxF5r4E46dw==" != 0);
      "=C7AbyOB7joas2oSfUfb6NF431LXcf" := TRUE;
      DBMS_OUTPUT.PUT_LINE('MESSAGE: '||"9C7AbyOB7jrbTAPf3Xpm7w==");
   IF NOT "=C7AbyOB7joas2oSfUfb6NF431LXcf" THEN
      DBMS_OUTPUT.PUT_LINE('No output in pipe.');
   END IF;
END get_line;
PROCEDURE get_line (p_pipe_name IN VARCHAR2,p_waittime_num IN NUMBER := 1,p_message OUT VARCHAR2)
-- Monitors the pipe based on a specified wait time, reading and
-- displaying the username and messages as they are sent from the
-- executing process.
   "CpaX6WrU0YmLxF5r4E46dw=="            PLS_INTEGER;
   "9C7AbyOB7jrbTAPf3Xpm7w=="           VARCHAR2(2000);
      "CpaX6WrU0YmLxF5r4E46dw==" := DBMS_PIPE.RECEIVE_MESSAGE(p_pipe_name, p_waittime_num);
      EXIT WHEN ("CpaX6WrU0YmLxF5r4E46dw==" != 0);
      PUT_LINE('KLRICE','got something');
      p_message :="9C7AbyOB7jrbTAPf3Xpm7w==";
END get_line;

END pipe_output;