Methods, questions and answers.

Today is one of those days when you've got a couple of recent blog posts which intersect beautifully.

Cary Millsap is here discussing why the right method is more important than the right answer.
Steven Feuerstein inadvertently opened up a can of worms regarding the seemingly simple task of extracting a list of a user's tables from the data dictionary.

The fallout from Steven's question in the PL/SQL Challenge is that the term 'table' is fuzzy and has become fuzzier over the years. Views, object tables, XML tables, Materialized Views, tables that have been dropped but are sitting in the recyclebin. The various answers (except for one) offered for that question in the PL/SQL all worked in SOME circumstances, but not necessarily all circumstances.

You can appreciate the confusion if you have had to explain to someone that, if you grant privileges on a procedure, the data dictionary shows that in USER_TAB_PRIVS.

Personally, I'm more attracted to DBMS_METADATA.GET_DDL these days. I just wish there was a (built-in) function that took the object name (and optionally schema) and worked out the object type for itself.

Anyway, the only thing I can add to the discussion is that testing is the process of proving that your method is applicable to a defined set of situations. The wider the testing, the more situations you can be confident of being addressed successfully.

In pure mathematics and some scientific disciplines, you can devise theoretical proofs that prove the wider applicability of a method without individual testing. That's harder in implementation. Maths can prove that multiply X by two, then divide by two, you get X as the result. In a physical implementation, you might have to deal with rounding imprecision or maximum permitted values or buffer overflows....

Creating objects in tablespaces without any quota

Say you've got a schema with privileges on multiple tablespaces and you want to make sure that objects get created in the appropriate one. One way to do that is make the user's default tablespace one that they don't have a quota on.

That way, if they try to create an object without specifying the tablespace, it picks the default one and errors because there is no quota. Or at least it used to.

Apparently in 11gR2 it doesn't work this way any more. With deferred segment creation, you can create objects in tablespaces where you don't have any quota, and it won't fail until a row gets inserted.

I can see another 'gotcha' for this if you are progressing code from DEV/TEST. The creation DDL make succeed by sticking the table in a USERS tablespace (which isn't expected to be used, except maybe for some trivial temporary tables) whereas before it would fail and red-flag it for re-assignment to the appropriate tablespace.

Puzzle me this

Like Jeff Kemp, I got yesterday's PL/SQL challenge wrong too.

The question rested on what identifier/alias Oracle would assign to a column derived from an expression if no explicit alias is supplied.

If you have a cursor with such a column, and you base a variable on a ROWTYPE of that cursor, you really want to know the identifier to use. Steve says "The problem is that this expression has not been assigned an alias. As a result, there is no way in my PL/SQL code to refer to that expression". Jeff says "I had this stupid idea that Oracle would step in and magically create an alias for the column".

Actually, Oracle does assign an alias, or at least an identifier. It is just a little more complicated than any of the offered choices (though in this example I've switched the table used so that I don't need to create any special objects)..

DECLARE
   CURSOR profitable_internet_stocks
   IS
      SELECT SUM (num_rows) FROM user_tables;
   few_and_far_in_between   profitable_internet_stocks%ROWTYPE;
BEGIN
   OPEN profitable_internet_stocks;
   FETCH profitable_internet_stocks
   INTO few_and_far_in_between;
   CLOSE profitable_internet_stocks;
   dbms_output.put_line(few_and_far_in_between."SUM(NUM_ROWS)");
END;  

The following block shows how to use DBMS_SQL.DESCRIBE_COLUMNS to determine the column names assigned by Oracle.



DECLARE
  PROCEDURE temp (p_query IN VARCHAR2) IS
    col_cnt   INTEGER;
    rec_tab   dbms_sql.desc_tab2;
    v_tab     dbms_sql.varchar2s;
    v_cursor  NUMBER;
  BEGIN
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, p_query, 1);
    dbms_sql.describe_columns2(v_cursor, col_cnt, rec_tab);
    dbms_sql.close_cursor(v_cursor);
    FOR v_pos in 1..rec_tab.LAST LOOP
      dbms_output.put_line(rpad(rec_tab(v_pos).col_name,32));
    END LOOP;
  END;BEGIN
  temp('SELECT SUM (num_rows) FROM user_tables');
END;
/

Of course it is generally better to use an alias when you have the choice.


Could Oracle be 'smarter' in allocating an identifier ? Probably not. There's nothing wrong with the identifier it came up with except that it has to be expressed as a quoted identifier. If the expression had been a simple numeric value (eg SELECT 1 FROM user_tables) then there isn't an appropriate identifier that it can come up with that doesn't require quoting.

