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_at_gmail.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l