|
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 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.
| 