This classroom excercise is by Edward Stoever - edward@1st-R8.com. 
(copyright 2003 - distribute freely - not for sale)

Purpose: Load the server with queries to learn performance tuning.

INSTRUCTIONS:

The following is a series of 7 scripts that will allow you to create two very 
large tables: sauce and pasta. Sauce is the parent table and pasta is the child
table. Sauce will have 10,000 rows. Pasta must be populated AFTER running the 
install. You can create as many rows as you like in pasta, depeding on how long you
run the "insert_load.sql" script, which runs through a loop to populate it.

General instructions:
Step one: Copy each script and paste it into a seperate document and save it as described.
You will have the following scripts:

        PASTA_INSTALL.SQL
        INSERT_LOAD.SQL	
        Q.SQL
        QUERY_LOAD.SQL
        PASTA_UNINSTALL.SQL
        START.SQL
        STOP.SQL

Step two: Run the PASTA_INSTALL.SQL
Step three: from a second login (as same user) run the start.sql
Step four: from the previous login run the insert_load.sql
Step five: wait a few minutes. The longer you wait, the larger the pasta table will be, just
           don't fall asleep or the table will fill your disk with garbage! ;-)
Step six: from the second login, run the stop.sql.  This will stop the looping in the first login.
Step seven: run the q.sql (this simply runs the query_load.sql over and over again, but 
          not in a never ending loop)

Notes: this is an excellent way to produce many, many queries to the database in a very short period 
       of time. Excellent for populating a statspack.snap for classroom work. Originally, I had 
       many NOT's in my queries, but they performed so badly that I took them out. So, if some
       of my queries look so stupid that you wonder what I was thinking, this may be why.
       Regardless, there are plenty of joins, and concats, and all kinds of strange queries
       in this script. You can easily add more of your own. This is an excellent learning tool!
       Special thanks to Orafaq.com for providing the RANDOM package.

----------------------------------------------------------------------------------
REM ------ SAVE AS PASTA_INSTALL.SQL

create or replace package random
is
   procedure srand(new_seed in number);
   procedure get_rand(r OUT number);
   procedure get_rand_max(r OUT number, n IN number);
   function  rand return number;
   function  rand_max(n IN number) return number;
   function  rand_string(ssiz IN number) return varchar2;
   function  smaller(x IN number, y IN number) return number;
   pragma restrict_references(rand, WNDS);
   pragma restrict_references(rand_max, WNDS);
   pragma restrict_references(random, WNDS, RNPS);
   pragma restrict_references(rand_string, WNDS);
   pragma restrict_references(smaller, WNDS);
end random;
/

create or replace package body random
is
   multiplier   constant number := 22695477;
   increment    constant number := 1;
   "2^32"       constant number := 2 ** 32;
   "2^16"       constant number := 2 ** 16;
   "0x7fff"     constant number := 32767;
   Seed         number          := 1;

   function  smaller(x IN number, y IN number) return number is
   begin
	if x <= y then
	    return x;
	else
	    return y;
	end if;
   end smaller;

   function rand_string(ssiz IN number) return varchar2 is
     i      number;
     m      number;
     c      char;
     result varchar2(2000) := '';
   begin
	m := smaller(ssiz,2000);
	for i in 1..m loop
	    c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
	    result := result || c;
        end loop;
	return result;
   end rand_string;

   procedure srand(new_seed in number) is
   begin
     Seed := new_seed;
   end srand;

   function rand return number is
   begin
     Seed := mod(multiplier * Seed + increment, "2^32");
     return bitand(Seed/"2^16", "0x7fff");
   end rand;

   procedure get_rand(r OUT number) is
   begin
     r := rand;
   end get_rand;

   function rand_max(n IN number) return number is
   begin
     return mod(rand, n) + 1;
   end rand_max;

   procedure get_rand_max(r OUT number, n IN number) is
   begin
     r := rand_max(n);
   end get_rand_max;

begin
   select userenv('SESSIONID')
   into   Seed
   from   dual;
