Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Storing Queries in Tables

Storing Queries in Tables

From: HectorTYC <>
Date: Wed, 04 Jul 2007 03:39:23 -0700
Message-ID: <>

As an intermittent reader (a DBA friend of mine occasionally points out items he thinks may interest or amuse) of this group I post this with some trepidation ...

Before I start I am not a Oracle DBA and my knowledge of Oracle is scant, I could try reading a manual but it wouldn't mean much to me. I'm a relatively recently appointed release manager who is responsible for various code deployments and deliveries in a small-to-medium-size, process-immature business/charity. I have a scenario where some my deployments are failing due to what I suspect to be a flawed methodology. Forgive me if I do not make myself absolutely clear when trying to describe the scenario, I'm very much a layman in your terms.

The situation is this as I understand it:

Reports are stored as SQL in tables in our main application schema. These reports are called by various processes which run the SQL to produce the reports.

Some of the queries are getting so big that they do not fit into the tables unless someone goes through them and removes spurious characters like tabs and spaces. Unfortunately the fact they don't fit is never discovered until the report fails. The situation is exacerbated by the fact that our production environments have larger character sets than our test environments so we never see these issues in test. (I should add that these reports are delivered as part of a monthly release package, obviously the error trapping in the scripts that deliver them is not good either).

The architecture and the applications were 'delivered' by a consultancy and our development team were moved from older technologies onto this platform so are unlikely to question the methodology or be in a position to suggest new ones. Our DBAs seem to be competent (not proficient ;) ) but are mostly reactive and rarely proffer alternative solutions.

I can't believe there aren't at least a dozen better ways of doing this and the problems we're seeing now will only get worse if we carry on down this road and the queries get larger. I've just been asking development and they seem not to understand the original reasoning behind the decision but it appears that all the reports, letters etc. are stored in these reference tables and are called through the application layer. The implication is "we don't know why we do it this way but the amount of work involved in changing is considerable."

So my questions really are:

We are running Oracle on the production database.

Many thanks Received on Wed Jul 04 2007 - 05:39:23 CDT

Original text of this message