/* * Test autonomous_transaction, simulation two session * bitmap will lock the column, when uncommit transaction exist */create table t( test_flag varchar2(1));create bitmap index t_idx on t(test_flag);-- uncommitinsert into t values(‘N‘);-- antonomous_transactiondeclarepragma autonomous_transaction;begin    insert into t values(‘Y‘);end;/

/* * This program will test index on function, * so when you want to use index on where condition,  * You need to use function.  */create table t(     id    number primary key,    test_flag    varchar2(1),    payload        varchar2(20));-- if test_flag == ‘N‘, return ‘N‘create index t_idx on t(decode(test_flag, ‘N‘, ‘N‘));    insert into tselect r,        case         when mod(r, 2) = 0 then ‘N‘        else ‘Y‘         end,        ‘payload ‘ || r   from (select level r           from dual           connect by level <= 5);/select * from t;create or replace function get_first_unlocked_rowreturn    t%rowtypeas    resource_busy exception;    pragma exception_init(resource_busy, -54);    l_rec t%rowtype;begin    for x in (select rowid rid                 from t               where decode(test_flag, ‘N‘, ‘N‘) = ‘N‘) -- index function    loop    begin        select * into l_rec          from t         where rowid = x.rid and test_flag = ‘N‘           for update nowait;        return l_rec;    exception        when resource_busy then null;        when no_data_found then null;    end;    end loop;    return null;end;/-- test functiondeclarel_rec t%rowtype;begin    l_rec := get_first_unlocked_row;                 dbms_output.put_line(‘I got row ‘ || l_rec.id || ‘, ‘ || l_rec.payload);    commit;end;/-- result is 2.declarel_rec t%rowtype;cursor cisselect *  from t where decode(test_flag, ‘N‘, ‘N‘) = ‘N‘    -- use decode function is for index   for update  skip locked;begin    open c;    fetch c into l_rec;    if (c%found)    then        dbms_output.put_line(‘I got row ‘ || l_rec.id || ‘, ‘ || l_rec.payload);    end if;    close c;end;/-- result is 2declarel_rec t%rowtype;pragma autonomous_transaction;cursor cisselect *  from t where decode(test_flag, ‘N‘, ‘N‘) = ‘N‘    -- use decode function is for index   for update  skip locked;  -- if you don‘t use this statement, the process will hang and wait begin    open c;    fetch c into l_rec;    if (c%found)    then        dbms_output.put_line(‘I got row ‘ || l_rec.id || ‘, ‘ || l_rec.payload);    end if;    close c;    commit;end;/-- result is 4

/* * This program will show use bind variable is very effect */create table t(x int);create or replace procedure proc1asbegin        for i in 1..10000        loop                execute immediate                ‘insert into t values(:x)‘                using i;        end loop;end;/create or replace procedure proc2asbegin        for i in 1..10000        loop                execute immediate                ‘insert into t values(‘|| i ||‘)‘;        end loop;end;/-- use runstats_pkg test to procedureexec runstats_pkg.rs_start;exec proc1;exec runstats_pkg.rs_middle;exec proc2;exec runstats_pkg.rs_stop(10000);

/* * This program will test SQL injection when you don‘t use bind variable. */create or replace procedure inj(p_date in date)as    l_rec    all_users%rowtype;    c        sys_refcursor;    l_query    long;begin    l_query := ‘        select *          from all_users         where created = ‘‘‘ || p_date || ‘‘‘‘;        dbms_output.put_line(l_query);        open c for l_query;        for i in 1..5        loop                fetch c into l_rec;                exit when c%notfound;                dbms_output.put_line(l_rec.username || ‘.....‘);        end loop;        close c;end;/-- you want to showexec inj(sysdate);-- show the dangerous part about this inj procedurecreate table user_pw( uname varchar2(30) primary key,  pw varchar2(30));insert into user_pw(uname, pw)values(‘TKYTE‘, ‘TO SECRET‘);COMMIT;-- now, some user don‘t know user_pw table exist, simulation this table-- +is very important.grant execute on inj to scott;-- so now, scott connect the database, and do as belowalter session set nls_date_format = ‘"‘‘union select tname, 0, null from tab--"‘;exec leon.inj(sysdate);/* * The result is: * -----------------------------------------   select *     from all_users    where created = ‘‘union select tname, 0, null from tab--‘ * ----------------------------------------- * we know table information, some important table. */-- in this way, scott can see the table user_pw(very important table)-- now they want to try to select this important table.select * from leon.user_pw;  -- but they can not, because they don‘t have privilege.alter session set nls_date_format = ‘"‘‘union select tname || cname, 0, null from col--"‘;exec leon.inj(sysdate);/* * The result is: * -----------------------------------------   select *     from all_users    where created = ‘‘union select tname || cname, 0, null from col--‘ * ----------------------------------------- * we know the column information in some important table. */-- use bind variable to pertect you.create or replace procedure NOT_inj(p_date in date)as    l_rec    all_users%rowtype;    s        sys_refcursor;    l_query    long;begin        l_query := ‘        select *          from all_users         where created = :x‘;        dbms_output.put_line(l_query);        open c for l_query using P_DATE;        for i in 1..5        loop                fetch c into l_rec;                exit when c%notfound;                dbms_output.put_line(l_rec.username || ‘....‘);        end loop;        close c;end;/-- test not_injexec not_inj(sysdate);/* * The result is: * -----------------------------------------   select *     from all_users    where created = :x * ----------------------------------------- */-- so from now on, you must use bind variable. ^^

