|
PL/SQL Fundamental Exam Answers
NOTE:
The answers go with their sequences. If a question was not answer, that means
that it a repeating question and the answer was given by the previous questions
or it is not in the scope of this subject.
|
“Watch
your thoughts; they become words. Watch
your words; they become actions. Watch
your actions; they become habits. Watch
your habits; they become character. Watch
your character; it becomes your destiny.”
--Frank Outlaw
|
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:\iself\test_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:\iself\test_myblock.sql
Q:
How do you run a saved PL/SQL block?
A:
SQL>
run c:\iself\test_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.
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 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 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,
CREATE 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 at 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:\ephoto';
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
|