Re: change / baypass sql

Ujang,

Outlines change *how* the statements are executed not *what* they do. What
you are probably looking for is triggers on the base table.

Read this:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#i6052

Make sure you understand difference of before/after and instead types of
triggers.

Vlad Sadilovskiy
Oracle Database Tools
http://www.fourthelephant.com

On 10/9/07, Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx> wrote:
>
> all,
>
> is there anyway to change or baypass sql statement in oracle?
> i tried using outline, but no luck.
>
> CREATE OR REPLACE OUTLINE ORIGINALSQL for category us ON
> update emp2 set last_name='XCXX'
> where last_name = :lastnm;
>
>
> CREATE OR REPLACE OUTLINE HINTSQL for category us ON
> select sysdate
> from dual;
>
> UPDATE OUTLN.OL$HINTS
> SET
> OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL')
> WHERE OL_NAME IN ('ORIGINALSQL','HINTSQL');
> commit;
>
> alter system set use_stored_outline=US;
>
> alter system flush shared_pool;
>
>
> update emp2 set last_name='XCXX'
> where last_name = :lastnm;
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 2492025900
>
>
> ---------------------------------------------------------------------------
> | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
> |
>
> ---------------------------------------------------------------------------
> |   0 | UPDATE STATEMENT   |      |     3 |    21 |     5   (0)| 00:00:01
> |
> |   1 |  UPDATE            | EMP2 |       |
> |            |          |
> |*  2 |   TABLE ACCESS FULL| EMP2 |     3 |    21 |     5   (0)| 00:00:01
> |
>
> ---------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>   2 - filter("LAST_NAME"=:LASTNM)
>
> Note
> -----
>   - outline "ORIGINALSQL" used for this statement
>
>
> but the row successfully updated.... :(
>
>
> --
> regards
> ujang
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Other related posts: