Re: Storing Queries in Tables

On Jul 4, 8:38 am, HectorTYC <rufus_mi..._at_btinternet.com> wrote:
> On Jul 4, 12:49 pm, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks Sybrand. That is useful in pointing me towards asking the right
> questions. Unfortunately the more that I ask the more confusing the
> scenario becomes, having spoken again to the DBA and a developer, I
> now no longer believe the character sets necessarily have any
> relevance.
>
> It seems the queries are stored in a CLOB and the stylesheets also
> seem to be stored in the correct Oracle format. I think there has been
> very lazy investigation into this issue by all parties. All that can
> really be said is that when sometimes reports have failed, they have
> removed all the indents from the query and then they have worked.
> Beyond that all is just guessing.
>
> For what it's worth I have a slightly better picture of what's
> supposed to happen/happens.
>
> User runs a report by running a generic ksh with parameters e.g. name
> of report, date, other qualifier etc.
>
> The script passes the parameters into a java bean on the application
> tier, the bean calls a reports package (pk_reports funnily enough) on
> the database which matches the report name in a reference table,
> matches the format then gets the appropriate sql and stylesheet and
> writes the output.
>
> The majority of the queries stored work fine and are indented, some of
> the larger ones do not and removing the indents 'fixes' them.
>
> Unless anyone has any brainwaves I wouldn't recommend they waste too
> much energy on this. I can't really trust the information I'm being
> given enough to warrant the effort on your parts. Thanks again to
> those for whom this is too late. :)
>
> Cheers- Hide quoted text -
>
> - Show quoted text -

• Repost due to Google post not responding after 3 min. If it is a duplicate then my apologies. --

Because the dbms_sql package also has a SQL statement limitation of 32K I suggest you consider Sybrand's recommendation to look into defining some views. If part of the code in some of your queries could be coded into a view that the report SQL could reference then the net effect would be to reduce the report SQL statement size by the number of bytes in the view minus the length of the table name and join conditions required in the report query to use the view.

You should also implement a parser that removes all unnecessary space from the stored SQL and send all statements through it.

Long term if SQL statements greater than 32k need to be supported then I suggest you look into coding the report processor as a Pro*C or OCI/ OCCI program where longer SQL statements are supported.

HTH -- Mark D Powell -- Received on Wed Jul 04 2007 - 09:08:01 CDT