Re: Storing Queries in Tables

On 5 Jul, 06:24, dean <deanbrow...@xxxxxxxxx> wrote:
On Jul 4, 8:38 am, HectorTYC <rufus_mi...@xxxxxxxxxxxxxx> wrote:

On Jul 4, 12:49 pm, sybrandb <sybra...@xxxxxxxxx> 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

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.


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

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.- Hide quoted text -

- Show quoted text -

Not as far as I can tell. Unfortunately I'm limited in the amount of
investigation I can do purely because I don't have access to the
relevant areas. I can only really suggest areas for investigation to
fairly unhelpful individuals, this forum has been exceedingly useful
in giving me suggestions for them, unfortunately it hasn't made them
more helpful. ;)

It does seem that the size of the query is relevant and that kind of
brings me back to character sets. In ASCII one of the offending
queries is 10k indented, can anyone confirm that this could
potentially exceed the 32k limit in the dbms_sql package on a database
with an AL32UTF8 character set?