PL/SQL

An Introduction

CS 262: DBMS Lab

 What is PL/SQL?

CS 262: DBMS Lab

 PL/SQL Blocks

• PL/SQL blocks can be divided into two groups:

1.Named and
2.Anonymous.
• Named blocks are used when creating subroutines. These subroutines are
procedures, functions, and packages.
• The subroutines can be stored in the database and referenced by their names
later on.
• Anonymous PL/SQL blocks do not have names. As a result, they cannot be
stored in the database and referenced later.

CS 262: DBMS Lab

 PL/SQL Block Structure

• PL/SQL blocks contain three sections

1.Declare section
2.Executable section and
3.Exception-handling section.

• The executable section is the only mandatory section of the block.

• Both the declaration and exception-handling sections are optional.

CS 262: DBMS Lab

 PL/SQL BLOCK STRUCTURE

• PL/SQL block has the following structure:

DECLARE
Declaration statements
BEGIN
Executable statements
EXCETION
Exception-handling statements
END ;

CS 262: DBMS Lab

 Declaration Section

• The declaration section contains definitions of PL/SQL identifiers such as

variables, constants, cursors and so on.
• Example
DECLARE
v_first_name VARCHAR2(35) ;
v_last_name VARCHAR2(35) ;
v_counter NUMBER := 0 ;

CS 262: DBMS Lab

 Executable Section

• The executable section contains executable statements that allow you to manipulate
the variables that have been declared in the declaration section.
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM student
WHERE student_id = 123 ;
DBMS_OUTPUT.PUT_LINE
(‘Student name :’ || v_first_name ||‘ ’|| v_last_name);
END;

CS 262: DBMS Lab

 Exception-Handling Section

• The exception-handling section contains statements that are executed when a

runtime error occurs within a block.
• Runtime errors occur while the program is running and cannot be detected by the
PL/SQL compiler.

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
(‘ There is no student with student id 123 ’)
END;

CS 262: DBMS Lab

 Executing a PL/SQL Program in
SQL*Plus

• Open text editor using ed in SQL*Plus

• Type the program
• Save the program with .sql extension in your user
• To execute a PL/SQL program, type the following command at the SQL prompt:
SQL> @Z:\plsql\DisplayAge.sql

CS 262: DBMS Lab

 Generating Output

• Like other programming languages, PL/SQL provides a procedure (i.e.

PUT_LINE) to allow the user to display the output on the screen. For a user to
able to view a result on the screen, two steps are required.
• First, before executing any PL/SQL program, type the following command at
the SQL prompt (Note: you need to type in this command only once for every
SQL*PLUS session):
SQL> SET SERVEROUTPUT ON;
• or put the command at the beginning of the program, right before the
declaration section.

CS 262: DBMS Lab

 Generating Output (Contd.,)

• Second, use DBMS_OUTPUT.PUT_LINE in your executable section to display

any message you want to the screen.
Syntax for displaying a message:
DBMS_OUTPUT.PUT_LINE(<string>);
• in which PUT_LINE is the procedure to generate the output on the screen, and
DBMS_OUTPUT is the package to which the PUT_LINE belongs.
DBMS_OUTPUT.PUT_LINE(‘My age is ‘ || num_age);

CS 262: DBMS Lab

 Variables

• Variables are
• Used to store numbers, character strings, dates, and other data values
• Avoid using keywords, table names and column names as variable names
• Must be declared with data type before use:
• variable_name data_type_declaration;

CS 262: DBMS Lab

 Reference Variables

• Reference variables directly reference a specific database field or record and

assume the data type of the associated field or record
%TYPE: same data type as a database field
%ROWTYPE: same data type as a database record
t_eno emp.eno%type;
t_emp emp%rowtype;

CS 262: DBMS Lab

 %ROWTYPE Example

DECLARE
d dept%ROWTYPE;
BEGIN
SELECT deptno,dname,loc INTO d FROM dept WHERE
deptno=10;
DBMS_OUTPUT.PUT_LINE(d.dname);
END;

CS 262: DBMS Lab

 Substitution Variables

• SQL*Plus allows a PL/SQL block to receive input information with the

