Storing Queries in Tables

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:

• Are there any obvious and elegant solutions which would be relatively straightforward to migrate to that I could suggest to the developers?
• What should I be expecting from the DBAs here? Obviously it's not their jobs to design the system but am I right to be disappointed that they have allowed this situation to continue for over 18 months (by all accounts) without offering more than an occasional explanation that "the character sets are larger on production"?

We are running Oracle 9.2.0.7 on the production database.

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