Re: Is it possible to filter or hook the Oracle SQL parser?

Shangwu wrote:
> Daniel,
>
> Thank you for the quick response. Pardon me for reposting on multiple
> groups. Because I am new in this area and not sure which group is proper for
> the question.
> What we want to do is to create a tool that can create table partitions and
> take some partition off line if the data in the partition is not accessed
> recently. This activity could cause the existing applications broken if we
> don't update their queries. So if we can filter the SQL parser of Oracle,
> all existing queries can be modified to exclude the offline partition.
> It seems that DBMS_RLS is not what we can utilize and DBMS_ADVANCED_REWRITE
> is only supported in 10g. Our target Oracle servers include 9.x.
>
> Regards,
>
> Shangwu
>
>
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1126126462.164787_at_yasure...
> > Shangwu wrote:
> >> Hello,
> >>
> >> I want to create a program in PL/SQL or C/C++ to interpret and modify all
> >> SQL statements coming to an Oracle server. Is it possible to hook such
> >> program to the SQL parser of Oracle?
> >> Any comment is appreciated.
> >>
> >> Shangwu
> >
> > And the point of reinventing the wheel?
> >
> > 1. Fine Grained Access Control ... DBMS_RLS built-in package
> > 2. Advanced Rewrite ... DBMS_ADVANCED_REWRITE built-in package
> >
> > Alternatively you can pass in SQL statements as a CLOB, do
> > whatever you wish with them inside a procedure, and then use
> > the DBMS_SQL package to execute them.
> >
> > But this really looks like nothing short of bad architecture.
> >
> > Please also do not repost to c.d.o.tools and c.d.o.misc as
> > this is off-topic and triple posting only gets people mad.
> > --
> > Daniel A. Morgan
> > http://www.psoug.org
> > damorgan_at_x.washington.edu
> > (replace x with u to respond)

Please do NOT top-post.

I can't understand how taking a partition 'off-line' will break a query against a partitioned table. And I don't understand how you'll modify queries unless the query specifically includes the 'off-line' partition.

Please explain further, as this still appears to be a bad idea taking shape.

David Fitzjarrell Received on Wed Sep 07 2005 - 17:07:35 CDT