PL/SQL User's Guide and Reference

Running the Samples

The sample programs in this appendix and many others throughout this Guide are available online. Therefore, they are preceded by the following comment:

-- available online in file <filename>

The list below gives their locations in this Guide and the names of the corresponding online files. However, the exact name and storage location of an online file are system dependent.

Location in Guide Online File
Chapter 1, EXAMP1
Chapter 1, EXAMP2
Chapter 1, EXAMP3
Chapter 2, EXAMP4
Chapter 5, EXAMP5
Chapter 5, EXAMP6
Chapter 5, EXAMP7
Chapter 5, EXAMP8
Chapter 9, EXAMP9
Chapter 9, EXAMP10
Chapter 10, EXAMP11
Chapter 10, EXAMP12
Chapter 10, EXAMP13
Chapter 10, EXAMP14
Appendix B, SAMPLE1
Appendix B, SAMPLE2
Appendix B, SAMPLE3
Appendix B, SAMPLE4
Appendix B, SAMPLE5
Appendix B, SAMPLE6

Some samples are run interactively from SQL*Plus; others are run from Pro*C programs. You can experiment with the samples from any Oracle account. However, the Pro*C examples expect you to use the SCOTT/TIGER account.

Before trying the samples, you must create some database tables, then load the tables with data. You do that by running two SQL*Plus scripts, EXAMPBLD and EXAMPLOD, supplied with PL/SQL. These scripts can be found in the PL/SQL installation library. Check the Oracle installation or user's guide for your system.

Creating the Tables

Below is a listing of the SQL*Plus script EXAMPBLD. The CREATE statements in this script build the database tables processed by the sample programs. To run the script, invoke SQL*Plus, then issue the following command:

SQL> START EXAMPBLD

EXAMPBLD Script

set compatibility V6

/
drop table accounts
/
create table accounts(
   account_id  number(4) not null,
   bal         number(11,2))
/
create unique index accounts_index on accounts (account_id)
/
drop table action
/
create table action(
   account_id  number(4) not null,
   oper_type   char(1) not null,
   new_value   number(11,2),
   status      char(45),
   time_tag    date not null)
/
drop table bins
/
create table bins(
   bin_num     number(2) not null,
   part_num    number(4),
   amt_in_bin  number(4))
/
drop table data_table
/

create table data_table(
   exper_num  number(2),
   n1         number(5),
   n2         number(5),
   n3         number(5))
/
drop table emp
/
create table emp(
   empno      number(4) not null,
   ename      char(10),
   job        char(9),
   mgr        number(4),
   hiredate   date,
   sal        number(7,2),
   comm       number(7,2),
   deptno     number(2))
/
drop table inventory
/
create table inventory(
   prod_id     number(5) not null,
   product     char(15),
   quantity    number(5))
/
drop table journal
/
create table journal(
   account_id   number(4) not null,
   action       char(45) not null,
   amount       number(11,2),
   date_tag     date not null)
/
drop table num1_tab
/
create table num1_tab(
   sequence   number(3) not null,
   num        number(4))
/
drop table num2_tab
/

create table num2_tab(
   sequence   number(3) not null,
   num        number(4))
/
drop table purchase_record
/
create table purchase_record(
   mesg        char(45),
   purch_date  date)
/
drop table ratio
/
create table ratio(
   sample_id  number(3) not null,
   ratio      number)
/
drop table result_table
/
create table result_table(
   sample_id  number(3) not null,
   x          number,
   y          number)
/
drop table sum_tab
/
create table sum_tab(
   sequence   number(3) not null,
   sum        number(5))
/
drop table temp
/
create table temp(
   num_col1   number(9,4),
   num_col2   number(9,4),
   char_col   char(55))
/
create or replace package personnel as
   type charArrayTyp is table of varchar2(10)
      index by binary_integer;
   type numArrayTyp is table of float
      index by binary_integer;

   procedure get_employees(
      dept_number in     integer,
      batch_size  in     integer,
      found       in out integer,
      done_fetch  out    integer,
      emp_name    out    charArrayTyp,
      job-title   out    charArrayTyp,
      salary      out    numArrayTyp);
end personnel;
/
create or replace package body personnel as
   cursor get_emp (dept_number integer) is
      select ename, job, sal from emp
         where deptno = dept_number;
   procedure get_employees(
      dept_number in     integer,
      batch_size  in     integer,
      found       in out integer,
      done_fetch  out    integer,
      emp_name    out    charArrayTyp,
      job_title   out    charArrayTyp,
      salary      out    numArrayTyp) is
   begin
      if not get_emp%isopen then
         open get_emp(dept_number);
      end if;
      done_fetch := 0;
      found := 0;
      for i in 1..batch_size loop
         fetch get_emp into emp_name(i),
            job_title(i), salary(i);
         if get_emp%notfound then
            close get_emp;
            done_fetch := 1;
            exit;
         else
            found := found + 1;
         end if;
      end loop;
   end get_employees;
end personnel;
/

Loading the Data

Below is a listing of the SQL*Plus script EXAMPLOD. The INSERT statements in this script load (or reload) the database tables processed by the sample programs. To run the script, invoke SQL*Plus in the same Oracle account from which you ran EXAMPBLD, then issue the following command:

SQL> START EXAMPLOD

EXAMPLOD Script

delete from accounts