help of substitution variables.
• Substitution variables cannot be used to output the values because no
memory is allocated for them.
• Substitution variables are usually prefixed by the ampersand(&)
character.
v_student_id NUMBER := &sv_student_id;
• When this example is executed, the user is asked to provide a value
for the student ID.

CS 262: DBMS Lab

 Assignmen
t
Statement
s

CS 262: DBMS Lab

 Decision Control Structures

CS 262: DBMS Lab

 IF Statements

Syntax
IF condition1 THEN
commands that execute if condition1 is TRUE;
ELSIF condition2 THEN
commands that execute if condition2 is TRUE;
ELSIF condition3 THEN
commands that execute if condition3 is TRUE;
...
ELSE
commands that execute if none of the
conditions are TRUE;
END IF;

CS 262: DBMS Lab

 Types of IF Statemnets

• Simple IF statement:
Set the manager ID to 22 if the employee name is Osborne.
IF v_ename = 'OSBORNE' THEN v_mgr := 22;
END IF;
Set the job title to Salesman, the department number to 35, and the
commission to 20% of the current salary if the last name is Miller
IF v_ename = 'MILLER' THEN
v_job := 'SALESMAN';
v_deptno := 35;
v_new_comm := sal * 0.20;
END IF;

CS 262: DBMS Lab

 Types of IF Statemnets

• IF-THEN-ELSE Statements
Set a flag for orders where there are fewer than five days between
order date and ship date.
IF v_shipdate - v_orderdate < 5 THEN
v_ship_flag := 'Acceptable';
ELSE
v_ship_flag := 'Unacceptable';
END IF;

CS 262: DBMS Lab

 Types of IF Statemnets

• Nested IF Statement
For a given value, calculate a percentage of that value
based on a condition.
IF v_start > 100 THEN
v_start := 2 * v_start;
ELSIF v_start >= 50 THEN
v_start := .5 * v_start;
ELSE
v_start := .1 * v_start;
END IF;

CS 262: DBMS Lab

 Null in Expressions and
Comparisons

• You can handle null values with the IS NULL operator.

• Any arithmetic expression containing a null value evaluates to
NULL.
• Concatenated expressions with null values treat null values as an
empty string.
• NULL acts as False
• The IS NULL condition evaluates to TRUE only if the variable it is
checking is NULL.

CS 262: DBMS Lab

 Loop Statements

• Loops repeat a statement or sequence of statements multiple

times.
• There are three types of loop statements:
• Basic loop
• FOR loop
• WHILE loop

CS 262: DBMS Lab

 Basic Loop

Syntax
LOOP
statement1;
...
EXIT [WHEN condition];
END LOOP;
A basic loop can contain multiple EXIT statements.

CS 262: DBMS Lab

 An Example

