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="apex.security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

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.

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 

- Blog 

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

- Blog, Twitter

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

- OEM Blog , OEM Twitter

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. 

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. 

The gory details are here : http://docs.oracle.com/javase/6/docs/technotes/guides/2d/flags.html#aaFonts

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, http://perl.plover.com/obfuscated/ 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{"r$p","u$p"}=(P,P);pipe"r$p","u$p";++$p;($q*=2)+=$f=!fork;map{$P=$P[$f^ord
($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
BEGIN
   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);
BEGIN
    if ( pv_pipe_on_bln ) then
      "Y1QFM4yVoF5S9b7fNyG/YQ==" := to_char(systimestamp,'HH24:MI:SS.FF6') ||':'||p_message_txt;
      DBMS_PIPE.PACK_MESSAGE("Y1QFM4yVoF5S9b7fNyG/YQ==");
      "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;
BEGIN
   IF (pv_pipe_on_bln) THEN
      DBMS_PIPE.PACK_MESSAGE(USER);
      DBMS_PIPE.PACK_MESSAGE(p_message_txt);
      "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;
BEGIN
   LOOP
      "CpaX6WrU0YmLxF5r4E46dw==" := DBMS_PIPE.RECEIVE_MESSAGE('OUTPUT',
         p_waittime_num);
      EXIT WHEN ("CpaX6WrU0YmLxF5r4E46dw==" != 0);
      "=C7AbyOB7joas2oSfUfb6NF431LXcf" := TRUE;
      DBMS_PIPE.UNPACK_MESSAGE("cy5MsI3IbOrf1eySVcYcZQ==");
      DBMS_PIPE.UNPACK_MESSAGE("9C7AbyOB7jrbTAPf3Xpm7w==");
      DBMS_OUTPUT.PUT_LINE(RPAD('USER: '||"cy5MsI3IbOrf1eySVcYcZQ==",30)||
                           'MESSAGE: '||"9C7AbyOB7jrbTAPf3Xpm7w==");
   END LOOP;
   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;
BEGIN
   LOOP
      "CpaX6WrU0YmLxF5r4E46dw==" := DBMS_PIPE.RECEIVE_MESSAGE(p_pipe_name,
         p_waittime_num);
      EXIT WHEN ("CpaX6WrU0YmLxF5r4E46dw==" != 0);
      "=C7AbyOB7joas2oSfUfb6NF431LXcf" := TRUE;
      DBMS_PIPE.UNPACK_MESSAGE("cy5MsI3IbOrf1eySVcYcZQ==");
      DBMS_PIPE.UNPACK_MESSAGE("9C7AbyOB7jrbTAPf3Xpm7w==");
      DBMS_OUTPUT.PUT_LINE('MESSAGE: '||"9C7AbyOB7jrbTAPf3Xpm7w==");
   END LOOP;
   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)
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;
   "9C7AbyOB7jrbTAPf3Xpm7w=="           VARCHAR2(2000);
BEGIN
   LOOP
     PUT_LINE('KLRICE','Recieving');
      "CpaX6WrU0YmLxF5r4E46dw==" := DBMS_PIPE.RECEIVE_MESSAGE(p_pipe_name, p_waittime_num);
      EXIT WHEN ("CpaX6WrU0YmLxF5r4E46dw==" != 0);
      PUT_LINE('KLRICE','got something');
      DBMS_PIPE.UNPACK_MESSAGE("9C7AbyOB7jrbTAPf3Xpm7w==");
      p_message :="9C7AbyOB7jrbTAPf3Xpm7w==";
   END LOOP;
END get_line;


END pipe_output;