Re: Storing Queries in Tables

On Jul 4, 12:39 pm, HectorTYC <rufus_mi...@xxxxxxxxxxxxxx> wrote:
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

Apparently the datatype that has been used to store the SQL-statements
is inappropiate. It smells heavily of VARCHAR2. The maximum length of
a VARCHAR2 is 4000 bytes.
Either you should follow the method Oracle is using to store SQL
internally, allowing mutiple lines, by affixing an id to the statement
and adding a column called 'Piece' in which piece denotes a sequence
number, or
you should have the VARCHAR2 replaced by a CLOB (maximum limit 2G).
This will of course result in a rewrite of the application.
I won't even mention the word 'VIEWS'. Likely parts of those
statements are identical, so one could potentially store them as a
view. This should be transparent to the application (ie the change
will have less impact on the actual code), however it might introduce
an extra layer of abstraction and confusion.

The second question is appalling, and shows ignorance on either the
developers part or the DBA part.
It looks like one of those databases has a multibyte characterset
(like UTF8) and the other database has a single byte characterset,
like WE8ISO8859P1. Even worse in both cases the table definition reads
varchar2(40 BYTE) instead of VARCHAR2(40) -- meaning characters
, so that in the former definition less multibyte characters can be
stored.

This can be resolved, but it is likely going to result in recreation
of the database.
Anyway, it should never have happened in the first place.

Hth,

--
Sybrand Bakker
Senior Oracle DBA

.

Relevant Pages