DECLARE
v_ordid item.ordid%TYPE := 601;
v_counter NUMBER(2) := 1;
BEGIN
LOOP
INSERT INTO item(ordid, itemid)
VALUES(v_ordid, v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 10;
END LOOP;
END;

CS 262: DBMS Lab

 FOR Loop
Syntax
FOR counter in [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
...
END LOOP;
• Do not declare the counter; it is declared implicitly.
• The lower bound and upper bound of the loop range can be literals,
variables, or expressions, but must evaluate to integers
• Do not reference the counter as the target of an assignment. An error
message rises if you do so.
CS 262: DBMS Lab
 An Example

DECLARE
v_ordid item.ordid%TYPE := 601;
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO item(ordid, itemid)
VALUES(v_ordid, i);
END LOOP;
END;

CS 262: DBMS Lab

 WHILE Loop

Syntax
WHILE condition LOOP
statement1;
statement2;
...
END LOOP;

CS 262: DBMS Lab

 An Example

DECLARE
v_count NUMBER(2) := 1;
num_depts NUMBER := &num_depts ;
BEGIN
WHILE v_count <= num_depts LOOP
INSERT INTO dept(deptno,dname)
VALUES (v_count, &v_dept_name);
v_count := v_count + 1;
END LOOP;
COMMIT;
END;

CS 262: DBMS Lab

 PL/SQL
Cursors

CS 262: DBMS Lab

 What is a Cursor?

• A cursor is a temporary work area created in the system memory when

an SQL statement is executed.
• This temporary work area is used to store the data retrieved from the
database, and manipulate this data.
• A cursor can hold more than one row, but can process only one row at
a time.
• There are two types of cursors in PL/SQL:
• Implicit cursors:
• Explicit cursors:
• Both implicit and explicit cursors have the same functionality, but they
differ in the way they are accessed.

CS 262: DBMS Lab

 Implicit cursors

• These are created by default when DML statements like, INSERT,

UPDATE, and DELETE statements are executed. They are also
created when a SELECT statement that returns just one row is
executed.
• Oracle provides few attributes called as implicit cursor attributes
to check the status of DML operations.

CS 262: DBMS Lab

 Implicit Cursor
Attributes

• %notfound Identifies whether the fetch executed

on the cursor did not return a row.
• %rowcount Identifies the number of rows that were
processed by this cursor.
• %found Identifies whether the fetch executed
on the cursor return a row.
• %isopen Identifies whether the cursor referred
to is opened and ready for use.

CS 262: DBMS Lab

 An Example

DECLARE var_rows number(5);

BEGIN
UPDATE employee SET salary = salary + 1000;
IF SQL%NOTFOUND THEN dbms_output.put_line('None of the salaries
were updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT; dbms_output.put_line('Salaries
for ' || var_rows || 'employees are updated');
END IF;
END;

CS 262: DBMS Lab

 Explicit cursors

• They must be created when you are executing a SELECT statement

that returns more than one row.

• Even though the cursor stores multiple records, only one record
can be processed at a time, which is called as current row.

• When you fetch a row the current row position moves to next
row.

CS 262: DBMS Lab

 An Example
DECLARE
myempid number;
mysal number;
CURSOR emp_crsr IS
SELECT empid, salary FROM employee;
BEGIN
OPEN emp_crsr;
LOOP
FETCH emp_crsr INTO myempid, mysal;
EXIT WHEN emp_crsr%NOTFOUND;
if myempid = 10 or myempid = 30 then
UPDATE employee SET salary = mysal + 5000 WHERE empid = myempid;
else
UPDATE emp SET salary = mysal + 1111 WHERE empid = myempid;
end if;
END LOOP;
END;

CS 262: DBMS Lab

 Exception
Handling

CS 262: DBMS Lab

 Types of Exceptions

• Named system exceptions.

• Named programmer-defined exceptions.
• Unnamed system exceptions.
• Unnamed programmer-defined exceptions.

CS 262: DBMS Lab

 Named system
exceptions

• The exceptions which are already given names by PL/SQL are

declared in the STANDARD package in PL/SQL.
• You do not have to declare them in your own programs.
Example
EXCEPTION
WHEN CURSOR_ALREADY_OPEN
THEN
CLOSE my_cursor;
END;

CS 262: DBMS Lab

 Some Predefined
Exceptions
in PL/SQL

• CURSOR_ALREADY_OPEN
• INVALID_CURSOR
• NO_DATA_FOUND
• TOO_MANY_ROWS
• ZERO_DIVIDE

CS 262: DBMS Lab

 An Example

Declare
sal emp.salary%type;
BEGIN
select salary into sal from emp where eno = 1;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘MORE THAN ONE ROW SELECTED…’ );
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘OTHER TYPE OF ERROR HAS OCCURED…’ );
END;

CS 262: DBMS Lab

 Named Programmer-
Defined
Exceptions

• Errors that are specific to the application program need to be

handled by this type of exceptions.
• For User-Defined Exception, the programmer should
• Name the Exception by declaring it in the declaration section of
PL/SQL block.
• Check for the error and raise the exception
• Handle the exception

CS 262: DBMS Lab

 An Example

Declare
negative_salary EXCEPTION;
sal emp.salary%type;
BEGIN
select salary into sal from emp where eno = 1;
if (sal < 0) then
RAISE negative_salary;
else
DBMS_OUTPUT.PUT_LINE(‘Salary =‘||sal);
end if;
EXCEPTION
WHEN negative_salary THEN
DBMS_OUTPUT.PUT_LINE(‘INVALID SALARY…’ );
UPDATE EMP SET SALARY = 0 WHERE ENO = 1;
END;

CS 262: DBMS Lab

 Unnamed System
Exceptions

• It is an internal error raised by PL/SQL or the SQL engine, which is

not given a predefined name
• It is identified only by its internal error number
• exception handlers need a name by which they can check for a
match
• Programmer can assign a name to that error that might be raised
in his program and then write a specific exception handler for that
custom-named exception.
• The pragma EXCEPTION_INIT tells the compiler to associate an
exception name with an Oracle error number.

CS 262: DBMS Lab

 An Example
DECLARE
e_MissingNull EXCEPTION;
PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);
BEGIN
INSERT INTO Employee (id) VALUES (NULL)
EXCEPTION
WHEN e_MissingNull then
DBMS_OUTPUT.put_line('ORA-1400 occurred');
DBMS_OUTPUT.put_line(‘Can not insert null for ID’);
END;

CS 262: DBMS Lab

 Unnamed Programmer-
Defined Exceptions

• procedure RAISE_APPLICATION_ERROR lets you issue user-

defined ORA- error messages from stored subprograms.
• That way, you can report errors to your application and avoid
returning unhandled exceptions.
• Syntax for calling RAISE_APPLICATION_ERROR procedure is
raise_application_error(error_number, message);
• error_number is a negative integer in the range -20000 .. -20999
and message is a character string up to 2048 bytes long.

CS 262: DBMS Lab

 An Example

DECLARE num_tables NUMBER;

BEGIN
SELECT COUNT(*) INTO num_tables FROM
USER_TABLES;
IF num_tables < 1000 THEN
raise_application_error(-20101,
'Expecting at least 1000 tables');
END IF;
END;

CS 262: DBMS Lab

 Functions &
Procedures

CS 262: DBMS Lab

 Stored Procedures

• Procedures are named PL/SQL blocks that are made up of:

• A declarative part
• An executable part, and
• An optional exception-handling part

CS 262: DBMS Lab

 Syntax for creating
a Procedure

CREATE [or REPLACE]

PROCEDURE [user name] procedurename (
Arguementname [IN / OUT / IN OUT] datatype ,
Arguementname [IN / OUT / IN OUT] datatype ,… ) [IS / AS]
variable declarations
BEGIN
PL/SQL statements
EXCEPTION
exception section statements
END;

CS 262: DBMS Lab

 An Example Procedure

PROCEDURE swapn(num_one IN OUT NUMBER,

num_two IN OUT NUMBER) IS
temp_num NUMBER;
BEGIN
temp_num := num_one;
num_one := num_two;
num_two := temp_num ;
END;

CS 262: DBMS Lab

 An Example Procedure

CREATE PROCEDURE FACT (n in number, f out number) AS

a number := 1;
b number;
BEGIN
b := n;
WHILE (b > 1) LOOP
a := a*b;
b := b-1;
END LOOP;
f := a;
END;

CS 262: DBMS Lab

 Invoking FACT procedure

DECLARE
m number := &m;
n number;
BEGIN
FACT(m,n);
dbms_output.put_line(n);
END;

CS 262: DBMS Lab

 Another Example
Procedure

PROCEDURE raise_sal(eid in number, increase in number) AS

current_sal emp.sal%type;
BEGIN
SELECT sal INTO current_sal FROM emp
WHERE empid = eid;
UPDATE emp SET sal = sal + increase
WHERE empid = eid;
END raise_sal;

CS 262: DBMS Lab

 Functions

CREATE [or REPLACE]

FUNCTION [user name] functionname (
Arguementname [IN] datatype ,
Arguementname [IN] datatype ,… ) RETURN datatype [IS / AS]
variable declarations
BEGIN
PL/SQL statements
END;

CS 262: DBMS Lab

 An Example Function

CREATE FUNCTION fact (n number) RETURN number AS

f number := 1;
a number := n;
BEGIN
WHILE (a > 1 ) LOOP
f := f * a;
a := a - 1;
END LOOP;
RETURN f;
END;

CS 262: DBMS Lab

 Invoking FACT function

DECLARE
m number := &m;
n number;
BEGIN
n := FACT(m);
dbms_output.put_line(n);
END;

CS 262: DBMS Lab

CS 262: DBMS Lab CS/IT 352: RDBMS Lab
 Triggers

CS 262: DBMS Lab

CS/IT 352: RDBMS Lab
 Database Triggers

• Triggers are procedures that run implicitly when an INSERT, UPDATE, or DELETE
statement is issued against the associated table or, in some cases, against a view, or
when database system actions occur.
• A trigger has three basic parts:
• A triggering event or statement
• It is an SQL statement or event that causes a trigger to fire.
• A trigger restriction
• A trigger restriction specifies a Boolean expression that must be true for the
trigger to fire.
• A trigger action
• A trigger action is the procedure that contains the SQL statements and code to
be run when the trigger fires.

CS 262: DBMS Lab

 Types of Triggers

• ROW TRIGGERS and STATEMENT TRIGGERS

• BEFORE and AFTER Triggers

CS 262: DBMS Lab

 Basic Trigger Syntax

•CREATE [OR REPLACE] TRIGGER <trigger_name>

•{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
•[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]

•[FOR EACH ROW]

•WHEN (<trigger_condition>)]]
•<trigger_body>

