The SQL Developer tool provides a way to develop, unit test, and handle
version control. In SQL Developer, you can set up basic frameworks for the
database objects.
## Statements generated by SQL Developer when creating new object
## Create procedure with two parameters passed in
CREATE PROCEDURE EXAMPLE_PROC1
( PARAM1 IN VARCHAR2 , PARAM2 IN NUMBER
)AS
BEGIN
NULL;
END EXAMPLE_PROC1
;
/
## Create trigger on insert
CREATE TRIGGER EXAMPLE_TRIGGER1
BEFORE INSERT ON EMP
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (DEPTNO=10)
BEGIN
NULL;
END;
/
As noted in Table 9-1, a semicolon (;) is the delimiter that marks the end
of the block. The forward slash (/) says to execute the code in SQL*Plus.
NOTE
SQL Developer and some of the other tools
have ways to execute statements with a run or
run script statement, which will run the code
without the forward slash. However, in
SQL*Plus, the

/
is required to have the code
run, like saying “Go.”
In SQL Server, you get an identity column, but Oracle doesn’t have an
identity type. Insert triggers are useful for generating IDs for primary keys.
Using a sequence, you can retrieve the next value for a unique number to
be used as an ID. You could also use a procedure for inserts to pull in the
242
Oracle Database Administration for Microsoft SQL Server DBAs
next value from the sequence without a trigger. This could be used on every
insert, as long as the application does not rely on ad hoc queries for inserts.
## Trigger for sequences and populating identity column
create sequence order_id_seq start with 1 increment 1;
create or replace trigger trg_i_orders before insert
for each row
begin
select order_id_seq.nextval
into :new.order_id from dual;
end;
/
You cannot use a role to grant permissions to the objects in a procedure
that the procedure owner is using in that code. Permissions for the objects
that are being accessed and used in the code must be explicitly granted to
the procedure owner.
Also worth mentioning is a difference in create and replace operations
with the database programming languages. In SQL Server, the object is
dropped first and then re-created, normally after first checking if the object
exists. In Oracle, there is no need to check first, because the create or
replace command will create the object if it is not there or replace the
existing object with the new code. This works for stored procedures,

packages, triggers, and views.
Packages and Package Bodies
Along with the usual objects of triggers, functions, and procedures, Oracle
also has packages and package bodies. The package is the collection of the
definitions of the procedures and functions that are found in the package
body. Variables, constants, and cursors are also declared in the package
definition, and can be used in the subprograms found in the package body.
Figure 9-1 shows an example of using SQL Developer to create the
framework for a package.
Using subprograms makes the code more modular, so it is easier to
manage changes to programs and global variables. Since the whole package
is loaded into memory, execution becomes faster; it’s not necessary to
recompile because of dependencies on other programs. Error handling can
also be defined at the package level for all of the subprograms to use, which
makes programming more consistent and avoids repeating the same steps in
several different procedures.
Chapter 9: PL/SQL
243
Here is an example of defining a package with package body, with error
handling (discussed later in this chapter) and variable declarations:
create or replace package modify_product_info
as
TYPE ProductRec is record (prod_id number,longname varchar2(50));
cursor desc_prod return ProductRec;
procedure upd_prod_name(v_prod_id in varchar2);
procedure ins_new_prod
(v_prod_id in varchar2,v_longname in varchar2);
function get_prod_id(v_prod_name in varchar2) RETURN ProductRec;
procedure raise_app_error(v_error_code in varchar2,
v_text in varchar2, v_name1 in varchar2, v_value1 in varchar2);

END modify_product_info;
/
create or replace package body modify_product_info
as
cursor desc_prod return ProductRec is
select prod_id, prod_name from orders;
244
Oracle Database Administration for Microsoft SQL Server DBAs
FIGURE 9-1.
Creating a new package in SQL Developer
procedure upd_prod_name(v_prod_id in number)
is
var_count number;
BEGIN

update products set prod_name = …

EXCEPTION
WHEN OTHERS
THEN
Rollback;
raise_app_error(v_error_code => 'UNANTICIPATED-ERROR',
v_text => 'Details…',v_name1 => 'TABLE_NAME');
END upd_prod_name;
function get_prod_id(v_prod_name in varchar2)
RETURN ProductRec
is
var_prod_id prod_id_type%TYPE;
BEGIN


select prod_id into var_prod_id
from orders
where

