New name for HTMLDB - Poll

So they've renamed HTMLDB. To be honest, I never liked the HTMLDB name. It sounds too much like a mumble. Old-time readers of AskTom will recall him referring to it as Project Marvel, which sounds much better.
Application Express is also good, but takes too long to type, so I've put a poll together (thanks Eddie for the pointer to dpolls) on an abbreviation for it. Feel free to add any other suggestions.

Wiki as reference

As a sort of follow-up to by previous blog entry on the wikipedia, yesterday I saw my first newspaper report citing Wikipedia as a source. It was in Sydney's Sunday Telegraph, in a report about Spielberg's new film, Munich. It had a side-bar article about the Black September Organisation with Wikipedia declared as the source.

Okay, the Sunday Telegraph doesn't rank amongst the elite of the world's newspapers. It's what UK readers would call a tabloid. Personally, I prefer 'zodiac press'. That is, you have to have some scepticism towards any newspaper that provides a horoscope for the benefit of its readers.

While my previous blog entry said that generally, accuracy was of less importance than usefulness, when basing an article on that information, accuracy is of prime importance. Personally, I don't think the Wikipedia entries themselves should ever be used as a sole resource. At a minimum the history and discussion pages should also be referred to, as there's always the chance that the latest edit was 'corrupt'. Better still, refer to several sources. Remember, writing an article based on one source of information is plagarism, using many is reseach.

And on an Oracle note, thanks to Dizwell for the space for an Oracle wiki.

Google CDOS ?

Seeing "Google CDOS" referred to here makes me feel OLD.
I remember using usenet back from CompuServe in the 80's, before there was even the concept of an internet search engine.
I remember using DejaNews to search the old USENET archive (which wikipedia tells me would have been in the 90's before Google was round and when AltaVista was king of the search engines).
In wonder if, in another decade, we'll have given up referring to plain old email, and just be talking of gmail. Or even worse, we'll start calling it GoogleNet.

I would have posted this as a comment on the blog, but it was erroring out with
Invalid [] range "r-c" in regex; marked by <-- HERE in
.....lots of naughty words in a regex list....
at /var/www/www.orablogs.com/web/mt-bin/extlib/jayallen/Blacklist.pm line 3095.

Thoughts on Oracle forums

A new Oracle Question and Answer site has been created by Eddie Awad. Some commentary has been made about the value of this resource, and whether it will result in duplication or dilution.

There are a number of Oracle forums about, and most have their own strengths and weaknesses. It would be interesting to see how big a list we can come up with so see if you can contribute here.

It also opens up some debate about 'fringe' sites, such as AskTom and Steven Feuerstein's PL/SQL Q&A where the vast majority of questions are answered by one individual (although in Tom's case rumours persist of clones) and static Q&A sites such as the Co-operative FAQ

In that light, I suggest that there are several 'customers' for these forums (fora ?).
Firstly there is the questioner, sometimes wanting discussion about the merits of various approaches, sometimes wanting an urgent response to an emergency situation.
Secondly, there are the respondants. I class them as a customer because their participation suggests they are also looking for something too. Perhaps the feeling of helping others, or returning the assistance offered to them in the past.
Thirdly, there are searchers who, like questioners, are looking for an answer but use the forum's search facilities rather than repeat a previous question.
Finally, there are a group I think of as gatherers, who read a forum not for a specific answer but in the hope of picking up a new nugget of information here and there. Many in this group could be or may become respondants.

I don't believe there is general shortage of questioners and searchers out in the Oracle community. There will always be newbies out there needing a hand, and when faced with new functionality or versions, everyone starts off as a newbie again.

I'm less sure whether there is a shortage of respondants. Thinking of people I've worked with, there's plenty of people capable of making a valuable contribution here. Some may be active in forums I don't know about, or under pseudonyms. Others will be too busy with other things, or simply spend their spare time 'oracle-free'.

In an ideal world, the Oracle resources would all be in one place, more questioners could be satisfied with being searchers, there would be less repetition in forums and more 'nuggets' for the gatherers and AskTom would be open for questions ALL the time.

I like the concept of wikis and oradot, where people can log into a common resource and maintain a single body of knowledge (or at least pointers to that information). So I'm going to try to use them more, and will (eventually) reformat a few of my previous blog entries into oradot contributions. That way, I'm not the only one responsible if they are wrong :).

PS. Yes, I'm toying with my blog template. Normal service will be resumed when I work out what is normal.

The 12th SELECT of Xmas - Data generation through the MODEL clause

Okay, I missed twelfth night, but here's my final SELECT of Christmas : Data generation using the MODEL clause.

SQL has this annoying habit of only dealing with data that actually exists. To generate 'missing' data Oracle developers have come up with various tricks for generating lots of rows from nothing. Sometimes these involve the standard Oracle views which the developer assumes (or hopes) will return enough rows to meet their needs. ALL_TAB_COLUMNS tends to be a good one as the standard Oracle views themselves will throw back several thousand rows, never mind your own application tables.

Unfortunately, these are real views, and returning those few thousand rows will actually involve a degree of IO activity which is especially irritating when you really have no interest in the results.

Other people have come up with creative uses of DUAL and hierachial queries.
Personally, I'm not keen on them. To my mind, it isn't the intended use of the CONNECT BY clause and it is not documented that it would work that way. I just don't really TRUST it.

But in 10g, Oracle came up with the MODEL clause. It looks very complicated. It probably is very complicated. And I've avoided even looking at it for a long while.
But it is intended to generate data that doesn't actually exist.