CS 262: DBMS Lab

 Operations On
Triggers

• Displaying Trigger Definition Errors

show errors trigger <trigger_name>;
• Viewing Defined Triggers
select trigger_name from user_triggers;
• Disabling Triggers
alter trigger <trigger_name> {disable|enable};

CS 262: DBMS Lab

 Trigger Example

CREATE TABLE T4 (a INTEGER, b CHAR(10));

CREATE TABLE T5 (c CHAR(10), d INTEGER);

CREATE TRIGGER trig1

AFTER INSERT ON T4 REFERENCING NEW AS newRow
FOR EACH ROW WHEN (newRow.a <= 10)
BEGIN
INSERT INTO T5 VALUES(:newRow.b, :newRow.a);
END trig1;

CS 262: DBMS Lab

 Another Trigger Example

create table Person (age int);

CREATE TRIGGER PersonCheckAge

AFTER INSERT OR UPDATE OF age ON Person
FOR EACH ROW
BEGIN
IF (:new.age < 0) THEN
RAISE_APPLICATION_ERROR(-20000, 'no negative age allowed');
END IF;
END;

CS 262: DBMS Lab

 Output Of Executing
Previous Example

If we attempted to execute the insertion:

insert into Person values (-3);
we would get the error message:
ERROR at line 1:
ORA-20000: no negative age allowed
ORA-06512: at "MYNAME.PERSONCHECKAGE", line 3
ORA-04088: error during execution of trigger
'MYNAME.PERSONCHECKAGE'
and nothing would be inserted. In general, the effects of both the trigger
and the triggering statement are rolled back.