return var_prod_id;
EXCEPTION
WHEN NO_DATA_FOUND
raise_app_error(v_error_code => 'NO DATA…'

END get_prod_id;

## Define other procedures and functions

END modify_product_info;
This example shows the flow of the package and package body
definition. As you can see, the functions and procedures are defined just
with the input and output procedures in the package. The functions and
stored procedures are then listed again in the body of the package with the
details of the procedure and statements to be processed. If the package
header has a procedure listed, the body must contain the definition. Of
course, the code is filled in with processing, conditional statements, data
processing, and so on.
Chapter 9: PL/SQL
245
Triggers
In both SQL Server and Oracle environments, triggers are available on
logon, object changes, and data changes. Triggers can be fired for events
such as startup or shutdown and when inserts, updates, and deletes are
issued. The concept of the trigger is the same in both Oracle and SQL
Server, but there are some differences in the types available and when they

execute. Table 9-2 summarizes the types of triggers available on both
platforms.
The triggers on the database system events, such as startup, shutdown,
and server message events, can be used for auditing or changing session
parameters.
##Audit logins via sqlplus into a table
create or replace trigger
after logon on database
begin
insert into logon_from_sqlplus (user_name,logon_time)
select username,sysdate from v$session where program='sqlplus.exe';
end;
/
##Trigger to prevent dropping of objects
create or replace trigger drop_not_allowed
before drop on database
begin
RAISE_APPLICATION_ERROR(-20001,'Drop table not allowed');
end;
/
246
Oracle Database Administration for Microsoft SQL Server DBAs
SQL Server Triggers Oracle Triggers
DML triggers
After
Instead of
DML triggers
Before and after
Instead of
Statement and row

DDL triggers DDL triggers
Event triggers, system and user Event triggers, system and user
TABLE 9-2.
SQL Server and Oracle Trigger Types
##Alter the session to have a different parameter setting
create or replace trigger
after logon on database
begin
execute immediate 'alter session set optimizer_mode=FIRST_ROWS';
end;
/
The EXECUTE IMMEDIATE statement executes a SQL statement that
can’t be executed normally in a block of code, such as ALTER SESSION,
ALTER TABLE, and other object changes. Dynamic SQL statements can
also be built and then executed using the EXECUTE IMMEDIATE statement
in the code of triggers, procedures, or functions.

sql_stmt varchar2(300);
var_col varchar2(30);
begin

select column_name into var_col from user_tab_cols
where table_name=v_table and column_name=v_column;
Sql_stmt := 'update '||v_table|| ' set price = :1 where '
|| var_col || ' =:2';
execute immediate sql_stmt USING amout, column_value;

Triggers on tables that fire on update, insert, and delete offer some
different options in Oracle than in SQL Server. In SQL Server, the triggers
fire after the change, or they can do something instead of the action. Oracle

triggers have the option of executing before or after, and they can be fired
for each row that is changed or once for the whole statement. So, if a delete
is run against the table, the statement-level trigger will fire once for the
whole delete, which would be good for an audit record. The row-level
trigger is useful for inserting the data that is being changed into another
table, for example.
The BEFORE trigger is useful for validating the data and checking that the
change should be performed. Being able to execute these actions before
the change occurs could prevent rollbacks, and even disallow changes if
an incorrect role or application is attempting to make them. The BEFORE
trigger also allows for the adjustment of values or determination of values
for another column, and could help maintain referential relationships. For
use with row-level triggers, the variables :NEW and :OLD refer to the new
Chapter 9: PL/SQL
247
and existing values for the columns, respectively. With BEFORE triggers, the
old values cannot be updated, but the new values can change in the trigger
body and be the “new” new values. BEFORE triggers are used on tables;
they cannot be used on views.
AFTER and BEFORE triggers are used in combination with the statement
and row triggers, which create the four types of triggers for actions on tables.
## Trigger examples
create or replace trigger trg_u_customers
after update on customers
for each row
begin
update orders set customer_name=:new.customer_name
where custumer_namer=:old.customer_name;
end;
/

## Trigger example to combine update, insert and deletes
create or replace trigger trg_iud_customers
after insert or update or delete on customers
for each row
declare
v_order_date date;
BEGIN
v_order_date := sysdate;
if INSERTING THEN
INSERT into orders
values(order_seq.nextval, :new.customer_id,
order_details,v_order_date);
other possible inserts or code
end if;
if DELETING THEN
INSERT into customer_hist_tbl
values(:old.customer_id,:old.customer_name,
:old_cust_details);
end if;
if UPDATING ('CUSTOMER_NAME') THEN
update customer_hist_tbl set
customer_name=:old.customer_name
where customer_id=:new.customer_id;
end if;
END;
/
248
Oracle Database Administration for Microsoft SQL Server DBAs
Updates and Conditions
Before getting into the transaction part of the procedures and other useful

information about PL/SQL, let’s take a brief look at UPDATE statements,
which tend to be very different in SQL Server and Oracle. The transition
from doing SQL Server updates to handling them in Oracle is not easy. It
may take several tries to not think in SQL Server syntax and get the correct
statement for an Oracle update. Table 9-3 shows a couple of examples.
One difference is that in Oracle, you can group the columns being
updated to set more than one column equal to the SELECT statement.
Another is that instead of needing to list the table again for joins, Oracle can
use the table being updated to join against in the query. To test the SQL
Server UPDATE statement, you can just run the query after the FROM to
Chapter 9: PL/SQL
249
Update SQL Server Example Oracle Example
Update
one
column
UPDATE titles SET
ytd_sales = t.ytd_sales +
s.qty
FROM titles t, sales s
WHERE t.title_id =
s.title_id
UPDATE titles t
SET ytd_sales=
(SELECT t.ytd_sales + s.qty
FROM sales s
WHERE t.title_id=s.title_id)
Update
multiple
columns

UPDATE orders SET
Customer_id=c.customer_id,
item_id=p.item_id
FROM (SELECT c.customer_id,
p.item_id
FROM products p, customers c,
orders o
WHERE c.order_id=o.order_id
and
o.product_name=p.product_
name)
WHERE order_id=1234
UPDATE orders o SET
(customer_id, item_id)=
(SELECT c.customer_
id,p.item_id
FROM products p, customers c
WHERE c.order_id=o.order_id
and o.product_nsme=
p.product_name)
WHERE o.order_id=1234
TABLE 9-3. UPDATE
Statements in SQL Server and Oracle
know which values you are getting. To test the update in Oracle, you can
pull the update table into the query.
SQLPLUS> SELECT c.customer_id,p.item_id
FROM products p, customers c, orders o
WHERE c.order_id=o.order_id and o.product_nsme=p.product_name)
SQLPLUS> UPDATE orders o SET (customer_id, item_id) =
(select c.customer_id,p.item_id FROM products p, customers c

WHERE c.order_id=o.order_id and o.product_nsme=p.product_name)
WHERE o.order_id=1234;
It does take some practice to get used to writing the updates differently.
Other statements that select with joins translate fairly easily. Also, INSERT
and DELETE statements are similar.
Since we are looking at some of the SQL statements here before putting
them into the PL/SQL code, another function worth mentioning is DECODE.
Like CASE (which Oracle also has), DECODE is useful for conditions.
SQLPLUS> select DECODE(deptno, 10, 'Technology',20,'HR', 30,
'Accounting','General') from departments;
## start with the value and if it matches then substitute the NEXT value
## the last value is the default which is optional
Ranges and not equal values are probably easier to define in a CASE
statement, but DECODE is useful for other situations. For example, you might
use it for date or number comparisons:
SQLPLUS> select DECODE(date1-date2)-abs(date1-date2), 0,
'Date 1 is greater than Date 2,
'Otherwise Date 2 is greater than Date 1')
from list_date_table;
These examples might be useful in developing your code and writing
more effective PL/SQL.
Transactions
Transactions are a main reason for writing procedures, and planning transaction
size, commits, and rollback points are part of good procedures. Transactions
that are too big will cause issues like filling up log space or blocking in SQL
Server, and possibly fill up the undo tablespace in Oracle. Transactions that
250
Oracle Database Administration for Microsoft SQL Server DBAs
are too small can have too many commits and checkpoints, which can slow
down processing.

The starting point for a transaction is defining the blocks of code to be
executed, where to roll back or commit, and then working in this framework
to define transaction size. SQL Server has BEGIN TRAN, and then you can
COMMIT or ROLLBACK TRAN after completion of the statement.
Beginning a Transaction
Oracle has a BEGIN statement to start the transaction, which works just like
BEGIN TRAN in SQL Server. For marking a point to be able to commit or
roll back to, you use SAVEPOINT transaction_name. This will start the
transaction either in a block of code or a stored procedure, or even in a
SQL*Plus session before executing a SQL statement.
SQLPLUS> begin
insert into emp values('Mandy',10);
end;
/
PL/SQL procedure successfully completed.
SQLPLUS> select * from emp;
EMP_NAME EMP_DEPT

Mandy 10
SQLPLUS> begin
insert into emp values('Emily',20);
insert into emp values('Gabrielle',50);
savepoint savepoint_before_delete;
delete emp where emp_dept=10;
end;
/
PL/SQL procedure successfully completed.
SQLPLUS> select * from emp;
EMP_NAME EMP_DEPT


Emily 20
Gabrielle 50
SQLPLUS> rollback to savepoint_before_delete;
Rollback complete.
SQLPLUS> select * from emp;
Chapter 9: PL/SQL
251