UKOUG DE SIG 13-MAR-2007 Announcement

Well it's four weeks until the first Development Engineering SIG of 2007. For the third year running we're doing a mini-conference with the App Server and Modelling & Design SIGs. There are three streams, one for each SIG and delegates can mix'n'match from any of the three streams.

The agenda for the DE SIG has been published for a while. Once again I have striven to cover new technologies and old technologies whilst still coming up with an offering which will justify a whole day out of the office. I hope I have succeeded.

First out of the stalls is Oracle's Grant Ronald talking about "Building Client/Server applications with Java Swing and ADF components". Everybody tends to associate Java with the web so this will be a useful reminder that it is possible to built client/server systems in Java. After Grant we have another Oracle employee, Danny Roach, who will be presenting a case study on
Internet banking. Danny will be talking about a system built using .Net, which is the first talk we have had on that product.

In the much coveted "between you and your lunch" slot is Tim Hall of UPS (although I suspect more people will know him from his Oracle-Base web site). Tim is supposed to be talking about the tools and processes available for tuning PL/SQL programs. Although there's always the chance he will spend the forty-five minutes on kung fu movies and the evils of pipe smoking. After lunch is Grant Ronald again, possibly sporting a fake moustache and a funny accent (make your own joke). Grant will be co-presenting with Peter Sechser from PITSS and they'll be talking about migrating Oracle Forms from client/server to the web.

The final session will be a joint session across all three streams. We SIG chairs are keeping the precise details of this under wraps. Does this mean we don't yet know quite what's going to happen here? You might think that, I couldn't possibly comment. After that there will be drinks in the bar.

The venue is Baylis House in Slough, which is a nice venue and isn't too bad for transport links. The food generally gets good marks in the feedback. Because it's a joint SIG each member organisation can send three delegates for free. Many people think of the UKOUG as primarily about the annual conference, which is the big event but the SIGs are a valuable learning resource too. My UKOUG colleague Neil Jarvis recently wrote about their benefits and his remarks are just as true for developers, designers and PMs as for DBAs. So please come along in large numbers. If your organisation is based in the UK and uses Oracle but isn't a member of the UKOUG, then why the heck isn't it? The membership rates represent very good value.

CREATE SCHEMA: a SQL curiosity

Pete Finnegan picked up on my recent piece USER != SCHEMA and linked to his article on the CREATE SCHEMA statement. This was prescient on his part, as I had decided against discussing it statement in that article (for reasons of length). There is nothing wrong with Pete's piece but I thought expanding on CREATE SCHEMA might be helpful, as one of the other people who commented seemed confused about it.

And who wouldn't be? For start, it's not really CREATE SCHEMA, it's CREATE SCHEMA AUTHORIZATION. ....

SQL> create schema c
  2  create table t3 (col1 number, col2 number)
  3  /
create schema c
              *
ERROR at line 1:
ORA-02420: missing schema authorization clause

SQL>

Furthermore, we are not creating a schema, we are adding new objects to a pre-existing schema.

SQL> create schema authorization c
  2  create table t3 (col1 number, col2 number)
  3  /
create schema authorization c
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier


SQL> 

The schema authorization identifier is invalid because my database does not have a user C. So let's try again with good ol' user A, who already has some objects in his schema.

SQL> select object_type, object_name from user_objects
  2  where object_type in ('TABLE', 'VIEW')
  3  /
OBJECT_TYPE        OBJECT_NAME
------------------ ----------------
TABLE              T1
TABLE              T2
VIEW               V1

3 rows selected.

SQL> create schema authorization a
  2  create view v2 as select * from t2
  3  /

Schema created.

SQL> 

A misleading response there: the schema already existed. But I suppose "Schema authorization applied" is a bit of a mouthful.

The cool thing about CREATE SCHEMA is that we can put together several CREATE statements and run them as a single transaction. So if one of the CREATE statements fails they all fail. It's the closest Oracle gets to being able to rollback DDL statements.

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create table t1  (col1 number, col2 number)
  4  /
create table t1  (col1 number, col2 number)
*
ERROR at line 3:
ORA-02425: create table failed
ORA-00955: name is already used by an existing object