CS 262: DBMS Lab

 Yet Another Example
Trigger

create table emp(eno number, name char(12), dno number,sal number);

create table auditemp(eno number, name char(12), dno number,sal number,d date);

create trigger emptri after update or delete on emp

for each row

declare

op varchar2(15); id number; name char(12); sal number; dno number;

begin

if updating then op := 'update';

end if;

if deleting then op := 'delete';

end if;

id := :old.eno;

name := :old.ename;

sal := :old.sal;

dno := :old.deptno;

insert into auditemp values(id,name,sal,dno,op,sysdate);

end;

CS 262: DBMS Lab

 Packages

CS 262: DBMS Lab

CS/IT 352: RDBMS Lab
 • PL/SQL packages:

PL/SQL • Group logically related components:

• PL/SQL types

Package • Variables, data structures, and

exceptions
• Subprograms: Procedures and
s: functions
• Consist of two parts:

Overvie • A specification
• A body

w • Enable the Oracle server to read

multiple objects into memory at
once

CS 262: DBMS Lab

 Components of a PL/SQL Package
Package
specification variable
Public
Procedure A declaration;

variable

Procedure B definition …

Procedure A definition Private

variable
Package BEGIN
body …
END;

CS 262: DBMS Lab

 Visibility of Package Components
Package
specification public_var

Procedure A;
External
code

private_var
Procedure B IS
BEGIN … END;
Procedure A IS
local_var
Package BEGIN
body …
END;

CS 262: DBMS Lab

 CREATE [OR REPLACE] PACKAGE package_name IS|AS