end random;
/
create or replace package my_dbms_sql
  as
      procedure define_all( p_cursor in integer );
      type varchar2_table is table of varchar2(4000) index by
      binary_integer;
      function fetch_row( p_cursor in integer )
          return varchar2_table;
  end;
  /



 create or replace package body my_dbms_sql
  as
  g_number_of_columns   dbms_sql.number_table;
  procedure define_all( p_cursor in integer )
  as
      l_columnValue   varchar2(4000);
     l_descTbl       dbms_sql.desc_tab;
      l_colCnt        number;
  begin
      dbms_sql.describe_columns( p_cursor,
                                 l_colCnt, l_descTbl );
      for i in 1 .. l_colCnt loop
          dbms_sql.define_column( p_cursor, i,
                                  l_columnValue, 2000 );
      end loop;
      g_number_of_columns(p_cursor) := l_colCnt;
  end;
  function fetch_row( p_cursor in integer )
       return varchar2_table
  is
      l_return        varchar2_table;
  begin
      for i in 1 .. g_number_of_columns(p_cursor) loop
          l_return(i) := NULL;
          dbms_sql.column_value( p_cursor, i,
                                 l_return(i) );
      end loop;
      return l_return;
  end;
  end;
  /
----------------------------------
	create table sauce (
	  cheese number(4),
	  tomato_paste varchar2(30), 
	  CONSTRAINT PK_SAUCE_CHEESE PRIMARY KEY (cheese)
			   )
	storage (minextents 7);
----------------------------------
	create table pasta (
	  cheese number(4) , 
	  tomato varchar2(20), 
	  salt varchar2(1), 
	  oregano varchar2(20), 
	  olive_oil varchar2(40),
	  CONSTRAINT FK_PASTA_CHEESE FOREIGN KEY (cheese) references sauce(cheese)
			    );
-----------------------------------
	CREATE TABLE STOPIT (STOP VARCHAR2(1));
	  INSERT INTO STOPIT VALUES ('N'); 
	  commit;
-----------------------------------
	Create table SQL_holder (
	  num number UNIQUE, 
	  sql_text varchar2(300)
				);
------------------------------------
pro PLEASE WAIT WHILE I POPULATE THE SAUCE TABLE!
-- THE FOLLOWING WILL POPULATE THE SAUCE TABLE!
declare 
i number:=0;
begin
for i in 0..9999 loop
declare	cursor c1 is select random.rand_string(30) wow from dual;
begin

			for c2 in c1 loop
			null;

	insert into sauce (cheese, tomato_paste) values (i, c2.wow);
			end loop;
