Re: Storing Queries in Tables

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
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 style*** 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 --

.