public type and variable declarations
subprogram specifications
END [package_name];

Creating
the • The OR REPLACE option drops and
re-creates the package
Package specification.
• Variables declared in the package
Specificati specification are initialized to NULL
by default.
on • All the constructs declared in a
package specification are visible to
users who are granted privileges on
the package.

CS 262: DBMS Lab

 CREATE OR REPLACE PACKAGE comm_pkg IS
std_comm NUMBER := 0.10; --initialized to 0.10
PROCEDURE reset_comm(new_comm NUMBER);

Example
END comm_pkg;
/
of
Package
Specificati • STD_COMM is a global variable
initialized to 0.10.
on: • RESET_COMM is a public
procedure used to reset the
comm_pkg standard commission based on
some business rules. It is
implemented in the package
body.

CS 262: DBMS Lab

 Creating the Package Body

• Syntax:
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
private type and variable declarations
subprogram bodies
[BEGIN initialization statements]
END [package_name];
• The OR REPLACE option drops and re-creates the package body.
• Identifiers defined in the package body are private and not visible
outside the package body.
• All private constructs must be declared before they are referenced.
• Public constructs are visible to the package body.

CS 262: DBMS Lab

 Example of Package Body: comm_pkg

CREATE OR REPLACE PACKAGE BODY comm_pkg IS

FUNCTION validate(comm NUMBER) RETURN BOOLEAN IS
max_comm employees.commission_pct%type;
BEGIN
SELECT MAX(commission_pct) INTO max_comm
FROM employees;
RETURN (comm BETWEEN 0.0 AND max_comm);
END validate;
PROCEDURE reset_comm (new_comm NUMBER) IS BEGIN
IF validate(new_comm) THEN
std_comm := new_comm; -- reset public var
ELSE RAISE_APPLICATION_ERROR(
-20210, 'Bad Commission');
END IF;
END reset_comm;
END comm_pkg;
CS 262: DBMS Lab
 Invoking Package Subprograms

• Invoke a function within the same package:

CREATE OR REPLACE PACKAGE BODY comm_pkg IS ...
PROCEDURE reset_comm(new_comm NUMBER) IS
BEGIN
IF validate(new_comm) THEN
std_comm := new_comm;
ELSE ...
END IF;
Invoke
•END a package procedure from iSQL*Plus:
reset_comm;
END comm_pkg;
• Invoke a package procedure in a different schema:

EXECUTE comm_pkg.reset_comm(0.15)

EXECUTE scott.comm_pkg.reset_comm(0.15)
CS 262: DBMS Lab
 Creating and Using Bodiless
Packages
CREATE OR REPLACE PACKAGE global_consts IS
mile_2_kilo CONSTANT NUMBER := 1.6093;
kilo_2_mile CONSTANT NUMBER := 0.6214;
yard_2_meter CONSTANT NUMBER := 0.9144;
meter_2_yard CONSTANT NUMBER := 1.0936;
END global_consts;

BEGIN DBMS_OUTPUT.PUT_LINE('20 miles = ' ||

20 * global_consts.mile_2_kilo || ' km');
END;
CREATE FUNCTION mtr2yrd(m NUMBER) RETURN NUMBER IS
BEGIN
RETURN (m * global_consts.meter_2_yard);
END mtr2yrd;
/
EXECUTE DBMS_OUTPUT.PUT_LINE(mtr2yrd(1))

CS 262: DBMS Lab

 Removing Packages

• To remove the package specification and the body, use the following
syntax:
DROP PACKAGE package_name;
• To remove the package body, use the following syntax:

DROP PACKAGE BODY package_name;

CS 262: DBMS Lab

 Viewing Packages in the Data
Dictionary
• The source code for PL/SQL packages is maintained and is
viewable through the USER_SOURCE and ALL_SOURCE tables
in the data dictionary.
• To view the package specification, use:

SELECT text
FROM user_source
WHERE name
• To view = 'COMM_PKG'
the package body, use: AND type = 'PACKAGE';

SELECT text
FROM user_source
WHERE name = 'COMM_PKG' AND type = 'PACKAGE BODY';

CS 262: DBMS Lab

CS 262: DBMS Lab