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.