|
PL/SQL Final
Exam Evaluation
|
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 |
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 procedures 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 dont 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: Dont 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 dont 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 procedures error handling.
Q: What would be happen if
you dont define your exception in the PL/SQL procedure?
A:
If there is an execution error, then it will crash. It crashes since
the program didnt 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 customers 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 users schema?
A:
We have users 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 doesnt 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 interpreters source area?
A:
Click on the icon next to the procedure and that will move the
procedure's source program to the PL/SQL interpreters 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 users 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. Bs object
privilege. What would be happen to Mr. Cs 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. Bs system
privilege. What would be happen to Mr. Cs 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, its 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
dont 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
employees 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: Dont 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. Bs object
privilege. What would be happen to Mr. Cs 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
|