Like many other Oracle peeps, I got an email telling me that the current Flash and HTML interfaces for My Oracle Support were to be replaced by a new snazzy ADF based UI.

What has irked me is the way that some people have implied that the new interface won't be HTML.One example is the Oracle Infogram ("There's a new version of MOS in the works, not Flash and not HTML"), though I've seen similar (especially on Twitter which is admittedly constrained by the 140-character limit).

Let's clarify this. Browsers mostly render HTML (though they deal with straight text, images, maybe video and some other stuff). Through plugins, they can do Flash or Java.

But the new Oracle UI will be HTML. HTML spewed by a bunch of App Servers running Java and ADF, true, but still HTML. Going further, if you actually do a 'View Source' in the current HTML support application, right at the top it has meta name="generator" content="Oracle ADF Faces"

I don't know how much, if any, of the ADF layer of the current HTML application is being re-used for the new UI. They may well have done a complete throw-away and rewrite. But it isn't a novel technology stack being used here.

Maybe this is nit-picky. I just had a horrible feeling that there might be some people out there in Oracle land (and, if I'm being blunt, I don't mean developers) who don't realise that Oracle Forms stuff is an exception and that the main web development paradigm consists of mid-tier code generating HTML (probably with lots of javascript too, but that's a separate issue).

Did you know that the SELECT privilege actually allows you to do a SELECT ... FOR UPDATE ? Yes, that means a user with just that privilege could lock an entire table.

I want a SELECT_NOLOCK privilege. The user can still do a SELECT, but they cannot have a lock on the table. Okay, while an SQL is executing, I guess that get the shared table lock. But that's it.

This is another of those 'chores' for which every DBA and his dog has a script.

I'm continuing on my theme of a wish list for 12c (or beyond), and this time I'm taking aim at destroying stuff.

Juggling development and test areas, every so often it is tempting to wipe the slate clean and start with a fresh schema. The simple way to do that is with a DROP USER blah CASCADE

It would be nice to have something one step below the 'nuclear' option. I'm thinking

DROP ALL TABLES UNDER SCHEMA blah

plus similar options for dropping sequences, views, procedures, triggers and so on. The user/schema continues to exist, with all their privileges and defaults. But you get to clear out the objects the schema owns.

Yes, you can script this. The trick is to disable all the referential integrity constraints first, so that you can drop the tables without worrying about the dependencies.

Cross-schema constraints could still be a problem, as could firing DDL triggers. I'm open to alternative mechanisms. Perhaps DBMS_METADATA could generate a script for dropping objects.

Last week Nuno posted his 'wish list' for 12c. Here's the "Number 1" on my list:

Oracle Personal Edition for Linux.

A long, long time ago...I can still remember
when Windows was the OS that ran on the PC on your desk. It made sense that someone running their 'personal' database would just install it natively on that desktop machine, and that meant a Windows install.

Too often that would just be an install on top of a Standard Operating Environment pre-configured with Office, a virus checker and the other bloat that hangs around. Yuck.

What I'd like to recommend is that you start with an OS that is stable and decidedly unglamorous, such as RedHat or Oracle Linux. That could run on a commodity x86 under your desk. It could run in a VirtualBox virtual machine on your SOE machine, or in a VMWare or Oracle VM environment managed by real server admins. It could migrate happily between any of them.

But when I go to the documentation I see "Personal Edition is available on Windows platforms only"
Oracle are saying go and buy software off Microsoft before you talk to us about running our database ?

I want Oracle Personal Edition on Linux. Why not a bundle price for the database and Oracle Linux ? You could even sell it as a pre-configured VirtualBox appliance - just add disk. You could 'give away' a one-year Personal Edition licence (cost around $100) with every OCP certification. But push Linux as the preferred platform.

I'm not sure of the production use cases for Personal Edition. I heard, many years ago, that it had market share in Life Sciences, but it may have since been eclipsed by more specialised solutions, open source RDBMS or NoSQL data stores. Maybe it won't even be offered in 12c, squashed out between Express Edition and MySQL.

-----------------------------------------------------------------------------------

I've got a few more items on my wish list which I'll post over the coming days. I figured I'd do them as individual posts. In theory that means Plus +1 and retweets might give a guage as to the support for each idea.

I enjoy working with Apex. However one challenge is that it doesn't sit too well with conventional source control systems. Plus the fact that not all organisations use source control systems, especially for the small departmental applications which are a particular sweet spot for Apex.

One item in my little library of scripts is based on the ApexExport utility. This is part of the main Apex install which you'll probably find on the server. If you don't have access to the server (and some DBAs are understandably cautious about that) ApexExport is also available as part of the Apex Listener. That's a much more compact download, and I've based my script on that.

The Apex Listener download is a zip file. When you unzip it you get a WAR file. If you unzip the WAR file, you find the "apex.jar" file, which is what you need. If you like playing Russian dolls, you can even unzip the JAR file into the class files, but I don't see the point.

My Apex diff script simply extracts the same application (based on the id) from two Apex workspaces and does a compare of the results. This is handy to see if the version in a development workspace is the same as that in Testing/Production (eg has another developer done some work on it). An alternative use case is to ensure that no-one has been sneaking unapproved amendments into Prod.

My script is a zip because downloading BAT files is often blocked. It is trivially small, so the code is shown below too.

I've been lazy and hard-coded the username password here. Feel free to replace them with more parameters. Similarly with directory names, and you can replace the MS-DOS "fc" call with one to your favorite diff utility. I've used ExamDiff for a long time and my preferred editor, PsPad has one built in as well.


REM
REM Usage "diff JDBC_conn1 JDBC_conn2 nnnn"
REM
REM Note: Use JDBC connection format 127.0.0.1:1521:xe
REM