create table resources( resource_name    varchar2(25) primary key,  other_data varchar2(25));create table schedules( resource_name varchar2(25) references resources,  start_time date,  end_time date);-- the purpose about schedules is when someone want to modify table resources-- he needs to check the schedules status, but if someone-- modify the table schedules, and you select schedules, you can not find -- the result modified.select count(*)   from schedules where resource_name = :resource_name   and (start_time < :new_start_time)   and (end_time > :new_end_time);-- so the correct selectment is as below:select * from resources where resource_name := resource_name for update;-- for update is very important, it is lock on the row. so if someone want to-- modify the table, you will know it.

variable scn number;exec :scn := dbms_flashback.get_system_change_number;print scn;select count(*) from emp;    -- 14 rowsdelete from emp;select count(*) from emp;     -- 0 rows-- use flashback, as of scn, as of timestampselect count(*)        :scn then_scn,        dbms_flashback.get_system_change_number now_scn  from emp as of scn :scn;    -- get the time point at :scn-- the result is/*COUNT(*) THEN_SCN NOW_SCN ---------- ---------- ---------- 14        6294536 6294537*/-- you can see the result is 14.commit;        -- commit the transactionselect cnt_now, cnt_then, :scn then_scn,        dbms_flashback.get_system_change_number now scn    from (select count(*) cnt_now from emp),         (select count(*) cnt_then, from emp as of scn :scn)//*CNT_NOW CNT_THEN THEN_SCN NOW_SCN ---------- ---------- ---------- ---------- 14               14 6294536 6294552 */flashback table emp to scn :scn;    -- the data return

declare    l_some_varibale    varchar2(25);begin    if (some_condition)    then        l_some_variable := f(...);    end if;        for x in (select * from t where x = l_some_variable)    loop        ...    end loop;-- as this statement-- in oracle, this query return no data when l_some_variable was not set to a specific value-- in sybase or sql server, the query would find rows where x was set to a null value.-- in oracle null can not use = to set condition, like as below:select * from dual where null = null;  -- return 0 rows selectedselect * from dual where null <> null; -- return 0 rows selectedselect * from dual where null is null; -- return X.-- to solve this problem select * from t where nvl(x, -1) = nvl(l_some_variable, -1);-- and you need to create a function indexcreate index t_idx on t(nvl(x, -1));

-- inline viewsselect p.id, c1_sum1, c2_sum2  from p,        (select id, sum(q1) c1_sum1           from c1          group by id) c1,        (select id, sum(q2) c2_sum2           from c2          group by id) c2    where p.id = c1.id      and p.id = c2.id-- sub query that run another query per rowselect p.id,        (select sum(q1) from c1 where c1.id = p.id) c1_sum1,        (select sum(q2) from c2 where c2.id = p.id) c2_sum2  from p where p.name = ‘1234‘-- sub factory with clausewith c1_vw as(select id, sum(q1) c1_sum1   from c1  group by id),c2_vw as(select id, sum(q2) c2_sum2   from c2  group by id),c1_c2 as(select c1.id, c1.c1_sum1, c2.c2_sum2   from c1_vw c1, c2_vw c2  where c1.id = c2.id)select p.id, c1_sum1, c2_sum2  from p, c1_c2 where p.id = c1_c2.id

create profile one_session limit sessions_per_user 1;alter user scott profile one_session;alter system set resource_limit = true;