So I've made a very simple example of a data generator using the MODEL clause.

SELECT days
FROM (select 1 days from dual) mnth
MODEL
  DIMENSION BY (days)
  MEASURES (days v)
  RULES UPSERT
  (v[FOR days FROM 1 TO 31 INCREMENT 1] = 1)
order by 1
/

Here is a slightly different technique which includes an UNTIL clause so that you can put in limits other than a simple count.

SELECT TO_CHAR(sysdate+days ,'DD-Mon-YYYY')
FROM (select 1 days from dual) mnth
MODEL
  DIMENSION BY (days)
  MEASURES (days v)
  RULES ITERATE (32)
  UNTIL (TO_CHAR(TRUNC(SYSDATE) +
             (ITERATION_NUMBER+1) ,'MON') !=
                     to_char(sysdate,'MON'))
  (v[ITERATION_NUMBER] = 1)
order by sysdate+days
/

12 Selects of christmas (the fourth instalment)

9. Unnesting a nested table

Okay, first I'll build a nested table using 10G's COLLECT operator.
Then I'll unnest it again..
A pointless exercise really, but then I haven't actually found a point to nested tables in the database anyway.

select m.table_name, c.column_value from
(select table_name,
       cast ((select collect(column_name)
        from user_tab_columns u
        where u.table_name = t.table_name) as sys.dbms_debug_VC2coll) cols
from user_tables t) m, table(cols) c

10. ROLLUP

select data_type, nullable, count(*)
from user_tab_columns
group by rollup (data_type, nullable)

As well as the counts for the individual data_type/nullable entries, it also gives subtotals for each data type, plus a grand total

11. CUBE

select data_type, nullable, count(*)
from user_tab_columns
group by cube (data_type, nullable)

In addition to the ROLLUP results, this will also gives subtotals for nullable.

12 Selects of christmas (the third instalment)

A couple of things (other than tables and views) that you can select from :

7. Selecting from a collection

You can (and should) create your own type for this :

create type tab_varchar2_64 as table of varchar2(64);

If you can't then you may be able to get by with using SYS.DBMS_DEBUG_VC2COLL

This will turn a list of values into rows.

SELECT column_value
from table(tab_varchar2_64('1','Fred','Amber'));

8. Selecting from a (Pipelined) function

Again, you should use your own collection for the return type.

create or replace function f_generate_dummy_name
return sys.DBMS_DEBUG_VC2COLL pipelined is
  type tab_char is varray(26) of varchar2(1);
  t_vowel tab_char := tab_char('A','E','I','O','U');
  t_other tab_char := tab_char('B','C','D','F','G','H','J','K','L','M','N',
                'P','Q','R','S','T','V','W','X','Y','Z');
begin
   for one in 1..t_other.count loop
     for two in 1..t_vowel.count loop
       for three in 1..t_other.count loop
          pipe row (t_other(three)||t_vowel(two)||t_other(one));
       end loop;
     end loop;
   end loop;
   return;
end;
.
/
select * from table(f_generate_dummy_name);

The holidays are over again

Okay, I only had the actual public holidays off, but you know what I mean.

We took all our decorations down yesterday (not quite twelfth night, but close enough given that it took half a day). My three year old son was pretty good at helping. My daughter just looked confused as the tree came apart. Still we've got her first birthday to look forward to next week.

The only disappointment was a Sesame Street PC game (Elmo's Workshop) for my son which wouldn't install. The AVG virus checker won't let it past a file (DSSAGENT.EXE) which the web tells me is a nasty piece of spyware and the little checkbox the install gives about this 'option' doesn't actually do anything. You expect this sort of **** from freeware downloaded from the web, not from something you've paid for under the name of a supposedly reputable organisation promoting children's education.

Fortunately, we borrowed a 'The Cat in the Hat' CD-ROM from the library which he is enjoying immensely.

12 Selects of christmas (the second instalment)

The followup to the first set of SQLs

4. Connect by

Show all your roles and the route you have obtained them through.

select SYS_CONNECT_BY_PATH(granted_role, '/') path
from dba_role_privs
start with grantee = user
connect by prior granted_role = grantee

5. WITH clause

The data dictionary views where there is an ALL_ version and a USER_ version

column a_name format a32
column u_name format a32

with w_tab as
(select distinct object_name, substr(object_name,instr(object_name,'_')+1) suffix,
        substr(object_name,1,instr(object_name,'_')-1) prefix
from dba_objects
where substr(object_name,1,instr(object_name,'_')-1) in ('ALL','USER'))
select a.object_name a_name, u.object_name u_name
from w_tab a, w_tab u
where a.prefix = 'ALL'
and u.prefix = 'USER'
and a.suffix = u.suffix;

6. ANSI style FULL Outer Join

I'm now bulding on the WITH select, but with a full outer join to find where there's a USER_ view with no corresponding ALL_ version or vice versa :

column suffix format a32
column a_name format a32
column u_name format a32

with w_tab as
(select distinct object_name name,
        substr(object_name,instr(object_name,'_')+1) suffix,
        substr(object_name,1,instr(object_name,'_')-1) prefix
from dba_objects
where substr(object_name,1,instr(object_name,'_')-1) in ('ALL','USER'))
select nvl(a.suffix,u.suffix) suffix,
       a.name a_name, u.name u_name from
   (select * from w_tab where prefix = 'ALL') a
   full outer join
   (select * from w_tab where prefix = 'USER') u
   on (a.suffix = u.suffix)
where a.name is null or u.name is null
order by 1;