/
insert into accounts values (1,1000.00)
/
insert into accounts values (2,2000.00)
/
insert into accounts values (3,1500.00)
/
insert into accounts values (4,6500.00)
/
insert into accounts values (5,500.00)
/
delete from action
/
insert into action values
   (3,'u',599,null,sysdate)
/
insert into action values
   (6,'i',20099,null,sysdate)
/
insert into action values
   (5,'d',null,null,sysdate)
/
insert into action values
   (7,'u',1599,null,sysdate)
/
insert into action values
   (1,'i',399,null,sysdate)
/
insert into action values
   (9,'d',null,null,sysdate)
/
insert into action values
   (10,'x',null,null,sysdate)
/
delete from bins
/

insert into bins values (1, 5469, 650)
/
insert into bins values (2, 7243, 450)
/
insert into bins values (3, 5469, 120)
/
insert into bins values (4, 5469, 300)
/
insert into bins values (5, 6085, 415)
/
insert into bins values (6, 5469, 280)
/
insert into bins values (7, 8159, 619)
/
delete from data_table
/
insert into data_table values
   (1, 10, 167, 17)
/
insert into data_table values
   (1, 16, 223, 35)
/
insert into data_table values
   (2, 34, 547, 2)
/
insert into data_table values
   (3, 23, 318, 11)
/
insert into data_table values
   (1, 17, 266, 15)
/
insert into data_table values
   (1, 20, 117, 9)
/
delete from emp
/
insert into emp values
   (7369,'SMITH','CLERK',7902,TO_DATE('12-17-80','MM-DD-YY'),
    800,NULL,20)
/
insert into emp values
   (7499,'ALLEN','SALESMAN',7698,TO_DATE('02-20-81','MM-DD-YY'),
    1600,300,30)
/
insert into emp values
   (7521,'WARD','SALESMAN',7698,TO_DATE('02-22-81','MM-DD-YY'),
    1250,500,30)
/

insert into emp values
   (7566,'JONES','MANAGER',7839,TO_DATE('04-02-81','MM-DD-YY'),
    2975,NULL,20)
/
insert into emp values
(7654,'MARTIN','SALESMAN',7698,TO_DATE('09-28-81','MM-DD-YY'),
    1250,1400,30)
/
insert into emp values
   (7698,'BLAKE','MANAGER',7839,TO_DATE('05-1-81','MM-DD-YY'),
    2850,NULL,30)
/
insert into emp values
   (7782,'CLARK','MANAGER',7839,TO_DATE('06-9-81','MM-DD-YY'),
    2450,NULL,10)
/
insert into emp values
   (7788,'SCOTT','ANALYST',7566,SYSDATE-85,3000,NULL,20)
/
insert into emp values
   (7839,'KING','PRESIDENT',NULL,TO_DATE('11-17-81','MM-DD-YY'),
    5000,NULL,10)
/
insert into emp values
   (7844,'TURNER','SALESMAN',7698,TO_DATE('09-8-81','MM-DD-YY'),
    1500,0,30)
/
insert into emp values
   (7876,'ADAMS','CLERK',7788,SYSDATE-51,1100,NULL,20)
/
insert into emp values
   (7900,'JAMES','CLERK',7698,TO_DATE('12-3-81','MM-DD-YY'),
    950,NULL,30)
/
insert into emp values
   (7902,'FORD','ANALYST',7566,TO_DATE('12-3-81','MM-DD-YY'),
    3000,NULL,20)
/
insert into emp values
   (7934,'MILLER','CLERK',7782,TO_DATE('01-23-82','MM-DD-YY'),
    1300,NULL,10)
/
delete from inventory
/
insert into inventory values
   ('TENNIS RACKET', 3)
/

insert into inventory values
   ('GOLF CLUB', 4)
/
insert into inventory values
   ('SOCCER BALL', 2)
/
delete from journal
/
delete from num1_tab
/
insert into num1_tab values (1, 5)
/
insert into num1_tab values (2, 7)
/
insert into num1_tab values (3, 4)
/
insert into num1_tab values (4, 9)
/
delete from num2_tab
/
insert into num2_tab values (1, 15)
/
insert into num2_tab values (2, 19)
/
insert into num2_tab values (3, 27)
/
delete from purchase_record
/
delete from ratio
/
delete from result_table
/
insert into result_table values (130, 70, 87)
/
insert into result_table values (131, 77, 194)
/
insert into result_table values (132, 73, 0)
/
insert into result_table values (133, 81, 98)
/
delete from sum_tab
/
delete from temp
/
commit

*Location in Guide*	*Online File*
Chapter 1,	EXAMP1
Chapter 1,	EXAMP2
Chapter 1,	EXAMP3
Chapter 2,	EXAMP4
Chapter 5,	EXAMP5
Chapter 5,	EXAMP6
Chapter 5,	EXAMP7
Chapter 5,	EXAMP8
Chapter 9,	EXAMP9
Chapter 9,	EXAMP10
Chapter 10,	EXAMP11
Chapter 10,	EXAMP12
Chapter 10,	EXAMP13
Chapter 10,	EXAMP14
Appendix B,	SAMPLE1
Appendix B,	SAMPLE2
Appendix B,	SAMPLE3
Appendix B,	SAMPLE4
Appendix B,	SAMPLE5
Appendix B,	SAMPLE6