Re: Storing Queries in Tables

On Jul 4, 12:39 pm, HectorTYC <rufus_mi..._at_btinternet.com> 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