|
Questions and Answers:
Q: What is PL/SQL?
A:
PL/SQL is a language that was
provided by Oracle.
Q: Where can you store a
PL/SQL procedure?
A:
It can be stored on the
Oracle database (server side) or in a
library that was created in the users� PC
(client side).
Q: What is the PL/SQL body
section?
A:
It is a section that executes
PL/SQL statements.
Q: What is the PL/SQL
declaration section?
A:
It is a section that you can
declare the stored procedure�s variables.
Q: What does the SET
SERVEROUTPUT command?
A:
We use the SET SERVEROUTPUT
command to display the content of the Oracle
buffer into our screen.
Q: What does the
DBMS_OUTPUT.PUT_LINE procedure?
A:
The DBMS_OUTPUT.PUT_LINE
procedure writes the passing string into the
Oracle buffer.
Q: How do you define a
variable or variables in the PL/SQL
declaration section?
A:
SQL> DECLARE
v_dname VARCHAR2(14);
BEGIN
v_dname := 'HR';
dbms_output.put_line(v_dname);
END;
/
Q: How do you save a PL/SQL
block in the client environment?
A:
There are so many ways. But
one way is:
SQL> SAVE c:_myblock.sql
Q: How do you use the %TYPE
keyword?
A:
v_dname dept.dname%TYPE;
We use the %type keyword, to declare the
v_dname variable as the same datatype and
size of the department name column of the
dept table.
Q: How do you open a saved
PL/SQL block?
A:
SQL> GET c:_myblock.sql
Q: How do you run a saved
PL/SQL block?
A:
SQL> run c:_myblock.sql
Q: What does the %ROWTYPE
keyword in the PL/SQL language?
A:
The %ROWTYPE keyword creates
a composite datatype in which all the
columns of a row are pieced together into a
record.
Q: What is an implicit cursor
in the PL/SQL language?
A:
If we define our cursor in
the PL/SQL body, it will be called an
implicit cursor.
Q: An implicit cursor must
have _________ on its SELECT SQL statement?
A:
INTO
Q: What does the SQL%NOTFOUND
reserved PL/SQL word?
A:
The SQL%NOTFOUND reserved
word returns the FALSE value if there are
records to read from cursor and returns the
TRUE value if there are not record exist to
read from cursor.
Q: What does the �SET
SERVEROUTPUT ON?�
A:
It displays the Oracle buffer
used by the DBMS_OUTPUT package.
Q: Write a PL/SQL block, to
output the "Hello iSelfSchooling" message.
A:
SQL> BEGIN
dbms_output.put_line('Hello
iselfschooling');
END;
/
Q: Use the %TYPE keyword, to
declare a variable as the same datatype and
size of the department name column of the
dept table.
A:
DECLARE
v_dname dept.dname%TYPE;
BEGIN
v_dname := 'HR';
dbms_output.put_line(v_dname);
END;
/
Q: Use the implicit cursor to
query the department table information where
deptno is 30. Check, if no record was found
then print �Record was not found.� Else
print the department name only.
A:
declare
v_drec dept% rowtype;
begin
select
* into v_drec
from dept
where deptno = 30;
if sql%notfound then
dbms_output.put_line('Record
was not found.');
else
dbsm_output.put_line(v_drec.dname);
end if;
end;
/
Q: Describe that why do we
need to use a solid naming convention in our
PL/SQL program.
A:
Easy to read.
Q: What is the explicit
cursor in the PL/SQL language?
A :
It is a cursor that was declared in the
PL/SQL declaration section and returns more
than one records.
Q: What are the differences
between the explicit and implicit cursors?
A:
1- Explicit cursor
will be defined in the declaration
section but implicit cursor will be
defined in the execution or body
section.
2- The implicit
cursor must have INTO clause in its
SQL statement.
3- The explicit
cursor can return more than one
record but the implicit cursor
should only return one and only one
record.
Q: Where do you declare an
explicit cursor in the PL/SQL language?
A:
In the PL/SQL declaration
section.
Q: Where do you declare an
implicit cursor in the PL/SQL language?
A:
In the PL/SQL body section.
Q: What is a simple loop in
the PL/SQL language?
A:
It is a loop that we must
program our exit from the loop. We must make
sure that we don�t get in an infinite loop.
Q: How do you open an
explicit cursor in the PL/SQL language?
A:
OPEN
cursor_name;
Q: What does the FETCH
statement in the Oracle PL/SQL language?
A:
It reads one record at a
time.
Q: How do you terminate from
a simple loop in the PL/SQL language?
A:
EXIT WHEN condition2exit;
Q: How do you OPEN or CLOSE a
cursor in the PL/SQL language?
A:
OPEN
cursor_name;
CLOSE
cursor_name;
Q: Declare a cursor to list
the department name (dname), total number of
employees (ttemp), total salary (ttsal), and
average salary (avsal) for each department
from the department table and employee table
order by the department name.
Write all department name
with their total number of employees for
each department using the notepad editor.
For example: ACCOUNTING has 3
employees.
(Note: Don�t use the ttemp,
ttsal, and avsal item at this time)
A:
DECLARE
-- Declare a variable for a
cursor.
TYPE t_ds IS
RECORD (
dname
dept.dname%type,
ttemp number(3),
ttsal
number(8,2),
avsal
number(8,2));
-- define department
statistics
CURSOR c_ds is
select dname, count (*)
ttemp,
sum(sal)
ttsal, avg(sal) avsal
from dept d, emp e
where d.deptno = e.deptno
group by
dname
order by 1;
-- define a
variable for cursor
v_ds t_ds;
BEGIN
-- open the
cursor
OPEN c_ds;
-- start loop
LOOP
--read a record
FETCH c_ds INTO
v_ds;
-- exit from loop
EXIT WHEN c_ds%notfound;
-- list dept. name
dbms_output.put_line
(v_ds.dname ||
� has
� || v_ds.ttemp || �
employees.�);
-- end the loop
END LOOP;
CLOSE c_ds;
END;
/
Q: What does the FOR LOOP
statement in the PL/SQL language?
A:
It is a loop statement.
Q: What are the differences
between a SIMPLE LOOP and FOR LOOP?
A:
We don�t need to use the OPEN, CLOSE, FETCH,
and EXIT PL/SQL statements, and also to
declare a cursor variable since the �FOR
LOOP� statement does them implicitly.
Q: What are the advantages of
using the FOR LOOP statement?
A:
It is very simple to write.
Q: What does the SHOW ERRORS
statement in the PL/SQL language?
A:
It displays the last existing
PL/SQL errors that was compiled.
Q: What is the IF-THEN-ELSE
statement?
A:
It is an example of
controlling process flow.
Q: Modify the previous PL/SQL
block and use the �FOR LOOP� statement vs
the simple �LOOP� statement. Also, list only
the department name that their total number
of employees is more than 4.
A:
DECLARE
CURSOR c_ds IS
SELECT dname, count (*)
ttemp,
sum(sal) ttsal,
avg(sal) avsal
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY
dname ORDER BY 1;
BEGIN
-- for loop to read cursor
record.
FOR v_ds IN c_ds LOOP
IF v_ds.ttemp > 4
THEN
DBMS_OUTPUT.PUT_LINE
(v_ds.dname || � has � ||
v_ds.ttemp || � employees.�);
END IF;
END LOOP;
END;
/
Q: Create a table named
"dept_stat". The table should have four
columns: department name (dname), total
number of employees (total_empno), total
salary of employees (total_sal), and average
salary of employees (avg_sal). And the
department name should be a primary key. The
following are its columns, datatypes and
index constraint:
dname VARCHAR2(20) primary
key
total_empno NUMBER(3)
total_sal NUMBER (8,2)
avg_sal NUMBER (8,2)
A:
SQL> CREATE TABLE dept_stat
(dname VARCHAR2(20) primary
key,
total_empno NUMBER(3),
total_sal NUMBER (8,2),
avg_sal NUMBER (8,2));
Q: Write a PL/SQL block to
populate the department table statistics
into the �dept_stat� table.
Statistics Information:
The Department
Number,
The total number of
employees in each department,
The total salary paid
in each department, and
The average salary
paid in each department.
A:
DECLARE
-- define department
statistics
cursor c_ds is
SELECT dname, count (*)
ttemp,
SUM(sal) ttsal, AVG(sal)
avsal
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY dname;
BEGIN
-- loop to read cursor
record.
FOR v_ds IN c_ds LOOP
-- insert into dept_stat
insert into dept_stat
values (v_ds.dname,
v_ds.ttemp,
v_ds.ttsal,
v_ds.avsal);
END LOOP;
-- save the insert
transaction.
commit;
END;
/
Q: What is the cursor
parameter in the PL/SQL language?
A:
It is a parameter that we
pass to a cursor.
Q: Where do you define a
cursor parameter in the PL/SQL language?
A:
In the PL/SQL CURSOR
statement in the PL/SQL declaration section.
Q: Write a PL/SQL block to
populate the department table statistics
into the �dept_stat� table for a specific
department.
Statistics Information:
The Department
Number,
The total number of
employees in each department,
The total salary paid
in each department, and
The average salary
paid in each department.
A:
>> DECLARE
-- define department
statistics
cursor c_ds ( p_deptno
dept.deptno%TYPE)
is
select dname, count (*) ttemp,
sum(sal)
ttsal, avg(sal) avsal
from dept d, emp e
where d.deptno = e.deptno
and d.deptno = p_deptno
group by
dname;
-- define deptno
variable
v_deptno
NUMBER(2);
BEGIN
-- assign deptno 10
v_deptno := 10;
-- loop to read cursor
record.
for v_ds in c_ds (v_deptno)
loop
-- insert into dept_stat
insert into dept_stat
values (v_ds.dname,
v_ds.ttemp,
v_ds.ttsal,
v_ds.avsal);
end loop;
-- save the insert
transaction.
COMMIT;
END;
/
Q: What is the EXCEPTION
section in the PL/SQL language?
A:
The PL/SQL EXCEPTION section
is a place that handles your errors that
occurs in the execution time.
Q: What do you use the
EXCEPTION section for?
A:
For stored procedure�s error
handling.
Q: What would be happen if
you don�t define your exception in the
PL/SQL procedure?
A:
If there is an execution
error, then it will crash. It crashes since
the program didn�t know how to handle the
errors.
Q: What is an Oracle Defined
EXCEPTION?
A:
They are those exceptions
that were defined by Oracle.
Q: What is a User Defined
EXCEPTION?
A:
They are those exceptions
that were defined by developers.
Q: What are the differences
between a User Defined and an Oracle defined
exceptions?
A:
The user defined exception
needs to be declared and also checked in the
PL/SQL body section.
Q: Modify the previous PL/SQL
block--last assignment in the previous
hands-on practice--to add a user defined
exception, to check the total number of
employees in a department. Check if the
total number of employees less than 10 then
the procedure raises an exception and print
a message - �We need more good employees.�
A:
>> DECLARE
-- define department
statistics
CURSOR c_ds (p_deptno
dept.deptno%type) IS
SELECT dname, count (*)
ttemp,
sum(sal)
ttsal, avg(sal) avsal
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY
dname;
-- define deptno
variable
v_deptno
NUMBER(2);
not_enough_emp
EXCEPTION;
BEGIN
-- assign deptno 10
v_deptno := 10;
-- loop to read cursor
record.
FOR v_ds in c_ds (v_deptno)
LOOP
IF v_ds.ttemp < 10 THEN
raise not_enough_emp;
END IF;
-- insert into dept_stat
INSERT INTO dept_stat
VALUES (v_ds.dname,
v_ds.ttemp,
v_ds.ttsal,
v_ds.avsal);
END LOOP;
-- save the insert
transaction.
COMMIT;
EXCEPTION
-- example of
user define exception
WHEN
not_enough_emp THEN
dbms_output.put_line(�We need
more employees�);
-- check deptno
WHEN invalid_number THEN
dbms_output.put_line(�Invalid
deptno: � || v_deptno);
WHEN others THEN
dbsm_output.put_line(�Other
problem.�);
END;
/
Q: How do you write a PL/SQL
language using NOTEPAD?
A:
I just open NOTEPAD, write my
PL/SQL program, and then save it.
Q: Create a table to keep
your customer�s portfolio statistics and
name it CUST_STAT. You should populate into
this table a customer last name, his/her
traded date, and total stock market value
for the traded date.
See the following columns and
datatypes:
customer_lname VARCHAR2(20)
trade_date DATE
portfolio_value NUMBER(8,2)
A:
SQL> CREATE TABLE cust_stat
(customer_lname VARCHAR2(20),
trade_date DATE,
portfolio_value NUMBER(8,2));
Q: Write a stored procedure
to populate the customer statistics table.
Declare a cursor to query all the customer
last names, the traded date, and the total
stock market value for the traded date. Use
a sub-query with a MAX (trade_date) function
to guarantee the current stock market value
for the traded date. In the PL/SQL body, use
the �FOR LOOP� statement to read the cursor
information one record at a time. Then
insert the summary statistics data into the
customer statistics table. Use �commit� to
save the transaction. In the exception
section, add the �no data found� exception
and use the �dbms_output� package to display
the error message. Add the �invalid number�
exception to detect any invalid input data
into the insert command. Add the �Others�
exception to detect other problems. Always
use the �others� exception in case you miss
some other exceptions.
A:
create or replace procedure
cust_stat_proc
IS
-- define cursor
CURSOR c_cs IS
SELECT last_name, trade_date,
sum(shares_owned*current_price)
portfolio_value
FROM customers, portfolio,
stocks s
WHERE id = customer_id AND
stock_symbol = symbol
AND trade_date = (SELECT
max(trade_date) FROM stocks
WHERE symbol = s.symbol)
GROUP BY last_name,
trade_date;
BEGIN
FOR v_cs in c_cs LOOP
- insert into cust_stat
INSERT INTO cust_stat
VALUES
(v_cs.last_name, v_cs.trade_date,
v_cs.portfolio_value);
-- save the insert
transaction.
COMMIT;
END LOOP;
EXCEPTION
-- no data found
WHEN no_data_found THEN
dbms_output.put_line(�No data
found.�);
WHEN invalie_number THEN
dbsm_output.put_line(�Invalid
number�);
WHEN others THEN
dbsm_output.put_line(�Other
problem.�);
END;
/
Q: Then run your created
procedure.
A:
SQL> EXECUTE cust_stat;
Q: Verify that your table was
populated.
A:
SQL> SELECT * FROM cust_stat;
Q: What is the Procedure
Builder Tool?
A:
The procedure Builder tool is
a software utility that helps developers to
write, debug, save, and test their PL/SQL
programs.
Q: What is the listener in
the Oracle database?
A:
A listener is an Oracle agent that monitors
a specific port. It is a gateway of
communication between clients and Oracle
server.
Q: How do you start or stop
your listener?
A:
On NT, that will be done
automatically.
On UNIX, just type: $ lsnrctl
start - to start and
$ lsnrctl stop -- to stop
Q: What is the Object
Navigator in the Procedure Builder tool?
A:
The Object Navigator window
is a place that a developer can browse and
navigate all its created objects.
Q: How to you open a database
using the Procedure Builder tool?
A:
Select the �connect� option
in the File menu.
Q: What is a user�s schema?
A:
We have user�s schema if the
user owns objects. No objects no schema.
Q: What type of objects can
you have under a schema?
A:
Tables, Indexes, Procedures,
Packages, Functions, Synonyms, etc.
Q: How do you create a
procedure using the Procedure Builder Tool?
A:
In the �Object Navigator�
window, highlight "Program Units� and click
on the green �+� sign which is the �Create�
icon.
Q: What is a Program Unit?
A:
It is a stored procedure such
as procedure, function, package body, and
package specification.
Q: Write a PL/SQL stored
procedure to add a record into the
department table (dept). You use three input
parameters to pass the department's columns
(Department number �DEPTNO,� department name
�DNAME,� and department location �LOC�); and
use one output parameter to check the status
of the insert transaction. You should use
the Procedure Builder.
Note that you should use the
"p_" prefix to name the parameters. You use
this parameter as an output parameter to
check the status of your transaction. Use
comments in your programs. Use double dashes
for a single line comment. And use �/*�
ended with �*/� for a multiple lines
comment. In the �EXCEPITON� section, define
the exception. Use the �duplicate value on
index� exception, the �invalid number�
exception, and the �OTHERS� exception. Use
the others in case you are missing other
exceptions.
A:
PROCEDURE add_dept
(p_deptno IN
dept.deptno%TYPE,
p_dname IN dept.dname%TYPE,
p_loc IN dept.loc%TYPE,
p_status OUT VARCHAR2)
IS
-- No variable
BEGIN
/* This program add dept.
record. */
INSERT INTO dept
VALUES (p_deptno, p_dname,
p_loc);
--- Save record.
COMMIT;
-- Added successfully if the
get to this line.
p_status := �OK�;
EXCEPTION
-- Check for an Unique or
Primary Key
WHEN dup_val_on_index THEN
p_status := �DUPLICATE
RECORD�;
-- Check for invalid input
data
WHEN invalid_number THEN
p_status := �INVALID INPUT
DATA�;
-- Check for any other
problems
WHEN others THEN
p_status := �CHECK THIS WE
HAVE UNKNOWN PROBLEM.�;
END add_dept;
/
Q: Write a stored procedure
to test the �add_department� procedure.
Declare a status variable and make sure to
call the �add_department� procedure. Enter
an invalid department number to see the
exception error message. To display the
status of your transaction value, use the
TEXT_IO instead of the DBMS_OUTPUT, when you
run the procedure locally.
A:
PROCEDURE test_add_dept
-- This procedure will test
add_dept procedure
v_status VARCHAR2(40);
BEGIN
-- Call add_dept with an
invalid number.
add_dept(100, �FINANCE�,
�OHIO�, v_status);
-- Print �OK� value if there
is no error.
TEXT_IO.PUT_LINE(v_status);
EXCEPTION
WHEN others THEN
p_status := �CHECK THIS WE
HAVE UNKNOWN PROBLEM.�;
END test_add_dept;
/
Q: What is the client side
environment?
A:
It is when we store the
PL/SQL stored procedures in a PC or a Server
that Oracle server doesn�t reside in.
Q: What is the server side
environment?
A:
It is when we store the PL/SQL stored
procedures in the Oracle database.
Q: How do you save the above
PL/SQL procedure in your local library?
A:
To save the program in the
local library, go to the �Object Navigator�
window, highlight PL/SQL libraries and click
on the create icon. Click �OK.� Choose the
�File� option and select �Save as.� Save any
name library in a folder. Then click �OK� as
�File System.� A library should be created.
Now, drag the procedure into its �Program
Units.� Highlight the library name and save
it again.
Q: Write a procedure to
remove a department record. Make sure to
define one input parameter for the
department number; and an output parameter
as a status parameter. You will use this
parameter to test the status of the deleted
transaction.
In the PL/SQL body, delete
the department record where its department
number matches with the input department
number parameter. Save the deleted
transaction and assign "OK" to the status
output parameter for a successful deleted
transaction.
A:
PROCEDURE remove_dept
(p_deptno IN
dept.deptno%TYPE,
p_status OUT VARCHAR2)
IS
-- Delete a record
DELETE FROM dept
WHERE deptno = p_deptno;
-- Save the transaction.
COMMIT;
-- Check the status.
p_status := �OK�;
EXCEPTION
WHEN no_data_found THEN
p_status := �NO DATA FOUND.�;
WHEN others THEN
p_status := �Other
Problems.�;
END remove_dept;
/
Q: Write a PL/SQL procedure
to test the above created PL/SQL procedure.
A:
PROCEDURE test_remove_dept
-- This procedure will test
remove_dept procedure
v_status VARCHAR2(40);
BEGIN
-- Call remove_dept with a
valid number.
remove_dept(40, v_status);
-- Print �OK� value if there
is no error.
TEXT_IO.PUT_LINE(v_status);
EXCEPTION
WHEN others THEN
p_status := �CHECK THIS WE
HAVE UNKNOWN PROBLEM.�;
END test_remove_dept;
/
Q: What does the TEXT_IO
package?
A:
It displays the results on
the screen.
Q: Name only one procedure
that is in the TEXT_IO package.
A:
PUT_LINE
Q: What are the differences
between the TEXT_IO and DBMS_OUTPUT
packages?
A:
You use the TEXT_IO package
in a client environment but use the
DBMS_OUTPUT package in a server environment.
Q: What is the PL/SQL
function?
A:
It is a stored procedure that
can have none or many input parameters, but
it returns one and only one value.
Q: What are the differences
between the PL/SQL function and procedure?
A:
A function returns one and
only one value but procedure can have many
outputs.
Q: When do you create the
PL/SQL function?
A:
CREATE OR REPLACE FUCNTION
function_name IS
Q: write a PL/SQL Function to
concatenate the customer's last name and
first name to be separated by a comma. For
example: Kazerooni, John. Name the function
"Full_Name,� and declare a datatype for the
Function return value. Declare a first name
and last name input parameters. Their
datatypes should match with the datatype of
the firstname and lastname in the customers
table.
In the PL/SQL body, return
the customers� concatenated name. Write the
exception. In the exception section, do
nothing in the case of an error handling
exception.
A:
FUNCTION full_name
(p_fname IN
customers.first_name%TYPE,
p_lname IN
customers.last_name%TYPE)
RETURN VARCHAR2
IS
-- No variables
BEGIN
-- Full name concatenation�
RETURN p_lname || �, � ||
p_fname;
EXCEPTION
WHEN others THEN
-- Do nothing�
NULL;
END full_name;
/
Q: How do you execute the
above created PL/SQL function in the SQLPLUS
tool?
A:
PL/SQL> SELECT
full_name(�John�,�Kazerooni�)
FROM dual;
Q: What is the PL/SQL
interpreter?
A:
The PL/SQL interpreter is a
module that allows the developers to run and
debug their stored procedures. It reads
PL/SQL statements interactively.
Q: How do you execute a
PL/SQL procedure in the PL/SQL interpreter?
A:
Just type the procedure name
ended with a semicolon.
Q: Write a PL/SQL Function to
return the department name (dname). You use
one input parameter to pass the department
number (DEPTNO) and return its department
name.
A:
FUNCTION dept_name
(p_deptno IN
dept.deptno%TYPE)
RETURN VARCHAR2
IS
-- Define dname variable
v_dname dept.dname%TYPE;
BEGIN
-- Get department name
SELECT dname INTO v_dname
FROM dept
WHERE deptno = p_deptno;
-- Return department name.
RETURN v_dname
EXCEPTION
-- Error messages�
WHEN no_data_found THEN
RETRUN �NO DATA FOUND��;
WHEN others THEN
RETURN �Other PROBLEM��;
END dept_name;
/
Q: In the �PL/SQL
interpreter� section, use the �select�
statement and use the department number 10
to test the function.
A:
PL/SQL> SELECT dept_name(10)
as �Department Name�
FROM dual;
Q: To test the exception,
call the function again using the department
number that does not exist in the department
table.
A:
PL/SQL> SELECT dept_name(55)
as �Department Name�
FROM dual;
Q: Query the department name
function against the employee table sorted
by the employee name.
A:
PL/SQL> SELECT ename,
dept_name(deptno) as �Department Name�
FROM emp
ORDER BY 1;
Q: How do you debug a PL/SQL
procedure?
A:
We should use the Procedure
Builder debugger module. Choose the
"Program" option and open the PL/SQL
interpreter. Then run the procedure that
needs to be debugged.
Q: How do you move a PL/SQL
procedure to the PL/SQL interpreter�s source
area?
A:
Click on the icon next to the
procedure and that will move the procedure's
source program to the PL/SQL interpreter�s
source area.
Q: What is the BREAKPOINT
indicator in the PL/SQL interpreter?
A:
It is a time the debugged
program will stop and we can check the
values of the program elements on that
specific interruption time.
Q: How do you create a
BREAKPOINT in the PL/SQL interpreter?
A:
Double click on line number
that contains an execution statement in
order to make a �BREAKPOINT.� Then a big red
dot will appears.
Q: How do you activate the
Step Into, Step Out, and Reset icon in the
PL/SQL interpreter?
A:
After defining the
"breakpoint", you can run the debugged
procedure which will activate the Step Into,
Step Out, and Reset icons.
Q: What does the Step Into
icon in the PL/SQL interpreter?
A:
The "Step Into" icon takes us
to the next line.
Q: What does the Step Out
icon in the PL/SQL interpreter?
A:
The "Step Out" icon takes us to the next
cycle of a breakpoint.
Q: What does the Reset icon
in the PL/SQL interpreter?
A:
The "Reset" icon terminates
the debug mode.
Q: What does the STACK
section contain?
A:
It contains the content of
all variables.
Q: How can you see the
columns and variables values in the PL/SQL
program using the PL/SQL interpreter?
A:
On the stack section, expand
"procedure body."
Q: Can you have multiple
versions of a PL/SQL procedure in the PL/SQL
library?
A:
Yes.
Q: How can you copy a PL/SQL
procedure to your database server?
A:
Click and drag the procedure
into �Stored Program Units.�
Q: What would be happen if
you move or copy a locally PL/SQL procedure
with its local packages into the database
server?
A:
The procedure will not be
compiled in the database server.
Q: What is an Object
Privilege?
A:
The object privileges will
allow users to manipulate the object by
adding, changing, removing, or viewing data
plus the ALTER, REFERENCES, and EXECUTE
privileges in the database object.
Q: What are System
Privileges?
A:
System privileges control the
altering, dropping, and creating of all
database objects, such as rollback segments,
synonyms, tables, and triggers.
Q: How do you create a user
in the Oracle database?
A:
SQL> CREATE USER newuser
IDENTIFIED BY newpass
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
Q: How do you assign a
default and temporary tablespace to a user
in the Oracle database?
A:
SQL> ALTER USER newuser
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
Q: What are the System
Privileges in the RESOURCE and CONNECT
roles?
A:
The CONNECT role
contains the following system privileges:
ALTER SESSION, CREATE
CLUSTER, CREATE DATABASE LINK, CREATE
SEQUENCE, CREATE SESSION, CREATE SYNONYM,
CREATE TABLE, and CREATE VIEW.
The RESOURCE role
contains the following system privileges:
CREATE CLUSTER, CREATE
INDEXTYPE, CREATE OPERATOR, CRREATE
PROCEDURE, CREATE SEQUENCE, CREATE TABLE,
CREATE TRIGGER, and CREATE TYPE.
Q: How do you grant an object
privilege to a user?
A:
SQL> GRANT SELECT ON customer
TO newuser;
Q: How do you grant a system
privilege to a user?
A:
SQL> GRANT CREATE ANY TABLE
TO newuser;
Q: What is the Public Synonym
in the Oracle database?
A:
It is a synonym that all
Oracle users can use it.
Q: How do you create a PUBLIC
SYNONYM?
A:
SQL> CREATE PUBLIC SYNONYM
customer FOR iself.customer;
Q: Why do you need a PUBLIC
SYNONYM?
A:
Easy of use and unique naming
convention.
Q: What is the EXECUTE
privilege? Is it a system privilege or an
object privilege?
A:
The EXECUTE privilege will be
given to a user in order to run other Oracle
user�s stored procedures. It is an object
privilege.
Q: Can you grant the EXECUTE
privilege to a table?
A:
No.
Q: What is the Private
Synonym in the Oracle database?
A:
It is used only privately for
the creator of the object.
Q: What are the differences
between a private synonym and public
synonym?
A:
The private synonym can not
be accessed by public.
Q: How do you revoke a system
privilege from an Oracle user?
A:
SQL> REVOKE CREATE ANY TABLE
FROM newuser;
Q: How do you revoke an
object privilege from an Oracle user?
A:
SQL> REVOKE SELECT ON emp
FROM newuser;
Q: Mr. A granted to Mr. B an
object privilege with a �WITH GRANT OPTION�
and then Mr. B granted the same privilege to
Mr. C. You decide to revoke the Mr. B�s
object privilege. What would be happen to
Mr. C�s granted object privilege?
A:
It will be revoked too.
Q: Mr. A granted to Mr. B a
system privilege with a �WITH ADMIN OPTION�
and then Mr. B granted the same privilege to
Mr. C. You decide to revoke the Mr. B�s
system privilege. What would be happen to
Mr. C�s granted system privilege?
A:
Nothing.
Q: How do you know that a
privilege is the system privilege or object
privilege?
A:
If there are not SELECT,
INSERT, UPDATE, DELETE, REFERENCES, EXECUTE,
and ALTER then they are system priviledges.
Q: On the GRANT ALL
statement, what ALL means if your grant is
on a PL/SQL procedure?
A:
It means execute only.
Q: What is an object
dependency in the Oracle database?
A:
An object may be created
based on the existence of another object or
objects. The purity of the created object
depends on the status of the other objects
that have already been created. If any of
those objects changed or deleted, the new
object can not perform its task completely.
Therefore, Oracle will change its status to
an INVALID mode.
Q: What is a timestamp?
A:
When you create or change
something in an object, it�s created or
modified date will be recorded. It is called
a timestamp. Now any objects that were using
this object are going to have an invalid
status since the timestamp shows a date that
is after creation of those objects.
Q: How do you query all the
objects that was create by you (your
schema)?
A:
SQL> SELECT object_name,
object_type, status
FROM user_objects;
Q: How do you change a
datatype of a column in a table?
A:
SQL> ALTER TABLE dept
MODIFY (loc VARCHAR2(14));
Q: How do you compile a
PL/SQL function?
A:
SQL> ALTER FUNCATION
dept_name COMPILE;
Q: What is the PL/SQL
package?
A:
A PL/SQL package is
collection of stored procedures such as
procedures and functions.
Q: What are the components of
a PL/SQL package?
A:
A package should have a
PL/SQL package specification and a PL/SQL
package body.
Q: What is a package body in
the PL/SQL language?
A:
A �PL/SQL package body�
contains a complete PL/SQL stored procedures
or functions.
Q: What is a package
specification in the PL/SQL language?
A:
A �PL/SQL package
specification� contains all your PL/SQL
functions header, procedures header, type,
variables, etc.
Q: Where do you save the
package body and its package specification?
A:
You can store them either in
the client or server environments.
Q: Can you store a PL/SQL
package in a client environment?
A:
Yes.
Q: How do you create a
package specification and body?
A:
Package specification:
CREATE OR REPLACE
PACKAGE pkg_dept IS
For Package BODY:
CREATE OR REPLACE
PACKAGE BODY pkg_dept IS
Q: What are the dependencies
between a package body and its package
specification?
A:
The package body contains the
source programs and package specification
contains the header programs.
Q: Write a PL/SQL package to
have all your created PL/SQL functions and
procedures?
A:
PACKAGE pkg_dept
IS
-- No variables
-- This is the add_dept
specification�
PROCEDURE add_dept
(p_dept_rec IN dept%ROWTYPE,
p_status OUT VARCHAR2);
-- This is the remove_dept
specification�
PROCEDURE remove_dept
(p_deptno IN dept.depno%TYPE,
p_status OUT VARCHAR2);
-- Add more and more�
END pkg_dept;
/
PACKAGE BODY pkg_dept
IS
-- Add department procedure�
PROCEDURE add_dept
(p_deptno IN
dept.deptno%TYPE,
p_dname IN dept.dname%TYPE,
p_loc IN dept.loc%TYPE,
p_status OUT VARCHAR2)
IS
-- No variable
BEGIN
/* This program add dept.
record. */
INSERT INTO dept
VALUES (p_deptno, p_dname,
p_loc);
--- Save record.
COMMIT;
-- Added successfully if the
get to this line.
p_status := �OK�;
EXCEPTION
-- Check for an Unique or
Primary Key
WHEN dup_val_on_index THEN
p_status := �DUPLICATE
RECORD�;
-- Check for invalid input
data
WHEN invalid_number THEN
p_status := �INVALID INPUT
DATA�;
-- Check for any other
problems
WHEN others THEN
p_status := �CHECK THIS WE
HAVE UNKNOWN PROBLEM.�;
END add_dept;
-- Remove department
procedure�
PROCEDURE remove_dept
(p_deptno IN
dept.deptno%TYPE,
p_status OUT VARCHAR2)
IS
-- Delete a record
DELETE FROM dept
WHERE deptno = p_deptno;
-- Save the transaction.
COMMIT;
-- Check the status.
p_status := �OK�;
EXCEPTION
WHEN no_data_found THEN
p_status := �NO DATA FOUND.�;
WHEN others THEN
p_status := �Other
Problems.�;
END remove_dept;
-- And more internal
procedures.
END pkg_dept;
/
Q: What is a public PL/SQL
procedure or function in a PL/SQL package?
A:
All the procedures that were
declared in the package specification.
Q: What is a private PL/SQL
procedure or function in a PL/SQL package?
A:
Those procedures that are in
the BODY but were not declared in the
package specification.
Q: What are the differences
between a public or private PL/SQL
procedure?
A:
The private PL/SQL procedure
can not be accessed by any users or objects.
Q: How do you run a PL/SQL
procedure or function in a PL/SQL package?
A:
PL/SQL> DECLARE
v_status VARCHAR2(40);
BEGIN
pkg_dept.remove_dept(40,
v_status);
TEXT_IO.PUT_LINE(v_status);
END;
/
Q: What is a database
trigger?
A:
A database trigger is a set
of PL/SQL statements that execute each time
an event such as an update, insert, or
delete statement occurs on the database.
They are similar to stored PL/SQL
statements. They are stored in the database
and attached to a table.
Q: How do you create a
trigger?
A:
Select �Triggers� and click
on the "create" icon.
Q: If you drop a table that
contains a trigger, does its trigger drop?
A:
Yes.
Q: Create a trigger to audit
department table (dept) to keep track of all
the insert, update, and delete transactions
and insert the audited transaction to a
table.
A:
BEGIN
-- audit if the user inserted
a record�
IF INSERTING THEN
INSERT INTO audit_dept
VALUES (user || � inserted
deptno: � || :new.deptno);
-- audit if the user updated
a record�
ELSIF UPDATING THEN
INSERT INTO audit_dept
VALUES (user || � updated
deptno: � || :old.deptno);
-- audit if the user deleted
a record�
ELSIF DELETING THEN
INSERT INTO audit_dept
VALUES (user || � deleted
deptno: � || :old.deptno);
-- end if
END ID;
END;
Q: How do you compile a
trigger?
A:
In the trigger window, click
save to compile. Then close the window.
Or SQL> ALTER TRIGGER
trigger_name COMPILE;
Q: How do you disable or
enable a trigger?
A:
One way is:
PL/SQL> ALTER TRIGGER
iself.audit_dept_table DISABLE:
PL/SQL> ALTER TRIGGER
iself.audit_dept_table ENABLE:
Q: How do you test your
created trigger?
A:
Execute a SQL statement that
should fire the created trigger.
Q: How do you modify a
trigger?
A:
In the Object Navigator, on
the database item, double click on the
trigger icon to open the trigger, and then
modify the trigger.
Q: How do you drop a trigger?
A:
PL/SQL> DROP TRIGGER
�audit_dept_table�;
Q: When you drop a trigger,
does its table drop?
A:
NO.
Q: How do you increase the
size of SERVEROUTPUT buffer?
A:
SQL> SET SERVEROUTPUT ON SIZE
400000
Q: Can you perform a DDL
statement in the PL/SQL block?
A:
Not directly. You should use
the Oracle packages to perform such task.
Q: How can you compile an object in a PL/SQL
block?
A:
SQL> BEGIN
DBMS_DDL.ALTER_COMPILE
('PROCEDURE','ISELF','TEST02_4DDL_PKG');
END;
/
Q: What does the DBMS_DDL
package?
A:
It will perform the DDL
statements in the PL/SQL stored procedures.
Q: What does the ANALZE_OBJECT procedure in
the DBMS_DDL package and how can you verify
that the object was ANALYZED?
A:
It analyze a table the same
as the Oracle ANALYZE statement. We can use
the following SQL statement to verify that
the object was ANALYZED or not.
SQL> SELECT
TO_CHAR
(LAST_ANALYZED,'mm-dd-yy hh24:mi:ss')
last_analyzed_time
FROM USER_TABLES
WHERE TABLE_NAME =
'TEST01_4DDL_PKG';
Q: What does the
ALTER_COMPILE procedure in the DBMS_DDL
package and how can you verify that the
object was compiled?
A:
It will compile a procedure.
We can use the following SQL statement to
verify that the object was compiled.
SQL> SELECT object_name,
to_char(last_ddl_time,'mm-dd-yy hh24:mi:ss')
ddl_time
FROM user_objects
WHERE object_name =
'TEST02_4DDL_PKG';
Q: What is a Native Dynamic
SQL statement?
A:
Native Dynamic SQL allows an
application to run SQL statements whose
contents are not known until runtime. The
statement is built up as a string by the
application and is then passed to the
server. Generally dynamic SQL is slower than
static SQL so it should not be used unless
absolutely necessary. Make sure to check the
syntax, since syntax checking and object
validation cannot be done until runtime. The
only advantage of dynamic SQL is that it
allows you to perform DDL commands and also
allows you to access objects that will not
exist until runtime.
Q: Write a stored procedure
to pass the table name and get back the
number of records that table contains. The
SELECT statement must be created
dynamically, since you don�t know what table
you are getting statistics from. You should
write your function so that your client can
display the tables� name, plus the number of
records contained each table.
A:
SQL> CREATE OR REPLACE
FUNCTION get_total_recs
(loc VARCHAR2)
RETURN NUMBER IS
Query_str VARCHAR2(1000);
Num_of_recs NUMBER;
BEGIN
Query_str := 'SELECT COUNT(*) FROM ' ||
loc;
EXECUTE IMMEDIATE query_str INTO
num_of_recs;
RETURN num_of_recs;
END;
SQL> /
Q: How do you check that you
have the JAVA tool installed in your server?
A:
SQL> SELECT COUNT(*) FROM
dba_objects
WHERE object_type LIKE 'JAVA%';
Q: What should it be a least
size for the JAVA pool memory usage?
A:
You must have at least 30
megabytes of memory.
Q: How do you create a JAVA
class?
A:
SQL> CREATE OR REPLACE JAVA
SOURCE NAMED "iself" AS
public class iself {
static public String message (String tail) {
return "iSelfSchooling-" + tail;
}
}
SQL> /
Q: How do you publish a JAVA
class?
A:
SQL> CREATE OR REPLACE
FUNCTION error_msg
(str VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
LANGUAGE JAVA NAME
'iself.message (java.lang.String)
return java.lang.String';
END error_msg;
SQL> /
Q: How do you test a JAVA
function?
A:
SQL> SELECT error_msg
('01320: Running JAVA was successful.')
as "Message Function"
FROM dual
SQL> /
Q: How do you drop a JAVA
source and Function?
A:
SQL> DROP JAVA SOURCE
"iself";
Q: What does the EMPTY_BLOB()
function?
A:
Empty the photo column in the
EMP table. The EMPTY_BLOB function returns
an empty locator of type BLOB (binary large
object). Use EMPTY_BLOB to initialize a BLOB
to "empty." Before you can work with a BLOB,
either to reference it in SQL DML statements
such as INSERTs or to assign it a value in
PL/SQL, it must contain a locator. It cannot
be NULL. The locator might point to an empty
BLOB value, but it will be a valid
BLOB locator.
Q: How do you create a
directory in the Oracle database?
A:
SQL> CREATE OR REPLACE
DIRECTORY photo_folder AS
'c:';
Q: Does everyone can create a
directory in the Oracle database?
A:
NO.
Q: Write a stored procedure
to read the employee number and its photo
file name and then store the employee�s
picture into the EMP table.
A:
SQL> CREATE OR REPLACE
PROCEDURE insert_photo
(p_empno NUMBER, p_photo VARCHAR2)
AS
f_photo BFILE;
b_photo BLOB;
BEGIN
-- Update the employee photo
UPDATE emp
SET photo = empty_blob()
WHERE empno = p_empno
RETURN photo into b_photo;
-- find where the photo's pointer is
located.
f_photo := bfilename('PHOTO_FOLDER',
p_photo);
-- open the photo as read-only option.
dbms_lob.fileopen(f_photo,
dbms_lob.file_readonly);
-- load the photo into column photo.
dbms_lob.loadfromfile(b_photo,f_photo,
dbms_lob.getlength(f_photo));
-- close the photo's pointer.
dbms_lob.fileclose(f_photo);
-- Save the loaded photo record.
COMMIT;
EXCEPTION
-- Check for your error messages
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check
you procedure.');
END;
SQL> /
Q: How do you test that there
is a picture in a column?
A:
SQL> SELECT empno, ename,
dbms_lob.getlength(photo) "Photo Size"
FROM emp
SQL> /
Q: What does the DBMS_LOB
package?
A:
The DBMS_LOB package contains
procedures and functions that manipulate
Oracle large objects.
Q: What does the GETLENGTH()
function in the DBMS_LOB package?
A:
The GETLENGHT() procedure is
one of the stored procedures in the DBMS_LOB
package. It returns the size of a large
object in the Oracle database.
Q: How do you drop a
directory from your Oracle database?
A:
SQL> DROP DIRECTORY
photo_folder;
Q: How and when do you grant
the CREATE ANY DIRECTORY privilege to a
user?
A:
How:
SQL> GRANT CREATE ANY
DIRECTORY TO iself
When a user needs to write or
read from that folder.
Q: How do you revoke the
CREATE ANY DIRECTORY privilege from a user?
A:
SQL> REVOKE CREATE ANY
DIRECTORY FROM iself
Q: What is PL/SQL?
A:
PL/SQL is a language that was
provided by Oracle. Stored procedure is a
collection of PL/SQL. Stored procedure is
like a program module in Oracle. It is
available for developers to code stored
procedures that easily integrate with
database objects via the SQL statements such
as INSERT, UPDATE, DELETE, and SELECT. This
language offers variable DECLARATION, LOOP,
IF-THEN-ELSE-END IF, EXCEPTION an advanced
error handling, cursor, and more.
Q: Where can you store a
PL/SQL procedure?
A:
A PL/SQL stored procedure can
be stored in an Oracle Database server or
user client machine in a PL/SQL library.
Q: What is the PL/SQL body
section?
A:
It is a section in a stored
procedure to execute PL/SQL statements. It
is also called the execution section.
Q: What is the PL/SQL
declaration section?
A:
It is a section that all
program variables, cursors, and types will
be declared.
Q: An implicit cursor must
have _________ on its SELECT SQL statement?
A:
INTO
Q: Write an anonymous stored
procedure to use the implicit cursor to
query the department table information where
deptno is 30. Check, if no record was found
then print �Record was not found.� Else
print the department name only.
A:
>>Declare
v_drec dept%rowtype;
begin
select deptno, dname,
loc into
v_drec.deptno,v_drec.dname,
v_drec.loc
from dept
where deptno = 30;
if sql%notfound then
dbms_output.put_line('Record
was not found.');
else
dbsm_output.put_line(v_drec.dname);
end if;
end;
/
Q: What are the differences
between the explicit and implicit cursors?
A:
4- Explicit cursor
will be defined in the declaration
section but implicit cursor will be
defined in the execution or body
section.
5- The implicit
cursor must have INTO clause in its
SQL statement.
6- The explicit
cursor can return more than one
record but the implicit cursor
should only return one and only one
record.
Q: Declare a cursor to list
the department name (dname), total number of
employees (ttemp), total salary (ttsal), and
average salary (avsal) for each department
from the department table and employee table
order by the department name. Write all
department name with their total number of
employees for each department. List only the
department name that their total number of
employees is more than 100.
For example: ACCOUNTING has
145 employees.
(Note: Don�t use the ttemp,
ttsal, and avsal item at this time)
A:
DECLARE
-- define department
statistics
d, emp e
WHERE d.deptno = e.deptno
GROUP BY
dname
ORDER CURSOR c_ds IS
SELECT dname, count (*)
ttemp,
Sum (sal) ttsal,
avg(sal) avsal
FROM dept BY
1;
BEGIN
-- FOR LOOP statement to read
cursor record.
FOR v_ds IN c_ds LOOP
IF v_ds.ttemp > 100
THEN
DBMS_OUTPUT.PUT_LINE
(v_ds.dname ||
� has
� || v_ds.ttemp || �
employees.�);
END IF;
END LOOP;
END;
/
Q: Write a PL/SQL Function to
return the department name (dname). You use
one input parameter to pass the department
number (DEPTNO) and return its department
name.
A:
CREATE
OR REPLACE
FUNCTION dept_name
(p_deptno IN
dept.deptno%TYPE)
RETURN VARCHAR2
IS
-- Define dname variable
v_dname dept.dname%TYPE;
BEGIN
-- Get department name
SELECT dname INTO v_dname
FROM dept
WHERE deptno = p_deptno;
-- Return department name.
RETURN v_dname
EXCEPTION
-- Error messages�
WHEN no_data_found THEN
RETRUN �NO DATA FOUND��;
WHEN others THEN
RETURN �Other PROBLEM��;
END dept_name;
/
Q: How do you revoke a system
privilege from an Oracle user?
A:
REVOLE CREATE ANY TABLE FROM
scott;
Q: Mr. A granted to Mr. B an
object privilege with a �WITH GRANT OPTION�
and then Mr. B granted the same privilege to
Mr. C. You decide to revoke the Mr. B�s
object privilege. What would be happen to
Mr. C�s granted object privilege?
A:
Mr. C will lose his granted
object privilege too.
Q: How do you change a
datatype of a column in a table?
A:
ALTER table_name MODIFY
(column_name new_datatype);
Q: What is a PL/SQL package?
A:
A package is a collection of
procedures and functions together as an
object.
Q: What is a package
specification?
A:
A package should have a
PL/SQL package specification and a PL/SQL
package body. A �PL/SQL package
specification� contains all your PL/SQL
functions, procedures, type, variables, etc.
All the declared PL/SQL functions,
procedures, variables, etc in a package
specification are called public procedures
and functions. They can be accessible to the
users who have privilege to execute them. In
the PL/SQL package specification, all the
functions and procedures must have a PL/SQL
procedure in its PL/SQL package body. It is
not necessary that all the PL/SQL procedures
in a PL/SQL package body have a
specification entry in its PL/SQL package
specification. Those PL/SQL procedures that
have not have any specification entry in the
PL/SQL package specification called private
PL/SQL procedures.
Q: What are the differences
between the statement and row triggers in
the Oracle database?
A:
There are two types of
database triggers: statement triggers and
row triggers. A statement trigger will fire
only once for a triggering statement. A row
trigger fires once for every row affected by
a trigger statement. Triggers can be set to
fire either before or after Oracle processes
the triggering insert, update, or delete
statement.
Q: What do the UPDATING,
DELETING, or INSERTING keywords?
A:
The keywords updating,
deleting, or inserting can be used when
multiple triggering events are defined. You
can perform different action based on the
UPDATE, DELETE, or INSERT statement that you
are executing.
Q: How do you enable,
disable, and drop a trigger in the Oracle
database?
A:
ALTER TRIGGER
iself.audit_dept_table DISABLE:
ALTER TRIGGER
iself.audit_dept_table ENABLE:
DROP TRIGGER
iself.audit_dept_table;
Q: What does the following
PL/SQL statements? What is the output of the
following SQL statement?
SQL> SELECT full_name (�Joe�,
�Smith�) as �Full Name� FROM DUAL;
CREATE OR REPLACE FUNCTION
full_name
(p_fname IN
customers.first_name%TYPE,
p_lname IN
customers.last_name%TYPE)
RETURN VARCHAR2
IS
-- No variables
BEGIN
-- Full name concatenation�
RETURN p_lname || �, � ||
p_fname;
EXCEPTION
WHEN others THEN
-- Do nothing�
NULL;
END full_name;
/
The output is:
Full Name
-----------------
Smith, Joe |
