OOUG Oracle Technology Day - July 20, 2005

drop table user_table;
set echo on
clear screen
create table user_table 
( username varchar2(30), password varchar2(30) );

insert into user_table values ( 'tom', 'top_secret_password' );
insert into user_table values ( 'sue', 'top_secret_password' );

commit;
pause

set echo off
clear screen
accept Uname prompt "Enter username: "
prompt enter things like  
prompt x' or 'x' = 'x
prompt x' or decode( rownum,1,'x') = 'x
accept Pword prompt "Enter password: "
set echo on

select count(*)
  from user_table
 where username = '&Uname'
   and password = '&Pword'
/
pause

clear screen
variable uname varchar2(30);
variable pword varchar2(30);
exec :uname := 'tom'
exec :pword := 'x'' or ''x'' = ''x'
pause 

select count(*)
  from user_table
 where username = :uname
   and password = :pword
/
	

@connect /
set termout off
@runstats
set termout on

set echo off
clear screen
prompt
prompt
prompt Bind variable comparision -- what happens when you DON'T use em 
prompt
prompt
set echo on
drop table t;
create table t ( x int, y char(80) );
pause

clear screen
create or replace procedure p1
as
begin
    for i in 1 .. 10000
    loop
        execute immediate '
        insert into t t' || mod(i,2) || ' (x,y) values ( :x, :y )' using i, i;
    end loop;
end;
/
pause
clear screen
create or replace procedure p2
as
begin
    for i in 1 .. 10000
    loop
        execute immediate '
        insert into t t' || mod(i,2) || ' (x,y) values 
		( ' || i || ', ''' || replace(i,'''','''''') || ''' )';
    end loop;
end;
/
pause

clear screen
exec runStats_pkg.rs_start
exec p1
exec runStats_pkg.rs_middle
exec p2
exec runStats_pkg.rs_stop(200)
	

@connect /

set termout off
@runstats
set termout on

set echo off
clear screen
prompt
prompt
prompt Bind variable comparision -- what happens when you DON'T use em 
prompt
prompt
set echo on
drop table t;
create table t ( x int, y char(80) );
pause

clear screen
create or replace procedure p1
as
begin
    for i in 1 .. 10000
    loop
        execute immediate '
        insert into t (x,y) values ( :x, :y )' using i, i;
    end loop;
end;
/
pause
clear screen
create or replace procedure p2
as
begin
    for i in 1 .. 10000
    loop
        execute immediate '
        insert into t (x,y) values 
		( ' || i || ', ''' || replace(i,'''','''''') || ''' )';
    end loop;
end;
/
pause

clear screen
exec runStats_pkg.rs_start
exec p1
exec runStats_pkg.rs_middle
exec p2
exec runStats_pkg.rs_stop(200)
	

create table job_parameters
( jobid number primary key,
  iterations number,
  table_idx number );


create or replace procedure dont_bind( p_job in number )
as
    l_rec job_parameters%rowtype;
begin
    select * into l_rec from job_parameters where jobid = p_job;
    for i in 1 .. l_rec.iterations
    loop
        execute immediate
        'insert into t' || l_rec.table_idx || '
         values ( ' ||  i || ' )';
        commit;
    end loop;
    delete from job_parameters where jobid = p_job;
end;
/

create or replace procedure bind( p_job in number )
as
    l_rec job_parameters%rowtype;
begin
    select * into l_rec from job_parameters where jobid = p_job;
    for i in 1 .. l_rec.iterations
    loop
        execute immediate
        'insert into t' || l_rec.table_idx || ' values ( :x )' using i;
        commit;
    end loop;
    delete from job_parameters where jobid = p_job;
end;
/

create or replace procedure simulation
( p_procedure in varchar2, p_jobs in number, p_iters in number )
authid current_user
as
    l_job number;
    l_cnt number;
begin
    for i in 1 .. p_jobs
    loop
        begin
            execute immediate 'drop table t' || i;
        exception
            when others then null;
        end;
        execute immediate 'create table t' || i || ' ( x int )';
    end loop;

    for i in 1 .. p_jobs
    loop
        dbms_job.submit( l_job, p_procedure || '(JOB);' );
        insert into job_parameters
        ( jobid, iterations, table_idx )
        values ( l_job, p_iters, i );
    end loop;

    statspack.snap;
    commit;
    loop
        dbms_lock.sleep(30);
        select count(*) into l_cnt from job_parameters;
        exit when (l_cnt = 0);
    end loop;
    statspack.snap;
end;
/
	

create table t1 ( x int primary key, y int )compress;
alter table t1 add z number;
alter table t1 drop column y;
alter table t1 set unused column y;
alter table t1 modify z number(12);