del f%3_1.sql
del f%3_2.sql

cd G:\oracleXE_11\apex_listener\WEB-INF\lib
set CLASSPATH=%CLASSPATH%;.\;.\ojdbc6.jar;.\apex.jar


java oracle.dbtools.apex.utilities.APEXExport -db %1 -user gary -password gary -application %3
rename f%3.sql f%3_1.sql


java oracle.dbtools.apex.utilities.APEXExport -db %2 -user gary -password gary -application %3
rename f%3.sql f%3_2.sql


fc /c /l /n /w f%3_1.sql f%3_2.sql

Let me tell you about an application I worked with some time ago.

Oracle cheats. Or, more generously, it sometimes plays by different rules. Normally a DDL will do an implicit commit of any outstanding transaction. Actually it does a couple of implicit commits, one before it tries the DDL and a second if it succeeds.


Furthermore, an 'ALTER PACKAGE .. COMPILE' is a DDL statement. And it does an implicit commit.


However, if the compilation occurs implicitly, because you are trying to execute a package that it is invalid, then your outstanding transaction ISN'T committed. That is what you want as your application can't cope with having partial transactions committed just because it needs to compile a package.


The question really is, if the commit isn't actually necessary, is it being performed by the ALTER PACKAGE simply to be consistent ?


Demo script follows:





XE> DROP TABLE plch_data PURGE;


XE> CREATE TABLE plch_data
  2    (id NUMBER, col_a NUMBER, col_b NUMBER);


XE> CREATE OR REPLACE PACKAGE plch_pkg
  2  IS
  3     FUNCTION ret_count RETURN NUMBER;
  4  END plch_pkg;
  5  /


XE> CREATE OR REPLACE PACKAGE BODY plch_pkg
  2  IS
  3   FUNCTION ret_count RETURN NUMBER IS
  4     v_num NUMBER;
  5   BEGIN
  6     SELECT COUNT(id) INTO v_num
  7     FROM plch_data;
  8     --
  9     RETURN v_num;
 10   END ret_count;
 11  END plch_pkg;
 12  /


XE> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'PLCH_PKG';


OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
PLCH_PKG   PACKAGE             VALID
PLCH_PKG   PACKAGE BODY        VALID


XE> ALTER TABLE plch_data DROP COLUMN id;
XE> ALTER TABLE plch_data RENAME COLUMN col_a TO id;


XE> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'PLCH_PKG';


OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
PLCH_PKG   PACKAGE             VALID
PLCH_PKG   PACKAGE BODY        INVALID


XE> INSERT INTO plch_data (id) VALUES (10);


XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1


XE> ROLLBACK;


XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
0




XE> REM =================================================================
XE> ALTER TABLE plch_data DROP COLUMN id;
XE> ALTER TABLE plch_data RENAME COLUMN col_b TO id;


XE> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'PLCH_PKG';


OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
PLCH_PKG   PACKAGE             VALID
PLCH_PKG   PACKAGE BODY        INVALID


XE> INSERT INTO plch_data (id) VALUES (10);


XE> ALTER PACKAGE PLCH_PKG COMPILE BODY;
XE> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'PLCH_PKG';


OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
PLCH_PKG   PACKAGE             VALID
PLCH_PKG   PACKAGE BODY        VALID


XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1


XE> ROLLBACK;


XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1

Most of OOW seemed to be about Boxes, the bigger the better. 


I'm not a DBA. The people at Pythian are, and they've said nice things about the Appliance. I think it suits their business model, which is external DBA support. If a company already has good DBAs and listens to them, it shouldn't fare too badly on getting the correct hardware setup. If it doesn't have good DBAs (or it doesn't listen to them), then it probably won't hear about the appliance, and they'll end up with some commodity kit (maybe running MySQL or even SQL Server).


The Exalytics machines is way out of my field. It is probably good. It is probably expensive. It is probably something I won't get my hands on. The same goes for the rest of this Big Data stuff. Clusters of scores of machines are expensive. 


We do have a nice NoSQL solution. I'll probably download that to play with, but it is something that is really for Java developers (or some of these newer languages that get fashionable every couple of years). I think I'll have to re-title myself as a 'Relational Database Developer'. 


So the 'cloud' bit was the part that appealed most. Not the Fusion Apps stuff. Its nice that it is finally out there, but that isn't my field either. 


Guy Harrison's tweets have included some nuggets "DB cloud service will not support direct SQL from outside oracle public cloud - stateless REST calls only." and "DB cloud service gives you a schema in a shared DB, not a whole instance like Amazon RDS. More like SQL azure than RDS"


Pricing won't be cheap. This is Enterprise Edition folks. It's not for startups trying to run a business on pizza, coke and little white pills. It is for people with big pockets. 


Roel Hartman indicates that SQL Developer 3.1 will have some special sauce that will allow you to connect to these cloudy databases. I think the twitter-stream has a screen shot, but I've lost it. [Twitter was in turbo mode the past few days - unfortunate for us down under where a lot of the action happens while we are in bed.]


Other IDEs may need to do some catching up to work with those databases. I'd also suspect that it might prove a barrier to any applications not running on Oracle's cloud too. While Java (and other JVM languages ?) might be doable, I wonder whether Forms will make the cut.


Application Express seems to be making its presence felt. Though after Larry aired his views on Salesforce, I wonder whether the "apex" name will become a battleground as a "cloud language". Hey folks, the appex.com domain name appears vacant at the moment.


Oh yes, the Social Network thing. My last company used Yammer, which was pretty similar to what Salesforce offers with Chatter, and the company I'm working at now has something similar. Yes they have a Facebook 'feel', but the focus is 'employer' related. People will have both and won't have a problem keeping them separate, mostly because your employer 'owns' one and all your friends who work elsewhere won't be there.