end;
end loop;
commit;
end;
/
------------------------------------
pro PLEASE WAIT WHILE I POPULATE THE SQL_HOLDER TABLE!
insert into sql_holder (num, sql_text) values (1, 'select /* yahoo 1 */ 
					cheese from pasta where cheese > 9990 order by cheese');
insert into sql_holder (num, sql_text) values (2, 'select olive_oil from pasta where cheese in
(select cheese from sauce where tomato_paste like ''%a%'')');
insert into sql_holder (num, sql_text) values (3, 'select tomato from pasta where cheese > 9609 order by tomato');
insert into sql_holder (num, sql_text) values (4, 'select * from pasta where cheese in (select max(cheese) from pasta)');
insert into sql_holder (num, sql_text) values (5, 'select * from pasta where oregano like ''%yo%''');
insert into sql_holder (num, sql_text) values (6, 'select p.cheese, p.tomato, p.salt, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese AND
p.cheese > 950');
insert into sql_holder (num, sql_text) values (7, 'select count(salt) "TOTAL", salt
from pasta
group by salt');
insert into sql_holder (num, sql_text) values (8, 'select tomato_paste from sauce where cheese in
(select cheese from pasta where salt = ''F'')');
insert into sql_holder (num, sql_text) values (9, 'select tomato_paste from sauce where cheese in 
(select cheese from pasta where salt <> ''F'' and cheese < 200)');
insert into sql_holder (num, sql_text) values (10, 'select s.cheese, s.tomato_paste, p.tomato
from sauce s, pasta p
where s.cheese = p.cheese
AND s.cheese = 13');
insert into sql_holder (num, sql_text) values (11, 'select salt, olive_oil 
from pasta 
where olive_oil like ''%oil%''');
insert into sql_holder (num, sql_text) values (12, 'select *
from pasta
where tomato like ''%55%''');
insert into sql_holder (num, sql_text) values (13, 'select tomato_paste from sauce where cheese > 88 and cheese < 102');
insert into sql_holder (num, sql_text) values (14, 'select oregano, olive_oil from pasta where salt = ''M'' and cheese > 2009');
insert into sql_holder (num, sql_text) values (15, 'select cheese, tomato, salt
from pasta where
cheese in (select cheese from sauce where tomato_paste like ''%aa%'')
order by cheese desc, tomato asc');
insert into sql_holder (num, sql_text) values (16, 'select oregano from pasta
where salt = ''F'' and cheese > 9000
order by oregano desc');
insert into sql_holder (num, sql_text) values (17, 'select p.olive_oil, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and p.cheese > 9500');
insert into sql_holder (num, sql_text) values (18, 'select p.cheese, p.tomato, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and p.cheese > 9000
order by s.tomato_paste');
insert into sql_holder (num, sql_text) values (19, 'select count(p.cheese), p.cheese, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
group by p.cheese, s.tomato_paste
order by p.cheese');
insert into sql_holder (num, sql_text) values (20, 'select olive_oil, salt from pasta order by olive_oil');
insert into sql_holder (num, sql_text) values (21, 'select salt from pasta where cheese in ( select cheese from sauce where tomato_paste like ''%bbb%'')');
insert into sql_holder (num, sql_text) values (22, 'select cheese, tomato, salt, olive_oil from pasta
where tomato  LIKE ''%a%'' AND
olive_oil LIKE ''%a%'' and
oregano like ''%1%''');
insert into sql_holder (num, sql_text) values (23, 'select cheese, tomato_paste from sauce where cheese > 20 and cheese < 44');
insert into sql_holder (num, sql_text) values (24, 'select cheese, tomato_paste from sauce where cheese > 20 and cheese < 44 order by cheese desc');
insert into sql_holder (num, sql_text) values (25, 'select p1.cheese, p1.tomato, p2.cheese, p2.tomato
from pasta p1, pasta p2
where p1.cheese <> p2.cheese
and substr(p1.tomato,1,4) = substr(p2.tomato,1,4)');
insert into sql_holder (num, sql_text) values (26, 'select floor(avg(cheese)) "AVERAGE CHEESE" from pasta');
insert into sql_holder (num, sql_text) values (27, 'select count(substr(tomato,1,2)) "COUNT EM!", substr(tomato,1,2) "TOMATO"
from pasta
group by substr(tomato,1,2)
order by count(substr(tomato,1,2))');
insert into sql_holder (num, sql_text) values (28, 'select p.olive_oil, s.cheese, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and s.tomato_paste like ''%''||(select substr(tomato_paste,3,2) from sauce where cheese = 447)||''%''');
insert into sql_holder (num, sql_text) values (29, 'select olive_oil, tomato, cheese from pasta where cheese between 10 and 15');
insert into sql_holder (num, sql_text) values (30, 'select * from pasta where
tomato like ''%cat%''
or oregano like ''%cat%''
or olive_oil like ''%cat%''
order by cheese');
insert into sql_holder (num, sql_text) values (31, 'select distinct s.cheese, s.tomato_paste, p.salt
from sauce s, pasta p
where s.cheese = p.cheese
and s.cheese between  500 and 1000
and p.salt = ''F''
order by s.cheese desc, s.tomato_paste desc');
insert into sql_holder (num, sql_text) values (32, 'select tomato from pasta 
where tomato like 
''%''||(select substr(tomato_paste,3,3) from sauce where cheese = 99)||''%''');
insert into sql_holder (num, sql_text) values (33, 'select cheese, tomato, salt, oregano
from pasta where tomato like ''%55%''
UNION
select cheese, tomato, salt, oregano
from pasta where oregano like ''%55''
order by cheese desc');
insert into sql_holder (num, sql_text) values (34, 'select sysdate from dual');
insert into sql_holder (num, sql_text) values (35, 'select rownum, tomato, salt, olive_oil
from (select tomato, salt, olive_oil from pasta order by olive_oil)
where rownum < 11');
insert into sql_holder (num, sql_text) values (36, 'select salt, pasta.cheese, olive_oil, tomato_paste
from pasta, sauce
where pasta.cheese = sauce.cheese
and pasta.cheese < 10
order by pasta.cheese');
insert into sql_holder (num, sql_text) values (37, 'select cheese, tomato, salt, oregano, olive_oil
from pasta
where cheese between 1 and 1001
and tomato like ''%a%''
and salt = ''M''
and oregano like ''%c%''
and olive_oil like ''%o%''
order by cheese');
insert into sql_holder (num, sql_text) values (38, 'select distinct salt from pasta');
insert into sql_holder (num, sql_text) values (39, 'with noodle as
(select decode(substr(tomato,1,2), ''aa'',tomato,''bb'',''HUNGRY?'',''cc'',''WATCH OUT!'', ''Mary had a little lamb'') food_fight from pasta)
select food_fight from noodle where food_fight <> ''Mary had a little lamb''
order by food_fight');
insert into sql_holder (num, sql_text) values (40, 'select s.cheese, s.tomato_paste, p.olive_oil
from sauce s, pasta p
where s.cheese = p.cheese
and substr(p.olive_oil,1,2)=''an''');
insert into sql_holder (num, sql_text) values (41, 'select * from pasta where tomato = ''5ykbaa20zao2596h9v24''');
insert into sql_holder (num, sql_text) values (42, 'select tomato from pasta where cheese in
(select cheese from sauce where tomato_paste like ''%54%'')');
insert into sql_holder (num, sql_text) values (43, 'select olive_oil
from pasta
where cheese = 981');
insert into sql_holder (num, sql_text) values (44, 'select olive_oil
from pasta
where cheese = 988');
insert into sql_holder (num, sql_text) values (45, 'select olive_oil
from pasta
where cheese = 989');
insert into sql_holder (num, sql_text) values (46, 'select olive_oil
from pasta
where cheese = 901');
insert into sql_holder (num, sql_text) values (47, 'select olive_oil
from pasta
where cheese = 977');
insert into sql_holder (num, sql_text) values (48, 'select * from pasta where cheese <> 44
and cheese between 1 and 300
and tomato like ''%aa%''
order by cheese');
insert into sql_holder (num, sql_text) values (49, 'select cheese, tomato_paste from sauce where cheese in
(select distinct cheese from pasta
where cheese between 1 and 30)');
insert into sql_holder (num, sql_text) values (50, 'select s.tomato_paste, p.salt, max(p.cheese) "MAX CHEESE"
from pasta p, sauce s
where p.cheese = s.cheese
and p.cheese between 10 and 100
group by s.cheese, s.tomato_paste, p.salt');
insert into sql_holder (num, sql_text) values (51, 'select max(cheese), salt from pasta
group by salt');
insert into sql_holder (num, sql_text) values (52, 'select tomato, salt, olive_oil
from pasta where cheese in
(select cheese from sauce where tomato_paste like ''%jjj%'')');
insert into sql_holder (num, sql_text) values (53, 'select cheese, tomato, salt from pasta where salt <> ''F'' and cheese < 200');
insert into sql_holder (num, sql_text) values (54, 'select * from pasta where salt = ''F'' and tomato  like ''%5%''');
insert into sql_holder (num, sql_text) values (55, 'select p.cheese, p.tomato, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and s.tomato_paste like ''%5%''
and p.cheese < 500');
insert into sql_holder (num, sql_text) values (56, 'select olive_oil, tomato_paste, p.cheese
from pasta p, sauce s
where p.cheese=s.cheese
and p.cheese between 500 and 603');
insert into sql_holder (num, sql_text) values (57, 'select oregano from pasta where
cheese = 101');
insert into sql_holder (num, sql_text) values (58, 'select oregano from pasta where
cheese = 111 or cheese = 9122');
insert into sql_holder (num, sql_text) values (59, 'select tomato from pasta where
cheese = 111 or cheese = 9122');
insert into sql_holder (num, sql_text) values (60, 'select cheese, tomato from pasta where cheese = 877 or cheese = 77');
insert into sql_holder (num, sql_text) values (61, 'select p.cheese, p.tomato, p.olive_oil, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and p.cheese in (select cheese from pasta where oregano like ''%4%'')');
insert into sql_holder (num, sql_text) values (62, 'select p.cheese, p.tomato, p.olive_oil, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and p.cheese in (select cheese from pasta where olive_oil like ''%4%'')');
insert into sql_holder (num, sql_text) values (63, 'select * from pasta where cheese in
(select max(cheese) cheese from pasta group by salt)');
insert into sql_holder (num, sql_text) values (64, 'select tomato, tomato_paste
from pasta, sauce
where pasta.cheese = sauce.cheese
and pasta.cheese > 9050');
insert into sql_holder (num, sql_text) values (65, 'select oregano, tomato, cheese from pasta
where oregano like ''%a%''
or oregano like ''%b%''
or oregano like ''%c%''
or oregano like ''%d%''
or oregano like ''%e%''
or oregano like ''%f%''
or oregano like ''%g%''');
insert into sql_holder (num, sql_text) values (66, 'select oregano, tomato, cheese from pasta
where oregano like ''%z%''
and tomato like ''%y%''
and tomato like ''%x%''
and tomato like ''%w%''
and tomato like ''%v%''
and tomato like ''%u%''
and tomato like ''%t%''
and tomato like ''%s%''');
insert into sql_holder (num, sql_text) values (67, 'select * from pasta where tomato like ''%en%''');
insert into sql_holder (num, sql_text) values (68, 'select tomato_paste from sauce where cheese in (33,34,35)');
insert into sql_holder (num, sql_text) values (69, 'select tomato_paste from sauce where cheese in (89,100,3003,4444,6001)');
insert into sql_holder (num, sql_text) values (70, 'select cheese, tomato_paste from sauce where tomato_paste like ''a%''
UNION
select cheese, olive_oil from pasta where olive_oil like ''a%''');
insert into sql_holder (num, sql_text) values (71, 'select cheese, tomato, olive_oil, oregano from pasta
where salt =''M''
and tomato like ''%tt%''
and olive_oil like ''%u%''');
insert into sql_holder (num, sql_text) values (72, 'select sauce.cheese, tomato_paste, olive_oil
from pasta, sauce
where pasta.cheese=sauce.cheese
and sauce.cheese between 55 and 99');
insert into sql_holder (num, sql_text) values (73, 'select salt from pasta where salt = ''F'' and cheese = 6777');
insert into sql_holder (num, sql_text) values (74, 'select cheese, tomato from pasta where cheese in
(select cheese from sauce where
tomato_paste like ''%1%''
and tomato_paste like ''%3%''
and tomato_paste like ''%5%''
and tomato_paste like ''%7%''
and tomato_paste like ''%9%'')');
insert into sql_holder (num, sql_text) values (75, 'select s.cheese, s.tomato_paste, p.tomato
from sauce s, pasta p
where s.cheese = p.cheese
and p.olive_oil like ''%6%''
and p.tomato like ''%6%''
and p.oregano like ''%6%''');
insert into sql_holder (num, sql_text) values (76, 'select tomato_paste from sauce where cheese = (select max(cheese) from sauce)');
insert into sql_holder (num, sql_text) values (77, 'select cheese, salt, oregano 
from pasta where cheese in ( select cheese from pasta where mod(cheese,2) = 1)');
insert into sql_holder (num, sql_text) values (78, 'select cheese, olive_oil from pasta where cheese = (select floor(avg(cheese
)) cheese from pasta)');
insert into sql_holder (num, sql_text) values (79, 'select oregano from pasta where cheese in (select cheese from sauce where tomato_paste like ''%11%'')');
insert into sql_holder (num, sql_text) values (80, 'select salt, tomato from pasta where salt = ''F''
and tomato like ''%abc%''');
insert into sql_holder (num, sql_text) values (81, 'select oregano, salt from pasta where cheese in
(select cheese from sauce where tomato_paste like ''%yy%'')');
insert into sql_holder (num, sql_text) values (82, 'select p.cheese, p.tomato, p.salt, p.olive_oil, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and p.salt = ''F''
and s.cheese between 698 and 1344');
insert into sql_holder (num, sql_text) values (83, 'select salt, oregano from pasta where cheese = 411');
insert into sql_holder (num, sql_text) values (84, 'select cheese, tomato_paste from sauce where tomato_paste like ''%zz%''');
insert into sql_holder (num, sql_text) values (85, 'select cheese, tomato, oregano, olive_oil from pasta where cheese between 5 and 55');
insert into sql_holder (num, sql_text) values (86, 'select tomato, salt, oregano from pasta where cheese  = 555');
insert into sql_holder (num, sql_text) values (87, 'select cheese, tomato_paste from sauce where cheese in 
(select cheese from pasta where tomato like ''%ii%'')');
insert into sql_holder (num, sql_text) values (88, 'select cheese, tomato_paste from sauce where cheese =
(select max(cheese) from pasta)');
insert into sql_holder (num, sql_text) values (89, 'select p.cheese, p.tomato, p.salt, p.oregano, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and s.cheese in (select cheese from sauce where
tomato_paste like ''%tt%'')');
insert into sql_holder (num, sql_text) values (90, 'select tomato, cheese, salt from pasta where
salt = ''F''
and tomato  like ''%5%''');
insert into sql_holder (num, sql_text) values (91, 'select cheese, tomato_paste from sauce where cheese = 7');
insert into sql_holder (num, sql_text) values (92, 'select olive_oil, oregano, salt, tomato, cheese
from pasta where cheese between 50 and 500
and salt <> ''F''
and tomato like ''%7%''');
insert into sql_holder (num, sql_text) values (93, 'select tomato from pasta where cheese in 
(select cheese from sauce where tomato_paste like ''%nn%'')');
insert into sql_holder (num, sql_text) values (94, 'select tomato, olive_oil from pasta where cheese in
(select cheese from sauce where
tomato_paste like ''%e%''
or tomato_paste like ''%f%''
or tomato_paste like ''%g%'')');
insert into sql_holder (num, sql_text) values (95, 'select salt, oregano, olive_oil 
from pasta 
where salt = ''F'' and cheese < 250');
insert into sql_holder (num, sql_text) values (96, 'select oregano, salt, tomato from pasta where
cheese in ( select cheese from sauce where tomato_paste
like ''%zz%'')');
insert into sql_holder (num, sql_text) values (97, 'select salt from pasta where cheese in
(select cheese 
from sauce 
where cheese < 500 
and tomato_paste like ''%5%'')');
insert into sql_holder (num, sql_text) values (98, 'select p.cheese, s.tomato_paste, p.tomato
from sauce s, pasta p
where p.cheese = s.cheese
and p.cheese < (select avg(cheese) from pasta)
and p.tomato like ''%y%''
order by p.cheese');
insert into sql_holder (num, sql_text) values (99, 'select cheese, tomato, salt 
from pasta 
where cheese > (select avg(cheese) from pasta)
and salt <> ''F''
and tomato like ''%dd%''');
insert into sql_holder (num, sql_text) values (100, 'select cheese, tomato
from pasta where cheese > 500
and cheese < 800
and cheese in (select cheese from
sauce where tomato_paste like ''%3%'')');
insert into sql_holder (num, sql_text) values (101, 'select olive_oil
from pasta where
olive_oil like ''%4%''
and olive_oil like ''%3%''
and olive_oil like ''%2%''');
insert into sql_holder (num, sql_text) values (102, 'select cheese, tomato, salt from pasta where cheese in (
select cheese from sauce where tomato_paste like ''%''||(
select olive_oil from 
(select substr(olive_oil,1,2) Olive_oil, rownum from pasta order by olive_oil) 
where rownum = 1)||''%'')');
insert into sql_holder (num, sql_text) values (103, 'select cheese, salt||tomato "SALTY TOMATO!" 
from pasta where salt||tomato like 
''F''||(select substr(tomato_paste,1,2) from sauce where cheese = 77)||''%''');
insert into sql_holder (num, sql_text) values (104, 'select count(noodleRoni) "TOTOAL NOODLES", noodleRoni from (
select substr(olive_oil,1,1)||salt noodleRoni from pasta
where substr(olive_oil,1,1)||salt in (select substr(tomato_paste,1,1)||''F'' from sauce
where cheese = 999)) group by noodleRoni');
insert into sql_holder (num, sql_text) values (105, 'select tomato, salt, oregano from pasta 
where salt <> ''F''
and cheese < 500
and substr(olive_oil,1,1) <> ''a''');
insert into sql_holder (num, sql_text) values (106, 'select oregano, cheese from pasta 
where cheese in (select cheese from sauce where substr(tomato_paste,1,3) 
in (select substr(tomato,1,3) from pasta where cheese < 1000))');
insert into sql_holder (num, sql_text) values (107, 'select cheese, oregano from pasta where
length(oregano) = 1
or length(oregano) =2
order by cheese asc, oregano desc');
insert into sql_holder (num, sql_text) values (108, 'select p.tomato, p.olive_oil, s.cheese, s.tomato_paste 
from pasta p, sauce s
where p.cheese = s.cheese and
substr(olive_oil,1,2) = substr(tomato,1,2)');
insert into sql_holder (num, sql_text) values (109, 'select olive_oil, salt from pasta where cheese = 
(select floor(months_between(sysdate, sysdate-500)) from dual)');
insert into sql_holder (num, sql_text) values (110, 'select p.cheese, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and p.cheese in (select cheese from sauce where
tomato_paste in (select tomato_paste from sauce where
cheese in (select cheese from pasta where olive_oil like
''%at%'')))');
insert into sql_holder (num, sql_text) values (111, 'select olive_oil, salt from pasta where
salt <> ''F''
and olive_oil like ''%''||
(select substr(tomato_paste,1,2) from sauce where cheese=44) ||''%''
order by olive_oil');
insert into sql_holder (num, sql_text) values (112, 'select cheese, salt, olive_oil from pasta
where tomato like ''%''||(select substr(tomato_paste,1,2) from sauce
where cheese = (select random.rand_max(9999) from dual))||''%''
order by cheese');
insert into sql_holder (num, sql_text) values (113, 'with xyz as (select random.rand_max(55)+1000 yoyo from dual)
select cheese, tomato, salt from pasta where
cheese between (select yoyo from xyz) and (select yoyo+2000 from xyz)
order by cheese');
insert into sql_holder (num, sql_text) values (114, 'select salt, tomato, olive_oil from pasta
where salt = ''F''
and cheese in
(select cheese from sauce
where tomato_paste like ''%11%'')');
insert into sql_holder (num, sql_text) values (115, 'select distinct salt from pasta where
cheese in (select  cheese from sauce
where tomato_paste
like ''%''||(select substr(tomato_paste,1,3) from sauce where cheese =
(select random.rand_max(9999) from dual))||''%'')');
insert into sql_holder (num, sql_text) values (116, 'select p.tomato, s.tomato_paste
from pasta p, sauce s
where p.cheese = s.cheese
and substr(p.tomato,1,2) = substr(s.tomato_paste,1,2)');
insert into sql_holder (num, sql_text) values (117, 'select cheese, tomato_paste from sauce where
cheese = 675');
insert into sql_holder (num, sql_text) values (118, 'select s.cheese, s.tomato_paste, p.salt
from sauce s, pasta p
where s.cheese = p.cheese
and s.cheese between 103 and 222');
insert into sql_holder (num, sql_text) values (119, 'select cheese, salt, tomato from pasta where
tomato like ''%''||
(select substr(tomato_paste,1,2)
from sauce where cheese =
(select random.rand_max(300)from dual))||''%''
order by cheese asc');
insert into sql_holder (num, sql_text) values (120, 'select tomato_paste, cheese from sauce where
tomato_paste like ''%''|| (select substr(tomato,1,2) from
(select tomato, rownum from pasta) where rownum = 1)||''%''');
insert into sql_holder (num, sql_text) values (121, 'select cheese, tomato_paste from sauce where
tomato_paste like ''%''||(select substr(tomato,1,2) from
(select tomato, rownum from pasta) where rownum = 1)||''%''');
insert into sql_holder (num, sql_text) values (122, 'select to_char(sum(cheese),''FM$999999999999.00'') "Total Cheese Sales", 
salt from pasta group by salt');
insert into sql_holder (num, sql_text) values (123, 'select tomato, cheese, salt from pasta where
cheese = (select floor(avg(cheese)/2) from  sauce)');
insert into sql_holder (num, sql_text) values (124, 'select salt, tomato from pasta where
tomato like ''%''||(select substr(tomato_paste,1,2) from sauce where
cheese = ( select random.rand_max(2000) from dual))||''%''');
insert into sql_holder (num, sql_text) values (125, 'select oregano, salt, cheese
from pasta where tomato like ''%a3%''
order by oregano');
insert into sql_holder (num, sql_text) values (126, 'select olive_oil, oregano, salt, tomato, cheese from pasta
where cheese in (
select random.rand_max(9999) from dual
UNION
select random.rand_max(9999) from dual
UNION
select random.rand_max(9999) from dual
)');
insert into sql_holder (num, sql_text) values (127, 'select cheese, tomato_paste
from sauce
where cheese < (select random.rand_max(200) from dual)');
insert into sql_holder (num, sql_text) values (128, 'select substr(tomato,1,3), rownum from (select tomato, rownum
from pasta order by tomato) where rownum < 11');
insert into sql_holder (num, sql_text) values (129, 'select tomato, salt, oregano from pasta
where length(oregano) = 1');
insert into sql_holder (num, sql_text) values (130, 'select cheese, oregano from pasta where cheese in
(select cheese from sauce where tomato_paste in
(select tomato_paste from sauce where cheese in
(select cheese from pasta where olive_oil like ''%dd%'')))');
insert into sql_holder (num, sql_text) values (131, 'select p.salt , s.tomato_paste from
pasta p, sauce s
where p.cheese = s.cheese
and p.tomato like ''%j%''
and s.cheese between 500 and 2500
order by s.tomato_paste');
insert into sql_holder (num, sql_text) values (132, 'select cheese, tomato_paste from sauce where tomato_paste
in (select tomato_paste from sauce where tomato_paste like ''%y%''
or cheese between 1000 and 9000)');
insert into sql_holder (num, sql_text) values (133, 'select tomato, salt, olive_oil from pasta where cheese in
(select cheese from sauce where tomato_paste like ''%a%''
or tomato_paste like ''%c%''
or tomato_paste like ''%d%'')');
insert into sql_holder (num, sql_text) values (134, 'select tomato from pasta where cheese in 
(select max(cheese) from pasta group by salt)');
insert into sql_holder (num, sql_text) values (135, 'select cheese, tomato_paste from sauce where tomato_paste
like ''%hh%'' and cheese between 300 and 6300');

commit;
/


----------------------------------------------------------------------------------
REM ------ SAVE AS INSERT_LOAD.SQL


set serveroutput on
declare 
v_stop_this stopit.stop%type;
yyy boolean := true;
zzz number := 0;

   begin
     loop
	select stop into v_stop_this from stopit;
	
          if yyy  and v_stop_this = 'Y' then
		dbms_output.put_line('----------------------------------------------------------'); 
		dbms_output.put_line('To insert many rows into the pasta table,                 '); 
		dbms_output.put_line('please run the @start script from a seperate session.     ');
		dbms_output.put_line('Then, rerun this script. You can stop this script by      '); 
		dbms_output.put_line('running the @stop script from the other session.          '); 
		dbms_output.put_line('## Scripts by Edward Stoever, (c)2003 edward@1st-R8.com ##'); 
		dbms_output.put_line('##  Distribution is  free because I am such a cool guy! ##');
		dbms_output.put_line('----------------------------------------------------------'); 
	  end if;

	
 	exit when v_stop_this = 'Y';
          yyy:=false;
-------------------------------------------------------
declare 
   x number :=1;
   i number :=0;
   n number :=0;
   mf pasta.salt%type;
   
   begin
        for x in 1..100  
        loop
          begin

		declare cursor c1 is select random.rand_string(60) www from dual;
		        cursor d1 is select random.rand_max(9999) zzz from dual;
		        cursor e1 is select random.rand_max(20) yyy from dual;

		begin


			for c2 in c1 loop
			null;
			for d2 in d1 loop
			null;
			for e2 in e1 loop
			null;
			n := e2.yyy;
			mf:='F';
		        if mod(n,2) = 1 then mf:='M';
			end if;
			insert into pasta (cheese, tomato, salt, oregano, olive_oil) 
				values (d2.zzz, 
					substr(c2.www,1,20),       
					mf,
					substr(c2.www,21,n),
					substr(c2.www,n+21,60)
					);
			end loop;
			end loop;
			end loop;
zzz := zzz + 1;
 	 	end;
	      		i := i + 1;              -- Commit after every X records
			if i > 9 then			
        		  commit;
         		i := 0;
      			end if;
	
	  end;

        end loop;
	commit;
   end;
-------------------------------------------------------
 	end loop;
if not yyy then
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line( to_char(zzz) || ' rows successfully inserted into the pasta table!');
dbms_output.put_line('---------------------------------------------------------');
end if;
   end;
/




----------------------------------------------------------------------------------
REM ------ SAVE AS Q.SQL

@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
@@Query_load
/

----------------------------------------------------------------------------------
REM ------ SAVE AS QUERY_LOAD.SQL

set serveroutput on
DECLARE
	v sql_holder.sql_text%type;
        l_theCursor     integer default dbms_sql.open_cursor;
        l_status        integer;
        l_data          my_dbms_sql.varchar2_table;
BEGIN
	dbms_output.enable(1000000);
	select sql_text into v from sql_holder where num = (select random.rand_max(135) from dual);
	dbms_output.put_line(v);
	

      dbms_sql.parse(  l_theCursor,v,dbms_sql.native );
         my_dbms_sql.define_all( l_theCursor );
      l_status := dbms_sql.execute(l_theCursor);





      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
--            dbms_output.enable;
		l_data := my_dbms_sql.fetch_row( l_theCursor );
            for i in 1 .. l_data.count loop
              dbms_output.put( substr(l_data(i),1,10) );

			for t in 1 .. 11-length(substr(l_data(i),1,10)) loop
	              dbms_output.put(' ');
			end loop;


              end loop;
	    dbms_output.new_line;
--	dbms_output.disable;
      end loop;


DBMS_SQL.CLOSE_CURSOR(l_theCursor);
  exception
      when others then
          execute immediate( 'alter session set
                         nls_date_format=''dd-MON-yy'' ');
      raise;



  end;
/

----------------------------------------------------------------------------------
REM ------ SAVE AS PASTA_UNINSTALL.SQL
drop table pasta;
drop table sauce;
drop table stopit;
drop table SQL_holder;
pro done!;

----------------------------------------------------------------------------------
REM ------ SAVE AS START.SQL
set serveroutput on
update stopit set stop = 'N';
commit;
begin
dbms_output.put_line('----------------------------------------------------------------');
dbms_output.put_line('You may now run the @insert_load script from a seperate session.');
dbms_output.put_line('To stop the inserts on the pasta table, run the @stop script');
dbms_output.put_line('from this session.');
dbms_output.put_line('----------------------------------------------------------------');
end;
/
----------------------------------------------------------------------------------
REM ------ SAVE AS STOP.SQL
set serveroutput on
update stopit set stop = 'Y';
commit;
begin
dbms_output.put_line('---------------------------------------');
dbms_output.put_line('The @insert_load script is now stopped.');
dbms_output.put_line('---------------------------------------');
end;
/