01 Developing Successful Oracle Application

/*
 * 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);

-- uncommit
insert into t values(‘N‘);

-- antonomous_transaction
declare
pragma 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 t
select 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_row
return    t%rowtype
as
    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 function
declare
l_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.

declare
l_rec t%rowtype;
cursor c
is
select *
  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 2

declare
l_rec t%rowtype;
pragma autonomous_transaction;
cursor c
is
select *
  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 proc1
as
begin
        for i in 1..10000
        loop
                execute immediate
                ‘insert into t values(:x)‘
                using i;
        end loop;
end;
/

create or replace procedure proc2
as
begin
        for i in 1..10000
        loop
                execute immediate
                ‘insert into t values(‘|| i ||‘)‘;
        end loop;
end;
/

-- use runstats_pkg test to procedure
exec 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 show
exec inj(sysdate);

-- show the dangerous part about this inj procedure
create 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 below
alter 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_inj
exec 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 rows
delete from emp;
select count(*) from emp;     -- 0 rows

-- use flashback, as of scn, as of timestamp
select 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 transaction
select 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 selected
select * from dual where null <> null; -- return 0 rows selected
select * 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 index
create index t_idx on t(nvl(x, -1));

-- inline views
select 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 row
select 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 clause
with 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;