Incidentally, it is the SQL engine, rather than the PL/SQL engine, that assigns these identifiers.
The following shows that the "SUM(NUM_ROWS)" identifier is automatically assigned to the first column as well as being used as the alias for the second. As such, Oracle can't work out what column is being referred to in the outer select.

select "SUM(NUM_ROWS)" from
(SELECT sum(num_rows), 1 as "SUM(NUM_ROWS)" FROM user_tables)

Extreme Querying Presentation

I gave a presentation to the Sydney Oracle meetup last week. It was primarily focussed on Oracle's Analytic functions, but also mentioned the MAX () KEEP DENSE_RANK functionality (which I was reminded about a few months back by a performance comparison here) and the ROLLUP part of GROUP BY. Morgan's Library has a good demonstration.

That's the tricky bit about presenting SQL stuff. You don't want slides full of queries that end up being too small to read, and no-one would remember them anyway. So you talk more about what they can and can't do (and a few gotchas). I'm not sure I got the ideal compromise, but I stick more details in the Notes part of the powerpoint presentations.

The presentation is available in the Files area of the Sydney Oracle Meetup website as both Powerpoint 2007 and a PDF of the Notes pages.  And on my slideshare account as a Powerpoint 2003 (the upload of the pptx failed for some obsure reason). And through the Presentations link on my homepage. Feel free to post critiques too.

In the end I rushed a bit and finished a bit earlier than I thought. I need to get some more presentation practice in. The company I work for used to do a couple of presentations a week internally, partly to spread knowledge but also a good way of gaining presentation experience. Unfortunately those stopped about a year back, and I'm waiting to see if the new boss (who came in a couple of months ago) shows any interest in putting them back on.

I'm on the committee for the Meetup and am on the lookout for meeting / presentation ideas. Feel free to add suggestions. I know most of you aren't in the Sydney area, but remember that I'll post any presentations I give publically, and I'm happy for anyone else to take the material (and build on it) for their own presentations (unless in a blatant commercially exploitative way). So let rip into any topics which you think could do with some presentation material.

Interesting Times