SQL> desc t3
ERROR:
ORA-04043: object t3 does not exist

SQL> 

Unlike with the table statement a CREATE VIEW exception doesn't give out the underlying error when it fails (at least in 9.2.0.6)....

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create view v1 as select * from b.t1
  4  /
create schema authorization a
*
ERROR at line 1:
ORA-02427: create view failed
SQL> 

As it happens we know the view already exists, so let's presume the error is ORA-955. Normally we could work around that with CREATE OR REPLACE VIEW but ...

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create or replace  view v1 as select * from b.t1
  4  /
create or replace  view v1 as select * from b.t1
       *
ERROR at line 3:
ORA-02422: missing or invalid schema element

SQL> 

CREATE SCHEMA is a "contractual obligation" command: Oracle has it because the ANSI standard says it has to be there. In Oracle SQL only three commands are supported: CREATE TABLE, CREATE VIEW and GRANT. (In DB2 the statement also supports creating indexes). It also only supports standard SQL, so there are some proprietary Oracle SQL which will cause the statement to hurl. CREATE SCHEMA is actually very restricted in its scope and consequently is of limited usefulness. I don't think it can replace a proper regression script for doing database deployments.

There is one last gotcha. Although the CREATE statements bundled with the CREATE SCHEMA statement are transactional and appear to be rollback-able, the statement itself is still plain DDL and issues the implicit commit before it executes.

SQL> insert into t1 values (566, 888)
  2  /

1 row created.

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create   view v1 as select * from b.t1
  4  /
create schema authorization a
*
ERROR at line 1:
ORA-02427: create view failed

SQL> rollback
  2  /

Rollback complete.

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
       566 888
         1 AAAAAAAAAA

SQL> 

Now, the ability to suspend the transactionality of individual DDLs would be quite helpful. My last installation (which was a change to an existing system) required the deployment of two new schemas with over a hundred tables each, plus indexes, several hundred types, procedures, packages and bodies, not to mention a number of changes to existing schemas. It would be nice to be able to rollback such gargantuan deployments if something goes wrong. But, even if the syntax allowed it, CREATE SCHEMA is not appropriate. Putting all that into a CREATE SCHEMA statement would have resulted in a command almost 2MB long. Debug that! The coming 11g Editioning feature (caveat: BETA!) strikes me as a more practical alternative.

So, CREATE SCHEMA looks like the SQL equivalent of the human appendix. It's there but it doesn't really do anything useful.

"Bootstrapping Web2.0": BCS SPA, 31-JAN-2007

According to presenter Adrian Van Emmenis this was "a brief, superficial and opinionated look at web technology past, present and future." In reality it was a splendid broadside against the current state of web development, with a quick scoot through AJAX, RIA, etc tacked on the end. Van did put up a big BETA! sign, presumably to cover any inaccuracies or problems with timing (very Web2.0).

Ninety minutes of splenetics runs the risk of boredom but Van did his best make his rant entertaining. His Powerpoint was lovely to look at (even though it broke all the rules - too many slides, too many words, too much animation). The SPA Players (Van with Immo Huneke) did a skit set in a greengrocers to illustrate the problems that the Back button poses for e-commerce. And he has a lovely turn of phrase, for instance, describing Java Server Pages as "a blizzard of punctuation".

So, according to Van, what is wrong with Web1.0?

• The REST model is suitable for static pages but inappropriate for dynamic or stateful transactions.
  
• Browsers are all different and broken.
  
• Developers are confused about when to use GET and POST, when to use buttons and links.
  
• CSS is a nightmare; furthermore it is used by non-programmers who teach themselves from books written by non-programmers.
  
• Page refresh/back button/browser caching cause problems within transactions, which often result in users being confronted with unhelpful error messages (POSTDATA)
  
• Browsers are all different and broken.
  

There are toolkits available to make lifer easier (PHP, JSP, ASP, Ruby On Rails, .Net, Django, etc, etc) but in Van's analysis they all suffer from one major problem: they try to work with HTML pages, but inserting their own markup. This is a mistake because HTML is not a good programming language.

