Click
here for important notes
|
More Resources by
Google: |
|
|
|
|
Case Study for
Hands-On PL/SQL Program Units
The Case Study for “Hands-On PL/SQL
Program Units” consists of more than 15 basic questions that we believe you
must know. In this Case Study, we
look at certain elementary questions, which will test your basic fundamental
understanding of PL/SQL Program Units. These
questions and their answers in the “Hands-On Training” enhance your skills
in using Oracle PL/SQL Program Units..
Hands-On
01 (PL/SQL)
- Manuscript
In
this Hands-On, you create a table called "cust_stat."
This table keeps the customers last name (customer_lname), the traded
date (trade_date), and the total stocks market value (portfolio_value) for that
traded date.
You
write a PL/SQL procedure to define an explicit cursor to query all the
customers, the trade date, and their current stocks market value for the traded
date. Then the procedure populates the customer portfolio statistics into the
“cust_stat” table.
You
will:
1- Create a table named “cust_stat.”
2- Write a stored procedure using notepad to populate customers
_statistics.
3- Run the stored procedure.
4- Test the stored procedure.
Hands-On
02 (PL/SQL)
- Manuscript
You
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
will:
1- Write a procedure to add a record using Procedure Builder.
2- Use input and output parameters.
3- Run the stored procedure.
4- Test the stored procedure.
Hands-On
03 (PL/SQL)
- Manuscript
In
this Hands-On, you write a PL/SQL stored procedure to remove a record from the
department table (dept). You use
one input parameter to pass the department number (deptno); and use one another
output parameter to check the status of the delete transaction.
You
will:
1- Write a PL/SQL procedure to remove a record using Procedure Builder.
2- Use input and output parameters.
3- Run the stored procedure.
4- Test the stored procedure.
5- Save the procedure.
Hands-On
04 (PL/SQL)
- Manuscript
you
write a PL/SQL Function to concatenate the customer's last name and first name
to be separated by a comma.
For
example (lastname, firstname).
You
will:
1- Write a PL/SQL function to concatenate the customer’s last name and
first name separated by comma.
2- Use input parameters.
3- Run the stored procedure.
4- Test the stored procedure.
5- Save the function program.
Hands-On
05 (PL/SQL)
-
Manuscript
You
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.
You
will:
1- Write a PL/SQL function to return a department name.
2- Use department number as an input parameter.
3- Run the stored procedure.
4- Test the stored procedure.
5- Save the function in the local library,
6- Save the function in the database server.
Hands-On
06 (PL/SQL)
- Manuscript
You
debug a procedure that is supposed to calculate the factorial of a number.
You
should pause and playback the movie as much as it is needed.
In this procedure, it is supposed that if the input parameter is 3, then
the program output should be 6.
(For
example: 3! = 1*2*3)
You
have a logical problem in the procedure and it does not work.
You have be assigned to investigate the problem.
The
Core Subject are:
1- Debugging a PL/SQL using Procedure Builder
2- Using Breakpoint
3- Using Stack
4- Compiling and running debug procedure
5- Test debug procedure
6- Introducing TEXT_IO package
7- PL/SQL Library
Hands-On
07 (PL/SQL)
- Manuscript
In
this Hands-On, you will open three sessions.
You login as iself/schooling in the first session, login as
system/manager in the second session, and login to the third session as a
newuser/newpass that is going to be created by system/manager (dba).
You
will:
1- Copy, alter, and drop a table.
2- Create a table.
3- Create, alter, and drop a view.
4- Create a private synonym.
5- Create a public synonym.
6- Create, alter and drop a user.
7- Grant object and system privileges.
8- Revoke object and system privileges.
Hands-On
08 (PL/SQL)
- Manuscript
In
this Hands-On, you will view some of the Oracle directory views and experience
the Oracle object dependencies (timestamp).
1- Directory Views
2- Security
3- Tracking Dependency
Hands-On
09 (PL/SQL)
- Manuscript
You
create a package by copy and pasting an existing PL/SQL stored procedures from
the database server into the local library or the database server.
A
package is a database construct that allows users to collect many program
units into one database object. They
consist of two components: a
package specification and a package body.
You
will:
1- Create a Package Specification.
2- Create a Package Body.
3- Copy and paste a stored program units’ header to a Package
Specification.
4- Copy and paste a stored program units to a Package Body.
5- Compile the Package Specification.
6- Compile the Package Body.
7- Run and test the package
8- Store packages locally or on database server.
Hands-On
10 (PL/SQL)
- Manuscript
You
create a table and name it "audit_dept" (audit department table).
The table contains only one column (audit_line) and it is big enough to
fit 80 characters.
You
create a trigger to audit department table (dept) to keep track of all insert,
update, and delete transactions.
For
example: If someone inserted a
department record, then a message should say “xxxxxx inserted deptno: nn”
You
will:
1- Creating Triggers.
2- Using Triggers.
3- Dropping Triggers.