One a day before the Socceroos faced they final match in the World Cup (they won, but still did't make the next round), interesting times were occurring in the political arena back home.
Australia's Prime Minister Kevin Rudd  (seen here, eating his own earwax) is about to be deposed. Australia follows the Westminster system of government. Local areas elect a member of parliament, and the parliament chooses the Prime Minister from within its ranks. Party politics inevitably gets involved so the Prime Minister is invariably the leader of the party with the most seats in the House.

The Labor party currently has the most seats, at least in the main House of Representatives. We will ignore the Senate, elected by a peculiar form of proportional representation and described by one former Prime Minister as "unrepresentative swill".

Having decided that the Prime Minister, elected less than three years ago after overturning a long running Liberal party administration, isn't the best choice to lead the party to the next election, the deputy leader is challenging for the job. Kevin Rudd isn't going quietly. He'd like at least a chance at the election he would have called within the next six months.

In an hour or so, we will find out whether Australia will get its first woman PM in Julia Gillard .

We've had women as party leaders before. The most recent example has been the Democrats party, now practically dead after committing the unmentionable act of actually keeping an election promise. Back in 1998, The Liberals proposed a wide-ranging Goods and Sevices Tax (GST) . The Democrats, under Meg Lees , campaigned on a GST that excluded food . When they got the balance of power (ie their votes were needed to secure passage of the Bill), they forced that amendment. The shock of a party actually following through on an election promise was such that practically no-one voted for them again. After a brief sojurn under a different woman leader (Natasha_Stott_Despoja ) , they vanished from parliament.

So the topic of conversation this morning will be either the Socceroos or politics. As a Pom (with dual citizenship) I may stick with the former.At least to encourage the Aussies to support the team playing Germany which flogged the Aussies 4-0, which was responsible for the poor goal difference that kept the Aussies from second spot. Oh, and the team playing the Germans next will be England.

Well its either that or support the Kiwis who still have a chance of qualifying if they can beat Paraguay. Though Nuno can support Portugal, I guess.

Stackoverflow and StackExchange

Like Tony Andrews, I am a big fan of the StackOverflow Q&A engine. I'm just not as good as him :)

If you haven't tried it, go ahead. Here are the questions tagged Oracle .

• I like that it uses OpenId so I don't have another username/password to remember.
• I like that code formatting is so simple. 
• I like the simply numbered 'reputation' and the baadges you can collect.
• I like the fact that all the questions, answers and comments are Creative Commons licensed and you can download the whole lot.
• I like the RSS feeds based on tags.

The only drawback is that the site is aimed at programming. It has a sister site, serverfault.comwhich is more for admins. But that is mostly for system admins. There is a third site in the trilogy called superuser.com  aimed at, well, superusers. I guess that Apex websheets might end up fitting in there.

In database work there is a lot that can fall in either a developer or DBA camp. Performance tuning is the prime example. There is a limit to what can be achieve by tweaking SQL and at some point it becomes beneficial to address more fundamental issues through indexes or storage options. And some places have DBAs or 'the Oracle guy' who does everything, and at the other end of the spectrum who may have someone who's sole domain is backup/recovery with a separate 'architect' who has visions dancing in his head (maybe of data models but possibly of sugar plums).

So is there room for a similar site dedicated to Oracle or dedicated to databases in general ? One site to rule them all ?

The people behind stackoverflow are opening up stackexchange 2.0. People get to vote for potential new sites. And there are two proposals up and running, one for Oracle and one for Databases. If you are interested in either of these, follow the link and click the FOLLOW button.

Then, if you want, come up with some sample questions. You can get some ideas from other forums or what the people around you always end up asking.

Once a proposal is sufficiently defined, and has enough committed supporters, it can be launched and then its just a matter of driving questioners to it.

I should point out that OracleOverflow started up under the StackExchange 1.0 model (ie paid for, in this case by RedGate who are better know in the SQL Server field) but didn't really get the momentum. I don't believe this site meets the conditions for migration. If some of the premium members of the database community (Oak table members, ACEs, high-profile bloggers, oracle-l members etc) can voice their support for this, then it shouldn't have any problem getting off the ground.

Spread the word please.

Long intervals, PL/SQL Functions and subtypes.

I was doing some work with the interval day to second datatype recently. I hit a problem and thought it worth documenting my solution.

Generally, when you create a PL/SQL function or parameter, the scale/precision/size of the parameters (including the return value of a function) is undefined.

For example, you say FUNCTION show_price RETURN NUMBER or FUNCTION show_name RETURN VARCHAR2. You are not allowed to have FUNCTION show_price RETURN NUMBER(10,2) or FUNCTION show_name RETURN VARCHAR2(20), and the length/precision/scale is supposedly derived from the calling environment.

This new fangled INTERVAL DAY TO SECOND datatype is a bit odd, in that it has a default precision of 2 for the days.  That is, it can only store a number of days up to two digits (99 days). So if you have a function with a INTERVAL DAY TO SECOND return type, it will balk at a value of 9999 hours (over 400 days), and throw a ORA-01873 precision error, even if the precision of the variable in the calling environment is sufficient.

DECLARE
  v_int INTERVAL DAY (4) TO SECOND(0);
  FUNCTION hhmm_to_interval return INTERVAL DAY TO SECOND IS
    v_hhmm INTERVAL DAY (4) TO SECOND(0);
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;
end;
/

ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at line 7
ORA-06512: at line 11

You are not allowed to specify the precision directly as part of the datatype returned by the function.

DECLARE
  v_int INTERVAL DAY (4) TO SECOND(0);
  FUNCTION hhmm_to_interval return INTERVAL DAY (4) TO SECOND IS
    v_hhmm INTERVAL DAY (4) TO SECOND(0);
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;
end;
/
ORA-06550: line 3, column 49:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
to

You can use a PL/SQL SUBTYPE however.

DECLARE
  subtype t_int is INTERVAL DAY (4) TO SECOND(0);
  v_int t_int;
  FUNCTION hhmm_to_interval return t_int IS
    v_hhmm t_int;
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;

  dbms_output.put_line(v_int);
end;
/
+0416 15:00:00

PL/SQL procedure successfully completed.

The documentation is a bit skimpy here though. It states that where a parameter is based on a subtype, a NOT NULL constraint on the subtype is applied to the parameter. It also states that for numbers and VARCHAR2 it does NOT apply the size/scale/precision constraints (though it will apply a range constraint for a numeric type).

I added a comment for the documentation with the hope that the INTERVAL datatypes get a specific mention.

A comment by stezgr on the same issue raised on the stubbisms blog pointed out a built-in DSINTERVAL_UNCONSTRAINED subtype for this. When searching on that, Google brings up Steven Feuerstein's Oracle PL/SQL Programming book which describes using that built-in as the solution.