At last, Web2.0

Of course, we all know that Web2.0 is made of badgers paws but the engine driving it is AJAX. This is just a catchy rebranding of some JavaScript routines and the use of XmlHttpRequest. I hadn't appreciated before, but it is the latter thing which is crucial to the improved user experience, because it allows post calls to communicate with the web server without causing a page refresh. Consequently the page can be altered dynamically without breaking the transaction.

AJAX on its own is (apparently) complicated. So there are lots of AJAX toolkits springing up. These all seem to have their own library and markup language, and some require plug-ins too. The key thing is, AJAX is still basically fiddling with markup. In Van's opinion a more sensible solution would be to have the pages abstracted into a stack of widgets stored on the server. Events should be communicated from the browser to the server, the changes calculated there and the resultant HTML returned to the browser as a delta. I had a spooky feeling as I listened to Van because this is the model for Oracle web-deployed Forms, which is being junked in favour of Java Server Faces with added AJAX sprinkles.

Oracle Forms uses an applet to achieve this marvel. Applets are very Web0.5 and never really took off. There are a number of reasons for this but the biggest hurdle remains the problem of installing a Java browser plug-in, which is harder than the equivalent task for Flash. Partly this was due to the wrangles between Microsoft and Sun, but Oracle didn't help their cause by insisting on the Jiniator plug-in. These proprietary issues show why Oracle, like so many others, are attracted by the promise of open standards.

No discussion of Web2.0 would be complete without a swipe at some of the more vacuous offerings. Van introduced us to Zebo, a site where you can list everything you own and then network with other people who own the same things. Brilliant! Although, for a demonstration of Web2.0's potential to generate revenue out of nothing in its purest form check out this Business2.0 interview with Richard Rosenblatt, the man who sold MySpace to Rupert Murdoch for $$$.

Update

There is a very cool albeit evangelical video about Web2.0 on (where else) YouTube. pdp has posted a perceptive retort to this video on his GNUCitizen blog, stressing the cross-scripting perils of Web2.0.

USER != SCHEMA

Most of us tend to bandy around the terms USER and SCHEMA is if they were synonyms, but in Oracle they are different objects. USER is the account name, SCHEMA is the set of objects owned by that user. True, Oracle creates the SCHEMA object as part of the CREATE USER statement and the SCHEMA has the same name as the USER but it is quite easy to demonstrate that they are different things.

SQL> conn b/b
Connected.
SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80116 BIG_PK
80114 BIG_TABLE
45709 BP
45710 BP_PK

SQL> grant select on my_tab to a
2 /

Grant succeeded.

SQL> conn a/a
Connected.
SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80404 ACTOR
80414 ACTOR_NT
45707 AP
45708 AP_PK
52765 ASSIGNMENT
49747 A_ID
52768 A_OBJTYP

7 rows selected.

SQL> alter session set current_schema=b
2 /

Session altered.

SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80116 BIG_PK
80114 BIG_TABLE
45709 BP
45710 BP_PK

SQL> select username, schemaname
2 from v$session
3 where sid in (select sid from v$mystat)
4 /
USERNAME SCHEMANAME
--------- -----------
A B

SQL>

The important thing to remember about alter session set current_schema is that it only changes the default schema. It does not change the privileges we have on that schema and it does not change the results when we issues queries that depend upon username, for instance against the USER_ views.

SQL> conn u2/u2
Connected.
SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2
T3

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 BBBBBBBBBB

SQL> grant select on t1 to u1
  2  /

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 AAAAAAAAAA

SQL> alter session set current_schema=U2
  2  /

Session altered.

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 BBBBBBBBBB

SQL> select * from t2
  2  /
select * from t2
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from u1.t2
  2  /

no rows selected

SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2

SQL> 

Of course, the confusion stems partly from the fact that there is a one-to-one correspondence between USER and SCHEMA, and a user's schema shares its name. But the fact that people who ought to know better use them interchangeably (me included) doesn't help matters.

Update

If you found this interesting you might also want to read a piece I have published on the CREATE SCHEMA statement.