ORA-32031: illegal reference of a query name in WITH clause

I was trying to use the subquery factoring clause, WITH, in a query as it was a fairly small subset but called a number of times...with the same predicates.

I ran into the above mentioned error.

Here's my query:

WITH periods
AS
(
  SELECT datefrom, datethru
  FROM vw_periods
  WHERE periodtypeid = 'WEEK'
    AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
)
SELECT *
FROM periods;

(Thanks Tom!) Pretty simple right? That's what I thought.

  FROM vw_periods
       *
ERROR at line 6:
ORA-32031: illegal reference of a query name in WITH clause

Hmmm...what's that? Since I can't get to the docs right now, I'll point you here.

Cause:
forward or recursive reference of a query name in WITH clause is not allowed.
Action:
Correct query statement, then retry.

That's not very helpful.

What's the definition of the view?

CREATE OR REPLACE
VIEW vw_periods
AS
SELECT
  periodid,
  periodtypeid,
  datefrom,
  datethru
FROM periods;

Nothing there...wait, what's the name of the table? Periods? Couldn't be that simple (and yet so obvious) could it?

SQL>WITH per
  2  AS
  3  (
  4    SELECT datefrom, datethru
  5    FROM vw_periods
  6    WHERE periodtypeid = 'WEEK'
  7      AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
  8  )
  9  SELECT *
 10  FROM per;

DATEFROM  DATETHRU
--------- ---------
08-DEC-08 14-DEC-08
15-DEC-08 21-DEC-08
22-DEC-08 28-DEC-08
29-DEC-08 04-JAN-09
05-JAN-09 11-JAN-09
12-JAN-09 18-JAN-09
19-JAN-09 25-JAN-09
26-JAN-09 01-FEB-09

8 rows selected.

Of course it can!

Lesson? Don't try to name your subquery with the same name as the base table.

Is DISTINCT a Bug?

In your application or data model that is.

On a well designed system, I've rarely seen the need to use DISTINCT.

If anything, I use it to do analysis on table data or maybe a summary report. Something like this:

SELECT
  COUNT( DISTINCT( col_1 ) ) col1_count,
  COUNT( DISTINCT( col_2 ) ) col2_count,
  COUNT( DISTINCT( col_3 ) ) col3_count,
  COUNT(*) count_all
FROM my_table;

That will tell me the basic distribution of certain data elements in a specific table. Good for possibly determining whether a column needs a Bitmap Index.

In an OLTP system however, the liberal use of DISTINCT signals (to me) a problem in the underlying model. Obviously not all instances are without merit, but I'd be willing to bet that the majority are an indicator.

How about you? What are you thoughts on DISTINCT? Good? Bad? Indifferent?

Developing a J2EE based SOA Architecture

No, I haven't gone to the dark side.

I just figured it would be best to know my enemy. ;)

Tonight I went to the Tampa JUG (no, it's not a code word for the local strip joint so stop laughing), or, the Tampa Java Users Group. The guy that runs is the the not so infamous Mr. V, from Application Developers vs. Database Developers Part II. I also got to meet/see some former WellCare colleagues as well (hi everyone!).

The presentation was done by Kevin Barfield from JBoss.

Interesting tool which incorporates (I think I'll get this right) the JBoss server, BPM and just about anything else you can imagine. It also included an overview of the Enterprise Service Bus (ESB). That's also a new file type that you can load (as opposed to .ear, .war, etc).

Overall pretty cool stuff, but I really can't put it into context. I'd have to monkey around with it for awhile before I could truly understand what to do with it.

Anyway, that was my trip on the dark side. Not a whole lot of fun but an opportunity to expand my horizons.

Dowload Source Code to Disk

I wanted to be able to download all the source to file system. The problem was that I couldn't figure out how to separate the Package Specification and the Package Body.

I sent out a tweet asking for help. @neilkod answered my plea. Try using DBMS_METADATA he said. I did I replied. He suggested using a filter. Hadn't tried that.

OK, I won't rehash the entire (slow) conversation here, suffice it to say, he pointed me in the right direction.

Here's the code I ended up with:

  FUNCTION get_ddl
    ( p_owner IN VARCHAR2,
      p_object_name IN VARCHAR2,
      p_object_type IN VARCHAR2 ) RETURN CLOB
  IS
    l_handle NUMBER;
    l_clob CLOB;
    l_transform_number NUMBER;
    l_object_type VARCHAR2(30);
  BEGIN
    
    l_object_type := get_object_type( p_object_type );

First, a call to an internal (my internal) routine that will determine the object type value for the OPEN function. I found that table here.

    l_handle := dbms_metadata.open( object_type => l_object_type );

    dbms_metadata.set_filter
      ( handle => l_handle,
        name => 'SCHEMA',
        value => p_owner );

    dbms_metadata.set_filter
      ( handle => l_handle,
        name => 'NAME',
        value => p_object_name );

The SET_FILTER value values can be found here.

    l_transform_number := dbms_metadata.add_transform
                            ( handle => l_handle,
                              name => 'DDL' );

If you don't set this you'll get a bunch of XML and I really wasn't in the mood for XML.

    l_clob := dbms_metadata.fetch_clob
                ( handle => l_handle,
                  cache_lob => TRUE,
                  lob_duration => DBMS_LOB.SESSION );

Fetch the CLOB...

    dbms_lob.write
      ( lob_loc => l_clob,
        amount => 1,
        offset => dbms_lob.getlength( l_clob ),
        buffer => '/' );

Append a run "/" sign at the end of the file...

    dbms_metadata.close( handle => l_handle );

    RETURN l_clob;
  EXCEPTION
    WHEN others THEN  
      dbms_metadata.close( handle => l_handle );
      RAISE;
  END get_ddl;

Voila!

All done. Now you just need a routine to write it to file and you're done.

I'm also putting this in the googlecode repository here: http://code.google.com/p/plsqlsourcedownload/

You should be able to view all the code there. If not, let me know. Not sure how that works yet but I'm giving it a go.