Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Storing Queries in Tables

From: dean <>
Date: Thu, 05 Jul 2007 05:24:28 -0000
Message-ID: <>

On Jul 4, 8:38 am, HectorTYC <> wrote:
> On Jul 4, 12:49 pm, sybrandb <> 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 -

Do you have any very large SQL statements that DO run, that are larger than the indented ones that fail? Do you see any other patterns wrt size limits? Sounds like the problem may not be in the Oracle side but either your java or report engine is running over some kind of space limit. Removing the indents may bump you back under the limits there. Received on Thu Jul 05 2007 - 00:24:28 CDT

Original text of this message